Changing Query definitions with VBA

[bevanre]

Hi there,

I'm using VBA to alter SQL query definitions in Access - I have the general process worked out but I'm having trouble with date criteria.

Hopefully I can explain this well enough for someone to understand and help me out!

I'm using a number of IF statements in VBA to determine the string for the SQL query definition, with some variables coming from a Form. It works perfectly for the variables which are text but it's doing something a bit odd (and very annoying) with the dates. I'll give you a basic version of my code then explain what it's doing!
​

Dim qryName As QueryDef
Dim myForm As Form
Dim StartDate As Date
Dim EndDate As Date

Set qryName = dbsCurrent.QueryDefs("qry_Report")
Set myForm = Forms!frm_Report_Criteria

StartDate = myForm![Criteria_Start]
EndDate = myForm![Criteria_End]

qryName.SQL = "SELECT tbl_Main.Client FROM tbl_Main WHERE [S1Date] BETWEEN " & StartDate & " AND " & EndDate & ";"




​

When I open the query in SQL view it looks correct i.e it has recognised that they are dates and put the '#'s round them e.g. Between #1/1/2008# And #3/31/2008# But the query doesn't return the results, and when I look at the query in design view it looks like this - Between 1/1/2008 And 31/3/2008. However if I go into 'Zoom' or 'Build' and just come straight back out without changing anything it corrects itself with the '#'s and the query runs correctly.

Does anyone have any ideas?? I'm tearing my hair out - it's the last thing stopping me from finishing a project!​

Any help greatly appreciated​

 

[SydneyGeek]

Hi, and welcome to the Board!

I'm assuming that you don't live in the US, or this would not be a problem for you. You have struck a nasty feature of SQL -- code created in VBA will always use US regional settings, regardless of how you set up your machine. I have used two workarounds successfully. The simplest is to convert the dates to numbers using the CDbl function. So...

qryName.SQL = "SELECT tbl_Main.Client FROM tbl_Main "
   & "WHERE [S1Date] BETWEEN " & CDbl(StartDate) & " AND " & CDbl(EndDate) & ";"

Denis

 

[bevanre]

Problem solved

Thanks a million!!


ps. I'm in the UK