Taking a date from a list box and entering it into a Query in VB

[Frankie_The_Flyer]

Hi Accessers!

Another little thing that is driving me insane and that I can't find an answer to on the forum or through Google!

I have a process that lifts a highlighted date from a List Box and puts it in the Criteria of a query. The process manages to move the numbers from one place to another, but ends up giving me a data mismatch in the query.
The process is as follows

Private Sub Command8_Click()
'Set it all up for Panel Meeting selection
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
'Identify Query
Set db = CurrentDb()
Set qdf = db.QueryDefs("Qry_Panel_Dates")

'Find selected data in Listbox
For Each varItem In Me!Dates_List.ItemsSelected
strCriteria = strCriteria & " '" & Me!Dates_List.ItemData(varItem) & "' "
Next varItem

' select all if no selection has been made
If Len(strCriteria) = 0 Then
MsgBox "You did not select anything from the list" _
, vbExclamation, "Nothing to find!"
Exit Sub
End If

'Prepare String for query
strSQL = "SELECT * FROM tbl_Panel_Meeting_Dates " & "WHERE tbl_Panel_Meeting_Dates.PanelDates IN (" & strCriteria & ");"

' put it in the Query
Debug.Print strSQL
qdf.SQL = strSQL
qdf.Close

'Open the query to view
DoCmd.Close
DoCmd.OpenQuery "Qry_Panel_Meeting_Date_List_Selected"

' Finish Macro
Set db = Nothing
Set qdf = Nothing
End Sub

(NOTE the DoCmd.OpenQuery is highlighted in Yellow)

strCriteria (which comes up as I hover over the IN bit of the WHERE) shows strCriteria= " '1/07/2013' " which is the correct date selected.


The Immideate debug shows...
SELECT * FROM tbl_Panel_Meeting_Dates WHERE tbl_Panel_Meeting_Dates.PanelDates IN ( '1/07/2013' );

I'm pretty sure (althiough always stand to be corrected!) that I need it to come out as

In (#1/07/2013#)

How can this be achieved please??

 

[bob fitz]

Just a guess. Have you tried:
strCriteria = strCriteria & " #" & Me!Dates_List.ItemData(varItem) & "# "

 

[Frankie_The_Flyer]

Works a treat thanks Bob. Just need to sort out how I get the date into "English" rather than American now!!
Actually, I've just noticed that the Immediate shows the date coming out in English. The date in the query is mm/dd/yyyy though

 

[Frankie_The_Flyer]

New Problem.... If I enter a date that is below the 12th of the month (i.e. 1 July 2013), the date that appears in the sql / Immediate is correct, but the date iin the query criteria comes out in the incorrect format. (07/01/2013) If I enter a date that is (say) 23 September, it goes into the query correctly as 23/9/13.

(I'm starting to hate this...........!!!)

 

[bob fitz]

Perhaps the following link will help: http://allenbrowne.com/ser-36.html

 

[Frankie_The_Flyer]

Almost Bob, but it seems to just miss the point. My regional settings are for Australia; the information coming out of the Code / process is in the correct format (dd/mmm/yyyy - as seen in Immidiate); the information shown in the sql of the query is in American format (mm/dd/yyy - as it says it would be), but what comes out in the Criteria in the query is almost random between US and English date formats depending on the size of the number in the dd or mm. It appears that Access just swaps between dates formats.
There are some examples of how you would ensure the date can be used in any part of the world, but aside from knowing where to stick them (keep the answers clean please!!), I don't think this is the problem.
Somehow I need to control the output of the query sql to ensure it always stays in the dd/mm/yyy format