> 100 is causing strange results

[Guest]

I have what I think should be a really simple query.
I am trying to create a 'all customers not contacted in X days' query where
x is a user input. So, to simplify the output, I have the customer id, and
the contact date, and an expression (calculated field) that is (todays date
minus the date of contact). As mentioned, the criteria for the todays date
minus the date of contact is a user input, eg: >=[enter days].

When the user enters a value lower than the lowest value, all works fine,
but when the user enters a value greater than the lowest value, the recordset
doesn't produce any results where the calculated field is greater than 100?

That is, where a customer hasn;t been contacted in 101 days, it doesn't
appear in the resulting recordset even f the variable input is 50 days.
Really weird. All the customer contacted up to 99 days seems to work fine.
Any ideas?

When I create this exact same query where the variable criteria is >= [enter
date], wher the date is the date of last contact, it works fine, it finds all
the records after the user input date, including those contacted over 100
days ago.

This is very confusing and sooooooo frustrating for something that should be
so simple.

SQL below.

SELECT CUSTOMER_CONTACT_NOTES.conta_date, CUSTOMER.cust_id,
Date()-[conta_date] AS Expr1
FROM CUSTOMER INNER JOIN CUSTOMER_CONTACT_NOTES ON CUSTOMER.cust_id =
CUSTOMER_CONTACT_NOTES.cust_id
WHERE (((Date()-[conta_date])>=[enter days]));

Any assistance is appreciaed.

 

[Brendan Reynolds]

When you deduct a date from another date (or a number from a date) the
result is a date, so you're trying to compare the number 100 to a date, a
comparison that will only be true if the date is 9 April 1900! Try using
DateDiff() instead to get the number of days between two dates. Here's a
couple of Immediate window examples that may help to illustrate the issue
....

? date - 10
11/12/2004
? datediff("d",date()-10,date)
10

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Guest]

Awsome. Thanks for the tip Brendan.

My query now is as follows and works a treat.

ELECT CUSTOMER_CONTACT_NOTES.conta_date, CUSTOMER.cust_id,
DateDiff("d",Date(),[conta_date]) AS NoContactTime
FROM CUSTOMER INNER JOIN CUSTOMER_CONTACT_NOTES ON CUSTOMER.cust_id =
CUSTOMER_CONTACT_NOTES.cust_id
WHERE (((DateDiff("d",Date(),[conta_date]))<=-[enter days]));

When you deduct a date from another date (or a number from a date) the
result is a date, so you're trying to compare the number 100 to a date, a
comparison that will only be true if the date is 9 April 1900! Try using
DateDiff() instead to get the number of days between two dates. Here's a
couple of Immediate window examples that may help to illustrate the issue
....

? date - 10
11/12/2004
? datediff("d",date()-10,date)
10

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

I have what I think should be a really simple query.
I am trying to create a 'all customers not contacted in X days' query
where
x is a user input. So, to simplify the output, I have the customer id,
and
the contact date, and an expression (calculated field) that is (todays
date
minus the date of contact). As mentioned, the criteria for the todays
date
minus the date of contact is a user input, eg: >=[enter days].

When the user enters a value lower than the lowest value, all works fine,
but when the user enters a value greater than the lowest value, the
recordset
doesn't produce any results where the calculated field is greater than
100?

That is, where a customer hasn;t been contacted in 101 days, it doesn't
appear in the resulting recordset even f the variable input is 50 days.
Really weird. All the customer contacted up to 99 days seems to work fine.
Any ideas?

When I create this exact same query where the variable criteria is >=
[enter
date], wher the date is the date of last contact, it works fine, it finds
all
the records after the user input date, including those contacted over 100
days ago.

This is very confusing and sooooooo frustrating for something that should
be
so simple.

SQL below.

SELECT CUSTOMER_CONTACT_NOTES.conta_date, CUSTOMER.cust_id,
Date()-[conta_date] AS Expr1
FROM CUSTOMER INNER JOIN CUSTOMER_CONTACT_NOTES ON CUSTOMER.cust_id =
CUSTOMER_CONTACT_NOTES.cust_id
WHERE (((Date()-[conta_date])>=[enter days]));

Any assistance is appreciaed.