Ampersand in Search 2

[lars7]

Hi Guys,
I'm trying to get my search criteria to find places with an Ampersand I tried this:

If Trim(Replace(Me!Text1, "&", "''")) <> "" Then
strwhere = strwhere & "AND Directorate='" & Me!Text1 & "' "
End If

but I'm getting an error can anyone help with this.

 

[PHV]

What do you really want to do ?

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

 

[lars7]

Hi PH,
I just wanted to be able to filter for the following Diretorates

Directorate
EMERGENCY CARE & MEDICAL SPECIALTIES
FACILITIES
ORAL HEALTH
PARTNERSHIPS
REGIONAL SERVICES
REHABILITATION & ASSESSMENT
SURGERY & ANAESTHETICS
WOMEN & CHILDREN'S

but was getting an error message with the original code:

If Trim(Me!Text1 & "") <> "" Then
strwhere = strwhere & "AND Directorate='" & Me!Text1 & "' "
End If

so I tried to add the replace but it still isn't working.
Do you know why?

 

[lars7]

Sorry I should have said Text1 is a combo box

 

[PHV]

Which error message ?

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

 

[lars7]

It is working Doh! I had to pick the one with a Hyphen in it also to try. :~/ Can the Hyphen replace be added too?

 

[lameid]

I'm back to PHV's post... What do you want to do?
OR
What are you trying to do with the data in the combobox?

This may not be relevant but it jumps out at me..
Text1 is a combo box... Populated how?

 

[lars7]

Hi Lameid,
I have 4 combo boxes that are used to send criteria to a query which is then opened for viewing or a report with the same query as record source.

The record source for Text1 is:

Directorate
EMERGENCY CARE & MEDICAL SPECIALTIES
FACILITIES
ORAL HEALTH
PARTNERSHIPS
REGIONAL SERVICES
REHABILITATION & ASSESSMENT
SURGERY & ANAESTHETICS
WOMEN & CHILDREN'S

my problem is now with this one:

WOMEN & CHILDREN'S

 

[lameid]

Instead of your entire If statment I would think the following would work...

 strwhere = strwhere & "AND Directorate=""" & Me!Text1 & """ "

If not, post the line where strwhere is used and the text of the error Access gives you.

 

[lars7]

Thanks Lameid,
That worked great.

 

[PHV]

my problem is now with this one:
WOMEN & CHILDREN'S
The problem is not the ampersand at all !
The correct way is:

strwhere = strwhere & "AND Directorate='" & Replace(Me!Text1, "'", "''") & "' "

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

 

[lars7]

Thanks Ph,
When I filtered for "WOMEN & CHILDREN'S" I assumed it was the "&", I tried the others later and discovered my error.

Thanks for the code to fix it.

 

[lameid]

My code still works for this scenario as it uses double qutoes to delimit text instead of single quotes. Personally, I find it always easier to stick to double quotes in Access. You have to use Double quotes in VBA so why compound the problem of reading it using single quotes in expressions (assuming you are using Jet SQL).

 

[PHV]

lameid, your suggestion don't work if the text contains double quote, mine works in all cases.

 

[lameid]

True.

My assumption was that the list is static and therefore double quotes would not be in there (and honestly when I changed the expression I only scanned for double quotes).

If I ran across a double quote I would do the same thing...

strwhere = strwhere & "AND Directorate = """ & Replace(Me!Text1, """", """""") & """ "

Granted, seeing 6 double quotes in a row is a bit tedious but then again that situation is obscure enough I have not run into it. I find 2 single quotes often look like 1 double quote, so by convention I only use double quotes because VBA forces me to use them to delimit strings and I do not want to mix single and double quotes.

 

[PHV]

I do not want to mix single and double quotes.
My personal way is to use double quotes for VBA literal constants and single quotes for SQL.

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