Passing a global variable

[Eradic8or]

I have a variable assigned in a global function. The purpose of this is for when a user selects a company from a drop down list, it then runs a query and filters on the company selected.

The reason I am using a variable is that I have also added the option of listing "All companies". However, if the user selects this, the variable is then change to Like "*" and passed into the query..... but, I get no results.

I am passing the variable like so StrSearch = "LIKE " & chr$(34) & "*" & chr$(34). In the dubug window, it looks fine but still no results.

Any suggestions.

 

[smedvid]

As opposed to a global variable, I've use a table to store data. It worked in my case, plus I really need to store the last defaults selected by the user.

Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[Hap007]

Dear Eradic8or,

You have a good idea there, but (if memory serves me correctly) functions do not execute in the 'Criteria' area of a query.

Here is how I handle what you are trying to do.

I define a field on the form and set the 'Visible' attribute to False.
Then I set that field to my desired value.
Finally, I reference the invisible field in my selection Criteria.

ie: In Query Criteria - [Forms]![frmPOPrintOptions]![CurrentPOMax]

Note, you must still have the form open for this to work. So if you want to hide the form, instead of closing the form, just set the visible property of the form to false, if this applies.

Good Luck,
Hap


Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[Ed2020]

Functions certainly execute in the criteria area of a query from Access 97 onwards. I can't say for certain if this is the case for prior versions (it's been a while), but if memory serves they do.

Ed.

Please do not feed the trolls.....

 

[Eradic8or]

Functions definitely work in the query as I already use it. It passes the value of the entry in the list box no problem and returns results.
The problem is when I want to display all the records in the table, I set the variable to read LIKE "*" but that doesn't work.

I'd rather not go down the route of using a hidden form becuase I do a lot more in the code than just change the value of this one variable.

Thanks.

 

[Ed2020]

Get the function to return a default value (-1) when you want to view all records then place the following in your criteria expression:

IIf(FunctionName()<>-1,FunctionName(),"")

Ed Metcalfe.

Please do not feed the trolls.....

 

[Hap007]

Dear Eradic8or,

I use functions all the time, but I use them to reurn data.
I have had problems using a function in the 'Selection Criteria'. Perhaps I just did something wrong, but that has been my experience in the past. The function just seemed to not fire.

Regarding your current problem, if you get the criteria/function to work, that's great. Let me know how you make out.

Another way to handle this would be to build your SQL in VBA, and pass that SQL statement to the RecordSource of your form/report or query.

This is the great thing regarding access. There is always more than one way to solve a problem.

Good Luck,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121