Filtering By Date

  • Thread starter Thread starter Bruce Rodtnick
  • Start date Start date
B

Bruce Rodtnick

I'm filtering a report using some modified code from FrmSmp00.mdb. This
database has a form with combo boxes on it that filetr as you go through
all the boxes. But on my report I also want to use a date field
(Workshop Date) to filter the report. I'm getting a "type mismatch"
error when I do this. I've narrowed the problem down to the fact that I
need to add an # to the beginning and the end of the date, but I don't
know what ascii code to use for #. Here is a modified sample of my
code:

strSQL = "[" & Me.Filter3.Tag & "] " = " & Chr(34) & Chr(35) &
Me.Filter3 & Chr(35) & Chr(34)"
'Set the Filter property
Reports![WorkshopAttendance].Filter = strSQL
Reports![WorkshopAttendance].FilterOn = True

Filter3 tag is Workshop Date so what I want to come out of this is
"[Workshop Date] = "#2/12/2004#""

.....I think. Can someone help

Bruce Rodtnick
 
Bruce said:
I'm filtering a report using some modified code from FrmSmp00.mdb. This
database has a form with combo boxes on it that filetr as you go through
all the boxes. But on my report I also want to use a date field
(Workshop Date) to filter the report. I'm getting a "type mismatch"
error when I do this. I've narrowed the problem down to the fact that I
need to add an # to the beginning and the end of the date, but I don't
know what ascii code to use for #. Here is a modified sample of my
code:

strSQL = "[" & Me.Filter3.Tag & "] " = " & Chr(34) & Chr(35) &
Me.Filter3 & Chr(35) & Chr(34)"
'Set the Filter property
Reports![WorkshopAttendance].Filter = strSQL
Reports![WorkshopAttendance].FilterOn = True

Filter3 tag is Workshop Date so what I want to come out of this is
"[Workshop Date] = "#2/12/2004#""
Click to expand...


You have way too many quotes in there. What you really want
the filter to end with is this:
[Workshop Date] = #2/12/2004#

So, try this:
strSQL = "[" & Me.Filter3.Tag & "] = #" & Me.Filter3 & "#"
 
Got it! Thanks

B

Marshall said:
Bruce said:
I'm filtering a report using some modified code from FrmSmp00.mdb. This
database has a form with combo boxes on it that filetr as you go through
all the boxes. But on my report I also want to use a date field
(Workshop Date) to filter the report. I'm getting a "type mismatch"
error when I do this. I've narrowed the problem down to the fact that I
need to add an # to the beginning and the end of the date, but I don't
know what ascii code to use for #. Here is a modified sample of my
code:

strSQL = "[" & Me.Filter3.Tag & "] " = " & Chr(34) & Chr(35) &
Me.Filter3 & Chr(35) & Chr(34)"
'Set the Filter property
Reports![WorkshopAttendance].Filter = strSQL
Reports![WorkshopAttendance].FilterOn = True

Filter3 tag is Workshop Date so what I want to come out of this is
"[Workshop Date] = "#2/12/2004#""
Click to expand...

You have way too many quotes in there. What you really want
the filter to end with is this:
[Workshop Date] = #2/12/2004#

So, try this:
strSQL = "[" & Me.Filter3.Tag & "] = #" & Me.Filter3 & "#"
Click to expand...
 
Back
Top