onClick Event - Search Using Dynamic Query (1 Viewer)

dillonhh

Registered User.
Local time
Today, 05:57
Joined
Nov 2, 2007
Messages
38
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).
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
 

boblarson

Smeghead
Local time
Today, 04:57
Joined
Jan 12, 2001
Messages
32,059
Just reformat the date (behind the scenes) to feed to the queries in U.S. date format (I think you'll find it will work for you).

Also, just a suggestion but I think you should change your concatenation character from + to & as + has the tendency to mess things up with strings if they have dates or numbers in them. While it is said that you can use + to concatenate, and a lot of the time it works, the ampersand (&) is much more consistent.
 

dillonhh

Registered User.
Local time
Today, 05:57
Joined
Nov 2, 2007
Messages
38
Just reformat the date (behind the scenes) to feed to the queries in U.S. date format (I think you'll find it will work for you).

How would I go about doing this? Do you mean change to the non-US date format, because I do not want US format, I want the other, not sure if it has a name..rest-of-the-world format.

And thanks for the tip, I will change the +'s.

Appreciate your help, Dillon
 

boblarson

Smeghead
Local time
Today, 04:57
Joined
Jan 12, 2001
Messages
32,059
I didn't say you had to display it in US format but SQL queries NEED it in US format (check for other posts on the subject here and you'll see).

Code:
...
If Not IsNull(Me![ReturnEndDateSearchText]) Then
    where = where & " AND Format([ReturnDate],"m/d/yyyy") between #" & Format(Me![ReturnStartDateSearchText],"m/d/yyyy") & "# AND #" & Format(Me![ReturnEndDateSearchText],"m/d/yyyy") & "#"
Else
    where = where & " AND Format([ReturnDate],"m/d/yyyy") >= #" & Format(Me![ReturnStartDateSearchText],"m/d/yyyy") & " #"
End If
 

dillonhh

Registered User.
Local time
Today, 05:57
Joined
Nov 2, 2007
Messages
38
Ok great, thanks very much, I will look into that some more. Much appreciated!
 

dillonhh

Registered User.
Local time
Today, 05:57
Joined
Nov 2, 2007
Messages
38
Yes, that indeed solved the problem! Thanks a lot!!


However, instead of formatting both the record field and the search field like this:

Code:
...
If Not IsNull(Me![ReturnEndDateSearchText]) Then
    where = where & " AND Format([ReturnDate],"m/d/yyyy") between #" & Format(Me![ReturnStartDateSearchText],"m/d/yyyy") & "# AND #" & Format(Me![ReturnEndDateSearchText],"m/d/yyyy") & "#"
Else
    where = where & " AND Format([ReturnDate],"m/d/yyyy") >= #" & Format(Me![ReturnStartDateSearchText],"m/d/yyyy") & " #"
End If

I only formatted the search field, like this:

Code:
...
If Not IsNull(Me![ReturnEndDateSearchText]) Then
    where = where & " AND [ReturnDate] between #" & Format(Me![ReturnStartDateSearchText],"m/d/yyyy") & "# AND #" & Format(Me![ReturnEndDateSearchText],"m/d/yyyy") & "#"
Else
    where = where & " AND [ReturnDate] >= #" & Format(Me![ReturnStartDateSearchText],"m/d/yyyy") & " #"
End If

I was getting errors formatting both of the fields. Just wanted to point that out in case someone searches this thread someday...

Dillon
 

boblarson

Smeghead
Local time
Today, 04:57
Joined
Jan 12, 2001
Messages
32,059
Yes, that indeed solved the problem! Thanks a lot!!

Happy we could help and my apologies for the fact that you had to do it this way. I think it would be better if they would have designed things so that the date format in regional settings would do the work for you. But, what can I say...
 

dillonhh

Registered User.
Local time
Today, 05:57
Joined
Nov 2, 2007
Messages
38
I think it would be better if they would have designed things so that the date format in regional settings would do the work for you. But, what can I say...

Ya I definitely agree, pretty weird, I am still a little confused about it, but now I know, so thanks for the instruction!

d
 

Users who are viewing this thread

Top Bottom