Older than/ New than with Dates

[The Archn00b]

Hello I've built an an update query that updates a Yes/No field "Expired?" to "Yes" if the expiration date has passed i.e. if the current date is newer than the expiration date. I thought this would translate into the current date being greater than the expiration date?

UPDATE Alert
SET Alert.[Expired?] = 'Yes'
WHERE Alert.Current_Date>Alert.Expiration_Date;

When I run this, it identifies the records to be updated, but then stops updating the fields due to "a conversion failure."

Any ideas?

Thanks for reading!

 

[namliam]

probably because either of your date fields are not of the date type

Regardless of this error though, storing things like Current_date and Expired which are "calculated fields" is a bad overall idea.... why not simply query for "ExpirationDate < Date()"

 

[The Archn00b]

probably because either of your date fields are not of the date type

Regardless of this error though, storing things like Current_date and Expired which are "calculated fields" is a bad overall idea.... why not simply query for "ExpirationDate < Date()"

So I should do the calculations within the query? Could I do something like:

SELECT * FROM Table
WHERE Date() > DateAdd('yyyy',1,[Last_Renewal]);

?

Am I missing any characters in this statement? This is what gets me usually.

 

[The Archn00b]

I've tried the above example, still a conversion failure.

 

[CJ_London]

you are trying to assign a text value to a yes/no field try

UPDATE Alert
SET Alert.[Expired?] = True
WHERE Alert.Current_Date>Alert.Expiration_Date;

 

[The Archn00b]

you are trying to assign a text value to a yes/no field try

UPDATE Alert
SET Alert.[Expired?] = True
WHERE Alert.Current_Date>Alert.Expiration_Date;

Putting '' around a value makes Access think it's a text value?

 

[namliam]

Is your Last_Renewal an actual date field, or a text field?
If the field is a date field, can this field be empty?
If the field is a text field, what format are you entering dates in?

 

[CJ_London]

Putting '' around a value makes Access think it's a text value?

Yes - you use them for literal strings e.g.

[myField]='smith'

you don't use them if comparing with another field

[myField]=[SomeOtherField]

For literal dates you should use # and the format is the US standard e.g.

[myDate]=#05/15/2013#

but when comparing with another field it is just

[myDate]=[SomeOtherDate]