Date problem on custom Query By Form

[NachoMan]

Friends,
I am having a problem getting my date range to work in my search form. I have the following code to pull dates from my QBF form fields [FromDate] and [ToDate]. When I run the query, however, I keep getting Type 13 mismatch error as if I did not format the date where clause correctly.

As far as I can tell this should check to see if there is both the [ToDate] and [FromDate] fields have values, then select records that fall into the specified range. If no [ToDate] is entered, it will just select records equal to or greater than the [FromDate].

If there is anyone out there who can see the error in my code, please point it out. Thanks.

'Dates
If Not IsNull(Me![ToDate]) Then
   
    where = where & " AND [BaseReportDate] between #" + Me![FromDate] + "# AND #" & Me![ToDate] & "#"

Else
   
    where = where & " AND [BaseReportDate] >= #" + Me![FromDate] + " #"

End If

 

[jfgambit]

Can you post the whole code?

Thanks

 

[NachoMan]

Here's the whole code.

Private Sub cmdRunQuery_Click()
Dim db As DAO.Database
Dim QD As DAO.QueryDef
Dim where As Variant
Dim strSQL As String

Set db = CurrentDb()
' Delete the existing dynamic query; trap the error if the query
' does not exist.
On Error Resume Next
db.QueryDefs.Delete ("Dynamic_Query")
On Error GoTo 0

where = Null

'Report Type
If left(Me![txtReportType], 1) = "*" Or right([txtReportType], 1) = "*" Then
    where = where & " AND [ReportType] like '" + Me![txtReportType] + "'"
Else
    where = where & " AND [ReportType]= '" + Me![txtReportType] + "'"
End If

'ReportNum field
If left(Me![txtHumintReportNumber], 1) = "*" Or right([txtHumintReportNumber], 1) = "*" Then
    where = where & " AND [HumintReportNum] like '" + Me![txtHumintReportNumber] + "'"
Else
    where = where & " AND [HumintReportNum]= '" + Me![txtHumintReportNumber] + "'"
End If

'Status field
If left(Me![txtStatus], 1) = "*" Or right([txtStatus], 1) = "*" Then
    where = where & " AND [Status] like '" + Me![txtStatus] + "'"
Else
    where = where & " AND [Status]= '" + Me![txtStatus] + "'"
End If

'Event ID
If left(Me![txtEventID], 1) = "*" Or right([txtEventID], 1) = "*" Then
    where = where & " AND [EventID] like '" + Me![txtEventID] + "'"
Else
    where = where & " AND [EventID]= '" + Me![txtEventID] + "'"
End If

'Source field
If left(Me![txtSource], 1) = "*" Or right([txtSource], 1) = "*" Then
    where = where & " AND [Source] like '" + Me![txtSource] + "'"
Else
    where = where & " AND [Source]= '" + Me![txtSource] + "'"
End If

'Classification field
If left(Me![txtClassification], 1) = "*" Or right([txtClassification], 1) = "*" Then
    where = where & " AND [Classification] like '" + Me![txtClassification] + "'"
Else
    where = where & " AND [Classification]= '" + Me![txtClassification] + "'"
End If

'AOR field
If left(Me![txtAOR], 1) = "*" Or right([txtAOR], 1) = "*" Then
    where = where & " AND [AOR] like '" + Me![txtAOR] + "'"
Else
    where = where & " AND [AOR]= '" + Me![txtAOR] + "'"
End If

'OpType
If left(Me![txtOpType], 1) = "*" Or right([txtOpType], 1) = "*" Then
    where = where & " AND [OpType] like '" + Me![txtOpType] + "'"
Else
    where = where & " AND [OpType]= '" + Me![txtOpType] + "'"
End If

'TrackStatus
If left(Me![txtTrackStatus], 1) = "*" Or right([txtTrackStatus], 1) = "*" Then
    where = where & " AND [TrackStatus] like '" + Me![txtTrackStatus] + "'"
Else
    where = where & " AND [TrackStatus]= '" + Me![txtTrackStatus] + "'"
End If

'HumintDepartCountry
If left(Me![txtHumintDepartCountry], 1) = "*" Or right([txtHumintDepartCountry], 1) = "*" Then
    where = where & " AND [HumintDepartCountry] like '" + Me![txtHumintDepartCountry] + "'"
Else
    where = where & " AND [HumintDepartCountry]= '" + Me![txtHumintDepartCountry] + "'"
End If

'HumintArriveCountry
If left(Me![txtHumintArriveCountry], 1) = "*" Or right([txtHumintArriveCountry], 1) = "*" Then
    where = where & " AND [HumintArriveCountry] like '" + Me![txtHumintArriveCountry] + "'"
Else
    where = where & " AND [HumintArriveCountry]= '" + Me![txtHumintArriveCountry] + "'"
End If

'ReportText
If left(Me![txtReportText], 1) = "*" Or right([txtReportText], 1) = "*" Then
    where = where & " AND [ReportText] like '" + Me![txtReportText] + "'"
Else
    where = where & " AND [ReportText]= '" + Me![txtReportText] + "'"
End If

'Dates
If Not IsNull(Me![To Date]) Then
    where = where & " AND [BaseReportDate] between '" + Me![From Date] + " AND " & Me![To Date] + "'"

    'where = where & " AND [BaseReportDate] between #" + Me![From Date] + "# AND #" & Me![To Date] & "#"
Else
    where = where & " AND [BaseReportDate] >= '" + Me![From Date] + "'"
    'where = where & " AND [BaseReportDate] >= #" + Me![From Date] + " #"
End If

strSQL = "SELECT tblHumintCases.*, tblIntelAgencies.*, tblLogos.*, tblReportText.* FROM ((tblLogos RIGHT JOIN tblHumintCases ON tblLogos.LogoID = tblHumintCases.LogoID) LEFT JOIN tblIntelAgencies ON tblHumintCases.Source = tblIntelAgencies.AgencyName) LEFT JOIN tblReportText ON tblHumintCases.HumintID = tblReportText.HumintID " & (" where " + Mid(where, 6) & ";")
'MsgBox strSQL
Set QD = db.CreateQueryDef("Dynamic_Query", strSQL)

'Make the Search Results subform visible again.
'Show search results in subform.
Me.fsubSearchResults.Visible = True
Me.fsubSearchResults.Form.RecordSource = strSQL


End Sub

 

[NachoMan]

Got it.

Here's the solution to my problem. The date in the string needs to be formatted to match the formatting of the date field being searched against. Just have to use the Format() function to make a quick and easy mod.

'Dates
If Not IsNull(Me![To Date]) Then
 where = where & " AND [BaseReportDate] Between #" + Format(Me![From Date], "mm/dd/yyyy") + "# AND #" & Format(Me![To Date], "mm/dd/yyyy") & "#"
    'Format(Me.textStartDate, "mm/dd/yy")
Else
       where = where & " AND [BaseReportDate] >= #" + Format(Me![From Date], “mm/dd/yyyy") + "#"
End If
[CODE]

Just to be clear, if your date field is formatted short date (mm/dd/yy) then you would just format the search field like this"
Format(Me![YourQBFdatefield], "mm/dd/yy").

Incidentally, my first forum searches didn't come across this, but I later came across [URL=http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=13503&highlight=date+literals]this post[/URL] that solved my problem. Can't take any credit myself

 

[NachoMan]

A possible related number problem

Friends,
Now I'm encountering a similar problem with my lone number field on my Query By Form. I get another Type 13 mismatch error when I search on that field. I don't know if the QBF number field has to also be formatted to match the number format of the field being searched on. I have no idea how to do this. If anyone has any insight, it would be greatly appreciated.

Here is the code for the number field:

'TRNumber (number format requires no delimiters in where clause, but requires parenthesis around syntax)
If left(Me![txtTRNumber], 1) = "*" Or right([txtTRNumber], 1) = "*" Then
    where = where & (" AND [tblTranscriptDetails.TRNumber] like " + Me![txtTRNumber])
Else
    where = where & (" AND [tblTranscriptDetails.TRNumber]= " + Me![txtTRNumber])
End If

 

[raskew]

Regional settings come into play here.

When you apply format() to an actual date, the result is a string. Same goes when formatting a number.

To illustrate, try this in the debug window. (I'm in the US, so the default regional setting is "short date" (mm/dd/yy)

mydate1 = date()
? mydate1
1/17/04 

'Is mydate1 stored as a date?
? cdbl(mydate1)
 38003 
 
mydate2 = format(mydate1, "medium date")
? mydate2
17-Jan-04

'Is mydate2 stored as a date?
? cdbl(mydate2)

'ERROR: Run-time error '13'; Type Mismatch

mydate3 = datevalue(mydate2)
? mydate3
1/17/04 

'Is mydate3 stored as a date?
? cdbl(mydate3)
 38003

 

[NachoMan]

Thanks for the reply.

Ok, I'm tracking with the dates, but how do I format my number in this case? Since my number field to be searched on has a Long Integer format, would the formatting be like Format([MyQBFNumberField], "Long Integer"), or should I be using the FormatNumber() function instead? Sorry, clueless over here.

 

[NachoMan]

Got it!

Took a bit of trial and error but I eventually figured this out. Here is the answer to the number specific problem. I needed to use the FormatNumber() as I suspected. Here is the correct code.

'TRNumber (number format requires no delimiters in where clause, but requires parenthesis around syntax
'and the appropriate number formatting)
If left(Me![txtTRNumber], 1) = "*" Or right([txtTRNumber], 1) = "*" Then
    where = where & (" AND [tblTranscriptDetails.TRNumber] like " + FormatNumber(Me![txtTRNumber], 0, , , vbFalse))
Else
    where = where & (" AND [tblTranscriptDetails.TRNumber]= " + FormatNumber(Me![txtTRNumber], 0, , , vbFalse))
End If

WIthout the FormatNumber() function, Access tries to match the number in the database (10001, for example) with the its default number formatting. Even though one enters 10001 in the QBF search field, Access automatically changes it to the default formatting of 10,001.00. Use the FormatNumber() function to remove the decimal places and the comma. Works great now!

 

[NachoMan]

QBF Almost Done!

Friends,
I have one last problem to resolve and this search form will be perfect (for my needs anyway). I cannot figure how to search my two memo fields with one QDF search field. I understand how this would be accomplished in a parameter-type query, but I am not interested in doing it that way. I have the following code snippet which is incorporated into the code from above. I need help on how to correctly write the SQL where clause for my code. I have tried a few things, but nothing that searches correctly. Basically, I have a field [txtBodyAndRemarks] on my search form that I need to search both the [TranscriptBody] and [TranscriptRemarks] fields from my table. It worked fine searching the [TranscriptBody] field alone, but I can't seem to write it the correct way to search both fields.

MY KINGDOM FOR AN ANSWER TO THIS PROBLEM!!

Here is the relevant code (rest of code has been posted above).

'Body and Remarks (search both with one criteria field)
If left(Me![txtBodyAndRemarks], 1) = "*" Or right([txtBodyAndRemarks], 1) = "*" Then
    where = where & " AND [TranscriptBody] like '" + Me![txtBodyAndRemarks] + "'" Or where & " AND [TranscriptRemarks] like '" + Me![txtBodyAndRemarks] + "'"
    
Else
    where = where & " AND [TranscriptBody] = '" + Me![txtBodyAndRemarks] + "'" Or where & " AND [TranscriptRemarks]= '" + Me![txtBodyAndRemarks] + "'"
    
End If