Can I use a number variable in a Query?

  • Thread starter Thread starter Kari
  • Start date Start date
K

Kari

I am trying to use a Global Variable as a criteria in a query. I learned how
to do that for a string variable on this discussion board (see below--Thanks
John W. Vinson!). However, it doesn't work for a number (double) variable.
The function works fine (as tested from another bit of code), but it doesn't
work in the query. The query builder keeps putting quotes around the
variable name and coming up with a "data type mismatch" error. Is my syntax
wrong? Should my variable and GetGlobal function be strings even though my
column (in the query) is a double?

Help!

Kari
Queries don't have direct access to VBA variables, global or not. You
need to write a dumb little wrapper function:

Public Function GetGlobal(varname as string) As Variant
GetGlobal = Eval(varname)
End Function

If the variable is named glUsername, you'ld put
GetGlobal("glUsername")
in the Criteria line of the query.
Click to expand...

[Note: I'm typing: GetGlobal(POforReport)
in my query, since both POforReport and the column in the query are doubles]
 
Kari

It all starts with the data ... and I don't have a very clear picture of
your underlying data.

The "data mismatch" error message means you are trying to compare apples and
oranges (or text and numeric). If Access puts quotes around the variable
name, that means it is trying to find a text string that looks like what's
between the quotes.

Any chance the underlying data was defined as a "lookup" datatype?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Kari said:
I am trying to use a Global Variable as a criteria in a query. I learned
how
to do that for a string variable on this discussion board (see
below--Thanks
John W. Vinson!). However, it doesn't work for a number (double)
variable.
The function works fine (as tested from another bit of code), but it
doesn't
work in the query. The query builder keeps putting quotes around the
variable name and coming up with a "data type mismatch" error. Is my
syntax
wrong? Should my variable and GetGlobal function be strings even though
my
column (in the query) is a double?

Help!

Kari
Queries don't have direct access to VBA variables, global or not. You
need to write a dumb little wrapper function:

Public Function GetGlobal(varname as string) As Variant
GetGlobal = Eval(varname)
End Function

If the variable is named glUsername, you'ld put
GetGlobal("glUsername")
in the Criteria line of the query.
Click to expand...

[Note: I'm typing: GetGlobal(POforReport)
in my query, since both POforReport and the column in the query are
doubles]
John W. Vinson[MVP]
Click to expand...
Click to expand...
 
Jeff,

The data field is [Order ID], defined as AutoNumber; could that be what's
gumming up the works? It is the Primary Key on the table (and the column in
the query that is causing me grief).

POforReport is a global variable defined as a Double. When I call
GetGlobal(POforReport) [called from a bit of test code] it returns a number
(I can do arithmetic with it).

In the Design View of the Query I type: GetGlobal(POforReport) and the
query builder puts in quotes to make: Get Global ("POforReport") and then
complains about the mismatched data types. :-(

The original code used the quotes, but that was for a string variable. I
figured since I have a numeric variable I should lose the quotes. And
neither way works.

I've checked everything I can think of to make sure it's a number not a
string; can you think of anything I've missed?

Thanks for your help.

Kari

P.S. What I'm ultimately trying to do, is open a report from two different
forms. Usually I would just use a reference to the text box on the form for
the criteria, but I want to be able to use the same report/query from either
form. I thought a Global Variable would be the best way to go. Maybe
there's an easier way?

Jeff Boyce said:
Kari

It all starts with the data ... and I don't have a very clear picture of
your underlying data.

The "data mismatch" error message means you are trying to compare apples and
oranges (or text and numeric). If Access puts quotes around the variable
name, that means it is trying to find a text string that looks like what's
between the quotes.

Any chance the underlying data was defined as a "lookup" datatype?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Kari said:
I am trying to use a Global Variable as a criteria in a query. I learned
how
to do that for a string variable on this discussion board (see
below--Thanks
John W. Vinson!). However, it doesn't work for a number (double)
variable.
The function works fine (as tested from another bit of code), but it
doesn't
work in the query. The query builder keeps putting quotes around the
variable name and coming up with a "data type mismatch" error. Is my
syntax
wrong? Should my variable and GetGlobal function be strings even though
my
column (in the query) is a double?

Help!

Kari
Queries don't have direct access to VBA variables, global or not. You
need to write a dumb little wrapper function:

Public Function GetGlobal(varname as string) As Variant
GetGlobal = Eval(varname)
End Function

If the variable is named glUsername, you'ld put
GetGlobal("glUsername")
in the Criteria line of the query.
Click to expand...

[Note: I'm typing: GetGlobal(POforReport)
in my query, since both POforReport and the column in the query are
doubles]
John W. Vinson[MVP]
Click to expand...
Click to expand...
Click to expand...
 
Note: varname AS STRING. GetGlobal is expecting to get a String as an
argument.
GetGlobal = Eval(varname)
End Function

If the variable is named glUsername, you'ld put
GetGlobal("glUsername")
in the Criteria line of the query.
Click to expand...

[Note: I'm typing: GetGlobal(POforReport)
in my query, since both POforReport and the column in the query are doubles]
Click to expand...

And you're not passing it a string, you're passing it a Double.

What's the point of doing an Eval() on a number anyway!? It'll just give you
the number back.

What are you expecting this function to accomplish? Why are you using a Double
when the field you're searching is an Autonumber (a Long Integer)?
 
As John points out, an Autonumber is a LONG, not a Double.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Kari said:
Jeff,

The data field is [Order ID], defined as AutoNumber; could that be what's
gumming up the works? It is the Primary Key on the table (and the column
in
the query that is causing me grief).

POforReport is a global variable defined as a Double. When I call
GetGlobal(POforReport) [called from a bit of test code] it returns a
number
(I can do arithmetic with it).

In the Design View of the Query I type: GetGlobal(POforReport) and the
query builder puts in quotes to make: Get Global ("POforReport") and then
complains about the mismatched data types. :-(

The original code used the quotes, but that was for a string variable. I
figured since I have a numeric variable I should lose the quotes. And
neither way works.

I've checked everything I can think of to make sure it's a number not a
string; can you think of anything I've missed?

Thanks for your help.

Kari

P.S. What I'm ultimately trying to do, is open a report from two
different
forms. Usually I would just use a reference to the text box on the form
for
the criteria, but I want to be able to use the same report/query from
either
form. I thought a Global Variable would be the best way to go. Maybe
there's an easier way?

Jeff Boyce said:
Kari

It all starts with the data ... and I don't have a very clear picture of
your underlying data.

The "data mismatch" error message means you are trying to compare apples
and
oranges (or text and numeric). If Access puts quotes around the variable
name, that means it is trying to find a text string that looks like
what's
between the quotes.

Any chance the underlying data was defined as a "lookup" datatype?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Kari said:
I am trying to use a Global Variable as a criteria in a query. I
learned
how
to do that for a string variable on this discussion board (see
below--Thanks
John W. Vinson!). However, it doesn't work for a number (double)
variable.
The function works fine (as tested from another bit of code), but it
doesn't
work in the query. The query builder keeps putting quotes around the
variable name and coming up with a "data type mismatch" error. Is my
syntax
wrong? Should my variable and GetGlobal function be strings even
though
my
column (in the query) is a double?

Help!

Kari

Queries don't have direct access to VBA variables, global or not. You
need to write a dumb little wrapper function:

Public Function GetGlobal(varname as string) As Variant
GetGlobal = Eval(varname)
End Function

If the variable is named glUsername, you'ld put
GetGlobal("glUsername")
in the Criteria line of the query.

[Note: I'm typing: GetGlobal(POforReport)
in my query, since both POforReport and the column in the query are
doubles]

John W. Vinson[MVP]
Click to expand...
Click to expand...
Click to expand...
 
John & Jeff,

Thank you both for replying to my questions. I had forgotten that
Autonumbers are Long--thanks for the correction. However, the query still
won't work. (Sorry about my delay in replying--I only work half days on
Friday.)

To answer your question John, I'm trying to use a global variable as a
criteria in a query. After searching these discussion boards I learned that
queries can't access global variables directly, but can access a user defined
function to feed them the value, hence my "wrapper" function, GetGlobal.

The function works fine (i.e. when I reference it from another bit of code
it returns the proper value and I can do arithmetic with it) but I can't
figure out the syntax to get the query to talk with it.

The original code was for passing a string value and used GetGlobal
("GlobalVariableName") Since I'm using a number I tried typing
GetGlobal(MyVariableName) but the query builder insists on putting in quotes
to make GetGlobal("MyVariableName") which, of course, generates a "Data type
mismatch" error.

So, my question is, what is the syntax for using a custom defined function
with a numeric parameter in a query? Or, is there a better way to acheive my
aim (to be able to call a report from 2 different forms while specifying
filtering info on the form)?

Any help would be most appreciated.

Kari

John W. Vinson said:
Note: varname AS STRING. GetGlobal is expecting to get a String as an
argument.
GetGlobal = Eval(varname)
End Function

If the variable is named glUsername, you'ld put
GetGlobal("glUsername")
in the Criteria line of the query.
Click to expand...

[Note: I'm typing: GetGlobal(POforReport)
in my query, since both POforReport and the column in the query are doubles]
Click to expand...

And you're not passing it a string, you're passing it a Double.

What's the point of doing an Eval() on a number anyway!? It'll just give you
the number back.

What are you expecting this function to accomplish? Why are you using a Double
when the field you're searching is an Autonumber (a Long Integer)?
Click to expand...
 
Sorry for the double posting, but I think I see where some confusion may be
coming from. The code I posted originally is what I found on the discussion
board and was intended to work with string values. I modified it to the
following:

Public Function GetGlobal(varname As Long) As Variant

GetGlobal = Eval(varname)
End Function

As I said earlier, the function works fine, I just can't get the query to
agree to send it a numberic variable. Now that I've posted the code here I
notice that the funtion is defined as a Variant, could that be a problem? (I
changed it to Long, tested it and got the same results, i.e. the query puts
in quotes and complains of a type mismatch.)

John W. Vinson said:
Note: varname AS STRING. GetGlobal is expecting to get a String as an
argument.
GetGlobal = Eval(varname)
End Function

If the variable is named glUsername, you'ld put
GetGlobal("glUsername")
in the Criteria line of the query.
Click to expand...

[Note: I'm typing: GetGlobal(POforReport)
in my query, since both POforReport and the column in the query are doubles]
Click to expand...

And you're not passing it a string, you're passing it a Double.

What's the point of doing an Eval() on a number anyway!? It'll just give you
the number back.

What are you expecting this function to accomplish? Why are you using a Double
when the field you're searching is an Autonumber (a Long Integer)?
Click to expand...
 
Sorry for the double posting, but I think I see where some confusion may be
coming from. The code I posted originally is what I found on the discussion
board and was intended to work with string values. I modified it to the
following:

Public Function GetGlobal(varname As Long) As Variant

GetGlobal = Eval(varname)
End Function

As I said earlier, the function works fine, I just can't get the query to
agree to send it a numberic variable. Now that I've posted the code here I
notice that the funtion is defined as a Variant, could that be a problem? (I
changed it to Long, tested it and got the same results, i.e. the query puts
Click to expand...

OK! I think I get it now. What you want to do is pass the function *the Name*
of a VBA public variable or constant, and have the function return the numeric
value of that constant. In that case I was mistaken in my advice. The function
should be

Public Function GetGlobal(varname As String) As Variant
GetGlobal = Eval(varname)
End Function


and if the public variable were named (say) CompanyID, you would use

=GetGlobal("CompanyID")

as a criterion in your query.

Since it returns a (typeless) Variant, this should work for numeric values.
For Text criteria you will probably need the syntactically required
quotemarks:

="""" & GetGlobal("CompanyName") & """"

to search for a text company name rather than a numeric company ID.
 
I must be really dense, because even with all this help I'm just not getting
it.

I tried the following:
Public Function TestPO()
POforReport = 500
Debug.Print GetGlobal("POforReport")
End Function

Public Function GetGlobal(varname As String) As Variant
Debug.Print POforReport
Debug.Print varname
GetGlobal = Eval(varname)
End Function

And I get "Run-time error 2482, MS Access can't find the name 'POforReport'
you entered in the expression" on the last line of the Function GetGlobal (so
does the query when I try to open it).


If I change the parameter type to Long, i.e.:
Public Function GetGlobal(varname As Long) As Variant
AND remove the quotes when calling it (otherwise I get a type mismatch error
in the TestPO code), i.e.:
Debug.Print GetGlobal(POforReport)

it works from my test code, but the query still gives me a "type mismatch"
error.

I'm sorry to be so dense but I've checked everything I can think of and
tried ever combination I can think of. What should I try now?
 
I must be really dense, because even with all this help I'm just not getting
it.

I tried the following:
Public Function TestPO()
POforReport = 500
Debug.Print GetGlobal("POforReport")
End Function

Public Function GetGlobal(varname As String) As Variant
Debug.Print POforReport
Debug.Print varname
GetGlobal = Eval(varname)
End Function

And I get "Run-time error 2482, MS Access can't find the name 'POforReport'
you entered in the expression" on the last line of the Function GetGlobal (so
does the query when I try to open it).
Click to expand...

That's because POforReport is a *LOCAL* variable, within the function TestPO.
Its existance is not known within the function GetGlobal. In order for
GetGlobal to work, the variable POforReport must be a *global* variable.

Try

I tried the following:

Option Explicit ' PUT THIS IN every module to be sure all variables are
' defined!
Public POForReport As Long ' Dimensions POForReport as a public variable

Public Function TestPO()
POforReport = 500 ' this will set the value of the public variable
Debug.Print GetGlobal("POforReport")
End Function

Public Function GetGlobal(varname As String) As Variant
GetGlobal = Eval(varname)
End Function
 
John,

Sorry, not only am I dense but I'm getting sloppy (an invitation for
disaster when dealing with variables).

POforReport was defined as a Public Long variable, I just didn't mention
it--my mistake.

I copied your code into a new module to make sure there were no surprises
(and renamed the old functions/variables to prevent issues), but I'm getting
the same results:

When I run TestPO I get "Run-time error 2482, MS Access can't find the name
'POforReport' you entered in the expression" on the last line of the Function
GetGlobal (so does the query when I try to open it).


If I change the parameter type to Long, i.e.:
Public Function GetGlobal(varname As Long) As Variant
AND remove the quotes when calling it (otherwise I get a type mismatch error
in the TestPO code), i.e.:
Debug.Print GetGlobal(POforReport)

it works from my test code, but the query still gives me a "type mismatch"
error.

The entire Module is below:


Option Compare Database
Option Explicit

Public POforReport As Long

Public Function TestPO()
POforReport = 500
Debug.Print GetGlobal("POforReport")
End Function

Public Function GetGlobal(varname As String) As Variant
GetGlobal = Eval(varname)
End Function
 
Back
Top