I have a search form used to search through reservations. One option is to search a range of dates for, say, the client's arrival date. So, you can enter a start date only, and the results will show all reservations with an arrival date >= to that date. If you enter a start and end date, the results will show all reservations where the arrival date is between those two dates (inclusive).
The code goes on to take care of some other fields, but no point in putting all that in here...
All works well minus one little problem. Where I live we write the date dd/mm/yyyy. My computer language settings are set that way, and all the dates fields perform just that way, perfect.
However, for this search field if I enter the date in that form, the computer sees the date in the form mm/dd/yyyy unless that is not possible due to the values. In other words, a date of 05/01/2008 is 1-May-2008 in the search field. But if that format is not possible...15/01/2008...the computer reads the date the way i want it to, 15-January-2008.
But I want the date to always be seen as dd/mm/yyyy, even for dates like 05/01/2008. Currently the Format property for the search field in the form is set to nothing at all. If I change this to Short Date, which is what I want, the code returns this error "Run-time error '13': Type Mismatch."
The format of the field in the table is set to Short Date. The search field references a query, but the format for all the fields in the query is just blank, even though their respective fields in the original tables is set correctly. I assume that just means it carries over the format but i really dont know. All that is automatically done when i created the query. I made no changes there.
I thought i solved the problem long ago when I created an input mask for the field, which works well for the user, but returns the same problem i mentioned.
Thanks a ton for the help!!
Dillon
Code:
Option Compare Database
Private Sub cmdReservations_Search_Click()
On Error GoTo ErrorHandlingCode
Dim db As Database
Dim QD As QueryDef
Dim where As Variant
Set db = CurrentDb()
' Delete the existing dynamic query; trap the error if the query does
' not exist.
On Error Resume Next
db.QueryDefs.Delete ("dynqryReservations_SearchResults_CO")
db.QueryDefs.Delete ("dynqryReservations_SearchResults_Countries")
db.QueryDefs.Delete ("dynqryReservations_SearchResults_Operators")
db.QueryDefs.Delete ("dynqryReservations_SearchResults")
DoCmd.Close acForm, "dynfrmReservations_SearchResults", acSaveNo
DoCmd.Minimize
On Error GoTo 0
' Note that there are no type-casting characters surrounding the
' following numeric fields.
where = Null
where = where & " AND [ReservationID]= " + Me![ReservationIDSearchText]
where = where & " AND [EmployeeID]= " + Me![EmployeeIDSearchText]
where = where & " AND [ReservationStatusID]= " + Me![ReservationStatusIDSearchText]
' The following evaluates the date search information.
' Note the number signs (#) surrounding the date fields.
If Not IsNull(Me![ArrivalEndDateSearchText]) Then
where = where & " AND [ArrivalDate] between #" + Me![ArrivalStartDateSearchText] + "# AND #" & Me![ArrivalEndDateSearchText] & "#"
Else
where = where & " AND [ArrivalDate] >= #" + Me![ArrivalStartDateSearchText] + " #"
End If
If Not IsNull(Me![ReturnEndDateSearchText]) Then
where = where & " AND [ReturnDate] between #" + Me![ReturnStartDateSearchText] + "# AND #" & Me![ReturnEndDateSearchText] & "#"
Else
where = where & " AND [ReturnDate] >= #" + Me![ReturnStartDateSearchText] + " #"
End If
Set QD = db.CreateQueryDef("dynqryReservations_SearchResults", _
"Select * from qryReservations_Search " & (" where " + Mid(where, 6) & ";"))
DoCmd.OpenForm "dynfrmReservations_SearchResults", acFormDS
Exit Sub
The code goes on to take care of some other fields, but no point in putting all that in here...
All works well minus one little problem. Where I live we write the date dd/mm/yyyy. My computer language settings are set that way, and all the dates fields perform just that way, perfect.
However, for this search field if I enter the date in that form, the computer sees the date in the form mm/dd/yyyy unless that is not possible due to the values. In other words, a date of 05/01/2008 is 1-May-2008 in the search field. But if that format is not possible...15/01/2008...the computer reads the date the way i want it to, 15-January-2008.
But I want the date to always be seen as dd/mm/yyyy, even for dates like 05/01/2008. Currently the Format property for the search field in the form is set to nothing at all. If I change this to Short Date, which is what I want, the code returns this error "Run-time error '13': Type Mismatch."
The format of the field in the table is set to Short Date. The search field references a query, but the format for all the fields in the query is just blank, even though their respective fields in the original tables is set correctly. I assume that just means it carries over the format but i really dont know. All that is automatically done when i created the query. I made no changes there.
I thought i solved the problem long ago when I created an input mask for the field, which works well for the user, but returns the same problem i mentioned.
Thanks a ton for the help!!
Dillon