Search query

bknight
BronzeLounger
Posts: 1323
Joined: 08 Jul 2016, 18:53

Search query

Post by bknight »

I spent an hour today searching records and correcting them, sloppy input. Here's my issue I have two fields that should agree
First is a field that has the form *???yymmdd the yymmdd should "match" an other field that has a date format m/d/yyyy, I couldn't think of a way to query those params so I went with manual. The ultimate goal is to make sure that the yymmdd is "equal" to m/d/yyy if not update, but that may require two queries. anyone have a solution to this brain test?

User avatar
HansV
Administrator
Posts: 78060
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Search query

Post by HansV »

The criteria for the first field could look like

Like "*" & Format([Second Field], "yymmdd")

if you want to find the matching records, or

Not Like "*" & Format([Second Field], "yymmdd")

if you want to find the non-matching records.
Best wishes,
Hans

bknight
BronzeLounger
Posts: 1323
Joined: 08 Jul 2016, 18:53

Re: Search query

Post by bknight »

I get a type mismatch. Perhaps I didn't set it up correctly
First field is a text that has records in the form *???yymmdd????. I left this part out, sorry. So the pertinent "date" is in the middle of alphanumerics
Second field is a date field formatted mm/dd/yyyy.
the *??? can be a single or double or triple or quadruple text.
The last ???? is text followed by numbers.

User avatar
HansV
Administrator
Posts: 78060
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Search query

Post by HansV »

If the first field is text, and the second field is a date field, the criteria should work - except that according to your new information, it should be

Like "*" & Format([Second Field], "yymmdd") & "*"
Best wishes,
Hans

bknight
BronzeLounger
Posts: 1323
Joined: 08 Jul 2016, 18:53

Re: Search query

Post by bknight »

Yes that worked for me, thanks.

ETA: although that worked for dissimilar values Not Like, Like output records that had hull second field values is that correct for the Like criteriaa?