01-06-2009 03:06 PM
This is more a query question. I am try to create a query in ACT that will pull up any record where the values in two different date fields are the same. It just won't do it.
The query can compare dates using the < or > operators and distinguish between what date comes before or after another one.
This statement will get me nothing, even when there are records that meet this criteria:
TodaysDate = ServiceReminderDate
This statement, however, will give me a correct return:
TodaysDate > ServiceReminderDate
(By the way, these fields are hardcoded. I have to run a "replace fields" every day to my database to keep TodaysDate current because I don't know of any way to get the query to check the system clock.)
Is there something about date values to where they can be compared but not seen to be equal?
Thanks for any insight!
01-06-2009 03:25 PM
01-07-2009 12:09 PM
I was hoping to dodge the version question. Lol. It's 220.127.116.112, aka Peachtree Contact Manager Powered by ACT!. There are plans to upgrade to 2009, but only if this system we're trying to implement in this version appears to work out. We are a company that services our customer's equipment, and I'm using ACT not only as a CRM, but also as a system that manages the servicing of their equipment. Other than this one little thing, it's working great.
I am using the advanced query, and I didn't imagine there would be differences in query language / functionality across different versions.
Here is the actual query:
"RecordType" = "equipment" && "In Service" = "yes" && "Compliant" = "yes" && "U-Today's Date" = "U-3 Week Warning"
The RecordType field distinguishes between a contact and a piece of equipment.
In Service just means that the item is in service, and has not been retired.
Compliant means that the item is not yet overdue for servicing. (We only care if it's overdue or if it's up for service withing 3 weeks.)
U-Today's Date is nothing but a date field that I update daily.
U-3 Week Warning is also a date field, which is also manually entered (during the time of servicing.)
I have stripped out everything except "U-Today's Date" = "U-3 Week Warning" and ran it on test records where this condition is met, and it still doesn't work.
As I said, the query IS able to compare dates. I use the below query to hunt down equipment that hasn't been in for a while and is expired for a service date:
"RecordType" = "equipment" && "In Service" = "yes" && "Compliant = "yes" && "Next Test Date" <= "U-Today's Date"
This works by checking those conditions, and then seeing if today's date is passed the next test date. No problems there.
So it seems to be able to compare two dates, but not be able to tell if two date values are equal.
01-07-2009 01:44 PM