Changing Text in Queries based on Date

[technician]

Hiya All

Hope you can help me here


I need help with an update query that will change records for me based on the date

In the record I have a TEXT record called [GI1] that can have a multitude of data in it.
The data can be “EVA”, “CON 10/12/06”, “REQ 10/12/06”, “PAS 10/12/06”, or “B 10/12/06”, and finally “A 10/12/06”

What I need is a query will look at the Data that starts with “B” and change the letter to “A” if the date in the record has passed. {The Date can be any date in any year }

Many Thanks for Looking

Paul

 

[Cosmos75]

Assuming [GI1] is in a table called 'tblData'.

UPDATE tblData SET tblData.GI1 = "A " & Right([GI1],Len([GI1])-2)
WHERE ((CDate(Right([GI1],Len([GI1])-2))<Date()) AND (Left([GI1],2))="B ");

This will update any row that starts with "B ", anot not "B". Just in case you had any other text starting with B, e.g. "BB 10/12/06".

Keep in mind that this assumes that date portion of the text will be correctly returned by Right([GI1],Len([GI1])-2).

 

[technician]

Excellent

Many Thanks