reference a query

[slimjen]

All; using 2010; I have the following code snippet. I am creating the output for report#1. It works. Now I want to output report#2 based on criteria in qryMarkNames. Ex. If letter_code is New I need Report 1 else report 2. CustID is in all my queries through the db. I am doing something wrong because its throwing errors like missing brackets and such. The query I’m trying to reference that has the field I need to criteria is on a different form. I am also using a different query for the reports. What do I need to do differently? Can someone assist please.

' Set current CustID (used by the reports' underlying queries).
g_varCurrentCustID = !client_id
'                            If CurrentDb().QueryDefs("[qryMarkNames]).[letter_code]" = "New" Then
                          
                                ' Create the report #1.
                                strPathAndFilename_Report1 = strPathToStatementFiles & strReportFilenamePrefix & strScrubbedBrokerName & " " & strState & " " & varCheckIssueDate & ".pdf"
                                DoCmd.OutputTo acOutputReport, "rptLetterNEW", acFormatPDF, strPathAndFilename_Report1, False
                                DoEvents     ' Allow this operation to be fully completed before proceeding.
                           Else
                           
                                ' Create the report #2.
                                strPathAndFilename_Report2 = strPathToStatementFiles & strReportFilenamePrefix & strScrubbedBrokerName & " " & strState & " " & varCheckIssueDate & ".pdf"
                                DoCmd.OutputTo acOutputReport, "rptLetterTrueUp", acFormatPDF, strPathAndFilename_Report1, False
                                DoEvents     ' Allow this operation to be fully completed before proceeding.
Endif

[Rawb]

On the first line of your If statement, you have starting quotes inside the parentheses, but the closing quotes outside of them.
You also have the entire first line of your If statement commented out (possibly due to debugging efforts on your end?).
You're defining the variable strPathAndFilename_Report1 in both branches of your If statement, but using strPathAndFilename_Report2 in the second branch.

I've never used a QueryDef in this manner, but try this and see if it works. If it doesn't, we'll need to change how the Column is accessed from your QueryDef:

' Set current CustID (used by the reports' underlying queries).
g_varCurrentCustID = !client_id
                          If CurrentDb().QueryDefs("qryMarkNames").[letter_code] = "New" Then
                                ' Create the report #1.
                                strPathAndFilename_Report1 = strPathToStatementFiles & strReportFilenamePrefix & strScrubbedBrokerName & " " & strState & " " & varCheckIssueDate & ".pdf"
                                DoCmd.OutputTo acOutputReport, "rptLetterNEW", acFormatPDF, strPathAndFilename_Report1, False
                                DoEvents ' Allow this operation to be fully completed before proceeding.
                          Else
                                ' Create the report #2.
                                strPathAndFilename_Report2 = strPathToStatementFiles & strReportFilenamePrefix & strScrubbedBrokerName & " " & strState & " " & varCheckIssueDate & ".pdf"
                                DoCmd.OutputTo acOutputReport, "rptLetterTrueUp", acFormatPDF, strPathAndFilename_Report2, False
                                DoEvents ' Allow this operation to be fully completed before proceeding.
                          Endif

[slimjen]

I made the changes and were able to debug. But when I run the code; I get type mismatched.

[Rawb]

What line do you get the error on?

[John_G]

In this expression:

CurrentDb().QueryDefs("qryMarkNames").[letter_code] = "New"

you can't reference a query field name like that - you need

CurrentDb().QueryDefs("qryMarkNames").fields("[letter_code]")

But that will only get you the properties of the query or its fields, not any of the data.

The query I’m trying to reference that has the field I need to criteria is on a different form.

Does this mean that the value you want to check is in a field on a form? If so, then you need to reference the form field, not the query, something like this:

if forms!formname!controlname = "New" then ... (the form must be open)

Replace the parts in italics with the actual names of your form and control.

[slimjen]

Sorry for the long pause. I've been trying to resolve some other issues in the code. The field does not exist on a form. I have not resolved this issue. I tried to correct the line but as it is; the name of the field in the query is [Letter Code]. I don't understand what should be the field name additionally.

[John_G]

You can reference the field in the query as shown in post #5, but I don't know of any easy way of extracting the current criteria setting for the field.

But why would you want to do that? You would have to go into query design, set or change the criteria, and save the query before running the code. It would be far easier to use a form to select or set the value you want.