Chg "day" portion of DateAdd Date

[Vicki-S]

I have a query where I'm subtracting months from a date field. I want to
change the DAY portion of the final date to a what I've calculated in another
field [DUEDAY]. Here's what I have so far (this is working the way I want it
to):

FirstDueDt_LTERM: DateAdd("m",-([LNS35-LTERM]-1),[LNS35-LMATDATE])

I'm no expert at this: Please be specific with what I need to do so that
instead of the [FirstDueDt_LTERM] being 12/15/2005, if my [DUEDAY] = 01 I
want the [FirstDueDt_LTERM] date to be 12/01/2005.

I'm using Access 2002-2003). Thanks in advance for your help.

 

[fredg]

I have a query where I'm subtracting months from a date field. I want to
change the DAY portion of the final date to a what I've calculated in another
field [DUEDAY]. Here's what I have so far (this is working the way I want it
to):

FirstDueDt_LTERM: DateAdd("m",-([LNS35-LTERM]-1),[LNS35-LMATDATE])

I'm no expert at this: Please be specific with what I need to do so that
instead of the [FirstDueDt_LTERM] being 12/15/2005, if my [DUEDAY] = 01 I
want the [FirstDueDt_LTERM] date to be 12/01/2005.

I'm using Access 2002-2003). Thanks in advance for your help.

You may know what data is stored in [LNS35-LTERM] and [LNS35-LMATDATE]
but we certainly don't.
i would expect that the DateSerial function is best suited for what I
think you want. Look it up in VBA help
=DateSerial(Year,Month,1)
to set the value to the 1st day of the month.

 

[John W. Vinson]

I have a query where I'm subtracting months from a date field. I want to
change the DAY portion of the final date to a what I've calculated in another
field [DUEDAY]. Here's what I have so far (this is working the way I want it
to):

FirstDueDt_LTERM: DateAdd("m",-([LNS35-LTERM]-1),[LNS35-LMATDATE])

I'm no expert at this: Please be specific with what I need to do so that
instead of the [FirstDueDt_LTERM] being 12/15/2005, if my [DUEDAY] = 01 I
want the [FirstDueDt_LTERM] date to be 12/01/2005.

I'm using Access 2002-2003). Thanks in advance for your help.

Use DateSerial() instead of DateAdd(), if I'm understanding you correctly. Are
[LNS35-LTERM] and [LNS35-LMATDATE] actually fieldnames? I've had all sorts of
problems in tables (not mine!) with hyphens or slashes in fieldnames...

Try

DateSerial(Year([LNS35-LMATDATE]), Month([LNS35-LMATDATE]) - [LNS35-LTERM] +
1, [DUEDAY])

May need some tweaking but it's probably simpler than DateAdd will end up
being.

 

[KARL DEWEY]

You will need to use the calculations for [DUEDAY] instead of the field name.

FirstDueDt_LTERM:
DateSerial(Year(DateAdd("m",-([LNS35-LTERM]-1),[LNS35-LMATDATE])),
Month(DateAdd("m",-([LNS35-LTERM]-1),[LNS35-LMATDATE])), [DUEDAY])