Report won't filter 3

[annie52]

I'm trying to filter a report based on the value selected from a combo box on an unbound form. This code results in Error #3075: Syntax error (missing operator) in query expression '(Tech=JoeSmith)'.

Dim stDocName As String, stSelectTech As String

stDocName = "rptFollowup"
stSelectTech = "Tech=" & Forms![frmTechnician]![cboTechnician]
DoCmd.OpenReport stDocName, acPreview, , stSelectTech

Exit_cmdTechnician_Click:
Exit Sub


I've just spent an hour searching this site and attempting different variations of the filter and where and just can't figure it out. I sure would appreciate some help.

 

[MajP]

Tech='" & Forms![frmTechnician]![cboTechnician]&"'"

Tech = 'Joe Smith'

 

[annie52]

MajP, thanks so much. I was really getting frustrated. I wonder how long it will take before I get comfortable with the mix of apostrophes? You certainly earned a star.

 

[PHV]

A safer way (if, eg, you have some Irish technician ...)

stSelectTech = "Tech='" & Replace(Forms!frmTechnician!cboTechnician, "'", "''") & "'"

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

 

[annie52]

I read about the Replace function but, since I simply don't grasp the logic or process of when to use single, double, or double-single quotes, I don't understand why PHV's suggestion is "safer".

 

[dhookom]

PH's suggestion is safer if you have cboTechnician names that might contain an apostrophe like O'Brien, O'Connor, or similar.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

This does not work in SQL

Tech = 'O'Malley'

You have to convert it to

Tech = 'O''Malley'

The rule is if it is numeric it needs to resolve to
Tech = 1234
String
Tech = 'Joe'

String with embedded single quote
Tech = 'Joe O''Malley'

Date
DueDate = #1/1/2009#

 

[annie52]

This is great. Thank you all so much!

 

[MajP]

There are a few other string searches that have to get manipulated as well. I actually do not remember the rules so someone needs to chime in.

A number sign in the string
The #1 Car Dealer
And I think a % sign
100% Floor Installers

And a combination get's real tough
Joe's #1 Crab Shack

 

[PHV]

Sorry MajP, but I don't follow you.
Maybe you're talking about the LIKE operator ????

 

[MajP]

I thought there were some other string characters that raise a problem in a string search. I thought the # sign causes a problem, but I guess I was wrong. I imagine using like on these would be complicated.