Date functions

[Myysterio]

I am attempting to take the current time and subtract another fields time and get a number of months in decimal format and then compare it to a range to set criteria for a query.


How do I enter ((now()-[arrivaldate])/30.4)>=0 and make it see if that value is truely >=0 in months.

 

[John Big Booty]

Check out the DateDif() function. There is a list of other date functions here.

 

[Myysterio]

problem with datediff() is apparently it doesnt give me the proper syntax when imbedded in a control source in a form and it doesnt do decimals.

=IIf([AFSC]="2A5X1","N/A",DCount("*","1-Primary Table","[phaseonereq]=Yes and [phaseonecomp]=null and ((Now()-[arrivaldate])/30.4)>=0 and ((Now()-[arrivaldate])/30.4)<=2 and [phaseonestatus]='UNQAL'"))

I am attempting to replace ((Now()-[arrivaldate]) with something that returns a number, instead of a date.

 

[John Big Booty]

Have you had a look at some of the other date/time functions, if you are only interested in time have you had a look at the time function Time(). Perhaps this link might help you.

 

[Myysterio]

I'm specifically trying to get the time between two dates.

 

[Myysterio]

This syntax "works", it just doesnt return anything

=IIf([AFSC]="2A5X1","N/A",DCount("*","1-Primary Table","[phaseonereq]=Yes and [phaseonecomp]=null and datediff('m', [arrivaldate], date())>=2 and datediff('m', [arrivaldate], date())<4 and [phaseonestatus]='UNQAL'"))

 

[Myysterio]

Yes I am definately having an issue with the datediff() command. I rewrote the command as an IIF(IIF(IIF))) statement and put it into a query and moved the falses around. I managed to find out that dispite the face datediff(expr)=1 that it is not >=0 and <2.