Where Clause in OpenReport Method

[Walter]

I am trying to pass a variant array to the where clause of
the OpenReport method.

When I run example 1 below I get the error message 'Data
type mismatch in criteria expression'.
However if I hardcode the value as a string (Ex 2) it
works, however its not quite what I'm after as I would
like nm to iterate through the array.

Anyone have any ideas.

Regards,

Walter


Ex 1

Option Compare Database
Sub Run_Daily_Sales_Reports()
nm = Array("000939", "000920")
For Each aa In nm
DoCmd.OpenReport "DailySales_Report",
acPreview, "", "[DailySales_Report]![BuyingGroupCode]=" &
aa
DoCmd.OutputTo
acReport, "DailySales_Report", "SnapshotFormat
(*.snp)", "K:\Market\Forecast\Reports\MTD Sales " & aa
& ".snp", False, ""
DoCmd.Close acReport, "DailySales_Report"
Next aa
End Sub


Ex 2

Option Compare Database
Sub Run_Daily_Sales_Reports()
nm = Array("000939", "000920")
For Each aa In nm
DoCmd.OpenReport "DailySales_Report",
acPreview, "", "[DailySales_Report]![BuyingGroupCode]
=""000939"""
DoCmd.OutputTo
acReport, "DailySales_Report", "SnapshotFormat
(*.snp)", "K:\Market\Forecast\Reports\MTD Sales " & aa
& ".snp", False, ""
DoCmd.Close acReport, "DailySales_Report"
Next aa
End Sub

 

[Emilia Maxim]

Walter,

try this:

'Fisrt thing put this in the module header
'Otherwise _every_ typo will be accepted and treated as
'a new variable! These errors are among
'the hardest to find!

Option Explicit

Sub Run_Daily_Sales_Reports()

Dim nm As Variant
Dim I As Integer

nm = Array("000939", "000920")

For I = LBound(nm) To UBound(nm)
DoCmd.OpenReport "DailySales_Report", acPreview, "",
"[DailySales_Report]![BuyingGroupCode]=" & nm(I)

DoCmd.OutputTo acReport, "DailySales_Report", "SnapshotFormat
(*.snp)", "K:\Market\Forecast\Reports\MTD Sales " & nm(I) & ".snp",
False, ""
DoCmd.Close acReport, "DailySales_Report"
Next I
End Sub

I don't think an array can be handled with the For Each loop.
LBound returns the lower limit for the array index and UBound the
upper limit.

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de

 

[Walter]

Thanks Amelia,

I have tried your module but I still receive the same
error message halting at the OpenReport line.

'Data type mismatch in criteria expression'

If I comment it out it works for the next line OutputTo.
The field for BuyingGroupCode is of type text.

Any other suggestions?

-----Original Message-----

Walter,

try this:

'Fisrt thing put this in the module header
'Otherwise _every_ typo will be accepted and treated as
'a new variable! These errors are among
'the hardest to find!

Option Explicit

Sub Run_Daily_Sales_Reports()

Dim nm As Variant
Dim I As Integer

nm = Array("000939", "000920")

For I = LBound(nm) To UBound(nm)
DoCmd.OpenReport "DailySales_Report", acPreview, "",
"[DailySales_Report]![BuyingGroupCode]=" & nm(I)

DoCmd.OutputTo

acReport, "DailySales_Report", "SnapshotFormat

 

[John Vinson]

'Data type mismatch in criteria expression'

If I comment it out it works for the next line OutputTo.
The field for BuyingGroupCode is of type text.

In that case you need the syntactically required quotemarks around the
value to be searched. Try:

DoCmd.OpenReport "DailySales_Report", acPreview, "", _
"[DailySales_Report].[BuyingGroupCode]='" & nm(I) & "'"

Note also that a period, not a !, is the appropriate delimiter between
a tablename and a fieldname.

 

[Walter]

Works now. Thanks John

-----Original Message-----

'Data type mismatch in criteria expression'

If I comment it out it works for the next line OutputTo.
The field for BuyingGroupCode is of type text.

In that case you need the syntactically required quotemarks around the
value to be searched. Try:

DoCmd.OpenReport "DailySales_Report", acPreview, "", _
"[DailySales_Report].[BuyingGroupCode]='" & nm(I) & "'"

Note also that a period, not a !, is the appropriate delimiter between
a tablename and a fieldname.




.