SQL date range dilemma

[LuigiCorleone]

I'm importing a date from a form and putting it into SQL as a string variable but its not finding any records.

Here's the important stuff

Dim SQL, str1, str2, strDate As String

str1 = Forms![TestForm].[Start_Date]
str2 = Forms![TestForm].[End_Date]
strDate = "Between #" & str1 & "# And #" & str2 & "#"

SQL = "SELECT asset.Local_ID, /the middle bit of the SQL statement/ '& "WHERE asset_defects.Created_Date " & strDate _
'& " ORDER BY asset.Local_ID"

Its falling over on the variable "strDate".

Any ideas? Formatting/conversion problem?

Thanks

Luigi

 

[Travis]

Add the line:

Debug.Print SQL

After your SQL = line.

Then Copy the result from the Imediate Window into the SQL view of a new query.

Run it from there.

This is a good way to find Query Problems. Such as one of the Variables not getting set.


Also as a note:
This line:
"Dim SQL, str1, str2, strDate As String"

is Dim'ing SQL, str1 and str2 as Variants

and strDate as String

Thought you might like to know that Access DIM does not work this way. To make them all strings you will have to do the following

"Dim SQL As String, str1 As String, str2 As String, strDate As String"

 

[Rich]

Can't you just use a query?