DateSerial Problem

[Yecats131]

Hello,

I’m not sure if this belongs in the general section.. so I apologize ahead of time if it does not. I am trying to calculate a date field based on an EffectiveDate in another table. This is the expression that I have:

=DateSerial(Year([Hires]![EffectiveDate]),Month([Hires]![EffectiveDate]),Day([Hires]![EffectiveDate])+15)

I am getting a #Name error. If anyone can help, I'd appreciate it.

Stacey

 

[pbaldy]

Where exactly do you have that? It couldn't be in a table, if that's what you're trying to do. It could work in a query/form/report, as long as that table was part of the source.

 

[Yecats131]

Oh, I have a Hire form and this is on a sub form (BenefitHires)

 

[pbaldy]

I tested something similar and it worked fine. Can you clarify whether the Hires table is a part of the control source of the form containing the textbox? In my test I did not specify the table, so try dropping that. If the value is on the main form, try:


=DateSerial(Year(Forms!MainFormName.EffectiveDate),Month(Forms!MainFormName.EffectiveDate),Day(Forms!MainFormName.EffectiveDate)+15)

 

[Yecats131]

Oh, it seemed to work when I removed the table information.

Thanks!

 

[boblarson]

And just as an aside - there really isn't any reason to use DateSerial in this case since the entire date is from the same location. Simplify and go with either

=DateAdd("d", 15, [EffectiveDate])

or

=[EffectiveDate]+15

 

[Brianwarnock]

But don't forget the ) on the DateAdd

Sorry guys but I did the other day and the poster got an error as he copied my syntax exactly.

Brian

 

[Yecats131]

Hehe!

What is the difference between DateSerial and DateAdd?

 

[boblarson]

DateSerial builds a date from individual parts (year, month, and day) and DateAdd adds (or subtracts) a number of days, months, weeks, or years from a date.

 

[Yecats131]

Oh interesting. So i take it I could modify:

=DateAdd("d", 15, [EffectiveDate])

to say

=DateAdd("y", 15, [EffectiveDate]) to update the year? or "m" to update the month?

Does anyone have a good site for these functions? I'd love to find a place to read about them all.

 

[dkinley]

http://www.techonthenet.com/access/functions/date/dateadd.php

-dK

 

[boblarson]

Oh interesting. So i take it I could modify:

=DateAdd("d", 15, [EffectiveDate])

to say

=DateAdd("y", 15, [EffectiveDate]) to update the year? or "m" to update the month?

Yes, except in the case of year you need to use "yyyy" instead of just "y"

 

[Yecats131]

Ahh I see! Thank you everyone.