Query Quirk

[Bill6868]

I have a field named [Stations] in my table, in which resides the names of our service stations I.e. “A Westport Cooperative Services”, “B Westport Cooperative Services”, “Spectrum Home Health”, “Don Bosco Center” etc.

I want to filter by station, so in the criteria line of the query (which is embedded in a report) I can type any of these stations, run the query and it will be filtered….except for “A Westport Cooperative Services”. For some reason with this station no records come up. I can type in “B Westport Cooperative Services” or any other station and get those records just fine.

The first word in “A Westport Cooperative Services” is the letter “A” and I suspect that this is a reserved Access character and herein lies my problem.

To solve this - in the criteria field I type in Not “Spectrum Home Health” And Not “Don Bosco Center” and so forth until I’ve typed Not for every station we have except for “A Westport Cooperative Services” and that would be the only station left, and I get my answer.

Would anyone have a solution to the first word being the letter “A” and screwing up the criteria line? I have tried the following:

Like ("*") & [Enter any part of the Description] & ("*")
Like ("*") & "A Westport Cooperative Services" & ("*")

I’ve tried brackets, brackets with quotation marks and just about everything I could think of…all with no luck.

I do not have the luxury of changing the station’s name to something else.

This little Access query quirk is driving me nuts. I’m using Access 2010.

Good grief!

Bill

 

[SkipVought]

Please post your entire query code

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[dhookom]

IMO, don't use parameter prompts faq701-6763. I would use a combo box on a form that is bound to a primary or foreign key field in your table.

Also, the ()s should be removed from your query.


Duane
Hook'D on Access
MS Access MVP

 

[Bill6868]

Skip,

The SQL in this field on the criteria line simply reads (for example) "B Westport Cooperative Services" - this work fine, as with any other station name I type in - it's when I enter "A Westport Cooperative Services" that no records are returned. It does not like that my first word is the letter A - any other station's name is fine.

Bill

 

[Bill6868]

Duane,

I understand what you're saying about parameter prompts and I don't generally do this, but this is a report I'm creating on the fly for a purpose we only use once a year. Next year it will be something different. The report is only for me and not my users.

Bill

 

[dhookom]

The letter A is no different from any other letter. There is some other issue with your data. It would take less time to create a form and control than post a question here.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Like Duane said the A is no different. Any chance you have an empty space before the A?

 

[Bill6868]

MajP - there are no space(s) before the A. I changed the station's name to Westport Cooperative Services A and now I don't have a problem. Still a mystery as to why the "A Westport Cooperative Services" criteria line failed when running the query.

I dunno.

Thanks for everyone's input. Greatly appreciated!