Need to pull data using specific criteria in the query

[Jamie]

Hello all - I'm sure this is simple but my googling yielded nothing. I'm trying to create an update query on a field that is supposed to have a specific naming convention and can't figure out how to phrase it in the query. The data in the field should be '1STA xx/xx/xx'. The xs in the data are actual dates, and there are many dates. Anything in the table that isn't created exactly like that is considered invalid and I need to update the table with a column indicating which rows are correct and leave the incorrect fields blank to enable me to pull them and correct them.

I've tried using spaces to just count the number of spaces but the query pulled 0 rows, e.g. '1STA '.

How can I phrase the criteria to pull just '1STA xx/xx/xx'?

Thanks all!

 

[isladogs]

Use 'Like 1STA*'

 

[CJ_London]

not sure I fully understand the question, but sounds like you need to use Like - something like

SELECT *
FROM myTable
WHERE myField LIKE "STA ##/##/##"

See this link for more information on wildcards

https://msdn.microsoft.com/en-us/library/bb208897(v=office.12).aspx

 

[The_Doc_Man]

OK, ... what they said.

BUT if this is something you are creating yourself, it would seem to me that your tables may not be normalized properly. If those are dates as field names, you should instead have a column with the data and a date as a field.

 

[Jamie]

Thanks all, who have offered answers! The field that I'm trying to audit is a freeform field, which is one of the reasons why I need to try and make the audit as easy as possible.

When I posted my question, the spaces I had entered for my example disappeared, eg the spaces after the "1STA" in my original post. Confusion, therefore, was understandable and I apologize.

I have a field that's supposed to be "1STA 05/25/17" and that's what I want. But many times the data entry person puts "1STA 05/25/2017" or "1STA 005/25/17" or many other variations. I want to update the main table with a label of "1STA" in a column to indicate the fields labeled correctly, leaving the incorrect fields blank.

The answers given were good, but I'd like to use an Update query with the exact criteria phrasing in a date format. Maybe there is nothing and a SELECT query is the only answer and I need to then Append?

 

[isladogs]

Maybe I'm being dense but I still think you can just use the wildcard solution from my first reply.
Like "1STA*" gets everything starting with 1STA

Not quite sure what your update query is meant to do, but try adapting one or both of the following:

Select query:
SELECT *
FROM TableName
WHERE FieldName LIKE '" & 1STA* & "';"


Update query:
UPDATE FieldName SET TableName.FieldName = "Whatever you want here" WHERE FieldName LIKE '" & 1STA* & "';"