|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
I have created a database project (Access 2003), created an installation
package with Access extension software and just distributed the first one on
a PC. Installation went fine and the database opens. However, anytime it runs
a routine that uses the "Str" function, an error message is displayed
"function isn't available" followed by the offending expression. For this
instance, the offending "Str" is in VBA code. I've also seen this same
problem when the "Str" function is used in query expression. I know it's the
"Str" because if I remove that function from the code, it runs fine. Of
course I can't do what I need to do without it, but the test proved "Str"
function was the offending function.
Why is this function not available in the Runtime version and works fine
when the code is run under the full version of Access on my development
machine. I tried the runtime switch on my development machine but the
shortcut properties always said the "/Runtime" after the "...msaccess.exe"
path was not valid. Anyone have any suggestions to a solution?
|
|
Randy[ at ]Fluke wrote:
[Quoted Text] > I have created a database project (Access 2003), created an
> installation package with Access extension software and just
> distributed the first one on a PC. Installation went fine and the
> database opens. However, anytime it runs a routine that uses the
> "Str" function, an error message is displayed "function isn't
> available" followed by the offending expression. For this instance,
> the offending "Str" is in VBA code. I've also seen this same problem
> when the "Str" function is used in query expression. I know it's the
> "Str" because if I remove that function from the code, it runs fine.
> Of course I can't do what I need to do without it, but the test
> proved "Str" function was the offending function.
You are seeing the very common "Missing Reference" issue. Your file
contains a reference to an external library file that doesn't exist or is
not properly registered on the target PC. This causes many built in
functions to fail and the missing library need not have anything to do with
the functions that fail. See article at link below...
http://support.microsoft.com/?id=160870
Never mind that the article only mentions Access 97. The problem applies to
all versions.
> Why is this function not available in the Runtime version and works
> fine when the code is run under the full version of Access on my
> development machine. I tried the runtime switch on my development
> machine but the shortcut properties always said the "/Runtime" after
> the "...msaccess.exe" path was not valid. Anyone have any suggestions
> to a solution?
To use /Runtime (or any other command line argument) you need a shortcut to
MSACCESS.EXE (the program), not a shortcut to your MDB. The target of the
shortcut would look like...\
"Full path to MSAccess.exe" /Runtime "full path to your MDB file"
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
|
|
Thank you Rick for taking the time to look at and respond to my problem.
First on the broken link issue: The article was great on information,
however, it doesn't tell me how I can fix the problem in my situation. The PC
that I am installing the project on doesn't have the full Access program and
therefore thte database can't be opened to edit forms or look at VBA code
where the references are set. You would think that the installation files
created by the Access Extensions program would check for all the required
references and ensure they are installed if they don't exist on the target
PC. That same database on the development PC works fine.
On the runtime issue: I was putting the /Runtime inside the quotes with the
MSACCESS.EXE path string.
I would appreciate any suggestions or references for fixing reference links
on PCs without the full Access software.
Thanks again.
Randy
"Rick Brandt" wrote:
[Quoted Text] > Randy[ at ]Fluke wrote: > > I have created a database project (Access 2003), created an > > installation package with Access extension software and just > > distributed the first one on a PC. Installation went fine and the > > database opens. However, anytime it runs a routine that uses the > > "Str" function, an error message is displayed "function isn't > > available" followed by the offending expression. For this instance, > > the offending "Str" is in VBA code. I've also seen this same problem > > when the "Str" function is used in query expression. I know it's the > > "Str" because if I remove that function from the code, it runs fine. > > Of course I can't do what I need to do without it, but the test > > proved "Str" function was the offending function. > > You are seeing the very common "Missing Reference" issue. Your file > contains a reference to an external library file that doesn't exist or is > not properly registered on the target PC. This causes many built in > functions to fail and the missing library need not have anything to do with > the functions that fail. See article at link below... > > http://support.microsoft.com/?id=160870> > Never mind that the article only mentions Access 97. The problem applies to > all versions. > > > Why is this function not available in the Runtime version and works > > fine when the code is run under the full version of Access on my > > development machine. I tried the runtime switch on my development > > machine but the shortcut properties always said the "/Runtime" after > > the "...msaccess.exe" path was not valid. Anyone have any suggestions > > to a solution? > > To use /Runtime (or any other command line argument) you need a shortcut to > MSACCESS.EXE (the program), not a shortcut to your MDB. The target of the > shortcut would look like...\ > > "Full path to MSAccess.exe" /Runtime "full path to your MDB file" > > -- > Rick Brandt, Microsoft Access MVP > Email (as appropriate) to... > RBrandt at Hunter dot com > > > > > > |
|
Randy[ at ]Fluke wrote:
[Quoted Text] > Thank you Rick for taking the time to look at and respond to my
> problem.
>
> First on the broken link issue: The article was great on information,
> however, it doesn't tell me how I can fix the problem in my
> situation. The PC that I am installing the project on doesn't have
> the full Access program and therefore thte database can't be opened
> to edit forms or look at VBA code where the references are set.
Couple things you can try. First, your file might very well have extra
references that are causing this problem that you don't even need. Open a
new blank file, press Ctl-G to open the VBA code editor window and then go
to Tools - References. The list of "checked" references will be the three
or four default ones that every Access file of the version you are using
will have. Now look at the references in your file. If you have ANY
additional ones then the extras are more than likely the problem and should
be eliminated.
Uncheck each of the extra ones one at a time and then try to compile the
project. If the project will still compile without errors with the
reference removed, then you don't need it. Once any un-needed references
are removed you can try your file again on the runtime PC.
If you intentionally have additional references to Word, Excel, Outlook,
etc., then you should change your code that needs them to late binding so
they can be removed. You have no license to distribute those libraries
anyway so no runtime project should ever depend on them. With late binding
the particular functions that need those referenced libraries will obviously
not work on PCs where they don't exist, but at least the basic functionality
of your app is not broken by having a missing reference
You can also add this code to your app and run it from some obscure button
or admin area of your file.
Sub EnumerateReferences()
Dim ref As Reference
Dim strOut As String
For Each ref In Access.References
strOut = strOut & vbCrLf & ref.Name & IIf(ref.IsBroken, "
Missing", " OK") & vbCrLf & ref.FullPath & vbCrLf
Next ref
MsgBox strOut
End Sub
That will display a message box showing all references and will indicate
which are broken. That can be used in the runtime to identify the problem.
> You
> would think that the installation files created by the Access
> Extensions program would check for all the required references and
> ensure they are installed if they don't exist on the target PC. That
> same database on the development PC works fine.
They couldn't do that because they have no idea whether you have any legal
right to distribute those libraries. You have MANY libraries on your system
as the result of installing software and some of them could be referenced
and used in an Access app on your PC. The fact that you purchased and
installed that software almost never means that you also are legally allowed
to distribute those reference libraries though.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
|
|
"Rick Brandt" <rickbrandt2[ at ]hotmail.com> wrote in message
news:lYEhg.40111$4L1.11642[ at ]newssvr11.news.prodigy.com...
[Quoted Text] > Randy[ at ]Fluke wrote:
> You can also add this code to your app and run it from some obscure button
> or admin area of your file.
>
> Sub EnumerateReferences()
> Dim ref As Reference
> Dim strOut As String
>
> For Each ref In Access.References
> strOut = strOut & vbCrLf & ref.Name & IIf(ref.IsBroken, " Missing",
> " OK") & vbCrLf & ref.FullPath & vbCrLf
> Next ref
>
> MsgBox strOut
> End Sub
Unfortunately, Access raises an error if you try to use either the Name or
FullPath properties for a broken reference.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
|
|
Douglas J. Steele wrote:
[Quoted Text] > "Rick Brandt" <rickbrandt2[ at ]hotmail.com> wrote in message
> news:lYEhg.40111$4L1.11642[ at ]newssvr11.news.prodigy.com...
> > Randy[ at ]Fluke wrote:
> > You can also add this code to your app and run it from some obscure
> > button or admin area of your file.
> >
> > Sub EnumerateReferences()
> > Dim ref As Reference
> > Dim strOut As String
> >
> > For Each ref In Access.References
> > strOut = strOut & vbCrLf & ref.Name & IIf(ref.IsBroken, "
> > Missing", " OK") & vbCrLf & ref.FullPath & vbCrLf
> > Next ref
> >
> > MsgBox strOut
> > End Sub
>
> Unfortunately, Access raises an error if you try to use either the
> Name or FullPath properties for a broken reference.
Really? Then where the heck did I get that code? :-)
I could have sworn it was from one of the various MVP sites. I don't know if I
have ever used it when it didn't report full success (I seldom use additional
references), but have just been assuming that it would report a bad reference if
one were there.
I think I added that to one of my apps that runs under the runtime on our Citrix
servers (just in case). Don't think I've ever had a need for it to actually
work yet though.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
|
|
"Rick Brandt" <rickbrandt2[ at ]hotmail.com> wrote in message
news:ifJhg.47155$Lm5.31149[ at ]newssvr12.news.prodigy.com...
[Quoted Text] > Douglas J. Steele wrote:
>> "Rick Brandt" <rickbrandt2[ at ]hotmail.com> wrote in message
>> news:lYEhg.40111$4L1.11642[ at ]newssvr11.news.prodigy.com...
>> > Randy[ at ]Fluke wrote:
>> > You can also add this code to your app and run it from some obscure
>> > button or admin area of your file.
>> >
>> > Sub EnumerateReferences()
>> > Dim ref As Reference
>> > Dim strOut As String
>> >
>> > For Each ref In Access.References
>> > strOut = strOut & vbCrLf & ref.Name & IIf(ref.IsBroken, "
>> > Missing", " OK") & vbCrLf & ref.FullPath & vbCrLf
>> > Next ref
>> >
>> > MsgBox strOut
>> > End Sub
>>
>> Unfortunately, Access raises an error if you try to use either the
>> Name or FullPath properties for a broken reference.
>
> Really? Then where the heck did I get that code? :-)
>
> I could have sworn it was from one of the various MVP sites. I don't know
> if I have ever used it when it didn't report full success (I seldom use
> additional references), but have just been assuming that it would report a
> bad reference if one were there.
>
> I think I added that to one of my apps that runs under the runtime on our
> Citrix servers (just in case). Don't think I've ever had a need for it
> to actually work yet though.
Well, it _will_ report a bad reference, but not in a very useful way! <g>
You know what your references are supposed to be. Just knowing that the 2nd
or the 4th reference is broken should be enough.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
|
|
Douglas J. Steele wrote:
[Quoted Text] >
> Well, it _will_ report a bad reference, but not in a very useful way!
> <g>
> You know what your references are supposed to be. Just knowing that
> the 2nd or the 4th reference is broken should be enough.
I wasn't sure how to easily simulate a broken reference so I made an
external reference to an MDE and then renamed the file I was referencing.
It created the broken reference symptoms perfectly. Several built in
functions failed and the reference was marked "MISSING" in the references
dialog.
I ran that code and it worked as advertised. My good references were
reported as good and my bad reference was reported as Missing and there were
no errors.
Perhaps an MDE reference is not a good test of the concept.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
|
|
OK Rick. I did as you suggested and was able to delete a reference I didn't
need. However, I put your code in my application and reloaded on the target
PC. Your program indicates that all the reference likes are OK but I still
can't run the procedure with the Str function in it.
Not sure what to do next.
Randy
"Rick Brandt" wrote:
[Quoted Text] > Douglas J. Steele wrote:
> >
> > Well, it _will_ report a bad reference, but not in a very useful way!
> > <g>
> > You know what your references are supposed to be. Just knowing that
> > the 2nd or the 4th reference is broken should be enough.
>
> I wasn't sure how to easily simulate a broken reference so I made an
> external reference to an MDE and then renamed the file I was referencing.
>
> It created the broken reference symptoms perfectly. Several built in
> functions failed and the reference was marked "MISSING" in the references
> dialog.
>
> I ran that code and it worked as advertised. My good references were
> reported as good and my bad reference was reported as Missing and there were
> no errors.
>
> Perhaps an MDE reference is not a good test of the concept.
>
> --
> Rick Brandt, Microsoft Access MVP
> Email (as appropriate) to...
> RBrandt at Hunter dot com
>
>
>
|
|
Randy[ at ]Fluke wrote:
[Quoted Text] > OK Rick. I did as you suggested and was able to delete a reference I
> didn't need. However, I put your code in my application and reloaded
> on the target PC. Your program indicates that all the reference likes
> are OK but I still can't run the procedure with the Str function in
> it.
>
> Not sure what to do next.
What references are you using?
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
|
|
"Rick Brandt" <rickbrandt2[ at ]hotmail.com> wrote in message
news:AOKhg.47184$Lm5.33706[ at ]newssvr12.news.prodigy.com...
[Quoted Text] > Douglas J. Steele wrote:
>>
>> Well, it _will_ report a bad reference, but not in a very useful way!
>> <g>
>> You know what your references are supposed to be. Just knowing that
>> the 2nd or the 4th reference is broken should be enough.
>
> I wasn't sure how to easily simulate a broken reference so I made an
> external reference to an MDE and then renamed the file I was referencing.
>
> It created the broken reference symptoms perfectly. Several built in
> functions failed and the reference was marked "MISSING" in the references
> dialog.
>
> I ran that code and it worked as advertised. My good references were
> reported as good and my bad reference was reported as Missing and there
> were no errors.
>
> Perhaps an MDE reference is not a good test of the concept.
I suspect it's not a good test, since there's no GUID property.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
|
|
I think I need to state the problem differently. I use VBA code to create an
SQL string and execute that string with an Execute command. A simple query.
That SQL string has the Str function as part of an expression, where it
converts a number field to a string field. The error appears when that SQL is
executed. So I don't think the problem is really a VBA problem but a query
problem.
To answer your question, the references are:
Visual Basis for Applications
Microsoft Access 11.0 Object Library
Microsoft Office XP Web Components
Microsoft DAO 3.5 Object Library
Randy
"Rick Brandt" wrote:
[Quoted Text] > Randy[ at ]Fluke wrote:
> > OK Rick. I did as you suggested and was able to delete a reference I
> > didn't need. However, I put your code in my application and reloaded
> > on the target PC. Your program indicates that all the reference likes
> > are OK but I still can't run the procedure with the Str function in
> > it.
> >
> > Not sure what to do next.
>
> What references are you using?
>
> --
> Rick Brandt, Microsoft Access MVP
> Email (as appropriate) to...
> RBrandt at Hunter dot com
>
>
>
|
|
Randy[ at ]Fluke wrote:
[Quoted Text] > I think I need to state the problem differently. I use VBA code to
> create an SQL string and execute that string with an Execute command.
> A simple query. That SQL string has the Str function as part of an
> expression, where it converts a number field to a string field. The
> error appears when that SQL is executed. So I don't think the problem
> is really a VBA problem but a query problem.
As stated previously, ANY broke VBA references causes many of the built in
functions to fail.
> To answer your question, the references are:
>
> Visual Basis for Applications
> Microsoft Access 11.0 Object Library
> Microsoft Office XP Web Components
> Microsoft DAO 3.5 Object Library
Most likely it is the Web Components library. Switch your code using that to
late binding. That will allow you to remove the reference. You're sure the
Enumerate References code indicates that the WEB library is okay?
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
|
|
In addition to the good advice you have received from others, you might want
to double-check the DAO reference. It should be 3.6, not 3.5.
Although I don't think it is the cause of your current problem there is a
peculiarity of the Str function that can be a problem if you are not aware
of it. The Str function places a space in front of positive numbers. This
can cause tests such as 'If Str(SomeNumber) = "0" Then' to produce
unexpected results, because if SomeNumber = 0 then Str(SomeNumber) is not
equal to "0", it is equal to " 0". For this reason, CStr is often a better
choice than Str - it does the same thing, but without the leading space.
--
Brendan Reynolds
Access MVP
"Randy[ at ]Fluke" <RandyFluke[ at ]discussions.microsoft.com> wrote in message
news:86E844DB-0EE2-4A70-8AE7-D7DD875E0C2B[ at ]microsoft.com...
[Quoted Text] >I think I need to state the problem differently. I use VBA code to create
>an
> SQL string and execute that string with an Execute command. A simple
> query.
> That SQL string has the Str function as part of an expression, where it
> converts a number field to a string field. The error appears when that SQL
> is
> executed. So I don't think the problem is really a VBA problem but a query
> problem.
>
> To answer your question, the references are:
>
> Visual Basis for Applications
> Microsoft Access 11.0 Object Library
> Microsoft Office XP Web Components
> Microsoft DAO 3.5 Object Library
>
> Randy
>
> "Rick Brandt" wrote:
>
>> Randy[ at ]Fluke wrote:
>> > OK Rick. I did as you suggested and was able to delete a reference I
>> > didn't need. However, I put your code in my application and reloaded
>> > on the target PC. Your program indicates that all the reference likes
>> > are OK but I still can't run the procedure with the Str function in
>> > it.
>> >
>> > Not sure what to do next.
>>
>> What references are you using?
>>
>> --
>> Rick Brandt, Microsoft Access MVP
>> Email (as appropriate) to...
>> RBrandt at Hunter dot com
>>
>>
>>
|
|
I was aware of CStr, but didn't know it would remove the leading space. I had
always used Trim with Str to strip off the space. In my application, I had
removed Trim from the SQL to see if that was the problem child. The Str by
itself still caused an error.
However, implementing your suggestion to switch to CStr allowed my
application to work on the Runtime system. I still don't know why Str won't
work in a query on the Runtime only PC, but I can move on with my application
and don't see any reason to use Str again.
Thanks for jumping in with your suggestion. It was a great help as were all
the other suggestions.
Randy
"Brendan Reynolds" wrote:
[Quoted Text] > In addition to the good advice you have received from others, you might want
> to double-check the DAO reference. It should be 3.6, not 3.5.
>
> Although I don't think it is the cause of your current problem there is a
> peculiarity of the Str function that can be a problem if you are not aware
> of it. The Str function places a space in front of positive numbers. This
> can cause tests such as 'If Str(SomeNumber) = "0" Then' to produce
> unexpected results, because if SomeNumber = 0 then Str(SomeNumber) is not
> equal to "0", it is equal to " 0". For this reason, CStr is often a better
> choice than Str - it does the same thing, but without the leading space.
>
> --
> Brendan Reynolds
> Access MVP
>
> "Randy[ at ]Fluke" <RandyFluke[ at ]discussions.microsoft.com> wrote in message
> news:86E844DB-0EE2-4A70-8AE7-D7DD875E0C2B[ at ]microsoft.com...
> >I think I need to state the problem differently. I use VBA code to create
> >an
> > SQL string and execute that string with an Execute command. A simple
> > query.
> > That SQL string has the Str function as part of an expression, where it
> > converts a number field to a string field. The error appears when that SQL
> > is
> > executed. So I don't think the problem is really a VBA problem but a query
> > problem.
> >
> > To answer your question, the references are:
> >
> > Visual Basis for Applications
> > Microsoft Access 11.0 Object Library
> > Microsoft Office XP Web Components
> > Microsoft DAO 3.5 Object Library
> >
> > Randy
> >
> > "Rick Brandt" wrote:
> >
> >> Randy[ at ]Fluke wrote:
> >> > OK Rick. I did as you suggested and was able to delete a reference I
> >> > didn't need. However, I put your code in my application and reloaded
> >> > on the target PC. Your program indicates that all the reference likes
> >> > are OK but I still can't run the procedure with the Str function in
> >> > it.
> >> >
> >> > Not sure what to do next.
> >>
> >> What references are you using?
> >>
> >> --
> >> Rick Brandt, Microsoft Access MVP
> >> Email (as appropriate) to...
> >> RBrandt at Hunter dot com
> >>
> >>
> >>
>
>
>
|
|
Thanks again Rick.
I had already tried a dumbed down version of the program without the WEB
Components reference and had the same problem. I am interested in how you set
late reference binding however.
I am now been able to run my program on the PC with only the Runtime.
Brendan's suggestion of using CStr instead of Str made the whole process
work. It doesn't solve the orginal problem, but for me at this point I don't
need to solve that problem.
Again, thanks for the help. I learned a few things from you.
Randy
"Rick Brandt" wrote:
[Quoted Text] > Randy[ at ]Fluke wrote:
> > I think I need to state the problem differently. I use VBA code to
> > create an SQL string and execute that string with an Execute command.
> > A simple query. That SQL string has the Str function as part of an
> > expression, where it converts a number field to a string field. The
> > error appears when that SQL is executed. So I don't think the problem
> > is really a VBA problem but a query problem.
>
> As stated previously, ANY broke VBA references causes many of the built in
> functions to fail.
>
> > To answer your question, the references are:
> >
> > Visual Basis for Applications
> > Microsoft Access 11.0 Object Library
> > Microsoft Office XP Web Components
> > Microsoft DAO 3.5 Object Library
>
> Most likely it is the Web Components library. Switch your code using that to
> late binding. That will allow you to remove the reference. You're sure the
> Enumerate References code indicates that the WEB library is okay?
>
> --
> Rick Brandt, Microsoft Access MVP
> Email (as appropriate) to...
> RBrandt at Hunter dot com
>
>
>
|
|
This is further evidence that the problem is a missing or mis-matched
reference, Randy. We see this frequently in the newsgroups where one
function causes an error but a similar function does not, for example Date
and Now, or Left and Mid. I would advise continuing to attempt to identify
the missing or mis-matched reference. It is likely to cause further problems
in the future if you do not.
Can you confirm that DAO reference? Does it really say 3.5, or was that just
a typo?
--
Brendan Reynolds
Access MVP
"Randy[ at ]Fluke" <RandyFluke[ at ]discussions.microsoft.com> wrote in message
news:D26EEE96-501D-41CE-B786-A181E745F224[ at ]microsoft.com...
[Quoted Text] >I was aware of CStr, but didn't know it would remove the leading space. I
>had
> always used Trim with Str to strip off the space. In my application, I had
> removed Trim from the SQL to see if that was the problem child. The Str by
> itself still caused an error.
>
> However, implementing your suggestion to switch to CStr allowed my
> application to work on the Runtime system. I still don't know why Str
> won't
> work in a query on the Runtime only PC, but I can move on with my
> application
> and don't see any reason to use Str again.
>
> Thanks for jumping in with your suggestion. It was a great help as were
> all
> the other suggestions.
>
> Randy
>
> "Brendan Reynolds" wrote:
>
>> In addition to the good advice you have received from others, you might
>> want
>> to double-check the DAO reference. It should be 3.6, not 3.5.
>>
>> Although I don't think it is the cause of your current problem there is a
>> peculiarity of the Str function that can be a problem if you are not
>> aware
>> of it. The Str function places a space in front of positive numbers. This
>> can cause tests such as 'If Str(SomeNumber) = "0" Then' to produce
>> unexpected results, because if SomeNumber = 0 then Str(SomeNumber) is not
>> equal to "0", it is equal to " 0". For this reason, CStr is often a
>> better
>> choice than Str - it does the same thing, but without the leading space.
>>
>> --
>> Brendan Reynolds
>> Access MVP
>>
>> "Randy[ at ]Fluke" <RandyFluke[ at ]discussions.microsoft.com> wrote in message
>> news:86E844DB-0EE2-4A70-8AE7-D7DD875E0C2B[ at ]microsoft.com...
>> >I think I need to state the problem differently. I use VBA code to
>> >create
>> >an
>> > SQL string and execute that string with an Execute command. A simple
>> > query.
>> > That SQL string has the Str function as part of an expression, where it
>> > converts a number field to a string field. The error appears when that
>> > SQL
>> > is
>> > executed. So I don't think the problem is really a VBA problem but a
>> > query
>> > problem.
>> >
>> > To answer your question, the references are:
>> >
>> > Visual Basis for Applications
>> > Microsoft Access 11.0 Object Library
>> > Microsoft Office XP Web Components
>> > Microsoft DAO 3.5 Object Library
>> >
>> > Randy
>> >
>> > "Rick Brandt" wrote:
>> >
>> >> Randy[ at ]Fluke wrote:
>> >> > OK Rick. I did as you suggested and was able to delete a reference I
>> >> > didn't need. However, I put your code in my application and reloaded
>> >> > on the target PC. Your program indicates that all the reference
>> >> > likes
>> >> > are OK but I still can't run the procedure with the Str function in
>> >> > it.
>> >> >
>> >> > Not sure what to do next.
>> >>
>> >> What references are you using?
>> >>
>> >> --
>> >> Rick Brandt, Microsoft Access MVP
>> >> Email (as appropriate) to...
>> >> RBrandt at Hunter dot com
>> >>
>> >>
>> >>
>>
>>
>>
|
|
It is 3.5. There is a 3.6 in the list of references. I could try it with that
reference and see what happens.
Randy
"Brendan Reynolds" wrote:
[Quoted Text] >
> This is further evidence that the problem is a missing or mis-matched
> reference, Randy. We see this frequently in the newsgroups where one
> function causes an error but a similar function does not, for example Date
> and Now, or Left and Mid. I would advise continuing to attempt to identify
> the missing or mis-matched reference. It is likely to cause further problems
> in the future if you do not.
>
> Can you confirm that DAO reference? Does it really say 3.5, or was that just
> a typo?
>
> --
> Brendan Reynolds
> Access MVP
>
> "Randy[ at ]Fluke" <RandyFluke[ at ]discussions.microsoft.com> wrote in message
> news:D26EEE96-501D-41CE-B786-A181E745F224[ at ]microsoft.com...
> >I was aware of CStr, but didn't know it would remove the leading space. I
> >had
> > always used Trim with Str to strip off the space. In my application, I had
> > removed Trim from the SQL to see if that was the problem child. The Str by
> > itself still caused an error.
> >
> > However, implementing your suggestion to switch to CStr allowed my
> > application to work on the Runtime system. I still don't know why Str
> > won't
> > work in a query on the Runtime only PC, but I can move on with my
> > application
> > and don't see any reason to use Str again.
> >
> > Thanks for jumping in with your suggestion. It was a great help as were
> > all
> > the other suggestions.
> >
> > Randy
> >
> > "Brendan Reynolds" wrote:
> >
> >> In addition to the good advice you have received from others, you might
> >> want
> >> to double-check the DAO reference. It should be 3.6, not 3.5.
> >>
> >> Although I don't think it is the cause of your current problem there is a
> >> peculiarity of the Str function that can be a problem if you are not
> >> aware
> >> of it. The Str function places a space in front of positive numbers. This
> >> can cause tests such as 'If Str(SomeNumber) = "0" Then' to produce
> >> unexpected results, because if SomeNumber = 0 then Str(SomeNumber) is not
> >> equal to "0", it is equal to " 0". For this reason, CStr is often a
> >> better
> >> choice than Str - it does the same thing, but without the leading space.
> >>
> >> --
> >> Brendan Reynolds
> >> Access MVP
> >>
> >> "Randy[ at ]Fluke" <RandyFluke[ at ]discussions.microsoft.com> wrote in message
> >> news:86E844DB-0EE2-4A70-8AE7-D7DD875E0C2B[ at ]microsoft.com...
> >> >I think I need to state the problem differently. I use VBA code to
> >> >create
> >> >an
> >> > SQL string and execute that string with an Execute command. A simple
> >> > query.
> >> > That SQL string has the Str function as part of an expression, where it
> >> > converts a number field to a string field. The error appears when that
> >> > SQL
> >> > is
> >> > executed. So I don't think the problem is really a VBA problem but a
> >> > query
> >> > problem.
> >> >
> >> > To answer your question, the references are:
> >> >
> >> > Visual Basis for Applications
> >> > Microsoft Access 11.0 Object Library
> >> > Microsoft Office XP Web Components
> >> > Microsoft DAO 3.5 Object Library
> >> >
> >> > Randy
> >> >
> >> > "Rick Brandt" wrote:
> >> >
> >> >> Randy[ at ]Fluke wrote:
> >> >> > OK Rick. I did as you suggested and was able to delete a reference I
> >> >> > didn't need. However, I put your code in my application and reloaded
> >> >> > on the target PC. Your program indicates that all the reference
> >> >> > likes
> >> >> > are OK but I still can't run the procedure with the Str function in
> >> >> > it.
> >> >> >
> >> >> > Not sure what to do next.
> >> >>
> >> >> What references are you using?
> >> >>
> >> >> --
> >> >> Rick Brandt, Microsoft Access MVP
> >> >> Email (as appropriate) to...
> >> >> RBrandt at Hunter dot com
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>
|
|
It should be DAO 3.6 for Access 2000 and later. DAO 3.50 was the version
originally shipped with Access 97, later service releases of Access
97/Office 97 introduced DAO 3.51.
--
Brendan Reynolds
Access MVP
"Randy[ at ]Fluke" <RandyFluke[ at ]discussions.microsoft.com> wrote in message
news:5E6F2580-D765-43FA-80F5-25928F2B29F1[ at ]microsoft.com...
[Quoted Text] > It is 3.5. There is a 3.6 in the list of references. I could try it with
> that
> reference and see what happens.
>
> Randy
>
> "Brendan Reynolds" wrote:
>
>>
>> This is further evidence that the problem is a missing or mis-matched
>> reference, Randy. We see this frequently in the newsgroups where one
>> function causes an error but a similar function does not, for example
>> Date
>> and Now, or Left and Mid. I would advise continuing to attempt to
>> identify
>> the missing or mis-matched reference. It is likely to cause further
>> problems
>> in the future if you do not.
>>
>> Can you confirm that DAO reference? Does it really say 3.5, or was that
>> just
>> a typo?
>>
>> --
>> Brendan Reynolds
>> Access MVP
>>
>> "Randy[ at ]Fluke" <RandyFluke[ at ]discussions.microsoft.com> wrote in message
>> news:D26EEE96-501D-41CE-B786-A181E745F224[ at ]microsoft.com...
>> >I was aware of CStr, but didn't know it would remove the leading space.
>> >I
>> >had
>> > always used Trim with Str to strip off the space. In my application, I
>> > had
>> > removed Trim from the SQL to see if that was the problem child. The Str
>> > by
>> > itself still caused an error.
>> >
>> > However, implementing your suggestion to switch to CStr allowed my
>> > application to work on the Runtime system. I still don't know why Str
>> > won't
>> > work in a query on the Runtime only PC, but I can move on with my
>> > application
>> > and don't see any reason to use Str again.
>> >
>> > Thanks for jumping in with your suggestion. It was a great help as were
>> > all
>> > the other suggestions.
>> >
>> > Randy
>> >
>> > "Brendan Reynolds" wrote:
>> >
>> >> In addition to the good advice you have received from others, you
>> >> might
>> >> want
>> >> to double-check the DAO reference. It should be 3.6, not 3.5.
>> >>
>> >> Although I don't think it is the cause of your current problem there
>> >> is a
>> >> peculiarity of the Str function that can be a problem if you are not
>> >> aware
>> >> of it. The Str function places a space in front of positive numbers.
>> >> This
>> >> can cause tests such as 'If Str(SomeNumber) = "0" Then' to produce
>> >> unexpected results, because if SomeNumber = 0 then Str(SomeNumber) is
>> >> not
>> >> equal to "0", it is equal to " 0". For this reason, CStr is often a
>> >> better
>> >> choice than Str - it does the same thing, but without the leading
>> >> space.
>> >>
>> >> --
>> >> Brendan Reynolds
>> >> Access MVP
>> >>
>> >> "Randy[ at ]Fluke" <RandyFluke[ at ]discussions.microsoft.com> wrote in message
>> >> news:86E844DB-0EE2-4A70-8AE7-D7DD875E0C2B[ at ]microsoft.com...
>> >> >I think I need to state the problem differently. I use VBA code to
>> >> >create
>> >> >an
>> >> > SQL string and execute that string with an Execute command. A simple
>> >> > query.
>> >> > That SQL string has the Str function as part of an expression, where
>> >> > it
>> >> > converts a number field to a string field. The error appears when
>> >> > that
>> >> > SQL
>> >> > is
>> >> > executed. So I don't think the problem is really a VBA problem but a
>> >> > query
>> >> > problem.
>> >> >
>> >> > To answer your question, the references are:
>> >> >
>> >> > Visual Basis for Applications
>> >> > Microsoft Access 11.0 Object Library
>> >> > Microsoft Office XP Web Components
>> >> > Microsoft DAO 3.5 Object Library
>> >> >
>> >> > Randy
>> >> >
>> >> > "Rick Brandt" wrote:
>> >> >
>> >> >> Randy[ at ]Fluke wrote:
>> >> >> > OK Rick. I did as you suggested and was able to delete a
>> >> >> > reference I
>> >> >> > didn't need. However, I put your code in my application and
>> >> >> > reloaded
>> >> >> > on the target PC. Your program indicates that all the reference
>> >> >> > likes
>> >> >> > are OK but I still can't run the procedure with the Str function
>> >> >> > in
>> >> >> > it.
>> >> >> >
>> >> >> > Not sure what to do next.
>> >> >>
>> >> >> What references are you using?
>> >> >>
>> >> >> --
>> >> >> Rick Brandt, Microsoft Access MVP
>> >> >> Email (as appropriate) to...
>> >> >> RBrandt at Hunter dot com
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>
|
|
|