How do i declare a variable inside of a query?

[Chris1977]

This maybe a simple question but i need to know the syntax to assign a variable as criteria inside a query.

Thanks

 

[ajdesalvo]

If you run the query as sql code in a module you can do it, but there isn't a way I know of to reference it in a query.

DoCmd.RunSql "Select * from [YourTable] WHERE [YourField] = '" & variablex & "';"


Good luck! Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[beetee]

FWIW, and this is highly impure non-portable SQL, but you can create an access module and add Get and Set routines...

e.g.

dim MyVariable as string

function GetValue() as string
GetValue = MyVariable
end function

sub SetValue(ToWhat as string)
MyVariable = SetValue
end sub

Now, in your sql expression you can create

"Select * from [YourTable] WHERE [YourField] = '" & GetValue() & "';"

Of course, you will want to change the data types as needed; if you plan on dealing with nulls you may consider using the variant data type.

 

[KenReay]

Hi

You can set parameters in an SQL query, is this waht you mean?

PARAMETER MyVariable AS text;
SELECT * FROM MyTable WHERE myColumn = [MyVariable];

this can be put in the Query builder (SQL View), thus making a Querydef

that query def will then have a parameters collection

eg

qdf.Parameters("MyVariable&quot

which you can set in code before executing the query

Look in Access help for Parameter for a more eloquent explanation than I can give Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK