US/International Dates

[Tom]

Ladies and Gents

I live in Australia and am developing an Access database which
hopefully will have international appeal. So we come to the subject of
US date conversion.
The following SQL code is used to within calculations. Hopefully it is
self explanatory:

INSERT INTO tblPirepsReliability1 ( ClientID, AircraftTypeID,
ATAChaptID )
SELECT tblAircraftReliability1.ClientID,
tblAircraftReliability1.AircraftTypeID,
tblAircraftReliability1.ATAChaptID
FROM tblAircraftReliability1
WHERE (((tblAircraftReliability1.PIREPDate) Between #" &
Format(Me.DateFrom, "mm\/dd\/yyyy") & "#
And #" & Format(Me.DateTo, "mm\/dd\/yyyy") & "#))
GROUP BY tblAircraftReliability1.ClientID,
tblAircraftReliability1.AircraftTypeID,
tblAircraftReliability1.ATAChaptID
HAVING (((tblAircraftReliability1.ClientID)=" & intClientID & ")
AND ((tblAircraftReliability1.AircraftTypeID)=" & intAircraftTypeID &
")
AND ((tblAircraftReliability1.ATAChaptID)>0));

I have read everything in sight including Allen Browne's essay on the
subject and have moved the # sign around as displayed on variations.
The result is always the same "syntax error" in the "DateFrom" part.
Interestingly the code won't run the first time but hit the same
action button again on the form on which the code resides and all is
well.
BTW I am not strong in SQL or VBA coding so a bit of detail would be
appreciated.
Your advice please


I am not strong

 

[Jeanette Cunningham]

Tom,
I am also in Australia and I always use this function for most date things.
Put the function in a standard module.

-------start code
Public Function SQLDate(varDate As Variant) As String
'Purpose: Return a delimited string in the date format used natively by
JET SQL.
'Argument: A date/time value.
'Note: Returns just the date format if the argument has no time
component,
' or a date/time format if it does.
'Author: Allen Browne. (e-mail address removed), June 2006.

If IsDate(varDate) Then
If DateValue(varDate) = varDate Then
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
Else
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
End If
End If

End Function
-------end code

Here is how I use the function (an Allen Browne example)

'Date field example. Use the format string to add the # delimiters and
get the right international format.
If Not IsNull(Me.txtFilterStartDate) Then
strWhere = strWhere & "([EnteredOn] >= " &
SQLDate(Me.txtFilterStartDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.txtFilterEndDate) Then 'Less than the next day.
strWhere = strWhere & "([EnteredOn] < " &
SQLDate(Me.txtFilterEndDate + 1) & ") AND "
End If

So the relevant part of your code becomes

WHERE tblAircraftReliability1.PIREPDate >= " & SQLDate(dteStart) & " AND
tblAircraftReliability1.PIREPDate < " & SQLDate(dteEnd) & "


Watch the line wraps, to see without the line wraps, you can copy the lines
and paste into Notepad.
You may find that you need to adjust the double quotes to
fit with the rest of the sql string.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Tom]

It appears that you may have missing spaces as well but could be the browser
word wrap

Tom wrote:

INSERT INTO tblPirepsReliability1 ( ClientID, AircraftTypeID,
ATAChaptID )
SELECT tblAircraftReliability1.ClientID,
tblAircraftReliability1.AircraftTypeID,
tblAircraftReliability1.ATAChaptID
FROM tblAircraftReliability1
 WHERE (((tblAircraftReliability1.PIREPDate) Between #" &
Format(Me.DateFrom, "mm\/dd\/yyyy") & "#
 And #" & Format(Me.DateTo, "mm\/dd\/yyyy") & "#))"
& " GROUP BY tblAircraftReliability1.ClientID,
tblAircraftReliability1.AircraftTypeID,
tblAircraftReliability1.ATAChaptID"
& " HAVING (((tblAircraftReliability1.ClientID)=" & intClientID & ")
AND ((tblAircraftReliability1.AircraftTypeID)=" & intAircraftTypeID & ")
 AND ((tblAircraftReliability1.ATAChaptID)>0));"

Thank you Jeanette and Vandal for your assistance.

Tom