Using Global variable in an sql statement

[RangerTen]

Can someone help me out with using a variable in a sql query

I want the global variable to be the condition

SELECT tblMain.autonumber, tblMain.CaseNumber, tblMain.Handler, tblMain.[K-9], tblMain.Date
FROM tblMain, tblDefaultValues
WHERE (((tblMain.Handler)=[tblDefaultValues].[Officer])); is what I have now

right now the WHERE condition is set from a field in a table. however, I want it to be set from a global variable called

OfficerLogged

WHERE (((tblMain.Handler)=OfficerLogged));

Anyone help me with this? Thanks

What is the syntax to use a variable rather than a field in a table or on a form.

 

[Calvin]

There is a couple of ways to do it but how are you planning to execute it? Is it a saved query that is being executed through code (OpenQuery) or are you executing the SQL through code? Are you opening a table view or a recordset? And how are the two tables joined? How do you plan to set the value of the OfficerLogged global variable?

Scouts Out

 

[RangerTen]

thanks

I am actually creating a query that will be used as a filter

I have already set the global variable with a logon form

Basically I have the query opened in design view and want to use my globalvariable as the criteria

I tried to use the SQL code builder, but couldn't get the sytax right

any ideas

 

[Calvin]

Hmmm...

If I understand you right does the following make sense?

Private Function fRangerTen() As Long

OfficerLogged = "Tom"

Dim strSQL As String
strSQL = "SELECT tblMain.autonumber, tblMain.CaseNumber, tblMain.Handler, tblMain.[K-9], tblMain.Date FROM tblMain, tblDefaultValues WHERE (((tblMain.Handler)='" & OfficerLogged & "'));"

Dim qDef As QueryDef
Set qDef = CurrentDb.QueryDefs("Query1")
qDef.SQL = strSQL

DoCmd.OpenQuery "Query1", acViewDesign, acEdit

qDef.Close
Set qDef = Nothing

End Function

This uses an existing Query named "Query1" change this to the name of the query your trying to filter.

But I'm not sure why you would allow the user to open the query in design view if that is not your intent then change the acViewDesign to acViewNormal to view the filtered data.

I'm going to logout tonight but I'll check in again in themorning if you have more questions.

 

[RangerTen]

hmmm

sorry bout that...don't think I am putting this together clearly. I am not "really" trying to code the whole procedure. Most of what I need is already hard wired into access.

The end user won't get into the form in design view (just me).

I have a login form that sets my global variable to a specific officers name. once that variable is set, it opens up a new form that contains a listbox that lists all of the cases (records) that the specific officer has completed. I wanted to create a query that would filter the list to only the cases that the officer who has logged in. WIthout the filter, the list box would contain all records of all the officers.

So while creating the filter, it jumps to the query design view of which I created a query. while in query design view, I want to set the CRITERIA box to hold a variable. When you right click and click on build... , the expression builder pops up...and I don't think I can build my criteria IN THAT PARTICULAR WINDOW with a variable (or I just don't know the syntax.

Basically the gist of it is this...

I want to create a query in design view (not in vba at this point). For my criteria, I want to specify a variable (rather than an object which is what the expression builder promotes). Is this possible in the query DESIGN VIEW. I tried switching my design view over to SQL view and still couldn't find the correct syntax to use with it. I'm not good enough to code the entire database in VBA, and use many of the shortcuts that access provides...but I can't figure out how to make a variable my criteria when working with the query designer.

This make a little more sense?

Thanks for all of your help so far!!

Pete

 

[Calvin]

Still here, havn't escape yet,

In that case...

I have an even simpler solution. I'm guessing that you are setting the ControlSource of the combo box = to the name of the query, if so, scrap the query your trying to filter and in the form Open or Load event place the following:

Me.Combo1.ControlSource = "SELECT tblMain.autonumber, tblMain.CaseNumber, tblMain.Handler, tblMain.[K-9], tblMain.Date FROM tblMain, tblDefaultValues WHERE (((tblMain.Handler)='" & OfficerLogged & "'));"

replace Combo1 with the name of your combobox/listbox

this will execute your query everytime the form is opened or loaded with the current OfficerLogged criteria

I know you didn't want to get into VBA but from what I've gathered this should be the easiest and simplest route for you.

 

[RangerTen]

no way to set it in the expression builder eh?

I'm getting zero results back with this. I have something messed up somewhere.,

 

[Rich]

Use a hidden form to hold the OfficerLogged value, set the criteria to the form

 

[RangerTen]

Calvin, I think I found out the reason you're code didn't work correctly...I don't have the datasource for the form tied to anything...therefore the recordsource wouldn't work.

I think the hidden form will work, but would prefer not to do that. Just seems a bit messy and a waste of resources. Good idea though. I would prefer to just use my variable... I have it hanging out there.

Bottom line...

Is it possible to use a global variable in the expression builder or in the sql view of the query?? Thats truly the way I would like to go....if its at all possible.

Anyone have any ideas on that??

Thanks for all the help!

 

[Mile-O]

Is it possible to use a global variable in the expression builder or in the sql view of the query?? Thats truly the way I would like to go....if its at all possible.

Almost.

Make a public function

i.e

Public Function blablabla()
   blablabla = yourglobalvariable
End Function

And call the function in your query.

 

[RangerTen]

Now you're talking. That will work....Thanks a million!! That will do the trick!! I'd hug ya and kiss ya if I could. Woo hoo.

 

[RangerTen]

I just programmed it and it worked like a charm. This bulletin board is a godsend. I'll be sure to pass your business name along to others.

Thanks again