problems filtering by date

[adaniele]

i have created a new form based on a database called RptSmp97 from microsoft knowledge DB.
My form, as in the sample, calls a report when it is opened and at the same time shows you a multicombo form for filtering the report. In the sample the form uses only text fields, but i need to use numeric and date fields too. I could solve the problem with the numeric combos adding a condition for them, however, when i add another condition for the date it doesnt work.
Also, at the end of the code i print the sql and it looks perfect.

here is the code when the apply filter button is pressed....

Private Sub Command18_Click()
Dim strSQL As String
Dim intCounter As Integer

'Build SQL String
For intCounter = 1 To 15
If Me("Filter" & intCounter) <> "" Then
MsgBox Me("Filter" & intCounter).Tag
If Me("Filter" & intCounter).Tag = "effective_dt" Or Me("Filter" &
intCounter).Tag = "issue_eff" Then
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & "
>= (" & Me("Filter" & intCounter) & ") And "
End If
If Me("Filter" & intCounter).Tag = "change_id" Or Me("Filter" & intCounter).Tag = "priority" Or Me("Filter" & intCounter).Tag = "Dom" Or Me("Filter" & intCounter).Tag = "Intl" Or Me("Filter" & intCounter).Tag = "Tasman" Or Me("Filter" & intCounter).Tag = "Regional" Or Me("Filter" & intCounter).Tag = "AA" Then
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " = " & Me("Filter" & intCounter) & " And "
End If
If Me("Filter" & intCounter).Tag = "name" Or Me("Filter" & intCounter).Tag = "status" Or Me("Filter" & intCounter).Tag = "assignee" Or Me("Filter" & intCounter).Tag = "app_status" Then
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
End If
End If
Next

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

End Sub

thank you in advance, max.

 

[adaniele]

here is the sample db...

 

[adaniele]

more data

Here is more info:
i discovered that the problem is in the report and not in the form, 'cos....if i put the filter on in the report and i filter by the effective_dt field, it doesnt work.
The report gets info from the table where all the data is stored.
The effective_dt field is a date/time field with a short date format, and the following mask 00/00/0000.

thx a lot.

 

[WayneRyan]

adaniele,

Where's the attachment?

Can't read the code. When you post code:

(code)
Some Code ...
(/code)

Change the parentheses to square brackets. I didn't do that just to
illustrate what it should look like.

Wayne

 

[adaniele]

sorry about the db but it was too big. now here is the sample db. Also, i added a date field in the table and modify the last combo to be the field combo.
thx

 

[adaniele]

help

i need some help please ....... thx

 

[adaniele]

the solution....

to solve this problem i just added the "#" before and after the value.
ex.
(code)
If Me("Filter" & intCounter).Tag = "Teffective_dt" Then
strSQL = strSQL & "[" & Me("Filter5").Tag & "] " & " <= " & "#" & Me("Filter" & intCounter) & "#" & " And "
End If
(/code)
thx, max.