open an access report filtered from a form (1 Viewer)

[kengooch]

I have a form that has 2 unbound text boxes in which I enter vDateSt and the other one vDateEnd I also have a third combobox that looks up the last name of a Pathologist vGetPatho. I have a report that is designed to show all records between the two above dates and the select Pathologist. I have the following code

DoCmd.OpenReport "rAccLogComplete", acViewReport, WhereCondition:="[tDateAcc] >= #" & Format(vDateSt, "yyyy-mm-dd") & "# AND [tDateAcc] <= #" & Format(vDateEnd, "yyyy-mm-dd") & "# And [vGetPatho] = [tPathLstNm]"

When I run this, it pops up a box and asks me for the value for vGetPatho, which is the item selected in the Drop Down Combo Box. If I type in the name of the Pathologist that I selected in the box, the report pops right up perfectly. So somehow the vGetPatho value is not getting assigned. I'm wondering if it is because it is a combo box and the bound value is not the last name but rather a numeric unique key.

Any suggestions?

 

[theDBguy]

Try:

DoCmd.OpenReport "rAccLogComplete", acViewReport, WhereCondition:="[tDateAcc] >= #" & Format(vDateSt, "yyyy-mm-dd") & "# AND [tDateAcc] <= #" & Format(vDateEnd, "yyyy-mm-dd") & "# And [tPathLstNm]='" & [vGetPatho] & "'"

 

[kengooch]

Try:

DoCmd.OpenReport "rAccLogComplete", acViewReport, WhereCondition:="[tDateAcc] >= #" & Format(vDateSt, "yyyy-mm-dd") & "# AND [tDateAcc] <= #" & Format(vDateEnd, "yyyy-mm-dd") & "# And [tPathLstNm]='" & [vGetPatho] & "'"

Works perfectly!!! I guess it's the single quotes that cause it to recognize the value correctly?... thanks so much for the solution.

 

[Uncle Gizmo]

In this YouTube video I demonstrate how to pass a filter into a Report from a Form no code required in the Report:-

I have a blog on filtering which may be of interest:-

Easily Apply Filters - Nifty Access

Filtering - MS Access Forms This video should be of interest to anyone who wants to easily apply filters to an MS Access form in datasheet view or continuous form view. In this demonstration I show how you can apply filters in a Form presented in Continuous view and also in Datasheet view. When you

www.niftyaccess.com

 

[kengooch]

Try:

DoCmd.OpenReport "rAccLogComplete", acViewReport, WhereCondition:="[tDateAcc] >= #" & Format(vDateSt, "yyyy-mm-dd") & "# AND [tDateAcc] <= #" & Format(vDateEnd, "yyyy-mm-dd") & "# And [tPathLstNm]='" & [vGetPatho] & "'"

Just dawned on me that sometimes I want the list of all Pathologist... is there a way to add a "*" in the drop down list that will read as any value or a way to adjust the equation to accomplish that?

 

[theDBguy]

Just dawned on me that sometimes I want the list of all Pathologist... is there a way to add a "*" in the drop down list that will read as any value or a way to adjust the equation to accomplish that?

Yes, you could either add "All" or assume an empty one means to show all.

 

[Pat Hartman]

I guess it's the single quotes that cause it to recognize the value correctly?

No, they are necessary but the problem was that you had your variable name embedded within the string.

Debugging Advice:
When building WHERE strings or SQL strings in VBA, always build them into a variable rather than directly in the expression. That lets you stop the code and print the string to the immediate window. You will almost always see the error once you see the constructed string.

Dim strWhere as String
strWhere "[tDateAcc] >= #" & Format(vDateSt, "yyyy-mm-dd") & "# AND [tDateAcc] <= #" & Format(vDateEnd, "yyyy-mm-dd") & "# And [tPathLstNm]='" & [vGetPatho] & "'"
DoCmd.OpenReport "rAccLogComplete", acViewReport, WhereCondition:=strWhere

That lets you see the string you are trying to use. To really understand this lesson, build your old string and print it too.

This technique will lead you to the answer to your follow on question.

strWhere "[tDateAcc] >= #" & Format(vDateSt, "yyyy-mm-dd") & "# AND [tDateAcc] <= #" & Format(vDateEnd, "yyyy-mm-dd") & "#"
If vGetPatho & "" <> "" Then
    strWhere = strWhere & " And [tPathLstNm]='" & [vGetPatho] & "'"
End If

 

[kengooch]

No, they are necessary but the problem was that you had your variable name embedded within the string.

Debugging Advice:
When building WHERE strings or SQL strings in VBA, always build them into a variable rather than directly in the expression. That lets you stop the code and print the string to the immediate window. You will almost always see the error once you see the constructed string.

Dim strWhere as String
strWhere "[tDateAcc] >= #" & Format(vDateSt, "yyyy-mm-dd") & "# AND [tDateAcc] <= #" & Format(vDateEnd, "yyyy-mm-dd") & "# And [tPathLstNm]='" & [vGetPatho] & "'"
DoCmd.OpenReport "rAccLogComplete", acViewReport, WhereCondition:=strWhere

That lets you see the string you are trying to use. To really understand this lesson, build your old string and print it too.

This technique will lead you to the answer to your follow on question.

strWhere "[tDateAcc] >= #" & Format(vDateSt, "yyyy-mm-dd") & "# AND [tDateAcc] <= #" & Format(vDateEnd, "yyyy-mm-dd") & "#"
If vGetPatho & "" <> "" Then
    strWhere = strWhere & " And [tPathLstNm]='" & [vGetPatho] & "'"
End If

I wondered why some people would code like that. It honestly never dawned on me to do that!! This is a VERY GOOD idea!!

 

[kengooch]

So, when I add this code to the database I get a Compile error.
I thought that by adding a Dim StrWhere as String would fix it, but it didn't.

 

[SHANEMAC51]

I thought that by adding a Dim StrWhere as String would fix it, but it didn't.

dim strWhere as string
strWhere = .....

 

[kengooch]

dim strWhere as string
strWhere = .....

If you look at the uploaded picture, that is my 3rd line of code and for whatever reason it gives me an error

 

[KitaYama]

You don't have a = sign after strWhere
strWhere = "[tDateAcc] .....

 

[kengooch]

OOPS!!!! What a dummy! thanks!!

Yes, you could either add "All" or assume an empty one means to show all.

where would I add the "All"?

 

[theDBguy]

OOPS!!!! What a dummy! thanks!!

where would I add the "All"?

In the RowSource of your Combobox. Then, you'll have to adjust your query criteria to handle it.

 

[KitaYama]

OOPS!!!! What a dummy! thanks!!

where would I add the "All"?

There are several ways. I use a union query. You can also use vba.

How to Add "All" or "N/A" Options to Combo box - iAccessWorld.com

How to Add <All> or <N/A> Options to Combo box A combo box is well known in the MS Access form. The combo box can be a Lookup field from a table. You can see more details at: How to Create Lookup Field in Table for MS Access here. The combo box can also be a stand-alone combo box on the Access...

www.iaccessworld.com