Date "format" switch between query sql and Criteria (1 Viewer)

[Frankie_The_Flyer]

Bit of a problem here that I can't seem to find an answer to anywhere.

I've started with a Listbox in a Form from a Table of meeting dates from which I highlight a date ane try to recall information about the meeting (past or proposed) from the main table by clicking on a button which in turn populates the criteria of a query that is then used to filter the data from the main table.

I can get the process to work apart from inputting any dates before the 12th of the month.
Selecting 1-July-2013 from the list comes up in the query Criteria as In (#7/1/2013#). However in the SQL for the same query the it appears to be correct!
WHERE (((tbl_Meeting_Dates.MeetingDate) In (#1/7/2013#)));

In the Control Panel the computer system is set to my region (Australia) with the date format shown as dd/mm/yyyy.

The Date Format in the Meeting Dates Table is set to a Short date (1-July-2013)

Any ideas how I can resolve this please as it's an important pary of making the database more user friendly

Thanks - A. Novice

 

[namliam]

query dates work as US dates, thus 1/7/2013 is actually Jan 7, not 1 Jul.

So when using the ## around dates you need to make sure to enter it in US Format, no changing that sadly...

 

[Frankie_The_Flyer]

Thanks namliam, but surely there is a way of "masking" the input so the team can still enter data in the national format? You'd still have to put the table into US but can the input be UK style

 

[Brianwarnock]

It is only in SQL and VBA that the date strings ## have to be coded in US format, if they are coded in the design grid or entered in a form field they are entered in local format.

If you code in the design grid and then switch to SQL view you will see that the system has switched the format.

I find what is happening to you confusing as the dates , having been stored in a table, will have been converted to double decimal and you will now only be displaying them , and thus they will be correct dates, and the SQL. view should show the US format.

Brian

 

[Frankie_The_Flyer]

CRACKED IT!!!!
Added in the Format bit and it puts the date in the SQL as US. That then transposes to Aussie date in Criteria!

Private Sub Command8_Click()
'Set it all up for 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_Meeting_Dates")
'Find selected data in Listbox
For Each varItem In Me!Dates_List.ItemsSelected
strCriteria = strCriteria & " #" & Me!Dates_List.ItemData(varItem) & "# "
[COLOR=red]strCriteria = Format$(Me!Dates_List.ItemData(varItem), "\#mm\/dd\/yyyy\#")[/COLOR]
Next varItem
' Message 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_Meeting_Dates " & "WHERE tbl_Meeting_Dates.Dates IN(" & strCriteria & ");"
' put it in the Query
Debug.Print strSQL
qdf.SQL = strSQL
qdf.Close
'Open the Form to view
DoCmd.Close
DoCmd.OpenForm "Frm_Meetings"
' Finish 
Set db = Nothing
Set qdf = Nothing
End Sub

Thanks for all inputs