Where condition in VBA

lryckman · May 5th, 2004, 04:13 PM

Can someone please tell me what I have wrong in this line of code?

Code:

Dim Spec As String
Spec = InputBox("Please enter the specification number with an * on each end", "Spec number please")
Docmd.OpenReport "Print Specification Listing",acViewPreview,,Where [Parts]![Specification] Like Spec or [Parts]!BOMSpecification] Like Spec

Parts = the table name
Specification = field name in the table
BOMSpecification = field name in the table

My problem is in the Where clause. What I want it to do is only show the records that contains (in either field) what they enter in the input box.

Thanks for your help!

Regards,
Laura

The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!

embreyo · May 5th, 2004, 05:13 PM

You need quotation marks around your WHERE condition. Change it to read as follows:

Docmd.OpenReport "Print Specification Listing",acViewPreview,,"[Parts]![Specification] Like """ & Spec & """ or [Parts]!BOMSpecification] Like """ & Spec & """"

That should do the trick.

Darrell L. Embrey

lryckman · May 6th, 2004, 07:53 AM

Thank you for your reply Darrell.
I updated the code as you said, but now I am getting a runtime error 3075.
The message with it reads as follows:

Quote:

quote:Syntax error (missing operator) in query expression '(Where
[Parts]![Specification] Like "d 2000" or [Parts]![BOMSpecification]
Like "d 2000")'.

Here is what my whole set of code looks like now:

Code:

Private Sub cmdPrintSpec_Click()
Dim Spec As String

Spec = InputBox("Please enter the specification number with an * on 
each end", "Spec number please")

DoCmd.OpenReport "Print Specification Listing", acViewPreview, , 
"Where [Parts]![Specification] Like """ & Spec & """
or [Parts]![BOMSpecification] Like """ & Spec & """", acWindowNormal
End Sub

Thanks in advance for any help!

Regards,
Laura

The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!

SerranoG · May 6th, 2004, 10:35 AM

Copy and paste this exactly. Note, in a DoCmd.OpenReport statement, the word "where" is assumed. You do NOT put it in the code at all.

Code:

DoCmd.OpenReport "Print Specification Listing", acViewPreview, , _
"[Parts]![Specification] Like '*" & Spec & _
"*' or [Parts]![BOMSpecification] Like '*" & Spec & "*'", acWindowNormal

Note, since you used the specifier "like" then you must surround SPEC with wildcards, too.

In a query: Like "*Text Here*"

In Code:

Code:

Like '*" & Spec & "*'"

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

SerranoG · May 6th, 2004, 10:37 AM

Oops, I mean

In a query: Like "*Text Here*"

In Code:

Code:

Like '*" & Spec & "*'"

I fixed the apostrophe.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

lryckman · May 6th, 2004, 11:33 AM

Worked perfect...THANK YOU SO MUCH!!!

Regards,
Laura

The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!