Help with date function please

[Confused]

Hi:

I am using Access 2003 and am having problems with a date function. On my
form is a field call "jobdate" and it represents the date the job was done.
I have another field called "paydate" that represents the payday. I need a
function that looks at the "jobdate" and if the date is within 1-15 of the
month, write month/15/year (8/15/04) in the paydate. Dates that are from
the 16 until the end of the month (each month has different number of days),
write the last day. Is something like this possible?


thanx

 

[Paul Johnson]

Use this expression to get paydate:

IIf(Day(jobdate) < 16, _
DateSerial(Year(jobdate), Month(jobdate), 15), _
DateSerial(Year(jobdate), Month(jobdate) + 1, 1))

HTH
Paul

 

[Dirk Goldgar]

Hi:

I am using Access 2003 and am having problems with a date function.
On my form is a field call "jobdate" and it represents the date the
job was done. I have another field called "paydate" that represents
the payday. I need a function that looks at the "jobdate" and if the
date is within 1-15 of the month, write month/15/year (8/15/04) in
the paydate. Dates that are from the 16 until the end of the month
(each month has different number of days), write the last day. Is
something like this possible?


thanx

If this value can always be calculated and cannot be overridden,
[paydate] can be a calculated control with this controlsource:

=IIf(Day(jobdate)<=15, DateSerial(Year(jobdate), Month(jobdate),
15), DateSerial(Year(jobdate), Month(jobdate) + 1, 0))

Note that the above was wrapped by the newsreader, but was actually
posted all on one line.

 

[Paul Johnson]

Oops, I made it return the first of the next month, not the last day of the
job month (for jobs on or after the 16th). I meant to subtract a day, using
DateSerial(Year(jobdate), Month(jobdate) + 1, 1)-1) for the last part of the
IIF statement, but sent the post before finishing.

I didn't see Dirk's simultaneous post at the time I was sending mine, but he
shows us a neat trick, using zero in the dateserial function to return the
day *before* the first of the specified month. I like that better.

Paul