Date Variable in Stored SQL

[Talgo]

I have been trying to use a date variable in a stored SQL statement but keep getting "Too few parameters Expected 1" errors. I get this error because the rst is emtpy but everything I have tried doesn't correct the problem. Here is the code I'm using:

Dim db As Database
Dim rst As Recordset
Dim strSQL As String
Dim dtWorkDate As Date

dtWorkDate = Forms!TimeCards!WorkDate

strSQL = "SELECT WorkDate, EmployeeID "
strSQL = strSQL & "FROM TimeCard "
strSQL = strSQL & "WHERE WorkDate >=(dtWorkDate-(Weekday(dtWorkDate))+2) "
strSQL = strSQL & &quot;AND WorkDate <=(dtWorkDate +(8-Weekday(dtWorkDate)));&quot;

Set db = CurrentDb

Set rst = db.OpenRecordset(strSQL)

Can anyone suggest corrections? I need to create a recordset containing a week's worth of employee time card info based on a given date (dtWorkDate).

 

[RoyVidar]

Hi!

You're assigning the text &quot;(dtWorkDate.... &quot; to the sql string. When using variables, one need to ensure that the values enter the strings. Perhaps something like the following might work:

[tt]strSQL = strSQL & &quot;WHERE WorkDate >= #&quot;
strSQL = strSQL & (dtWorkDate-(Weekday(dtWorkDate))+2)
strSQL = strSQL & &quot;# AND WorkDate <= #&quot;
strSQL = strSQL & (dtWorkDate +(8-Weekday(dtWorkDate))) & &quot;#&quot;[/tt]

Just wondering if it might be better to perform the date calculations before entering them in the query, but I don't know.

Following thread contains a bit more explanation on using variables (or rather form controls) in vba queries (I refer to my contribution, but there's also other interessting reading there, perhaps specially FancyPraire's tip on using the immidiate pane to check the sql-string). thread705-700362

Just one note - If your regional settings is providing a non US recognizable date format, you'll need to format it. Samples in my contribution in mentioned thread.

HTH Roy-Vidar

 

[Talgo]

Hi RoyVidar,

Thanks for your help & pointing me to the thread. The info was very helpful, however, I'm still having a problem. I took you suggestion about calculating dates before entering them into query. I now use a date variable to represent the date. When I run the code in the Watch Window I can confirm that the dates contained in the two variables (dtFromDate & dtToDate) are correct but I get an error message saying &quot;Too few Parameters. Expected 1&quot;. I can not figure out what parameter is missing. The rst has no records, so I assume something is wrong with the strSQL. Here is the code I'm using:

Dim db As Database
Dim rst As Recordset
Dim strSQL As String
Dim dtFromDate As Date
Dim dtToDate As Date

dtFromDate = Me!FromDate
dtToDate = Me!ToDate

strSQL = &quot;SELECT WorkDate, EmpoyeeID &quot;
strSQL = strSQL & &quot;FROM TimeCard &quot;
strSQL = strSQL & &quot;WHERE WorkDate >= #&quot;
strSQL = strSQL & dtFromDate
strSQL = strSQL & &quot;# AND WorkDate <= #&quot;
strSQL = strSQL & dtToDate & &quot;#&quot;

Set db = CurrentDb

Set rst = db.OpenRecordset(strSQL)

Any suggestions or help would be appreciated.

 

[RoyVidar]

Hi again!

Can't see any errors there now, unless there are typos, or the fields does not exist in the table TimeCard.

To check that, I've included som of the parts from FancyPraires post, wich was to put in the line

[tt] Debug.Print strSQL ' Before the line
Set rst = db.OpenRecordset(strSQL)[/tt]

Quote:
&quot;The SQL statement will be printed in debug's Immediate Window. Simply highlight the statement and press ctrl+c (copy). Then open the Query Builder and select SQL view and paste your SQL statement, then try running it. That will help you figure out if your syntax is correct or not.

To enter the immidiate pane from a module, hit CTRL+G

The calculation of datas before assigning them to the SQL string, is probably just my preference.

HTH Roy-Vidar