Running queries with parameters from VBA 1

[smada80]

Hi all

I am trying to run a query that has a parameter from VBA.
I have a query that takes a date from a form as its parameter. When a field is updated, I want the query excecuted and to use the data from the recordset.

The code I am using is:

rsMyRs = dbMyDb.OpenRecordset("qryGetByDate&quot

where rsMyRs is a recordset, dbmydb is the current database and qryGetByDate is the query.

When I open the query from the Access window, the query runs fine and gets the parameter from the form. However, whne I run it using the code above I get an error message "Too few parameters: Expected 1".

I'd appreciate any help you can give!

Thanks

Matt

 

[ajdesalvo]

Running the query in this way, with a form parameter, makes it a QueryDef.

Create a SQL variable string that has your query syntax with the form reference. Then use that in your recordset definition:

Dim SQLStr as String

SQLStr = "Select * from [YourTable] WHERE [YourField] = '" & Forms![YourForm]![FieldName] & "'"

rsMyRs = dbMyDb.OpenRecordset(SQLStr)

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

 

[smada80]

Thanks for that.

I tried this first but for some reason it never returns any data, even if I use the same SQL as in the query.
I thought this might be due to the way I am putting dates into the SQL string.

The query is something like:

"SELECT period_name FROM tblPeriod WHERE
tblPeriod.start_date <= &quot; & [notice_start_date] & &quot; AND
tblPeriod.end_date >= &quot; & [notice_start_date] & &quot;;&quot;

[notice_start_date] is the form field that contains a date.

When I run this and check to see if the recordset is EOF it always is, however running the same thing in a query works fine!

Matt

 

[ajdesalvo]

You need # symbols for the date parameters:

&quot;SELECT period_name FROM tblPeriod WHERE
tblPeriod.start_date <= #&quot; & [notice_start_date] & &quot;# AND
tblPeriod.end_date >= #&quot; & [notice_start_date] & &quot;# ;&quot;


Good luck! Anthony J. DeSalvo
President - ScottTech Software
&quot;Integrating Technology with Business&quot;

 

[KenReay]

Hi

You also need to ensure that date is presented in US format (mm/dd/yy), so if you are not in the USA (ie you do not have your locale settings as USA), then you may need to reformat the date so:

tblPeriod.end_date >= #&quot; & Format([notice_start_date],&quot;mm/dd/yy&quot & &quot;# ;&quot;


Regards

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

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

UK

 

[smada80]

Thanks to both of you. After I did both suggestions it worked fine. Much appreciated.