Discussions

 View Only
  • 1.  Query to find all records that have a date field in the same month

    Posted 05-06-2022 13:17
    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
    ------------------------------


  • 2.  RE: Query to find all records that have a date field in the same month

    Posted 05-06-2022 19:22
    Edited by Jeff Peterson 05-06-2022 19:25
    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
    ------------------------------



  • 3.  RE: Query to find all records that have a date field in the same month

    Posted 05-06-2022 21:43
    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
    ------------------------------