select query using user defined variable

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
Can someone tell me how i can write a select query using user defined
variable in the where clause?

e.g.

SELECT tblOrders.RockName, FROM tblOrders INNER JOIN tblOrderDetails ON
tblOrders.EntryNumber = tblOrderDetails.EntryKey
WHERE (((tblOrders.RockName)=[gsRockName]));


[gsRockName] is the name of the variable.
when the query is triggered it pops up a msg box asking for [gsRockName]

Thanks.
 
Hi,
Can someone tell me how i can write a select query using user defined
variable in the where clause?

e.g.

SELECT tblOrders.RockName, FROM tblOrders INNER JOIN tblOrderDetails ON
tblOrders.EntryNumber = tblOrderDetails.EntryKey
WHERE (((tblOrders.RockName)=[gsRockName]));


[gsRockName] is the name of the variable.
when the query is triggered it pops up a msg box asking for [gsRockName]
Click to expand...

SQL is one language, VBA another; SQL has no knowledge of VBA
variables.

What you'll need to do is make gsRockName a Public variable, and
create a dumb little wrapper function:


Public Function GetRockName()
GetRockName = gsRockName
End Function

Then use a criterion

WHERE tblOrders.RockName = GetRockName()


John W. Vinson[MVP]
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

That's a parameter. You can set a parameter in the pop-up prompt, thru
the QueryDef's Parameters property, or w/ a reference to a form.

Setting parameter via VBA code:

dim db as dao.database
dim qd as dao.querydef
dim rs as dao.recordset

set db = currentdb
set qd = db.querydefs("myquery")

' Here's where we set the query's parameters
qd.parameters!gsRockName = "quartz"
' also qd(0) = "quartz" 'cuz the zeroth param is gsRockName

' do something w/ query - only in recordset
set rs = qd.OpenRecordset()
=====

Setting parameter via form reference:

Set up a form w/ a TextBox named txtRockName. The form has to be open
when the query runs & there must be valid data in txtRockName. Change
the query's criteria to use the form reference instead of "gsRockName."
The SQL would look like this:

PARAMETERS Forms!FormName!txtRockName Text;
SELECT tblOrders.RockName
FROM tblOrders INNER JOIN tblOrderDetails ON
tblOrders.EntryNumber = tblOrderDetails.EntryKey
WHERE tblOrders.RockName=Forms!FormName!txtRockName

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQkw5e4echKqOuFEgEQJdZACgkkuLVRxoXIHMr2nzLe3nPL9wXi4AoMM/
UUQv4pjlu7Zhe8g0fDZGID4+
=mkbF
-----END PGP SIGNATURE-----
 
Back
Top