Hi

I've created an SQL query where I imput a dates, check records that have been updated between the dates, then export results to an Excel spreadsheet, however, my query always uses an American date eg. If I input 1/06/2004 and 01/07/2004, I get no results but if I input 06/01/2004 and 07/01/2004 I get records displayed in Excel, although display in UK format in the date field.

Can any one help as to how I can input an English date? I've checked my settings - all UK.

SQL uses US settings, and that's about all there is to say.
If you've got all your settings to be UK, then the Design View of a query will show your UK date format (i.e. dd/mm/yyyy), but the SQL itself will still be in US format (i.e. mm/dd/yyyy)
Are you creating/altering the query via VBA (i.e. writing the actual SQL), or just using a parameter query?
If you're actually writing the SQL through VBA, you'll need to convert the format entered to the format needed, such as:
Mid(sInputDate, 4,2) & "/" & Left(sInputDate, 2) & "/" & Right(sInputDate,4)
assuming the date is input as dd/mm/yyyy

HTH

Steven

I am a loud man with a very large hat. This means I am in charge

Hello,

In addition to Jet SQL requiring US dates, also note that any values delimited by # in VBA code will also be converted to US dates. That can cause a little weird behavior when using VBA to pass parameters (see below).

Re: Inputting UK dates

Another way to enable inputting UK dates would be to call a short wrapper function anytime you are building a SQL WHERE clause. The function recieves a UK date and returns a US formatted date:

In a standard module...

' Format dates as US dates
Function JetSQLDate(varDate As Variant) As String
    If IsDate(varDate) Then
        JetSQLDate = "#" & Format$(varDate, "mm\/dd\/yyyy") & "#"
    End If
End Function

Then if you wanted to open a report based on a query, for example, you could build a WHERE clause with values pulled from a date range dialog, and call the wrapper function in your WHERE clause string:

' date range dialog module
Private Sub cmdOK_Click()

    Dim strWhere As String

    ' Input dates as UK
    If Not IsNull(Me.txtBeginDate) Or Not IsNull(Me.txtEndDate) Then

        ' Format dates as US, opens correct resultset.
        strWhere = "OrderDate >= " & JetSQLDate(Me.txtBeginDate) & _
                   " AND OrderDate <= " & JetSQLDate(Me.txtEndDate)

    End If
    DoCmd.OpenReport "rptOrders", acViewPreview, , strWhere
    DoCmd.Close acForm, "frmDateRange"
End Sub



Re: # delimited parameters in VBA

On the # delimiter issue, say you have a procedure definition like:

Sub Test(BeginDate As Date, EndDate As Date)

and you pass in the following UK dates (July 1, July2):

Call Test(#1/7/2004#, #2/7/2004#)

If you're regional settings are set to UK English:

BeginDate = 7/1/2004 (Jan 7 UK)
End Date = 7/2/2004 (Feb 7 UK)

The values delmited by # were first converted to US dates, and then your regional settings formatted them as their UK equivalent once you passed them to the sub.

In other words, you can't pass # delimited UK dates as parameters in VBA (unless you then reformat them as UK dates.)

For example, the following will work when called with:

Call Test(#1/7/2004#, #2/7/2004#) [UK dates]

Sub Test(BeginDate As Date, EndDate As Date)
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim strSQL As String
    Dim strCriteria As String

    Set cnn = CurrentProject.Connection
    Set rst = New ADODB.Recordset

    ' Reformat date parameters.
    If Not IsNull(BeginDate) And Not IsNull(EndDate) Then
        strCriteria = "WHERE tblOrders.OrderDate " & _
                      "BETWEEN " & UKDate(BeginDate) & _
                      " AND " & UKDate(EndDate)
    End If

    strSQL = "SELECT * FROM tblOrders " & strCriteria

    rst.Open strSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText

    Debug.Print rst.GetString(adClipString, , ";")

End Sub

' Format dates as UK dates
Function UKDate(varDate As Variant) As String
    If IsDate(varDate) Then
        UKDate = "#" & Format$(varDate, "dd\/mm\/yyyy") & "#"
    End If
End Function

HTH,

Bob

You may also want to look at the system value formats for dates in Control Panel, Regional Settings to see if you can make an easier change there. But rmemeber that any chaage there will also affect all other programs on your system.

Hi

Many thanks for the replies and code. Problem now . Didn't realise SQL's default is American standard. Used function to convert to format - works like a dream.