setting up parameter to recognize a bank field

A

Amy Schmid

Good morning,

I have the following query:

SELECT dbo_Clients.Cltnum, dbo_Clients.Cltname, dbo_Clients.Cltsort,
dbo_MM_Fixed_Fee_Payment_Info.[FFRetainer$],
dbo_MM_Fixed_Fee_Payment_Info.FFPayCode, dbo_Clients.Engfye,
dbo_MM_Fixed_Fee_Payment_Info.FFAgreementYear,
dbo_MM_Fixed_Fee_Payment_Info.FFDraftDate,
dbo_MM_Fixed_Fee_Payment_Info.FFPropsalDate,
dbo_MM_Fixed_Fee_Payment_Info.FFProposalSigned,
dbo_MM_Fixed_Fee_Payment_Info.FFPlanStartDate,
dbo_MM_Fixed_Fee_Payment_Info.FFPlanComplDate,
dbo_MM_Fixed_Fee_Payment_Info.FFComment, dbo_Clients.Cltoff,
dbo_Clients.CPPFname, dbo_Clients.CBMFname, dbo_Clients.CTRFname
FROM (dbo_Clients LEFT JOIN dbo_MM_Fixed_Fee_Payment_Info ON dbo_Clients.ID
= dbo_MM_Fixed_Fee_Payment_Info.FFCltID) INNER JOIN dbo_Office ON
dbo_Clients.Cltoff = dbo_Office.OffID;

I would like to set a parameter that lets me choose one of three options:
Option 1:
FFDraftDate - where this field is filled in but the FFProposalDate and the
FFProposalSigned fields are blank.

Option 2:
FFDraftDate and FFProposalDate - where these fields are filled in but the
FFProposalSigned field is blank.

Option 3:
Where all three fields are filled in an no blanks.

I also need to specify the FFAgreementYear for each option.

We currently have a copy of this query set up to require the AgreementYear,
PayCode are required. But this parameter does not require looking at blank
fields.

Any way this is possible? My final goal is to have three separate reports
based on each of the three fields and a chosen agreement year, showing all
PayCode types .
 
K

Klatuu

When you say report, are you saying the query is the record source for a
report you want to filter?

If so, then you can use the Where argument of the OpenReport method to
filter the report whichever way you want to see it. I would suggest an
Option Group control with 3 buttons to select how you want the report
filtered. Then, typically I use 2 command buttons for the user to select the
output, one for preview and one for print. Both buttons call the same sub to
output the report and the sub's argument determines how it is output. So the
Preview button would be like:

PrivateSub cmdPreviewRpt_Click()
Call ReportOut(acViewPreview)
End Sub

PrivateSub cmdPrintRpt_Click()
Call ReportOut(acViewNormal)
End Sub

Private Sub ReportOut(ViewType As Long)
Dim strWhere As String

Select Case Me.opgReportFilter
Case Is 1
strWhere = "[FFDraftDate] Is Not Null AND [FFProposalDate] Is
Null AND [FFProposalSigned] Is Null"
Case Is 2
strWhere = "[FFDraftDate] Is Not Null AND [FFProposalDate] Is
Not Null AND [FFProposalSigned] Is Null"
Case Is 3
strWhere = "[FFDraftDate] Is Not Null AND [FFProposalDate] Is
Not Null AND [FFProposalSigned] Is Not Null"
End Select

Docmd.OpenReport "MyReportName", ViewType, , strWhere
End Sub

--
Dave Hargis, Microsoft Access MVP


Amy Schmid said:
Good morning,

I have the following query:

SELECT dbo_Clients.Cltnum, dbo_Clients.Cltname, dbo_Clients.Cltsort,
dbo_MM_Fixed_Fee_Payment_Info.[FFRetainer$],
dbo_MM_Fixed_Fee_Payment_Info.FFPayCode, dbo_Clients.Engfye,
dbo_MM_Fixed_Fee_Payment_Info.FFAgreementYear,
dbo_MM_Fixed_Fee_Payment_Info.FFDraftDate,
dbo_MM_Fixed_Fee_Payment_Info.FFPropsalDate,
dbo_MM_Fixed_Fee_Payment_Info.FFProposalSigned,
dbo_MM_Fixed_Fee_Payment_Info.FFPlanStartDate,
dbo_MM_Fixed_Fee_Payment_Info.FFPlanComplDate,
dbo_MM_Fixed_Fee_Payment_Info.FFComment, dbo_Clients.Cltoff,
dbo_Clients.CPPFname, dbo_Clients.CBMFname, dbo_Clients.CTRFname
FROM (dbo_Clients LEFT JOIN dbo_MM_Fixed_Fee_Payment_Info ON dbo_Clients.ID
= dbo_MM_Fixed_Fee_Payment_Info.FFCltID) INNER JOIN dbo_Office ON
dbo_Clients.Cltoff = dbo_Office.OffID;

I would like to set a parameter that lets me choose one of three options:
Option 1:
FFDraftDate - where this field is filled in but the FFProposalDate and the
FFProposalSigned fields are blank.

Option 2:
FFDraftDate and FFProposalDate - where these fields are filled in but the
FFProposalSigned field is blank.

Option 3:
Where all three fields are filled in an no blanks.

I also need to specify the FFAgreementYear for each option.

We currently have a copy of this query set up to require the AgreementYear,
PayCode are required. But this parameter does not require looking at blank
fields.

Any way this is possible? My final goal is to have three separate reports
based on each of the three fields and a chosen agreement year, showing all
PayCode types .
Click to expand...
 
A

Amy Schmid

I think I followed what you have here. Either way, it works as I need it to.
:)

--
Thanks,
Amy and Jill
Still Newbies, but learning :0)


Klatuu said:
When you say report, are you saying the query is the record source for a
report you want to filter?

If so, then you can use the Where argument of the OpenReport method to
filter the report whichever way you want to see it. I would suggest an
Option Group control with 3 buttons to select how you want the report
filtered. Then, typically I use 2 command buttons for the user to select the
output, one for preview and one for print. Both buttons call the same sub to
output the report and the sub's argument determines how it is output. So the
Preview button would be like:

PrivateSub cmdPreviewRpt_Click()
Call ReportOut(acViewPreview)
End Sub

PrivateSub cmdPrintRpt_Click()
Call ReportOut(acViewNormal)
End Sub

Private Sub ReportOut(ViewType As Long)
Dim strWhere As String

Select Case Me.opgReportFilter
Case Is 1
strWhere = "[FFDraftDate] Is Not Null AND [FFProposalDate] Is
Null AND [FFProposalSigned] Is Null"
Case Is 2
strWhere = "[FFDraftDate] Is Not Null AND [FFProposalDate] Is
Not Null AND [FFProposalSigned] Is Null"
Case Is 3
strWhere = "[FFDraftDate] Is Not Null AND [FFProposalDate] Is
Not Null AND [FFProposalSigned] Is Not Null"
End Select

Docmd.OpenReport "MyReportName", ViewType, , strWhere
End Sub

--
Dave Hargis, Microsoft Access MVP


Amy Schmid said:
Good morning,

I have the following query:

SELECT dbo_Clients.Cltnum, dbo_Clients.Cltname, dbo_Clients.Cltsort,
dbo_MM_Fixed_Fee_Payment_Info.[FFRetainer$],
dbo_MM_Fixed_Fee_Payment_Info.FFPayCode, dbo_Clients.Engfye,
dbo_MM_Fixed_Fee_Payment_Info.FFAgreementYear,
dbo_MM_Fixed_Fee_Payment_Info.FFDraftDate,
dbo_MM_Fixed_Fee_Payment_Info.FFPropsalDate,
dbo_MM_Fixed_Fee_Payment_Info.FFProposalSigned,
dbo_MM_Fixed_Fee_Payment_Info.FFPlanStartDate,
dbo_MM_Fixed_Fee_Payment_Info.FFPlanComplDate,
dbo_MM_Fixed_Fee_Payment_Info.FFComment, dbo_Clients.Cltoff,
dbo_Clients.CPPFname, dbo_Clients.CBMFname, dbo_Clients.CTRFname
FROM (dbo_Clients LEFT JOIN dbo_MM_Fixed_Fee_Payment_Info ON dbo_Clients.ID
= dbo_MM_Fixed_Fee_Payment_Info.FFCltID) INNER JOIN dbo_Office ON
dbo_Clients.Cltoff = dbo_Office.OffID;

I would like to set a parameter that lets me choose one of three options:
Option 1:
FFDraftDate - where this field is filled in but the FFProposalDate and the
FFProposalSigned fields are blank.

Option 2:
FFDraftDate and FFProposalDate - where these fields are filled in but the
FFProposalSigned field is blank.

Option 3:
Where all three fields are filled in an no blanks.

I also need to specify the FFAgreementYear for each option.

We currently have a copy of this query set up to require the AgreementYear,
PayCode are required. But this parameter does not require looking at blank
fields.

Any way this is possible? My final goal is to have three separate reports
based on each of the three fields and a chosen agreement year, showing all
PayCode types .
Click to expand...
Click to expand...
 
K

Klatuu

LOL, I have code I have downloaded that works for me, but I couldn't write it
myself, so don't feel lost.
As long as it works :)
--
Dave Hargis, Microsoft Access MVP


Amy Schmid said:
I think I followed what you have here. Either way, it works as I need it to.
:)

--
Thanks,
Amy and Jill
Still Newbies, but learning :0)


Klatuu said:
When you say report, are you saying the query is the record source for a
report you want to filter?

If so, then you can use the Where argument of the OpenReport method to
filter the report whichever way you want to see it. I would suggest an
Option Group control with 3 buttons to select how you want the report
filtered. Then, typically I use 2 command buttons for the user to select the
output, one for preview and one for print. Both buttons call the same sub to
output the report and the sub's argument determines how it is output. So the
Preview button would be like:

PrivateSub cmdPreviewRpt_Click()
Call ReportOut(acViewPreview)
End Sub

PrivateSub cmdPrintRpt_Click()
Call ReportOut(acViewNormal)
End Sub

Private Sub ReportOut(ViewType As Long)
Dim strWhere As String

Select Case Me.opgReportFilter
Case Is 1
strWhere = "[FFDraftDate] Is Not Null AND [FFProposalDate] Is
Null AND [FFProposalSigned] Is Null"
Case Is 2
strWhere = "[FFDraftDate] Is Not Null AND [FFProposalDate] Is
Not Null AND [FFProposalSigned] Is Null"
Case Is 3
strWhere = "[FFDraftDate] Is Not Null AND [FFProposalDate] Is
Not Null AND [FFProposalSigned] Is Not Null"
End Select

Docmd.OpenReport "MyReportName", ViewType, , strWhere
End Sub

--
Dave Hargis, Microsoft Access MVP


Amy Schmid said:
Good morning,

I have the following query:

SELECT dbo_Clients.Cltnum, dbo_Clients.Cltname, dbo_Clients.Cltsort,
dbo_MM_Fixed_Fee_Payment_Info.[FFRetainer$],
dbo_MM_Fixed_Fee_Payment_Info.FFPayCode, dbo_Clients.Engfye,
dbo_MM_Fixed_Fee_Payment_Info.FFAgreementYear,
dbo_MM_Fixed_Fee_Payment_Info.FFDraftDate,
dbo_MM_Fixed_Fee_Payment_Info.FFPropsalDate,
dbo_MM_Fixed_Fee_Payment_Info.FFProposalSigned,
dbo_MM_Fixed_Fee_Payment_Info.FFPlanStartDate,
dbo_MM_Fixed_Fee_Payment_Info.FFPlanComplDate,
dbo_MM_Fixed_Fee_Payment_Info.FFComment, dbo_Clients.Cltoff,
dbo_Clients.CPPFname, dbo_Clients.CBMFname, dbo_Clients.CTRFname
FROM (dbo_Clients LEFT JOIN dbo_MM_Fixed_Fee_Payment_Info ON dbo_Clients.ID
= dbo_MM_Fixed_Fee_Payment_Info.FFCltID) INNER JOIN dbo_Office ON
dbo_Clients.Cltoff = dbo_Office.OffID;

I would like to set a parameter that lets me choose one of three options:
Option 1:
FFDraftDate - where this field is filled in but the FFProposalDate and the
FFProposalSigned fields are blank.

Option 2:
FFDraftDate and FFProposalDate - where these fields are filled in but the
FFProposalSigned field is blank.

Option 3:
Where all three fields are filled in an no blanks.

I also need to specify the FFAgreementYear for each option.

We currently have a copy of this query set up to require the AgreementYear,
PayCode are required. But this parameter does not require looking at blank
fields.

Any way this is possible? My final goal is to have three separate reports
based on each of the three fields and a chosen agreement year, showing all
PayCode types .
Click to expand...
Click to expand...
Click to expand...
 
Top