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
------------------------------
  • Hi Amber, 

    I would suggest removing the extra space before and after the "OR" to see if that fixes the issue. Also, it does seem like you should be using "AND".

    -Sharon

    ------------------------------
    Quick Base Junkie
    Quick Base Junkie
    https://quickbasejunkie.com
    ------------------------------
    • JohnCrosland's avatar
      JohnCrosland
      Qrew Trainee

      Hi Amber,
      Like Sharon, the only problem that I see with your formula is the OR instead of the AND.

      According to this, https://help.quickbase.com/user-assistance/formula_queries.html, you can create a Formula Field for the first day of the month or the month name, and then use Size([Formula Field])

      If you just need this information to be summarized, then I'd recommend a Summary report, then you can use Month as the Combine by inside of the Grouping of the date field.

      Good Luck!

      John



      ------------------------------
      John Crosland
      ------------------------------