First, everything described here works fine on my system (Win 2000 /
Access 2002). The weird behavior happens on my client's system (Win 98
/ Access 2002). The references are set identically on these systems:
VBA
Access 10.0 Object Lib
OLE Automation
Office XP Web comps
DAO 3.6
my personal code library

Part of the app is a form with a variety of unbound list boxes. These
all have Row Source Type = "Table / Query" and the row sources are
various Select statements. Some of these statements involve VBA
functions. In particular, list box "A" uses the function Format() in
the SQL string and list box "P" uses Format$(). (Note: no particular
reason for the difference; i believe Format$ is more efficient than
Format, but i don't always remember to use it.) If i open this form, i
get error messages that these two functions are undefined. The form
then opens and list boxes A and P are both blank (the others are fine).
If i go to the immediate pane in VB, i have no problem evaluating these
functions (so they must be defined -- as they should be). My program
uses the Format function in many places and these are all ok. Now,
normally this form is not opened directly by the user. Instead, my
program will copy one of the list boxes into another form with which the
user interacts. When list box P is copied, it continues to come up
empty (due to error with Format$()). But, when list box A is copied, it
works.
So -- Format and Format$ appear to be undefined in a SQL string as row
source for list box -- although they work fine in code. Also, Format
appears to somehow start working whereas Format$ does not. And, again,
this all works without a glitch on my system.

Any ideas ?

> First, everything described here works fine on my system (Win 2000 /
> Access 2002). The weird behavior happens on my client's system (Win 98
> / Access 2002). The references are set identically on these systems:
> VBA
> Access 10.0 Object Lib
> OLE Automation
> Office XP Web comps
> DAO 3.6
> my personal code library

You may still want to check for a "references" problem. If you aren't already
familiar with it, see the following page at Doug Steele's web site:

http://members.rogers.com/douglas.j....nceErrors.html

--
Bruce M. Thompson, Microsoft Access MVP
(E-Mail Removed) (See the Access FAQ at http://www.mvps.org/access)
>> NO Email Please. Keep all communications
within the newsgroups so that all might benefit.<<

Likely to be the Reference Errors. See Douglas J. Steele's article:

http://members.rogers.com/douglas.j....nceErrors.html

--
HTH
Van T. Dinh
MVP (Access)



"jfp" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> First, everything described here works fine on my system (Win 2000 /
> Access 2002). The weird behavior happens on my client's system (Win 98
> / Access 2002). The references are set identically on these systems:
> VBA
> Access 10.0 Object Lib
> OLE Automation
> Office XP Web comps
> DAO 3.6
> my personal code library
>
> Part of the app is a form with a variety of unbound list boxes. These
> all have Row Source Type = "Table / Query" and the row sources are
> various Select statements. Some of these statements involve VBA
> functions. In particular, list box "A" uses the function Format() in
> the SQL string and list box "P" uses Format$(). (Note: no particular
> reason for the difference; i believe Format$ is more efficient than
> Format, but i don't always remember to use it.) If i open this form, i
> get error messages that these two functions are undefined. The form
> then opens and list boxes A and P are both blank (the others are fine).
> If i go to the immediate pane in VB, i have no problem evaluating these
> functions (so they must be defined -- as they should be). My program
> uses the Format function in many places and these are all ok. Now,
> normally this form is not opened directly by the user. Instead, my
> program will copy one of the list boxes into another form with which the
> user interacts. When list box P is copied, it continues to come up
> empty (due to error with Format$()). But, when list box A is copied, it
> works.
> So -- Format and Format$ appear to be undefined in a SQL string as row
> source for list box -- although they work fine in code. Also, Format
> appears to somehow start working whereas Format$ does not. And, again,
> this all works without a glitch on my system.
>
> Any ideas ?
>