Hi There,

I'm a total noob. I'm building a very simple "Time Track" program in Access 2007 for my company. However, I'm looking to filter a report that I want to generate. What I want to accomplish is allow a pop-up window to prompt users and let them set "Variables"/"filters" to give them control of what the report would display. What I would also need is a prompt asking date as well, but I can't figure that one out. Anyways, I found on MS website a help and support document detailing exactly what I need (except the date option) the URL is:

http://support.microsoft.com/kb/q208529

I followed the instructions to the letter several time and I keep getting a type mismatch "13" error. When I debug I get the following highlighted in yellow

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " _
& " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & "" _
And ""

I don't know how to fix it and I'm surprised because this is code right on MS website??????

Anyways, I'm attaching the file I would really appreciate it if someone would look at it and let me know what is wrong.

When you open the file, goto "Forms" and open "frmFilter"... try to select variables and then click on the "set filter" button.

Also, if you can guide me to code that would permit me to add an option to query the report by date...that would be so awesome.

example: only display results from the database from “Oct 1” to “Oct 10”... with whatever other variables that was selected.

I would really like to thank you... hopefully I can get this done, so I can enjoy the rest of my long weekend!!!!

Thanks again

-Keith

:)

I seem to find an inconsistency in that code. Even Microsoft makes typos.

Here's the code you cited:

StrSQL & "[" & Me("Filter" & intCounter).Tag & "] " _
& " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & "" _

Notice that in the first case the word "Tag" is included. In the second case "Tag" is omitted.

I'm guessing it should be omitted in both cases, but I am only a novice on Acess forms.

Well, nevermind, I don't even understand that code at all. I'll take another look.

Well, nevermind, I don't even understand that code at all. I'll take another look.

Did you happen to follow the link I provided... it show all the steps outlined by MS to pass on variables to a report.

:)

Well, nevermind, I don't even understand that code at all. I'll take another look.

Also, take a look at the atached file, it will be clear what I'm trying to accomplish... and thanks for looking into this!

What's confusing to me, again, is the Tag property. I think that's the wrong property. I am still looking...

I think the problem is that Access 2000 comboboxes had a "Tag" property that is no longer in use. However, the general concept of filtering shouldn't be too terribly difficult to reproduce.

A filter is a WHERE clause (but VBA allows you to omit the keyword WHERE) such as:

WHERE ContactName = "John Smith"

You don't need to build the filter in a loop running from 1 to 5. You can do it one combobox (cbo) at a time. Like this. Let's say you have two cbo's, one for ContactName and one for Country. Probably something l ike this

Dim quote as string
Quote = Trim(" ' ") 'single quote

if cboContactName <> "" then strSQl = strSql & " ContactName = " & quote & cboContactName & quote & " AND "

if cboCountry <> "" Then strSQl = strSql & " Country = " & quote & cboCountry & quote & " AND "

And so on, for each cbo.

By the way, I don't have Access 2007 installed so I can't read your sample. Not sure I want to, anyway. I'm just trying to point you in the right direction, at the moment.

I think the problem is that Access 2000 comboboxes had a "Tag" property that is no longer in use. However, the general concept of filtering shouldn't be too terribly difficult to reproduce.

A filter is a WHERE clause (but VBA allows you to omit the keyword WHERE) such as:

WHERE ContactName = "John Smith"

You don't need to build the filter in a loop running from 1 to 5. You can do it one combobox (cbo) at a time. Like this. Let's say you have two cbo's, one for ContactName and one for Country. Probably something l ike this

Dim quote as string
Quote = Trim(" ' ") 'single quote

if cboContactName <> "" then strSQl = strSql & " ContactName = " & quote & cboContactName & quote & " AND "

if cboCountry <> "" Then strSQl = strSql & " Country = " & quote & cboCountry & quote & " AND "

And so on, for each cbo.

Ah man... so that procedure outline by MS is not valid for Access 2007? - man, Im lost now... I need to use multi filters in a form with the ability to filter by date as well.

Please advise!

Thanks again.

-Keith

No, the genral idea is valid. See my last post. Basically, I'm just saying that you need to
(1) slightly modify the strSql statements (see my last post)
(2) forget about the loop, since at this point I don't know how to convert that loop to Access 2007.

Again, see my last post, give it a try, and then let us know what problems you have.

Members of this forum do filtering all the time, I'm sure they can help you, but we need to see you put in a little effort.

No, the genral idea is valid. See my last post. Basically, I'm just saying that you need to
(1) slightly modify the strSql statements (see my last post)
(2) forget about the loop, since at this point I don't know how to convert that loop to Access 2007.

Again, see my last post, give it a try, and then let us know what problems you have.

Members of this forum do filtering all the time, I'm sure they can help you, but we need to see you put in a little effort.

Sorry, I didn't mean I don't want to try... I will... It's just that I don't understand code much. I take bits and pieces from the web and malipulate them to fit my needs... but I never do anything too complex... I'm I over my head here? - I will try your code.

No, the genral idea is valid. See my last post. Basically, I'm just saying that you need to
(1) slightly modify the strSql statements (see my last post)
(2) forget about the loop, since at this point I don't know how to convert that loop to Access 2007.

Again, see my last post, give it a try, and then let us know what problems you have.

Members of this forum do filtering all the time, I'm sure they can help you, but we need to see you put in a little effort.

Ok I edited to the following code... so far so good, the errors are gone, but when I click on the "set Filter" button, nothing happens, no report by the variables I selected.

Private Sub Set_Filter_Click()

Dim quote As String
quote = Trim(" ' ") 'single quote
If cboFilter1 <> "" Then strSQL = strSQL & " Employee = " & quote & cboFilter1 & quote & " AND "

If cboFilter2 <> "" Then strSQL = strSQL & " Company = " & quote & cboFilter2 & quote & " AND "

If cboFilter3 <> "" Then strSQL = strSQL & " Project = " & quote & cboFilter3 & quote & " AND "

If cboFilter4 <> "" Then strSQL = strSQL & " Task = " & quote & cboFilter4 & quote & " AND "

' Set the Filter property.
Reports![Main_Database_Query].Filter = strSQL
Reports![Main_Database_Query].FilterOn = True
End Sub


Thanks for looking into this!

Before you set the filter you will need:

strSQL = Left(strSQL, Len(strSQL)-5)

to strip the last
" AND "
off

Yes, at some point make sure your filter doesn't end with AND. To check this, do something like this:

MsgBox(strSql)

And then you can use control-C to copy it from the msgbox to the clipboard, if you want to paste it here for one of us to see.

Before you set the filter you will need:

strSQL = Left(strSQL, Len(strSQL)-5)

to strip the last
" AND "
off

I get an error with:
strSQL = Left(strSQL, Len(strSQL) - 5)'

Runtime error '5':
Invalid proceedure call or argument

Why is there an apostrophe at the end?

Left(strSQL, Len(strSQL) - 5)'

should be

Left(strSQL, Len(strSQL) - 5)

Why is there an apostrophe at the end?

Left(strSQL, Len(strSQL) - 5)'

should be

Left(strSQL, Len(strSQL) - 5)


I still get the same error????

Why is there an apostrophe at the end?

Left(strSQL, Len(strSQL) - 5)'

should be

Left(strSQL, Len(strSQL) - 5)

This is how the code looks now:

Private Sub Set_Filter_Click()
Dim quote As String
quote = Trim(" ' ") 'single quote

If cboFilter1 <> "" Then strSQL = strSQL & " Employee = " & quote & cboFilter1 & quote & " AND "
If cboFilter2 <> "" Then strSQL = strSQL & " Company = " & quote & cboFilter2 & quote & " AND "
If cboFilter3 <> "" Then strSQL = strSQL & " Project = " & quote & cboFilter3 & quote & " AND "
If cboFilter4 <> "" Then strSQL = strSQL & " Task = " & quote & cboFilter4 & quote & " AND "

strSQL = Left(strSQL, Len(strSQL) - 5)

Reports![Main_Database_Query].Filter = strSQL
Reports![Main_Database_Query].FilterOn = True

End Sub

I still get the same error????

I don't know why you'd get the same error. It worked fine for me, using the following test:



Dim strSql As String
strSql = "ContactName = 'John Smith' AND "
strSql = Left(strSql, Len(strSql) - 5)
MsgBox (strSql)

Are you sure the error is on that line?

Ok, i was able to reproduce the error. The problem is that if the strSQL is empty, you get the error, maybe do this:

if Len(strSql) > 5 Then strSql = Left(strSql, Len(strSql) - 5)

I don't know why you'd get the same error. It worked fine for me, using the following test:



Dim strSql As String
strSql = "ContactName = 'John Smith' AND "
strSql = Left(strSql, Len(strSql) - 5)
MsgBox (strSql)


It only works, and does not produce an error if this line of code is added (without it, I get the error)
strSql = "ContactName = 'John Smith' AND "


This is how the code looks now.

Private Sub Set_Filter_Click()
Dim quote As String
Dim strSql As String
quote = Trim(" ' ") 'single quote

If cboFilter1 <> "" Then strSql = strSql & " Employee = " & quote & cboFilter1 & quote & " AND "
If cboFilter2 <> "" Then strSql = strSql & " Company = " & quote & cboFilter2 & quote & " AND "
If cboFilter3 <> "" Then strSql = strSql & " Project = " & quote & cboFilter3 & quote & " AND "
If cboFilter4 <> "" Then strSql = strSql & " Task = " & quote & cboFilter4 & quote & " AND "
strSql = "Employee = 'Keith Beland' AND "
strSql = Left(strSql, Len(strSql) - 5)
MsgBox (strSql)
Reports![Main_Database_Query].Filter = strSql
Reports![Main_Database_Query].FilterOn = True
End Sub

Ok, i was able to reproduce the error. The problem is that if the strSQL is empty, you get the error, maybe do this:

if Len(strSql) > 5 Then strSql = Left(strSql, Len(strSql) - 5)

Ok, that worked... I no longer get the error - however, nothing happens now, the report does not get filtered????

Ok, i was able to reproduce the error. The problem is that if the strSQL is empty, you get the error, maybe do this:

if Len(strSql) > 5 Then strSql = Left(strSql, Len(strSql) - 5)


Ok it now works... I mis-titled the combo boxes... it was labled cboFilter1, but should be only "Filter1"

Im testing right now.

Ok it now works... I mis-titled the combo boxes... it was labled cboFilter1, but should be only "Filter1"

Im testing right now.

OMG!!!!!! - It works.... I would like to thank you so much!!!

Now on to my next problem, I need to be able to filter the report by a dates, i.e. enter a range, example Oct 1 to Oct 10, and only show those results, of course with the variables seleted by the combo boxes.

How do I begin?

OMG!!!!!! - It works.... I would like to thank you so much!!!

Now on to my next problem, I need to be able to filter the report by a dates, i.e. enter a range, example Oct 1 to Oct 10, and only show those results, of course with the variables seleted by the combo boxes.

How do I begin?

Basically the same thing you already did, wouldn't you say?

Probably have two textboxes, txtStartDate and txtEndDate.

And then suppose your dateColumn is called OrderDate

Dim startDate as Date, endDate as Date
If Len(txtstartDate) > 0 or Len(txtEndDate) > 0 Then
On Error Goto InvalidDate
startDate = CDate(txtStartDate)
endDate = CDate(txtEndDate)
InvalidDate: if err.NumBer > 0 Then
MsgBox("Bad date.")
exit sub
end if
strSql = StrSql & " OrderDate >= " & quote & startDate & quote & " Order Date <= " & quote & endDate & Quote & " AND "
On Error Goto 0 'resets it to regular error mode

End if


Something like that, but I didn't test it.

I made some changes. Hope I got it right this time.

Basically the same thing you already did, wouldn't you say?

Probably have two textboxes, txtStartDate and txtEndDate.

And then suppose your dateColumn is called OrderDate

Dim startDate as Date, endDate as Date
If Len(txtstartDate) > 0 or Len(txtEndDate) > 0 Then
On Error Goto InvalidDate
startDate = CDate(txtStartDate)
endDate = CDate(txtEndDate)
InvalidDate: if err.NumBer > 0 Then
MsgBox("Bad date.")
exit sub
end if
strSql = StrSql & " OrderDate >= " & quote & startDate & quote & " Order Date <= " & quote & endDate & Quote & " AND "
On Error Goto 0 'resets it to regular error mode

End if


Something like that, but I didn't test it.


Thank you... we are getting close...

Here is how the code looks like now.

Private Sub Set_Filter_Click()
Dim quote As String
Dim strSql As String
quote = Trim(" ' ") 'single quote

If Filter1 <> "" Then strSql = strSql & " Employee = " & quote & Filter1 & quote & " AND "
If Filter2 <> "" Then strSql = strSql & " Company = " & quote & Filter2 & quote & " AND "
If Filter3 <> "" Then strSql = strSql & " Project = " & quote & Filter3 & quote & " AND "
If Filter4 <> "" Then strSql = strSql & " Task = " & quote & Filter4 & quote & " AND "
Dim startDate As Date, endDate As Date
If Len(txtStartDate) > 0 Or Len(txtEndDate) > 0 Then
On Error GoTo InvalidDate
startDate = CDate(txtStartDate)
endDate = CDate(txtEndDate)
InvalidDate: If Err.Number > 0 Then
MsgBox ("Bad date.")
Exit Sub
End If
strSql = strSql & " Date >= " & quote & startDate & quote & " Date <= " & quote & endDate & quote & " AND "
On Error GoTo 0 'resets it to regular error mode
End If
If Len(strSql) > 5 Then strSql = Left(strSql, Len(strSql) - 5)
Reports![Main_Database_Query].Filter = strSql
Reports![Main_Database_Query].FilterOn = True



When I execute... I get the folowing error:

"Syntax error (missing operator) in querry expression '(Date >='01/10/2008' Date <='01/10/2008')' "

strSql = strSql & " Date >= " & quote & startDate & quote & " Date <= " & quote & endDate & quote & " AND "

Sorry, I missed an AND.

Try this:

strSql = strSql & " Date >= " & quote & startDate & quote & " AND Date <= " & quote & endDate & quote & " AND "

Also, if your column is called "Date" that's proabably a reserved keyword. Somehow you will need to get brackets in there so it reads as:

[Date]

otherwise it conflicts with reserved keyword

For dates you have to use the # delimiter and you shouldn't have a field named date because that is an Access reserved word. So you have to put it in brackets.

strSql = strSql & " [Date] >=#" & startDate & "# And [Date] <=#" & endDate & "# AND "

Also, if your column is called "Date" that's proabably a reserved keyword. Somehow you will need to get brackets in there so it reads as:

[Date]

otherwise it conflicts with reserved keyword

Ok this is confusing as when I try to change the date column in the table... an error comes up - it won't let me.

Ok this is confusing as when I try to change the date column in the table... an error comes up - it won't let me.

Did you try Bob's advice?

Ok this is confusing as when I try to change the date column in the table... an error comes up - it won't let me.

What error message?

By the way I wasn't asking you to put brackets in the column design view. I was asking you to add them to your strSql.

By the way I wasn't asking you to put brackets in the column design view. I was asking you to add them to your strSql.

I'm sorry, misunderstood.

For dates you have to use the # delimiter and you shouldn't have a field named date because that is an Access reserved word. So you have to put it in brackets.

strSql = strSql & " [Date] >=#" & startDate & "# And [Date] <=#" & endDate & "# AND "


This worked... OMG thanks you both!!!!!

Only one small problem left.... Filter by date works like a charm except when I use single digits for the "Day", perhaps this might happen for month as well - I will test.

example.... I enter start date (dd/mm/yyyy) "10/10/2008" end date "31/10/2008" and the filter works great, but If I use the following, start date "01/10/2008 and end date "09/10/2008" - it blanks out the report.... as if there is no entry within those variables... I checked my database and data should be there in the report... weird eh?

I will do more testing, you do you think the problem is?


Again, thank you both for your help - you guys are awesome!

Also, I will study all the code you share, so I know how to do this on my own... later on today, after I get this resolved, I will purchase a book... any recommendations?

Okay, for the SQL statement you need to use U.S. formatted dates (mm/dd/yyyy). So you should probably encapsulate the date field in the SQL with

Format(startDate,"mm/dd/yyyy")

etc.

As far as books, I like Safari books online, because

(1) It's cheap. Order a monthly subscription and then cancel or renew at any time (about $10 per month).

(2) You can copy and paste code snippets into your notes (if you change the view mode to HTML).

Okay, for the SQL statement you need to use U.S. formatted dates (mm/dd/yyyy). So you should probably encapsulate the date field in the SQL with

Format(startDate,"mm/dd/yyyy")

etc.

This line of code? :
strSql = strSql & " [Date] >=#" & startDate & "# And [Date] <=#" & endDate & "# AND "

Okay, for the SQL statement you need to use U.S. formatted dates (mm/dd/yyyy). So you should probably encapsulate the date field in the SQL with

Format(startDate,"mm/dd/yyyy")

etc.

Ok this is how the code looks like now: - It didn't work, still blank report.

Private Sub Set_Filter_Click()
Dim quote As String
Dim strSql As String
quote = Trim(" ' ") 'single quote

If Filter1 <> "" Then strSql = strSql & " Employee = " & quote & Filter1 & quote & " AND "
If Filter2 <> "" Then strSql = strSql & " Company = " & quote & Filter2 & quote & " AND "
If Filter3 <> "" Then strSql = strSql & " Project = " & quote & Filter3 & quote & " AND "
If Filter4 <> "" Then strSql = strSql & " Task = " & quote & Filter4 & quote & " AND "
Dim startDate As Date, endDate As Date
If Len(txtStartDate) > 0 Or Len(txtEndDate) > 0 Then
On Error GoTo InvalidDate
startDate = CDate(txtStartDate)
endDate = CDate(txtEndDate)
InvalidDate: If Err.Number > 0 Then
MsgBox ("Bad date.")
Exit Sub
End If
strSql = strSql & " [Date] >=#" & Format(startDate, "mm/dd/yyyy") & "# And [Date] <=#" & endDate & "# AND "
On Error GoTo 0 'resets it to regular error mode
End If
If Len(strSql) > 5 Then strSql = Left(strSql, Len(strSql) - 5)
Reports![Main_Database_Query].Filter = strSql
Reports![Main_Database_Query].FilterOn = True

You didn't format the end date too. I just did the one because I figured you would automatically do the other one too. Sorry for assuming such. Make sure to format the end date also.

You didn't format the end date too. I just did the one because I figured you would automatically do the other one too. Sorry for assuming such. Make sure to format the end date also.

My apologies... should of caught that.

Anyways, everything is working as it should... thank you...

I'm curious though, why doesn't it like dd/mm/yyyy?

It's a Microsoft thing. It started off in the U.S. and they never made it work with regional settings for some reason. Perhaps someone like Pat Hartman, Brent (datAdrenaline) or Leigh Purvis would know.