How can I use Global String in Query Criterion?

  • Thread starter Thread starter Eomer
  • Start date Start date
E

Eomer

I want to run a query using "strCustPartNumber" as my
criterion. The program has the following:

I have dimmed the string in the Global module as follows:
Global strCustPartNumber As String

the form module (VBA code) has:
strCustPartNumber = .Fields("CustPartNumber")

I can get the correct answer in the immediate window but
whenever I try to put an expression in the criterion box,
either it comes up as zero records to append or the
Parameter Input box pops up and I have to manually put in
the value.

I have tried using = [strCustPartNumber], (), "", "[]"
etc. but I just can not get it to recognize that
strCustPartNumber is a variable and use the correct value
(the query runs great if I manually put in the code, but
I need to automate this!)

What is the proper syntax to make this work?

As always - thank you very much!
 
JET cannot access the value of the Global / Public variable directly.

You need to use a "wrapper" UDF in a Standard module like:

Public Function fnCustPartNumber() As String
fnCustPartNumber = strCustPartNumber
End Function

and then use this UDF in your Query / SQL String.
 
I understand your comment, but I still can not get it to
work.

I added the the Public Function to the Global module (cut
& paste) and I put the following in the query criterion:

[fnCustPartNumber]

and I still get the "Enter Parameter Value" box.

Thanks,
-----Original Message-----
JET cannot access the value of the Global / Public variable directly.

You need to use a "wrapper" UDF in a Standard module like:

Public Function fnCustPartNumber() As String
fnCustPartNumber = strCustPartNumber
End Function

and then use this UDF in your Query / SQL String.

--
HTH
Van T. Dinh
MVP (Access)


I want to run a query using "strCustPartNumber" as my
criterion. The program has the following:

I have dimmed the string in the Global module as follows:
Global strCustPartNumber As String

the form module (VBA code) has:
strCustPartNumber = .Fields("CustPartNumber")

I can get the correct answer in the immediate window but
whenever I try to put an expression in the criterion box,
either it comes up as zero records to append or the
Parameter Input box pops up and I have to manually put in
the value.

I have tried using = [strCustPartNumber], (), "", "[]"
etc. but I just can not get it to recognize that
strCustPartNumber is a variable and use the correct value
(the query runs great if I manually put in the code, but
I need to automate this!)

What is the proper syntax to make this work?

As always - thank you very much!
Click to expand...


.
Click to expand...
 
I can not get it to work despite spending an entire day,
using your methodology, reading countless books, and
surfing the Internet.

I am going to scrap the whole qdel / qapp approach and
just go straight to using a recordset. Now that I think
about it, it is a much better way to go.

I appreciate the help - this was one astronomical
learning curve today.

Take Care and hope you have a nice weekend.

-----Original Message-----
JET cannot access the value of the Global / Public variable directly.

You need to use a "wrapper" UDF in a Standard module like:

Public Function fnCustPartNumber() As String
fnCustPartNumber = strCustPartNumber
End Function

and then use this UDF in your Query / SQL String.

--
HTH
Van T. Dinh
MVP (Access)


I want to run a query using "strCustPartNumber" as my
criterion. The program has the following:

I have dimmed the string in the Global module as follows:
Global strCustPartNumber As String

the form module (VBA code) has:
strCustPartNumber = .Fields("CustPartNumber")

I can get the correct answer in the immediate window but
whenever I try to put an expression in the criterion box,
either it comes up as zero records to append or the
Parameter Input box pops up and I have to manually put in
the value.

I have tried using = [strCustPartNumber], (), "", "[]"
etc. but I just can not get it to recognize that
strCustPartNumber is a variable and use the correct value
(the query runs great if I manually put in the code, but
I need to automate this!)

What is the proper syntax to make this work?

As always - thank you very much!
Click to expand...


.
Click to expand...
 
Criterion expression should be
=fnCustPartNumber()


--

Ken Snell
<MS ACCESS MVP>

eomer said:
I understand your comment, but I still can not get it to
work.

I added the the Public Function to the Global module (cut
& paste) and I put the following in the query criterion:

[fnCustPartNumber]

and I still get the "Enter Parameter Value" box.

Thanks,
-----Original Message-----
JET cannot access the value of the Global / Public variable directly.

You need to use a "wrapper" UDF in a Standard module like:

Public Function fnCustPartNumber() As String
fnCustPartNumber = strCustPartNumber
End Function

and then use this UDF in your Query / SQL String.

--
HTH
Van T. Dinh
MVP (Access)


I want to run a query using "strCustPartNumber" as my
criterion. The program has the following:

I have dimmed the string in the Global module as follows:
Global strCustPartNumber As String

the form module (VBA code) has:
strCustPartNumber = .Fields("CustPartNumber")

I can get the correct answer in the immediate window but
whenever I try to put an expression in the criterion box,
either it comes up as zero records to append or the
Parameter Input box pops up and I have to manually put in
the value.

I have tried using = [strCustPartNumber], (), "", "[]"
etc. but I just can not get it to recognize that
strCustPartNumber is a variable and use the correct value
(the query runs great if I manually put in the code, but
I need to automate this!)

What is the proper syntax to make this work?

As always - thank you very much!
Click to expand...


.
Click to expand...
Click to expand...
 
You need to use parentheses () and don't include the function name in square
brackets like Ken's example.

HTH
Van T. Dinh
MVP (Access)
 
That did it!!

It is so sweet when it works right; I am planning to use
the recordset approach, but this will streamline the data
input tremendously.

Thanks a lot - you have saved my a great deal of time and
aggravation.

Take Care,

-----Original Message-----
I understand your comment, but I still can not get it to
work.

I added the the Public Function to the Global module (cut
& paste) and I put the following in the query criterion:

[fnCustPartNumber]

and I still get the "Enter Parameter Value" box.

Thanks,
-----Original Message-----
JET cannot access the value of the Global / Public variable directly.

You need to use a "wrapper" UDF in a Standard module like:

Public Function fnCustPartNumber() As String
fnCustPartNumber = strCustPartNumber
End Function

and then use this UDF in your Query / SQL String.
Click to expand...
put
in
the value.

I have tried using = [strCustPartNumber], (), "", "[]"
etc. but I just can not get it to recognize that
strCustPartNumber is a variable and use the correct value
(the query runs great if I manually put in the code, but
I need to automate this!)

What is the proper syntax to make this work?

As always - thank you very much!
Click to expand...


.
Click to expand...
.
Click to expand...
 
Back
Top