Filter using a date field? 
Author Message
 Filter using a date field?

Hi,
I am using a form as a filter with code that is available in MS sample
reports db. This form has several controls each filtering various fields on
the report. When I changed one of the controls to a date type I got an error
message that this error message "datatype mismatch in criteria expression".
With the following code, how do I include a date type. Actually, I would
like to have a date type that opened to all records if no date were selected
or if selected to filter between two dates, i.e. date1 and date2. Any help
with this is much appreciated.

Alan Schwartz

Dim strSQL As String, intCounter As Integer
     'Build SQL String
     For intCounter = 1 To 5
       If Me("Filter" & intCounter) <> "" Then
         strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " =
" & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
       End If
     Next

     If strSQL <> "" Then
        'Strip Last " And "
        strSQL = left(strSQL, (Len(strSQL) - 5))
        'Set the Filter property
        Reports![rptbilling].Filter = strSQL
        Reports![rptbilling].FilterOn = True
     End If



Mon, 25 Sep 2000 03:00:00 GMT  
 Filter using a date field?

I'm no expert on using dates in SQL strings, but the string you're
building appears to use chr(34) (") as a delimiter in all cases.

With dates, I believe you need #, not " (or is it both?).

As well, depending on what version of Access you're using, the format of
the date is critical.

Quote:

> Hi,
> I am using a form as a filter with code that is available in MS sample
> reports db. This form has several controls each filtering various fields on
> the report. When I changed one of the controls to a date type I got an error
> message that this error message "datatype mismatch in criteria expression".
> With the following code, how do I include a date type. Actually, I would
> like to have a date type that opened to all records if no date were selected
> or if selected to filter between two dates, i.e. date1 and date2. Any help
> with this is much appreciated.

> Alan Schwartz

> Dim strSQL As String, intCounter As Integer
>      'Build SQL String
>      For intCounter = 1 To 5
>        If Me("Filter" & intCounter) <> "" Then
>          strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " =
> " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
>        End If
>      Next

>      If strSQL <> "" Then
>         'Strip Last " And "
>         strSQL = left(strSQL, (Len(strSQL) - 5))
>         'Set the Filter property
>         Reports![rptbilling].Filter = strSQL
>         Reports![rptbilling].FilterOn = True
>      End If

--

Beer, Wine and Database Programming.  What could be better?
Visit "Doug Steele's Beer and Programming Emporium"
http://webhome.idirect.com/~djsteele/



Tue, 26 Sep 2000 03:00:00 GMT  
 Filter using a date field?

For Setting a criteria based on a date you have to get the SQL statement in
a format like:
[DateField]=#4/14/98#

if you are including times then the following is appropriate:
[DateField]=#4/14/98 12:00 PM#

The # must be around the date value you are searching on or you will get a
data type missmatch error.

so the code below would have to look like the following when dealing with a
date field:
strSQL=strSQL & "[" & Me("Filter" & intCounter).tag &"]" & "=#" &
Me("Filter" & intCounter) &"# And "

By the Way.... if you were dealing with numeric (not date) data type fields,
you would to use the following code:
strSQL=strSQL & "[" & Me("Filter" & intCounter).tag &"]" & "=" & Me("Filter"
& intCounter) &" And "

Quote:

>Hi,
>I am using a form as a filter with code that is available in MS sample
>reports db. This form has several controls each filtering various fields on
>the report. When I changed one of the controls to a date type I got an
error
>message that this error message "datatype mismatch in criteria expression".
>With the following code, how do I include a date type. Actually, I would
>like to have a date type that opened to all records if no date were
selected
>or if selected to filter between two dates, i.e. date1 and date2. Any help
>with this is much appreciated.

>Alan Schwartz

>Dim strSQL As String, intCounter As Integer
>     'Build SQL String
>     For intCounter = 1 To 5
>       If Me("Filter" & intCounter) <> "" Then
>         strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " =
>" & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
>       End If
>     Next

>     If strSQL <> "" Then
>        'Strip Last " And "
>        strSQL = left(strSQL, (Len(strSQL) - 5))
>        'Set the Filter property
>        Reports![rptbilling].Filter = strSQL
>        Reports![rptbilling].FilterOn = True
>     End If



Sat, 30 Sep 2000 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Filter using a date field?

2. Filtering ADO Recordset on Date Field using NON-US Locale

3. Command for filter the date field in crystal report control

4. SQL for filtering pn a Date field

5. Command for filter the date field in crystal report control

6. Command for filter the date field in crystal report control

7. Finding date field using LIKE operator using ADO

8. Filter By Form Using Subform Fields

9. Using Filter with Long Field Names

10. ADO Filtering using LIKE and numeric fields?

11. Filtering using a calculation of 2 fields?

12. Format Date field in query using VBA

 

 
Powered by phpBB® Forum Software