August is a problem

[Guest]

I have built a set of forms that query my table for a date and the
application/query works fine for all dates except the ones from August.

The field/column can be either a short date type (01-Aug-05) or a simple
type (01/08/05) and in both cases the July date works fine and the August
date does not fetch back any records. The following is the sub routine:

Private Sub cmdTestQuery_Click()
Dim cnn As ADODB.Connection
Dim intCounter As Integer
Dim response As String

If Me.txtQuery.Value = "Select * from Station where " Then
response = MsgBox("There appears to be no parameters selected."
+ Chr(13) + Chr(13) + " Would you like to see all the records?", vbYesNo,
"Query String Error")
If response = vbNo Then
Me.cmdTestQuery.Visible = False
Exit Sub
Else
strQueryString = "Select * from Station"

End If
End If

Set cnn = CurrentProject.Connection
objRS.Open strQueryString, cnn, adOpenKeyset, adLockOptimistic, adCmdText
intCounter = objRS.RecordCount

If intCounter <= 0 Then
MsgBox "There are no records for this query, please try again.",
vbOKOnly, "Query Return : 0"
Call formCleanUp
Call reSetForm
ElseIf intCounter = 1 Then
If objRS.Fields("stationid").Value = "" Or
IsNull(objRS.Fields("stationid")) Then
MsgBox "There are no records for this query, please try again.",
vbOKOnly, "Query Return : 0"
Call formCleanUp
Call reSetForm
End If
response = MsgBox("There is " & CStr(intCounter) & " record for this
query. Would you like to view it?.", vbYesNo, "Query Return : " & intCounter)
If response = vbNo Then
objRS.Close
Exit Sub
End If
Call openFormDetails(strQueryString, "frmStation", 0)
Else
response = MsgBox("There are " & CStr(intCounter) & " records for this
query." & Chr(13) & " Would you like to view these records in a form view?.",
vbYesNo, "Query Return : " & intCounter)
If response = vbNo Then
Call openFormDetails(strQueryString, "frmStation", 3)
Else
Call openFormDetails(strQueryString, "frmStation", 0)
End If
End If

cnn.Close
Set objRS = Nothing
Call hideButton


So the question being is there something about august or is it a matter that
the Access SQL client thinks I am looking for the American date January 8th?

 

[Guest]

What part is evaluating the date you are entering?

 

[Guest]

Try declaring the parameter. A parameter value in short date format dd/mm/yy
or mm/dd/yy might otherwise be interpreted as an arithmetic expression and
evaluate to whatever date the result of the expression represents, though why
it would only do so for August dates, and why it would do so if the parameter
is entered in the format dd-mmm-yyyy, if that is also the case, is a mystery.
You can declare the parameter in query design view via the parameters
dialogue or in SQL view:

PARAMETERS [Forms]![YourForm]![YourControl DATETIME;
SELECT ………

Ken Sheridan
Stafford, England