Showing results for 
Search instead for 
Do you mean 
Community Home Request Access Read Blogs Share Your Ideas Search Community View My Settings
Reply
New Member
timothywelch
Posts: 13
Country: United Kingdom
Message 1 of 7 (2,501 Views)

Incorrect Results when using a Query on "Date Field" and "Month Equals"

I'm using ACT! by Sage Premium 2009 (11.0) Version 11.1.183.0 and I've done this query:

 

 

query_error 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

...and you can see although I'm only looking for anything where the date has "06" as the month, it is bringing up other records as well (they appear to be dates where the days are 01 of the next month).

 

Can you please confirm this is a bug and/or advise of a solution.

Please use plain text.
Platinum Elite Contributor
Roy_Laudenslage
Posts: 5,628
Country: United States
Message 2 of 7 (2,475 Views)

Re: Incorrect Results when using a Query on "Date Field" and "Month Equals"

Good catch. It's related to dates that are on the first or last day of the month. All dates in SQL have a time components and that appears to be throwing the query off. I've separately reported it as a bug.
Roy Laudenslager
ACT! Certified Consultant
ACT! Report Expert
Tech Benders
royel@techbenders.com
Please use plain text.
New Member
timothywelch
Posts: 13
Country: United Kingdom
Message 3 of 7 (2,465 Views)

Re: Incorrect Results when using a Query on "Date Field" and "Month Equals"

Thanks, but I'm really disappointed. We used to use Act 2000 and it worked perfectly for us, but since now using Act 2009, we've found at least half a dozen bugs in what I would call fairly basic functionality. It's a real shame, especially as fixes are so slow in coming. :smileysad:
Please use plain text.
Moderator
dlunceford
Posts: 4,380
Country: USA
Message 4 of 7 (2,459 Views)

Re: Incorrect Results when using a Query on "Date Field" and "Month Equals"

I am unable to reproduce in ACT! 2009 Premium 11.1.  I'm using the same dates in the screenshot.

 

Please use plain text.
Platinum Elite Contributor
Roy_Laudenslage
Posts: 5,628
Country: United States
Message 5 of 7 (2,454 Views)

Re: Incorrect Results when using a Query on "Date Field" and "Month Equals"

David

Check my other post to this thread. I was able to reproduce the problem. This is an ongoing problem with date fields in the ACT! program. All date fields have a time component in the SQL database and if it's a date field that does not use the time component, that time component is set to midnight. In addition the dates are time zone sensitive as an offset from Greenwich, accessing the date field for queries from a different time zone or influenced by daylight savings time, you can get these kind of one day date errors. My fix would be to set the default time to noon rather than midnight to at least minimize the problem. I have separately reported this as a bug on the ACC newsgroup.

Roy Laudenslager
ACT! Certified Consultant
ACT! Report Expert
Tech Benders
royel@techbenders.com
Please use plain text.
Moderator
dlunceford
Posts: 4,380
Country: USA
Message 6 of 7 (2,447 Views)

Re: Incorrect Results when using a Query on "Date Field" and "Month Equals"

I haven't been able to reproduce in house on any workstations or images.  After speaking with another agent, we found an existing reported issue for the UK locale that will be resolved in a future version.

Please use plain text.
Platinum Elite Contributor
Roy_Laudenslage
Posts: 5,628
Country: United States
Message 7 of 7 (2,445 Views)

Re: Incorrect Results when using a Query on "Date Field" and "Month Equals"

David

I'm sending you a private message 

Roy Laudenslager
ACT! Certified Consultant
ACT! Report Expert
Tech Benders
royel@techbenders.com
Please use plain text.