My Query reads some dates as American dates?

[David Ball]

Hi,
I have a query that gets it’s criteria from a listbox selection. This is a date. The problem is that the query interprets some of these as “American” dates. For example, if I select 7/Apr/2017 in my list box what appears in the criteria row of the query is 4/07/2017 (4th of July). This only happens on some dates and not others.
How can I solve this problem?
Thanks very much

Dave

 

[Gasman]

Is the query built up in VBA?

If so format the date in American format as Access expects it that way.

One easy way is

Public Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Needed for dates in queries as Access expects USA format.

then just use

myDate = format(ControlDate, strcJetDate)

Saves having to remember the format of the format string

HTH

 

[CJ_London]

This only happens on some dates and not others

this is because it (it being the sql code) expects to get a date in American format -mm/dd/yyyy. But if it is not a valid American format, but is a valid uk one it will accept it

e.g.

10/7/2017 - US 7th Oct UK 10th July

31/10/2017 - US 31st Oct. UK 31st Oct

 

[MarkK]

Use a parameterized querydef, like...

   const SQL as string = "SELECT * FROM Table1 WHERE SomeDate = prm0"
   dim rst as dao.recordset

   with currentdb.createquerydef("", sql)
      .parameters(0) = [COLOR="Blue"]< your date criteria here >[/COLOR]
      set rst = .openrecordset
      .close
   end with

 

[David Ball]

Hi Gasman and Markk, thanks for the advice. Unfortunately, this is all way over my head and I have no idea where the code you suggest would go into my code. Can you please advise how it would fit into my code (shown below)? Either solution is fine with me, as long as it works.

Thanks very much.

Private Sub cmdOpenQuery5_Click()
On Error GoTo Err_cmdOpenQuery5_Click
Dim MyDB As DAO.Database
Dim qdef As DAO.QueryDef
Dim i As Integer
Dim strSQL As String
Dim strWhere As String
Dim strIN As String
Dim flgSelectAll As Boolean
Dim varItem As Variant
Set MyDB = CurrentDb()
strSQL = "SELECT * FROM qryJC_Manhours_JC_DC_SI"
'Build the IN string by looping through the listbox
For i = 0 To List9.ListCount - 1
If List9.Selected(i) Then
If List9.Column(0, i) = "All" Then
flgSelectAll = True
End If
strIN = strIN & "#" & List9.Column(0, i) & "#,"
End If
Next i
'Create the WHERE string, and strip off the last comma of the IN string
strWhere = " WHERE [Reporting Week Ending] in " & _
"(" & Left(strIN, Len(strIN) - 1) & ")"
'If "All" was selected in the listbox, don't add the WHERE condition
If Not flgSelectAll Then
strSQL = strSQL & strWhere
End If
MyDB.QueryDefs.Delete "qryJC_Manhours_JC_DC_SI_2"
Set qdef = MyDB.CreateQueryDef("qryJC_Manhours_JC_DC_SI_2", strSQL)
'Open the query, built using the IN clause to set the criteria
DoCmd.OpenReport "rptJC_Manhours_JC_DC_SI", acViewReport
'Clear listbox selection after running query
For Each varItem In Me.List9.ItemsSelected
Me.List9.Selected(varItem) = False
Next varItem

Exit_cmdOpenQuery5_Click:
Exit Sub
Err_cmdOpenQuery5_Click:
If Err.Number = 5 Then
MsgBox "You must make a selection(s) from the list" _
, , "Selection Required !"
Resume Exit_cmdOpenQuery5_Click
Else
'Write out the error and exit the sub
MsgBox Err.Description
Resume Exit_cmdOpenQuery5_Click
End If

End Sub

Dave

 

[MarkK]

In the original post the date is singular...

...from a listbox selection. This is a date.

...but the code you posted compares a list of dates.

 

[David Ball]

Sorry if I was not clear. I only need to select a single date, although the code may allow for something else.

 

[JHB]

I suppose it is the code line where you pick the date, so .
Change it from:

strIN = strIN & "#" & List9.Column(0, i) & "#,"

To

strIN = strIN & "#" & Format(List9.Column(0, i),"mm/dd/yyyy") & "#,"

 

[Gasman]

JHB has given you the way mine would be implemented.

You just use the constant defined (put that in a module somewhere) and use that as the format.
Makes the code just a little cleaner (I think). Normally I would not be appending your "," to the date.

strIN = strIN & Format(List9.Column(0, i),strcJetDate) & ","

 

[JHB]

JHB has given you the way mine would be implemented.

You've an error in here:

Public Const strcJetDate = "\#mm\/dd\/yyyy\#"

 

[Gasman]

You've an error in here:

Public Const strcJetDate = "\#mm\/dd\/yyyy\#"

Really?, what is wrong with it as it appears to work fine.?
It is being used in at least one of my databases since I discovered it.

? format(date,strcjetdate)
#06/04/2017#

 

[isladogs]

Hi Gasman
I'm confused by the backslashes in the code you posted

Public Const strcJetDate = "\#mm\/dd\/yyyy\#"

What do they mean/how do they work?

 

[Gasman]

Colin,

I have no idea. It is a piece of code i picked up googling when I was having problems with my dates as the o/p is having.

I just thought it was a neat way of being able to format dates more easily with less code in each format statement.

Hi Gasman
I'm confused by the backslashes in the code you posted

Public Const strcJetDate = "\#mm\/dd\/yyyy\#"

What do they mean/how do they work?

 

[isladogs]

Ah well... maybe someone else will be able to explain
Does it still work if you remove the backslashes?

 

[MarkK]

I think this code does about the same thing as the code you posted, but without looping thru multiple values in the list...

Private Sub cmdOpenQuery5_Click()
    Const QN As String = _
        "qryJC_Manhours_JC_DC_SI_2"
    Const SQL_BASE As String = _
        "SELECT * FROM qryJC_Manhours_JC_DC_SI "
    Const SQL_WHERE As String = _
        "WHERE [Reporting Week Ending] = p0 "
    
    Dim sql As String
    
[COLOR="Green"]    'construct the sql text[/COLOR]
    sql = SQL_BASE
    If Not Me.list9 = "All" Then sql = sql & SQL_WHERE
    
[COLOR="Green"]    'replace the existing query sql[/COLOR]
    CurrentDb.QueryDefs(QN).sql = sql
    
[COLOR="Green"]    'set the paremeter value and open the report[/COLOR]
    DoCmd.SetParameter "p0", Me.list9
    DoCmd.OpenReport "rptJC_Manhours_JC_DC_SI", acViewReport
    
[COLOR="Green"]    'clear the list selection[/COLOR]
    Me.list9 = Null
    
End Sub

You need to make sure the Listbox.MultiSelect property is set to 'None'. This code also does not delete and recreate the query as happened here...

MyDB.QueryDefs.Delete "qryJC_Manhours_JC_DC_SI_2"
Set qdef = MyDB.CreateQueryDef("qryJC_Manhours_JC_DC_SI_2", strSQL)

...instead it just modifies the SQL of the existing query...

    CurrentDb.QueryDefs(QN).sql = sql

hth
Mark

 

[Gasman]

Ah well... maybe someone else will be able to explain
Does it still work if you remove the backslashes?

No

Public Const strcJetDate = "#mm/dd/yyyy#"  'Needed for dates in queries as Access expects USA format.

? format(date,strcjetdate)
4289mm/dd/yyyy0

 

[bob fitz]

Good info about dates in Access from Allen Browne at this link: http://allenbrowne.com/ser-36.html

 

[Gasman]

Thank you Bob
I thought it might have something to do with escaping/protecting a character, but not certain.

DoCmd.OpenReport "MyReport", acViewPreview, , "InvoiceDate > #12/31/2000#"
Debug.Print DLookup("StudentID", "tblStudent", "EntryDate = #6/30/1953#")
strSQL = "SELECT * FROM tblDonation WHERE DonationDate > #" & Format(Me.StartDate, "mm\/dd\/yyyy") & "#;"

The third example demonstrates how to concatenate a date into a SQL string. The Format() function is essential to force the date into American format. Unfortunately, Format() replaces the slashes with the date separator character defined in Control Panel | Regional Settings, so you must specify literal slashes in the format string by preceding the slash with backslashes.

 

[Galaxiom]

The backslashes are "literal escapes", meaning that the subsequent character is to be interpreted exactly as typed. The backslash before the hash is fairly obvious but the one before the forward slashes deserves some explanation.

A forward slash in a format string is actually a placeholder for the regional date separator. In many regions this separator is the forward slash so the escape is not essential. However including it ensures that the format will work in all regions.

 

[David Ball]

Thanks so much everybody who has contributed to this discussion.