multiple criteria for parameter query that creates a report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have several forms that use the same parameter query to open a report.
From the form I want to send three parameters to a query to generate the
report, however, I do not want to specify the form as the forms could be
different. In what format do I send the parameters, and how do I set up the
query to accept the parameters that I am sending. I use a button to create
the report and set the three criteria I need to send to the query.

So far I have tried


Dim ProjNum As String
Dim VenNum As String
Dim RevuDt As Date

ProjNum = Me.ProjectID
VenNum = Me.VendorID
RevuDt = Me.ReviewDate

If Me.OSHA1 > 0 Or Me.OSHA2 > 0 Or Me.OSHA3 > 0 Or Me.OSHA4 > 0 Or Me.OSHA5
0 Then
Click to expand...
DoCmd.OpenReport "OSHArptProj", acviewPrint, "", [ProjectID] = ProjNum & ";"
& [VendorID] = VenNum & ";" & [ReviewDate] = RevuDt, acNormal
End If

But this does not work. (Be gentle I am just learning)

I have no idea what to put in the query for criteria. Everything I read
says base it on the form, but I can't because different forms use the query.
Also, I have 51 different buttons to create 51 different reports or I would
create duplicate queries for the different forms.

Any and all help would be aprreciated!!!

Thanks in advance.
 
It would be better to have no parameters in your query at all and use the
Where argument of the OpenReport method.

Dim strWhere As String

strWhere = "[ProjNum] = '" & Me.ProjectID & "' And [VenNum] = " &
Me.VendorID & " And [RevuDt = #" & Me.ReviewDate & "#"

Docmd.OpenReport "MyReport", , ,strWhere

The above syntax assumes ProjNum is text, VenNum is numeric, and RevuDt is
Date. You will have to adjust the syntax to accomodate your actual data
types.
--
Dave Hargis, Microsoft Access MVP


MoonBlosm said:
I have several forms that use the same parameter query to open a report.
From the form I want to send three parameters to a query to generate the
report, however, I do not want to specify the form as the forms could be
different. In what format do I send the parameters, and how do I set up the
query to accept the parameters that I am sending. I use a button to create
the report and set the three criteria I need to send to the query.

So far I have tried


Dim ProjNum As String
Dim VenNum As String
Dim RevuDt As Date

ProjNum = Me.ProjectID
VenNum = Me.VendorID
RevuDt = Me.ReviewDate

If Me.OSHA1 > 0 Or Me.OSHA2 > 0 Or Me.OSHA3 > 0 Or Me.OSHA4 > 0 Or Me.OSHA5
0 Then
Click to expand...
DoCmd.OpenReport "OSHArptProj", acviewPrint, "", [ProjectID] = ProjNum & ";"
& [VendorID] = VenNum & ";" & [ReviewDate] = RevuDt, acNormal
End If

But this does not work. (Be gentle I am just learning)

I have no idea what to put in the query for criteria. Everything I read
says base it on the form, but I can't because different forms use the query.
Also, I have 51 different buttons to create 51 different reports or I would
create duplicate queries for the different forms.

Any and all help would be aprreciated!!!

Thanks in advance.
Click to expand...
 
MoonBlosm said:
I have several forms that use the same parameter query to open a report.
From the form I want to send three parameters to a query to generate the
report, however, I do not want to specify the form as the forms could be
different. In what format do I send the parameters, and how do I set up the
query to accept the parameters that I am sending. I use a button to create
the report and set the three criteria I need to send to the query.

So far I have tried

Dim ProjNum As String
Dim VenNum As String
Dim RevuDt As Date

ProjNum = Me.ProjectID
VenNum = Me.VendorID
RevuDt = Me.ReviewDate

If Me.OSHA1 > 0 Or Me.OSHA2 > 0 Or Me.OSHA3 > 0 Or Me.OSHA4 > 0 Or Me.OSHA5
0 Then
Click to expand...
DoCmd.OpenReport "OSHArptProj", acviewPrint, "", [ProjectID] = ProjNum & ";"
& [VendorID] = VenNum & ";" & [ReviewDate] = RevuDt, acNormal
End If

But this does not work. (Be gentle I am just learning)

I have no idea what to put in the query for criteria. Everything I read
says base it on the form, but I can't because different forms use the query.
Also, I have 51 different buttons to create 51 different reports or I would
create duplicate queries for the different forms.
Click to expand...


You never said what the query parmeters are used for, but in
general, the way to do that is to remove the parameter
criteria from the query. You are already using the
WhereCondition argument in the OpenReport method so I'm
pretty sure the only other thing you need to do is fix the
syntax. I think all you need is:

If Me.OSHA1 > 0 Or Me.OSHA2 > 0 Or Me.OSHA3 > 0 _
Or Me.OSHA4 > 0 Or Me.OSHA5 > 0 Then
DoCmd.OpenReport "OSHArptProj", acviewNormal, , _
"ProjectID = " & Me.ProjectID & _
" And VendorID = " & Me.VendorID & _
" And ReviewDate = " & _
Format(Me.ReviewDate, "\#yyyy\/m\/d\#")
End If
 
Thank you thank you thank you thank you... I can not thank you enough... if I
could, I would send you a drink (your choice) through the wire!

This is exactly what I needed!!!

You are wonderful and helpful and I am about to fall outta my chair I am so
happy (can you tell I have been wrestling with this problem for awhile!)

Thank you again!!!

Klatuu said:
It would be better to have no parameters in your query at all and use the
Where argument of the OpenReport method.

Dim strWhere As String

strWhere = "[ProjNum] = '" & Me.ProjectID & "' And [VenNum] = " &
Me.VendorID & " And [RevuDt = #" & Me.ReviewDate & "#"

Docmd.OpenReport "MyReport", , ,strWhere

The above syntax assumes ProjNum is text, VenNum is numeric, and RevuDt is
Date. You will have to adjust the syntax to accomodate your actual data
types.
--
Dave Hargis, Microsoft Access MVP


MoonBlosm said:
I have several forms that use the same parameter query to open a report.
From the form I want to send three parameters to a query to generate the
report, however, I do not want to specify the form as the forms could be
different. In what format do I send the parameters, and how do I set up the
query to accept the parameters that I am sending. I use a button to create
the report and set the three criteria I need to send to the query.

So far I have tried


Dim ProjNum As String
Dim VenNum As String
Dim RevuDt As Date

ProjNum = Me.ProjectID
VenNum = Me.VendorID
RevuDt = Me.ReviewDate

If Me.OSHA1 > 0 Or Me.OSHA2 > 0 Or Me.OSHA3 > 0 Or Me.OSHA4 > 0 Or Me.OSHA5
0 Then
Click to expand...
DoCmd.OpenReport "OSHArptProj", acviewPrint, "", [ProjectID] = ProjNum & ";"
& [VendorID] = VenNum & ";" & [ReviewDate] = RevuDt, acNormal
End If

But this does not work. (Be gentle I am just learning)

I have no idea what to put in the query for criteria. Everything I read
says base it on the form, but I can't because different forms use the query.
Also, I have 51 different buttons to create 51 different reports or I would
create duplicate queries for the different forms.

Any and all help would be aprreciated!!!

Thanks in advance.
Click to expand...
Click to expand...
 
Glad I could help
--
Dave Hargis, Microsoft Access MVP


MoonBlosm said:
Thank you thank you thank you thank you... I can not thank you enough... if I
could, I would send you a drink (your choice) through the wire!

This is exactly what I needed!!!

You are wonderful and helpful and I am about to fall outta my chair I am so
happy (can you tell I have been wrestling with this problem for awhile!)

Thank you again!!!

Klatuu said:
It would be better to have no parameters in your query at all and use the
Where argument of the OpenReport method.

Dim strWhere As String

strWhere = "[ProjNum] = '" & Me.ProjectID & "' And [VenNum] = " &
Me.VendorID & " And [RevuDt = #" & Me.ReviewDate & "#"

Docmd.OpenReport "MyReport", , ,strWhere

The above syntax assumes ProjNum is text, VenNum is numeric, and RevuDt is
Date. You will have to adjust the syntax to accomodate your actual data
types.
--
Dave Hargis, Microsoft Access MVP


MoonBlosm said:
I have several forms that use the same parameter query to open a report.
From the form I want to send three parameters to a query to generate the
report, however, I do not want to specify the form as the forms could be
different. In what format do I send the parameters, and how do I set up the
query to accept the parameters that I am sending. I use a button to create
the report and set the three criteria I need to send to the query.

So far I have tried


Dim ProjNum As String
Dim VenNum As String
Dim RevuDt As Date

ProjNum = Me.ProjectID
VenNum = Me.VendorID
RevuDt = Me.ReviewDate

If Me.OSHA1 > 0 Or Me.OSHA2 > 0 Or Me.OSHA3 > 0 Or Me.OSHA4 > 0 Or Me.OSHA5
0 Then
DoCmd.OpenReport "OSHArptProj", acviewPrint, "", [ProjectID] = ProjNum & ";"
& [VendorID] = VenNum & ";" & [ReviewDate] = RevuDt, acNormal
End If

But this does not work. (Be gentle I am just learning)

I have no idea what to put in the query for criteria. Everything I read
says base it on the form, but I can't because different forms use the query.
Also, I have 51 different buttons to create 51 different reports or I would
create duplicate queries for the different forms.

Any and all help would be aprreciated!!!

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top