asked on

Query not detecting > number

I have an interesting issue with one of my queries, which is it wont let me select >90.

The query should look at a table of users, and report back on the users that have accounts still active but left more than 90 days ago. The query works great without a >90 criteria:-

SELECT [_Users].User, [_Users].Username, [_Users].Branch, [_Users].[Finish Date], DateDiff("d",[_Users].[Finish Date],Now()) AS [Days since left]
FROM _Users
WHERE (((IsNull([Date Accounts Removed]))=True) AND ((IsNull([Finish Date]))=False));

Open in new window

But as soon as you add >90 in it doesnt work, telling me 'Data type mismatch in criteria expression':-

SELECT [_Users].User, [_Users].Username, [_Users].Branch, [_Users].[Finish Date], Int(DateDiff("d",[_Users].[Finish Date],Now())) AS [Days since left]
FROM _Users
WHERE (((Int(DateDiff("d",[_Users].[Finish Date],Now())))>90) AND ((IsNull([Date Accounts Removed]))=True) AND ((IsNull([Finish Date]))=False));

Open in new window

I initially thought it wasnt detecting that the coloum was numeric (hence the int() ), however once the query loads i can select >90 and it will show me the results.

Any ideas?

Guy Hengel [angelIII / a3]

datediff IS already int, no need to cast that?!
just remove Int( ... ) part from the expression

also, do not use ISNULL() function , but:

SELECT [_Users].User, [_Users].Username, [_Users].Branch, [_Users].[Finish Date], DateDiff("d",[_Users].[Finish Date],Now()) AS [Days since left]
FROM _Users
WHERE [Date Accounts Removed] IS NULL 
  AND Finish Date] IS NOT NULL
  AND DateDiff("d",[_Users].[Finish Date],Now()) > 90 ;

Open in new window

you could also do like this:

SELECT [_Users].User, [_Users].Username, [_Users].Branch, [_Users].[Finish Date], DateDiff("d",[_Users].[Finish Date],Now()) AS [Days since left]
FROM _Users
WHERE [Date Accounts Removed] IS NULL 
  AND Finish Date] IS NOT NULL
  AND [_Users].[Finish Date] <  DATEADD("d", Now(), -90) ;

Open in new window

tonelm54

ASKER

The first query, gives the same issue, however the second runs, but returns 'Days since left' with results of 31,31,35 and -149, also it miss's several rows out completly.

Totally confused :-S

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

tonelm54

ASKER

That would help, for some reason it has been set as a text field, not a date field.

Guy Hengel [angelIII / a3]

that means 2 things:
* you should change the field to date type
* currently you have "at least" 1 row in the table with a invalidate "date" string ..

Query not detecting &gt; number

Query not detecting > number