DoCmd.OpenReport not using given Where Condition

[DarthLink]

I was given the task at my office of porting data from an Access Database into our SQL Server while keeping the data linked to an Access Project to use the forms already written.

This I have done, but in so doing created several problems in the code that I have not been able to quickly overcome.

The problem that I need help with is that, when a button is clicked on the main form, a report is supposed to open up based on the information put into the form. The report opens, but it does not filter the results based upon the where clause I put into it, it shows the entire table.

I have set a messagebox to show the query, and it is good SQL:

[item_no] = '19250' AND [Serial_No] = '0712345'

Has anyone else had this problem, and if so how did you work around it/fix it?

Thanks in advance.

 

[Remou]

Where does this go? If Filter =, have you set FilterOn?

 

[DarthLink]

This goes to a Report, Filter is blank, and FilterOn=true

 

[PHV]

Why not simply use the 4th argument of the DoCmd.OpenReport method ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DarthLink]

I am. It's ignoring it, or simply not using it.

That's the problem; sorry if I forgot to mention that.

 

[Remou]

Why not post the complete line?

 

[PHV]

How are defined item_no and Serial_No in the linked table(s) ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DarthLink]

item_no and Serial_no are both integers.

The complete code:

strSerial = "[item_no] = '" & ModelNo & "' AND [Serial_No] = '" & Format(SerialNo, "0000000") & "'"
MsgBox (strSerial)
MsgBox (Grpt)
DoCmd.OpenReport Grpt, acViewPreview, , strSerial

ModelNo, SerialNo, and Grpt are defined earlier in the subroutine.

 

[Remou]

You should not use quotes with numeric fields.

 

[PHV]

item_no and Serial_no are both integers
So, get rid of the single quotes in strSerial.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[missinglinq]

Yes, are item_no and Serial_No numeric or text?

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[DarthLink]

They're numeric - good catch. I always do that.

Removing them doesn't fix the problem though; it's still not running the Where Statement.

 

[PHV]

No VBA in the Open and/or Load event procedures of the Report ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DarthLink]

There is a Detail_Format function in the Report.

It loads an external picture into an Image.