Var inside a SELECT Statement

[David Portwood]

I want to build a SELECT statement as recordsource for a form with a WHERE
clause that varies depending on the branch office. Something like this
(option 1):

SELECT * FROM tablename WHERE [Office]=gstrOffice;

gstrOffice is a global (Public) string variable. But this doesn't look
right. I had some trouble with it, forget what the error was now. What I
ended up doing was defining a very simple function GetOffice() which looked
like:

Public Function GetOffice() as String

GetOffice = gstrOffice

End Function

Then I build my SELECT statement (option 2):

SELECT * from tablename WHERE [Office]=GetOffice();

This worked and I'm using it now, but it seems a little silly defining a
function like this. Can you help me make option 1 work?

 

[Douglas J. Steele]

There's no way to make option 1 work, because Jet doesn't know anything
about VBA variables.

What you're doing is your only option.

 

[Steve Schapel]

What you're doing is your only option.

.... unless you are using Access 2007, in which case you can use a TempVar.

 

[David Portwood]

Thanks for the info, Douglas. And no, Steve, my office is not using A2007. I
don't know when that might happen.

I can live with what I've got, but it certainly looks awkward.

There's no way to make option 1 work, because Jet doesn't know anything
about VBA variables.

What you're doing is your only option.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I want to build a SELECT statement as recordsource for a form with a WHERE
clause that varies depending on the branch office. Something like this
(option 1):

SELECT * FROM tablename WHERE [Office]=gstrOffice;

gstrOffice is a global (Public) string variable. But this doesn't look
right. I had some trouble with it, forget what the error was now. What I
ended up doing was defining a very simple function GetOffice() which
looked like:

Public Function GetOffice() as String

GetOffice = gstrOffice

End Function

Then I build my SELECT statement (option 2):

SELECT * from tablename WHERE [Office]=GetOffice();

This worked and I'm using it now, but it seems a little silly defining a
function like this. Can you help me make option 1 work?