Need help

[Randy]

I have a formula of sort that is suppose to count the number of days between
two dates. I have that part down I think. I need this to happen only when the
beginning date is a day other than the first day of a month.

In other words if the date is 3/1/06 I need the formula to put in a zero
“0â€â€¦if the date is anything other than the 1st of a month then it should
calculate the number of days between the two dates.

Here is what I have so far…The calculation part seems to work but entering a
zero if the date is the 1st day of a month is not working.


=IIf((DatePart("d",[lease begin date]="1"),"0"),Sum((DatePart("d",[lease end
date]-[lease begin date]-1,0))))

Any assistance will be greatly appreciated. Thank you for your time.

 

[Steve Schapel]

Randy,

There are a few problems with what you've got there. For one, you have
quotes around numerical values, which is not right. Also, I am not sure
of why you have the Sum function in there, as this does not seem to
relate to anything in your description of the problem. And the second
DatePart function is not used appropriately. Assuming I understand what
you want, try it like this...

=IIf(Day([lease begin date])=1,0,[lease end date]-[lease begin date]-1)

 

[Randy]

Thanks Steve...you are awesome...Worked like a charm after a little
tweaking...this is what I finally ended up with that gave me what I was
looking for.

=IIf(Day([lease begin date])=1,0,DatePart("d",[lease end
date])-DatePart("d",[lease begin date])+1)

Thank you very much for your assistance.
--
Randy Street
Rancho Cucamonga, CA

Randy,

There are a few problems with what you've got there. For one, you have
quotes around numerical values, which is not right. Also, I am not sure
of why you have the Sum function in there, as this does not seem to
relate to anything in your description of the problem. And the second
DatePart function is not used appropriately. Assuming I understand what
you want, try it like this...

=IIf(Day([lease begin date])=1,0,[lease end date]-[lease begin date]-1)

--
Steve Schapel, Microsoft Access MVP

I have a formula of sort that is suppose to count the number of days between
two dates. I have that part down I think. I need this to happen only when the
beginning date is a day other than the first day of a month.

In other words if the date is 3/1/06 I need the formula to put in a zero
“0â€â€¦if the date is anything other than the 1st of a month then it should
calculate the number of days between the two dates.

Here is what I have so far…The calculation part seems to work but entering a
zero if the date is the 1st day of a month is not working.


=IIf((DatePart("d",[lease begin date]="1"),"0"),Sum((DatePart("d",[lease end
date]-[lease begin date]-1,0))))

Any assistance will be greatly appreciated. Thank you for your time.