Store Access Criteria in a Global String

[Ksabai]

I want to store the below Form Criteria in a Global String
Like " * " & [Forms]![frmReports]![cmbCropYear] & " * "

When i store it returns nothing, request someone to help.

 

[Ranman256]

It's not really global if you are pinning it to a form. Thus modular.
If the form isn't open then it won't work.

You can either set the global var as a literal hard coded value in the form,
Val= "like '*smith*'"

but why do to needed it if the form is not open?

 

[Ksabai]

iam looking to use a single access query for different forms

 

[Ksabai]

tried "Like " * " & [Forms]![frmReports]![cmbCropYear] & " * ""
its not working

 

[Minty]

Why not simply store the year in a local table and include that table in your queries?
Also using a text value as a year isn't very efficient, you'd be better off storing it as a date and simply extracting the year portion in your queries.

 

[Ksabai]

i do understand, may be i don't know how to do it. What iam trying to do is Use a Single Query and a Report for Different forms. Iam able to open a Query with DoCmd.OpenQuery but DoCmd.OpenReport gives and unknown error.

 

[Gasman]

Please do not post the same question in different sub forums. Almost as bad a cross posting.

https://www.access-programmers.co.uk/forums/threads/store-criteria-in-a-global-string.310504/

 

[Ksabai]

sorry i didn't know whether to post here or there, let me remove it there.

 

[Gasman]

I would use a Tempvar.?

Set it once

 

[Ranman256]

if the form (frmReports) is open, and the combo box is filled, then the query should work.
are all these true?
and there are no spaces in around the asterisk in the criteria: "*"

if the query works, then the report aint far behind.

 

[Pat Hartman]

1. Like is used ONLY with strings
2. Like is used ONLY when you want to search for partial strings.
3. It doesn't make sense to use Like with a field that contains a Year in any case.
4. You CANNOT pass operands to a query so you couldn't pass this string into a query.

Your query should use a tempvar as Gasman suggested. Each form would populate the tempvar at some appropriate point. Then the query uses the = operand to compare to the tempvar.

Select ...
From ...
Where Somefield = yourtempvar;

I don't care for tempvars so I am not up on the syntax. That might have to be:

Where Somefield = yourtempvar.Value

 

[Gasman]

A working example would be

((tblSubmitterClient.SubmitterID)=[TempVars]![SubmitterID])

 

[HiTechCoach]

I agree with Gasman about TempVars. It may be one of the best features ever added to Access.

TempVars are great for passing values to Query. It is the only way I would do it now.

I have started converting all my existing queries to use TempVars and remove all form reference.

 

[zeroaccess]

I insert TempVar criteria into my queries when possible, as well. Great feature.