Skip to main content
Question

Query Question - search after date

  • September 1, 2022
  • 0 replies
  • 160 views

EdwardHefter
Forum|alt.badge.img+3
I am trying to count how many records there are that are tied to the same customer and that come after the current record by date using the formula:
Size(GetRecords("({6.EX."&[CustomerID]&"}AND{8.AF.'"&[Activity Date]&"'})",[_DBID_ACTIVITIES]))

Where field 6 is CustomerID and field 8 is [Activity Date]. The formula should be looking for all records with the same CustomerID (that part works - I took out the second part to check) and with an Activity Date after the current one. If there are 3 activities for a customer on different dates, I would expect one to have a value of 2, another to have a value of 1, and the third to have a value of 0 (no records with a date later than the latest one).

Instead, I am getting a seemingly random result of 1s and 2s (no customer has more than 2 activities yet).

I don't know if it matters, but since this system is for use in Canada, it has the date format the "right" way, with "DD-MM-YYYY" rather than our American way of "MM-DD-YYYY"

What's wrong with my syntax or logic?

------------------------------
Edward Hefter
www.Sutubra.com
------------------------------
This topic has been closed for replies.

RaziD
Forum|alt.badge.img+3
  • Registered
  • September 2, 2022
It is maybe happening because of parenthesis you have for query.
Try this:

Size(GetRecords("{6.EX."&[CustomerID]&"}AND{8.AF."&[Activity Date]&"}",[_DBID_ACTIVITIES]))

------------------------------
Razi D.
Desta Tech LLC
razi@destatechs.com
------------------------------