Forum Discussion

AmberGartner's avatar
AmberGartner
Qrew Cadet
3 years ago

Query to find all records that have a date field in the same month

Can anyone help me figure out how to query to find all the records that have a certain date field that's in the same month (and year)? This is what we have, and it's correct for April, but then it's doing a backwards running total, so March is including April and March. Feb is including Feb, March and April. Thank you!


var text query ="{48.OAF.'"&FirstDayOfMonth( [date field] )&"'} OR {48.OAB.'"&LastDayOfMonth (   [date field]   )&"'} ";

Size(GetRecords($query))

BTW - I tried using "AND" instead of "OR," but that returned a number of 1 or 0 usually, when it should be more like 10-30.
Thanks again!

------------------------------
Amber Gartner
------------------------------
formula queries
Formulas and functions
  • JeffPeterson1's avatar
    JeffPeterson1
    Qrew Captain
    3 years ago
    It should be like this:

    var text QUERY = "{48.OAF.'" & [Date Field] & "'}AND{48.OBF.'" & LastDayOfMonth([Date Field]) & "'}";

    Keep in mind that if your [Date Field] is a date/time field you'll need to convert it using ToDate()

    Also you have 'OAB' instead of 'OBF' in your query.

    ------------------------------
    Jeff Peterson
    ------------------------------
    • AmberGartner's avatar
      AmberGartner
      Qrew Cadet
      3 years ago
      I think I may have replied to you directly before somehow... but I did update the OAB to OBF -- THANK YOU for that. I had to keep in the FirstDayOfMonth formula in order for it to work, but then also changing the OR (back) to AND worked as well. So again, thank you thank you!!

      ------------------------------
      Amber Gartner
      ------------------------------