How can I use Global String in Query Criterion?

[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!

 

[Van T. Dinh]

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.

 

[eomer]

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!

.

 

[eomer]

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!

.

 

[Ken Snell [MVP]]

Criterion expression should be
=fnCustPartNumber()


--

Ken Snell
<MS ACCESS MVP>

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!

.

 

[Van T. Dinh]

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)

 

[eomer]

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.

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!

.

.