Special Characters in data values (1 Viewer)

[Hagridore]

I originally posted this as a reply to a question posted in 2013 and didn't get a reply, So I thought maybe it was too old. In my database there is one value that requires the use of a /. (This is not as a name of anything...just a value stored in one of the fields.) I have a form which functions beautifully in all other regards, but it produces an error about syntax of the subquery in the expression if I try to use this value as a criteria for a search/filter (screen shot of error message attached).

I've tried using double quotes and square brackets around the / and a \ in front of it to no avail. I don't have the option of changing the value...it is defined by this multi-billion dollar project. Again, this is just a piece of data in a field in a record which also needs to be a criteria in some searches/filters.

Can anyone help?

 

[spikepl]

So you want us to guess or what exactly?

How about showing the subquery or query or whatever, in the context it is to be used, with the data, and which generates the error

 

[Hagridore]

How about showing the subquery or query or whatever, in the context it is to be used, with the data, and which generates the error

That's just the point. There is NO subquery. The system appears to be reading the slash mark in the DATA...the contents of a field...as an operator. However it appears only to be doing this with the specific words All/Integrated and not as a general rule. I created an example that represents what's going on, expecting the same problem to happen here. (I'm not interested in comments about whether or not there should be a /, btw, just on how to make the slash read as part of the data instead of as an operator.) In a table called tblFaculty:

Faculty ID Faculty Name Faculty Type Department

TKAZ Ted Kaczynski Full Time Political Science
AEIN Albert Einstein Adjunct Physics/Mathematics
FMAG Ferdinand Magellan Adjunct Geography
OZEL Oberon Zell Full Time Religious Studies
JHEN Jimi Hendrix Full Time All/Integrated
HPOW Higher Power Full Time All/Integrated,
Anthropology, English,
Geography,
Physics/Mathematics,
Religious Studies

Using a form frmFaculty with controls for each:

txtFacID
txtFacName
lstFacType
lstDept (allowing multiple selections)

When I run a Filter by Form operation using Department as a criteria for the search, selecting "Political Science" in lstDept will pull up Kacyinski in txtFacName, Magellan for Geography, and Zell for Religious Studies, etc. When I tried to select Physics/Mathematics, I expected the aforementioned error message to appear. It did not. However, selecting "All/Integrated" results in the same error in this database as in my real database. So I went into my original table and added something/whatever to the field. It worked perfectly.

It appears that Access is treating "All/Integrated" differently than anything else. Is this normal? Is there a way around it?

 

[spikepl]

No stories. You need to show code or exact input or both.

 

[Hagridore]

Spikepl..there's no code to show. I'm not sure what you're asking or what I'm not making clear. I described the process. It's a form. It is being used for filter by form, pulling data from a table, where, in one field, the value "All/Integrated" lives. The filter, with no code...just filter by form...is treating "All/Integrated" differently than any other data entered into a table. It brings this error message ONLY when the value "All/Integrated" is entered into a control (selected from a list box)...ANY control...my "story" demonstrates that I tried it in an entirely different setting and still got the same result...and used as a criteria for the filter. "Something/Whatever" doesn't create the error. Neither does "Physics/Mathematics". NO other combination of words with the slash between them appears to create the error. I'm hoping this makes it clear. Again, there is NO CODE to show. No subquery. The system...Access...doesn't even THINK there's a subquery unless these specific words are used in the search/filter. I hope this is clear now. (BTW, just wanted to point out that I'm using quotes in what's above to indicate exactitude of contents, not that I've used quotes in the entries themselves.)

 

[Hagridore]

Here's a database that is different than mine but demonstrates what is going on. It opens with the form open. Clicking "New Search" button starts the filter by form process. Selecting any of the "Department" entries as criteria for the filter will bring up a subset of the Faculty records. Except for "All/Integrated". Selecting this Department as filter criteria will bring up the error message that I noted. I hope this helps.