Wildcard type character in Field Record (1 Viewer)

[Alansidman]

I have a recordset that has some characters that are at the start of the string value that resemble wildcards, ie. ?. I must be having a senior moment, because I cannot recall how I would filter in a query in the criteria to return a particular record, ie. ?S8ZQN8T. This data is coming from a linked source in another database that I do not have any control over. If I recall it has something to do with square brackets [ ], but when I wrap the character or the string in square brackets the criteria defaults to a Like statement. Some thoughts on this?

Alan

 

[spikepl]

office.microsoft.com/en-us/access-help/find-wildcard-characters-in-an-access-database-HA001171536.aspx

 

[Alansidman]

Thanks Spikepl. I had been there, but it was not clear on how to handle a wildcard character in the first position that is not a wildcard. I attempted the square brackets but Access still wants to put the Like function into the criteria. I need to have it try and find the exact record. I just noticed that the forum broke the record so that it was perhaps not clear. The record and similar records are structured as follows. ?S8ZQN8T

Alan

 

[spikepl]

So what is wrong with

SELECT tblMain.[sometxt]
FROM tblMain
WHERE (((tblMain.[sometxt]) Like "[?]S8ZQN8T"));

 

[Alansidman]

Spike;
That's exactly what I have and it is working. I just thought that it would have been = vs Like. It was one of those mystery things that something unexpected appears and you wonder why even though it works. Thanks much. I'm moving on.

 

[spikepl]

I get you. You expected, actually just like me, the logical consequence of [?] being the literal character ?, and so "=" being the applicable comparison operator. Whereas Access apparently decided that it needs its "pattern matching" and hence "Like" to extract a string like
"?S8ZQN8T"

 

[boblarson]

You could also have used a field to replace that character with something else and then put the criteria on that.

Like

Replace([OriginalFieldNameHere], Chr(63), "|") As NewFieldName

And then make sure the criteria gets the same treatment.

Just another way and it may not be efficient.