Help with OpenReport syntax

[Jackie]

Below is a line of code that works:

DoCmd.OpenReport "rpt_General_Letter", acViewPreview, , "[transaction number] = 8"

I would like to use a variable name instead of hardcoding as follows:

DoCmd.OpenReport "rpt_General_letter", acViewPreview, , "[transaction number] = strTransNo"

I have tried a variety of syntax structures, with the current code it produces a parameter box asking for the value of strTransNo.

In the Main Module, strTransNo is declared as a Public variable.

What is the proper systax for substituting a variable name instead of an actual value?

sql = "SELECT [Leave Bank Transaction Log].[transaction number], [Leave Bank Transaction Log].[transaction code], " _
& "[Leave Bank Transaction Log].VLtype FROM [Leave Bank Transaction Log]" _
& " WHERE " _
& "([Leave Bank Transaction Log].[transaction number]>=" & [Forms]![frm_letter_generation]![begintrans] & &quot" _
& &quot; And ([Leave Bank Transaction Log].[transaction number]<=&quot; & [Forms]![frm_letter_generation]![endtrans] & &quot&quot; _
& &quot; AND ([Leave Bank Transaction Log].[transaction code]=&quot;&quot;GC&quot;&quot&quot; _
& &quot; AND ([Leave Bank Transaction Log].VLtype=&quot;&quot;VLBP&quot;&quot&quot; _
& &quot; ORDER BY [Leave Bank Transaction Log].[transaction number];&quot;



Set CheckForRecords = CurrentDb.OpenRecordset(sql, DB_OPEN_DYNASET)

With CheckForRecords
If .RecordCount > 0 Then

Do While Not .EOF

strTransNo = .Fields(&quot;[Transaction Number]&quot





DoCmd.OpenReport &quot;rpt_General_letter&quot;, acViewPreview, , &quot;[transaction number] = strTransNo&quot;


.MoveNext

Loop

MsgBox &quot;General Contribution Letters Completed&quot;

Else
MsgBox &quot;No data selected for general contribution letters.&quot;
.Close
End If
End With

 

[vb6novice]

Jackie,

If the data type for [transaction number] is numeric, then use:

DoCmd.OpenReport &quot;rpt_General_letter&quot;, acViewPreview, , &quot;[transaction number] = &quot; & strTransNo

If the data type for [transaction number] is string, then use:

DoCmd.OpenReport &quot;rpt_General_letter&quot;, acViewPreview, , &quot;[transaction number] = '&quot; & strTransNo &&quot;'&quot;

 

[Jackie]

This syntax gives the message

&quot;The expression is typed incorrectly or is too complex to be evaluated&quot;

In Debug mode, the strTransNo variable is showing up correctly. [transaction number] is a field in the query on which the report is based.

 

[Jackie]

Hi,

I double-checked the transaction number data type and it is numeric.

Your code suggestion is correct.

Thank you for your help.

-jackie

 

[vb6novice]

Jackie,

The logic of your code seems sound. You define a recordset with SQL with transaction numbers between a high and low value. You open that recordset. If the recordset has records, you go to the first and ask for a report of all records with a transaction number equal to the first record in your recordset. If the recordset has no records you give a message.

I think the probelm is that [transaction number] is field in the query on which the record is based but also a field in the recordset you are looping through.

Try changing the DoCmd.OpenReport to

DoCmd.OpenReport &quot;rpt_General_letter&quot;, acViewPreview, , &quot;CheckForRecords![transaction number] = &quot; & strTransNo

 

[vb6novice]

Jackie,

That last line is a mistake. It should refer to the query that the report is based on, NOT the CheckForRecords recordset. I think the reference in the DOCmd statement implicitly refers to the report's query. Try giving the transaction number field a different name in you SQL statement, so that Access won't be confused about which [transaction number] you are referring to.