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

kengooch

Member
Local time
Today, 03:43
Joined
Feb 29, 2012
Messages
137
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

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

I’m here to help
Staff member
Local time
Today, 03:43
Joined
Oct 29, 2018
Messages
21,358
Try:
Code:
DoCmd.OpenReport "rAccLogComplete", acViewReport, WhereCondition:="[tDateAcc] >= #" & Format(vDateSt, "yyyy-mm-dd") & "# AND [tDateAcc] <= #" & Format(vDateEnd, "yyyy-mm-dd") & "# And [tPathLstNm]='" & [vGetPatho] & "'"
 

kengooch

Member
Local time
Today, 03:43
Joined
Feb 29, 2012
Messages
137
Try:
Code:
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

Nifty Access Guy
Staff member
Local time
Today, 10:43
Joined
Jul 9, 2003
Messages
16,245
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:-

 

kengooch

Member
Local time
Today, 03:43
Joined
Feb 29, 2012
Messages
137
Try:
Code:
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

I’m here to help
Staff member
Local time
Today, 03:43
Joined
Oct 29, 2018
Messages
21,358
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

Super Moderator
Staff member
Local time
Today, 06:43
Joined
Feb 19, 2002
Messages
42,981
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.

Code:
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.
Code:
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

Member
Local time
Today, 03:43
Joined
Feb 29, 2012
Messages
137
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.

Code:
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.
Code:
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

Member
Local time
Today, 03:43
Joined
Feb 29, 2012
Messages
137
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.

strWhere.PNG
 

KitaYama

Well-known member
Local time
Today, 19:43
Joined
Jan 6, 2022
Messages
1,490
You don't have a = sign after strWhere
strWhere = "[tDateAcc] .....
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:43
Joined
Oct 29, 2018
Messages
21,358
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

Well-known member
Local time
Today, 19:43
Joined
Jan 6, 2022
Messages
1,490
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.

 

Users who are viewing this thread

Top Bottom