Something wrong with MS sample code (VB) Access 2007 (1 Viewer)

[piedpiper8]

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

 

[jal]

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.

 

[jal]

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

 

[piedpiper8]

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.

 

[piedpiper8]

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!

 

[jal]

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

 

[jal]

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.

 

[jal]

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.

 

[piedpiper8]

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

 

[jal]

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.

 

[piedpiper8]

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.

 

[piedpiper8]

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!

 

[boblarson]

Before you set the filter you will need:

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

to strip the last
" AND "
off

 

[jal]

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.

 

[piedpiper8]

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

 

[boblarson]

Why is there an apostrophe at the end?

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

should be

Left(strSQL, Len(strSQL) - 5)

 

[piedpiper8]

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????

 

[piedpiper8]

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

 

[jal]

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)

 

[jal]

Are you sure the error is on that line?