append EOY date based on current year

[BladeCanyon]

Hello,

I am trying to come up with the proper syntax or function for an append query
that updates ExpirationDate based on IncidentDate. I have already created the
Date+365 for expiration of one year from incident. now, I need to create
expiration for end of incident year.

for example:
any incident expires 12/31 of that year.

Any hints or suggestions?

thanks in advance

BC

 

[Douglas J. Steele]

DateSerial(Year(IncidentDate), 12, 31)

 

[John W. Vinson]

Hello,

I am trying to come up with the proper syntax or function for an append query
that updates ExpirationDate based on IncidentDate. I have already created the
Date+365 for expiration of one year from incident.

That will fail in leap years - it'll be a day off. You can use

DateAdd("yyyy", 1, [IncidentDate])

instead.

now, I need to create
expiration for end of incident year.

for example:
any incident expires 12/31 of that year.

See Douglas's suggestion.

 

[BladeCanyon via AccessMonster.com]

Hello,

I am trying to come up with the proper syntax or function for an append query
that updates ExpirationDate based on IncidentDate. I have already created the
Date+365 for expiration of one year from incident.

That will fail in leap years - it'll be a day off. You can use

DateAdd("yyyy", 1, [IncidentDate]) instead. Yes, this worked. I was aware that it would be 1 day off on leap years. thanks for the fix.

now, I need to create
expiration for end of incident year.

for example:
any incident expires 12/31 of that year.

See Douglas's suggestion. these suggestions did not work type mismatch.

 

[John W. Vinson]

Please post your exact expression, and indicate the datatype of the
IncidentDate field - we were both assuming it was a Date/Time field; you'll
get a type mismatch if it's not.

 

[BladeCanyon via AccessMonster.com]

my apologies and I meant to say thanks again yesterday, I partly botched the
post. lol
DateAdd("yyyy",1,[IncidentDate]) work for the anniversary query. Thanks!!

for the End of year query:

yes, it is a datetime field. both fields IncidentDate and IncidentExpiration
are set the exact same format, short date

I tried both of these expressions with:
when I put in DateSerial(year_value, month_value, day_value) in the update to
field box in the query design, it changes it to DateSerial("year_value",
"month_value","day_value")I click run and I receive data type mismatch in
criteria expression.

when I put this DateSerial(Year(incident_year), 12, 31) in the update to
field box in the query design, it changes it to DateSerial(Year
("incident_year"),12,31), I click run and I receive data type mismatch in
criteria expression.
I believe I am getting the error becaue of the quotes, but it is putting them
in there automatically..

Thanks again, in advance for your assistance and persistance.
BC

 

[John W. Vinson]

when I put this DateSerial(Year(incident_year), 12, 31) in the update to
field box in the query design, it changes it to DateSerial(Year
("incident_year"),12,31), I click run and I receive data type mismatch in
criteria expression.

It should be

DateSerial(Year([IncidentDate]), 12, 31)

The square brackets around the fieldname are supposedly optional, but they
often seem to help convince the program that you really mean a fieldname
rather than a text string.

 

[BladeCanyon via AccessMonster.com]

Yes!! this worked. Thanks again for your assistance.
BC