Help with date function please

  • Thread starter Thread starter Confused
  • Start date Start date
C

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
 
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
 
Confused said:
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
Click to expand...

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.
 
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
 
Back
Top