Multiple filters on report

[automaticbaby]

Is there a way to have multiple filters on one report? I have a report that I would like to filter by date and by User ID. I can only get it to work with the date, but not the User ID.

I'm using the following code for the date:

DoCmd.OpenReport "Transaction Report", acPreview, , "[Date] between #" & Format(Me.StartDate, "mm\/dd\/yyyy&quot & "# And #" & Format(Me.EndDate, "mm\/dd\/yyyy&quot & "#"

When I use the following code for the User ID and rem out the above code, the program asks me to enter Me.User_ID:

DoCmd.OpenReport "Transaction Report", acPreview, , "[User ID] = Me.User_ID"

When I try try to use Me.Filter it doesn't work either. I created the following code:

Me.Filter = "User ID = User_ID"
Me.FilterOn = True

I've also added Me.Filter = "User ID = User_ID" to the filter of the report, but it doesn't work.

Anyone know if I can make this work?

Thanks.

 

[AnalystDBA]

Try this automaticbaby

DoCmd.OpenReport "Transaction Report", acPreview, , "[User ID] = '" & Me.User_ID & "'"

You can also assign the filter to a variable and then open the report with the following code.

Dim FilterCriteria as string

FilterCriteria = "[User ID] = '" & Me.User_ID & "'"

DoCmd.OpenReport "Transaction Report", acPreview, , FilterCriteria

If the User_ID is a numeric then you will need to omit the single quotes around the User_ID in the FilterCriteria string

eg. FilterCriteria = "[User ID] = " & Me.User_ID

Hope this helps.

Cheers
AnalystDBA

 

[PassingBy]

AnalystDBA; I too have been having extreme frustration with this type of command line.

Can you extend your example a little to show the syntax required for mixed data types within a WHERE part of the docmd

How for example would you incorporate AutomaticBaby's date selection
"[Date] between #" & Format(Me.StartDate, "mm\/dd\/yyyy&quot & "# And #" & Format(Me.EndDate, "mm\/dd\/yyyy&quot & "#"

within your example below?

DoCmd.OpenReport "Transaction Report", acPreview, , "[User ID] = '" & Me.User_ID & "'"

Hope you can help
Martin