Dates as variables in an SQL statement

[Peter Bellamy]

Can someone please give some guidance on how to construct an SQL that is taking two dates from a form.
The section I can't get right is:

....Between dStart AND dFinish......

dStart and dFinish show in the Locals windows correctly as #01/01/2009# and #12/01/2009#

But I cannot get the right syntax!

Cheers

 

[boblarson]

If this is in a query then it would be

SELECT WhateverField, WhatOtherField
FROM TableName
WHERE DateFieldName Between [Forms]![FormName]![dStart] And [Forms]![FormName]![dFinish]

 

[Peter Bellamy]

Thanks for your reply Bob.
Yes they are in a SELECT statement which will be used to filter a report.

The dates are entries into unbound controls on the form.
I have already read them in and checked them for null entries with

If IsNull(Me.StartDate.Value) Then
dStart = "01/01/2000"
Else
dStart = Me.StartDate.Value
End If

If IsNull(Me.FinishDate.Value) Then
dFinish = Format(Now(), dd & "/" & mm & "/" & yyyy)
Else
dFinish = Me.FinishDate.Value
End If
To provide 'all' dates if none are entered.

I have tried ....Between [dStart] AND [dFinish]...... and it did not work!

 

[boblarson]

What is your SQL Statement and are you doing that in code?

 

[Peter Bellamy]

The code is:
strSQL = "SELECT [Analysis Report Query 2].[Post Code], [Analysis Report Query 2].[Visit No], [Analysis Report Query 2].[Serial No], [Analysis Report Query 2].[Date Of Visit], [Analysis Report Query 2].SerialNo, [Analysis Report Query 2].[Job Complete], [Analysis Report Query 2].[Equipment Type], [Analysis Report Query 2].[Heater Model], [Analysis Report Query 2].[Cover Model], [Analysis Report Query 2].[Spa Model]" & _
" FROM [Analysis Report Query 2]" & _
" WHERE ((([Analysis Report Query 2].[Date Of Visit]) Between [dStart] And [dFinish] AND (([Analysis Report Query 2].[Job Complete])=0) AND (([Analysis Report Query 2].[Equipment Type])='Heater')) AND (([Analysis Report Query 2].[Heater Model]) Like '*'));"

Hope that reads ok in the forum window!

 

[boblarson]

Very obvious about the problem. Your variables need to be OUTSIDE of the quotes.

So, this line:

" WHERE ((([Analysis Report Query 2].[Date Of Visit]) Between [dStart] And [dFinish] AND (([Analysis Report Query 2].[Job Complete])=0) AND (([Analysis Report Query 2].[Equipment Type])='Heater')) AND (([Analysis Report Query 2].[Heater Model]) Like '*'));"

Should be

" WHERE ((([Analysis Report Query 2].[Date Of Visit]) Between #" & dStart & "# And #" & dFinish & "# AND (([Analysis Report Query 2].[Job Complete])=0) AND (([Analysis Report Query 2].[Equipment Type])='Heater')) AND (([Analysis Report Query 2].[Heater Model]) Like '*'));"

 

[Peter Bellamy]

Thanks that is it!
It that a rule, variables are always outside quotes?

 

[boblarson]

Thanks that is it!
It that a rule, variables are always outside quotes?

Yes, for variables, yes. For form references - sometimes it needs to be but it can be done most all of the time and I do it always for form references as well as for variables. But I was shown that you don't, in some cases, need to have the form reference outside but I prefer to not have to worry about remembering when it needs to be and when not as it should always work if it is outside.

 

[Peter Bellamy]

Cheers, I'll try and remember that!

 

[Galaxiom]

Getting quotes right is not so much a matter of remembering but understanding how they are structured. In statements including comparisons with nested strings and string argumented functions along with variables can be particularly confusing. Sometime double quotes need to be escaped. This can lead to as may as four in a row which can be quite bewildering.

Juste remember quotes have a paired partner somewhere in the expression and work in from the edges. The expression can be reducted by evaluating the subexpression and literals in a series of steps. Continue to write out the statement until there are no more quotes.

 

[Peter Bellamy]

Ta.
It isn't the count of quotes that trips me, it is knowing to use them!
One more question.

It looks like I have to reformat the dates to mmddyy, is that right?

Cheers

 

[boblarson]

Getting quotes right is not so much a matter of remembering but understanding how they are structured.

That's not quite what I was getting at. What I was saying is that you can use:

Dim strSQL As String

strSQL = "SELECT * FROM TableName WHERE FieldName=[Forms]![FormName]![TextBoxName]"

DoCmd.RunSQL strSQL

and it will work with the form reference INSIDE the quotes. But, I always use them outside of them like:

strSQL = "SELECT * FROM TableName WHERE FieldName=" & [Forms]![FormName]![TextBoxName]

because that works in all cases where leaving them within the quotes doesn't work in things like the DLookup, DCount, etc. The resolution of the values of the control is what we're doing when we have things out of the quotes.

As for the number of quotes, if I need quotes around an object I use the Chr(34) character instead of quotes so I end up with

=" & Chr(34) & Forms!FormName.TextBoxName & Chr(34)

instead of

=" & """ & Forms!FormName.TextBoxName & """

because I find the extra quotes are sometimes hard to read within a lot of code and Chr(34) is a single character and easier to manage.

 

[Peter Bellamy]

Thanks.
I was actually replying to G.. Athome, but the info is helpfull.

I tend to alternate " with ' to make them more readable, but that is probably because I am not good at typing and less is better!

Cheers

 

[boblarson]

Thanks.
I was actually replying to G.. Athome, but the info is helpfull.

I tend to alternate " with ' to make them more readable, but that is probably because I am not good at typing and less is better!

Cheers

Using single quotes, depending on the data can also get you in trouble. So, I tend to avoid them for surrounding field information or criteria for field information because if that field has single quotes in it, the code will BARF and throw an error. So I will, most of the time, use the CHR(34) character in place of a single quote as well for criteria.

 

[boblarson]

It looks like I have to reformat the dates to mmddyy, is that right?

Yes, format the dates to US format (thanks to Microsoft) when querying data.