Between dates works once then goes wrong

[hawkesms]

Hi

I am using the following vba / SQL to create a query which looks between two dates in a table. It seems to work first time but thereafter my query pulls back dates which are not in the criteria (dates are selected from Combo boxes - search_chasestartdate and search_chaseenddate):

If (Search_ChaseStartDate <> "" And Search_ChaseEndDate <> "") And intPosition = 0 Then
strParameter = strParameter & "Sales.Chase between #" & Search_ChaseStartDate & "#And#" _
& Search_ChaseEndDate & "# "
intPosition = 1
Else
If (Search_ChaseStartDate <> "" And Search_ChaseEndDate <> "") And intPosition = 1 Then
strParameter = strParameter & "AND Sales.Chase between #" & Search_ChaseStartDate & "#And#" _
& Search_ChaseEndDate & "# "
End If
End If

If strParameter <> "" Then
strSQL = "SELECT Sales.* FROM Sales WHERE " & strParameter & ";"
Set qdf = dbs.CreateQueryDef("Business Master Query", strSQL)
DoCmd.OpenQuery "Business Master Query", acViewNormal, acReadOnly
Else
MsgBox "You have not selected any parameters to search on. Please try again.", vbOKOnly
End If

Does anyone have any idea why this is happening? I have been stuck for a while on this.


Thanks!

Mark

 

[RuralGuy]

Use MsgBox "[" & strParameter & "]" to look at your string and see if it looks as you expect.

 

[hawkesms]

Yes getting back what expected

Sales.Chase between #16/08/2004# And #02/11/2004#

but after first run i then get dates from June???

 

[RuralGuy]

Are you having a problem with the difference between US dates and UK dates? You may have to use the Format() function that is described here.

 

[Pat Hartman]

Also, "" is NOT a null. "" is a zero-length string. The best way to check for valid dates is to use the IsDate() function:

IsDate(Search_ChaseStartDate) And IsDate(Search_ChaseEndDate)

 

[hawkesms]

Thanks all!! All works great!

This is the first real project I have done in Access so really appreaciate all the help!

Mark