Community
Showing results for 
Search instead for 
Do you mean 
Reply

Query Question About Date Values

Copper Contributor
Posts: 7
Country: United States

Query Question About Date Values

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!

Platinum Elite Contributor
Posts: 6,652
Country: USA

Re: Query Question About Date Values

Two questions: What version of the ACT! program are you using? Are you using the Advanced Query? I just tried what you seem to be doing on ACT! 2008 premium and was able to easily find all the records edited today.
Roy Laudenslager
ACT! Certified Consultant
ACT! Report Expert
Durkin Impact Report Designer
www.techbenders.com
royel@techbenders.com
541-343-8129
Copper Contributor
Posts: 7
Country: United States

Re: Query Question About Date Values

I was hoping to dodge the version question.  Lol.  It's 5.0.4.502, 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.

 

 

 

 

Platinum Elite Contributor
Posts: 6,652
Country: USA

Re: Query Question About Date Values

You are making a fundamental logic error, you are trying to compare the values in two fields and that can't be done in ACT! 5 or later. The logic required for each element of the queries is field - operator - value. Elements can be grouped with AND, OR, and parenthesis to create a complex query and to control the order of evaluation. I believe what you want to do is possible with ACT! 2009 because you can declare Today as a value. ACT! 5 did not have that capability.
Roy Laudenslager
ACT! Certified Consultant
ACT! Report Expert
Durkin Impact Report Designer
www.techbenders.com
royel@techbenders.com
541-343-8129