datediff()

[Myysterio]

when I do

datediff(expr)<=0 and datediff(expr)>=2

and the result being evaluated is 1, why does it not count as true? Better yet, how do I fix it so it does?

 

[DCrake]

You code is wrong

There are three things you need to provide

1 the time value you want to see (days, months, years, etc)
2 the lower date
3 the upper date

Example

Elapsed = DateDiff("d",Date(),[DOB])

This shows the differenc ein days between the date of birht and today.

Form more help type in DateDiff and press F1.

David

 

[Myysterio]

I am well aware, I was short handing it cause the info between the parenthesis doesnt appear to matter. I had it set to "m" and "d" and neither worked.

 

[DCrake]

apart from not being a mind reader, a number can't be <=0 AND >=2

Why not if expr<> 1

David

 

[Myysterio]

a number cant be both less than 0 and greater than 2 but a result can be both.

plus, the result can be a range of numbers, the key is I am attempting to evaluate one table against multiple logical tests to get records that meet my criteria. the only part that doesnt work is this datediff() issue. I've even plugged the date diff into a query to test the results and evaluate true/false against a number I could clearly see is 1 but it didn't work.

It doesn't work if I switch the symbols around either. there's something clearly wrong with the conversion and I don't know what.

 

[Brianwarnock]

Dear me David you really are a poor mind reader, it was obvious that expr and expr were different otherwise he would have written expr1 and expr2.

why not just <1 and >1 or do we have decimal places involved?

Brian

Edit what is the full formula

 

[Myysterio]

actually the expr in the parenthesis are exactly the same. I am just evaluating [date ranges] out of tables vs. the current date to get records within my current criteria.

its something along the lines of =dcount(*, 'tablename', criteria1 and criteria2 and criteria3 and datediff(expr)>X and datediff(expr)<Y)

I don't have the exact code because its at work.

Needless to say, I have got rid of the datediff() and it counts it perfectly. the Datediff() is clearly not converting to proper integers in the statement and failing the logical evaluation.

 

[Brianwarnock]

actually the expr in the parenthesis are exactly the same. I am just evaluating [date ranges] out of tables vs. the current date to get records within my current criteria.

Then David's comment in post 4 must stand as both Datediff's will produce the same result.

Brian

 

[Myysterio]

but if you datediff("m", [tabledate], date()) in a query, you get a range of dates depending on which value of [tabledate] is being evaluated. I've plugged it into a query, I can clearly see the result it 1 but it is failing a logical evaluation even when I evaluate the query without all the dcount() nonsense.

It also fails when you do the math manually with (now()-[tabledate])/30. You can clearly see the value is between 0 and 2 (usually its 1.220202020002 or something) but its failing the logical evaluation.

The eventual wish is to make a report which counts up the records which meet the criteria i have set and are between 0-2 months, 2-4 months, 4-6 months, 6-8 months and 8+ months.

 

[DCrake]

Try putting an extra set of brackets around the mathmatical part of the filter so that this will be processed first. Think BODMAS.

David

 

[Myysterio]

Done, no go. Still is returning a weird result.

 

[DCrake]

It also fails when you do the math manually with (now()-[tabledate])/30.

You should be using Date() instead of Now(). Now() includes the time element as well

If you take out all the criteria except the datediff element do you still get garbage?

David

 

[Myysterio]

I was just using the now() equation as an example. Datediff(), now() and date() all return the same result of false when datediff("m", [tabledate], date())=1.

The equation I am using is

DCount("*","1-Primary Table","[phaseonereq]=Yes and [phaseonecomp]=0 and datediff('m', [arrivaldate], date())>=0 and datediff('m', [arrivaldate], date())<2 and [phaseonestatus]='UNQAL'"))

I have literally made that an IIF(IIF(IIF())) statement to test to check validity and it broke every time at the datediff(expr) part.

apparently datediff(expr)=1 does not equal 1.

 

[DCrake]

If you insist on posting syntax that is different form the code you are using then how do you expect us to spot any possible errors. This is becoming a theme in your posts.

David

 

[Myysterio]

I didnt have the exact code till i got to work. The exact details of my entire line are irrelevant. I was just posting it to show you what I was doing and where it was breaking to validate my point.

The problem isnt with the syntax, the syntax works. The conversion of the datediff() is picking up a false when it isn't because its evaluating something as other than what it appears to be.

 

[DCrake]

When you are next at work and have access to your actual code then we can recommence our conversation using real examples and real data.

David

 

[Myysterio]

What do you want to see? I am at work finally. Thats why I posted that code a few posts ago. I didnt have it for the first 4 because I hadn't left yet.

Is there a way to convert an integer in the code to a date using some sort of wrapped command? For instance, if I wanted to make something an integer I would use INT([value]) but with dates?

 

[DCrake]

I have just tried this in the immediate window

datediff("d",#13/09/2009 00:15:29#,#23/10/2009 15:15:29#)
As you can see the dates are different and the times are different but the answer was 40

I did not get any long decimal places, as I said lets use some real data it may be something to do with that. Remember Garbage In Garbage Out.

David

 

[Myysterio]

i have
datediff("m", [arrivaldate], date())=x
values I am getting for the first several records are
1,7,14,8,2,1,4
when I am evaluating if x<2 and x>0 I am getting false for ALL RECORDS.

 

[DCrake]

Come on, lets be honest and try to help me help you. if your results are
1,7,14,8,2,1,4 for the first 4 records then display them as they are appearing

Expr1 Expr 2

1 7
14 8
2 1
4 ???