How do I use a cell reference as a parameter in a query? 1

[kclose]

I would like to have a user enter a parameter into a cell in a worksheet, then run a saved query on that worksheet that uses that (cell reference) parameter. Has anyone ever done this that could help me?

 

[ETID]

Try turning on the macro recorder, then step through the edit query process (this will write a VB SQL version of your query), complete all the steps including reurn data to excel, then stop the recorder. then alt+f11 to view the code that you have recorded. it will look something like this. Ive added things you wont see so look for the line "With Selection.QueryTable", in that section you'll see where I substituted " + Trim(Str$(date_start)) + " for the parameter that was origionaly recorded, the SQL statement is a string so you have to concatenate your cell reference accordingly!

such as: my_string=sheets("sheet1&quot.range("a1&quot

then code the variable my_string into the sql statement



-----------------------------------------------------
Private Sub Worksheet_Activate()
End
Cells(1, 1).Select 'ensures that reserved range in the spreadsheet is valid

rq$ = InputBox("Do you want to Update the Test Hours data?", "Re-Query the Data source", "N&quot
If UCase(rq$) <> &quot;Y&quot; Then End

get_dates 'branches to sub to get users date range via inputbox
With Selection.QueryTable
.Connection = Array(Array( _
&quot;ODBC;DSN=MS Access Database;DBQ=N:\ODBC_Databases\General Sample Queries.mdb;DefaultDir=N:\ODBC_Databases;DriverId=25;FIL=MS Access;MaxBuf&quot; _
), Array(&quot;ferSize=2048;PageTimeout=15;&quot)
.CommandText = Array( _
&quot;SELECT `hours worked CH`.LABAREAS, `hours worked CH`.REFRNUMB, `hours worked CH`.MTRLCODE, `hours worked CH`.MTLCLASN, `hours worked CH`.TESTCODE, `hours worked CH`.DTARDLAB, `hours worked CH`.DTESETU&quot; _
, _
&quot;P, `hours worked CH`.DTCOMAPP, `hours worked CH`.SMPLSTAT, `hours worked CH`.RSLTSEQN, `hours worked CH`.AVLBLHRS, `hours worked CH`.TESTDESR, `hours worked CH`.MTLCODED&quot; & Chr(13) & &quot;&quot; & Chr(10) & &quot;FROM `c:\mcgon_stuff\General &quot; _
, _
&quot;Sample Queries`.`hours worked CH` `hours worked CH`&quot; & Chr(13) & &quot;&quot; & Chr(10) & &quot;WHERE (`hours worked CH`.DTCOMAPP>=&quot; + Trim(Str$(date_start)) + &quot; And `hours worked CH`.DTCOMAPP<=&quot; + Trim(Str$(date_stop)) + &quot&quot; _
)
.Refresh BackgroundQuery:=False
End With

End Sub


---------------------------------
Hope this is some help!

 

[ETID]

oops...i forgot to rem out the first &quot;end&quot; statement in the sub