Getting Started

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

    Posted 19 days ago
    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 14 days ago
    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
    ------------------------------



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

    Posted 13 days ago

    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
    ------------------------------