Baq to return each date within a two date parameters

Wonder if anyone has successfully built a BAQ to generate a list of dates between two date parameters? If you can share outline?

Thanks ahead of time.
I’m just starting on this one.
I saw some SQL examples listed on the web but…
TBD if anything will translate to a BAQ.

Two date params, one is StartDate, one is EndDate. Put the StartDate param on the date field of your table and do >=, put the EndDate param on the same field (with AND) and do <=.

There is a CTE that someone made, but the CTE fails after 100.

Still… I liked the example
I’m thinking I can get away with a months worth of days.
but… I see farther down in that thread there is a workaround if I need it.


I needed a cheat that required having a list of numbers from 1 … N - where N was driven by the user.

So I just had a UD table with 1000 rows, with Key1 = 1, 2, 3, 4, …999, 1000

Then I join that table (with a table criteria of Key1 <= N) and it gives me that many rows of what I join it to.

FWIW - this was to create N qtys of labels, with N specified during Order Entry.

OrderDtl.LblQty_c held an int for the num of labels to print
OrderDtl.LblText_c held the text to print

Joining OrderDtl to UD05, with UD05.Key1 <= OrderDtl.LblQty_c, would giver me multiple records for each OrderLine. Then the BAQ Report would print a label for each record. This allowed us to print different qty’s of labels for each order line, and didn’t require the user to enter a qty during printing.

FWIW, I usually just find a well populated table at least midly related to what I am doing and just return the date and group by it. I functionally get a list of every date that means something to what I’m doing. So far it’s worked out for me well. If I’m looking in the past, I can used parttran or laborDtl, as they usually have some record on a date, and if there wasn’t anything on that date, it’s probably not needed for what I’m doing anyways.

It hasn’t bit me yet doing it that way.

Yes, I am still thinking about getting dates from the parttran table. It’s about as related & well populated as I will get.

Even though I have the basic CTE generating a the date list…
I would still need to pull in A LOT of other data, where hairy linking is already involved.
Regardless of what I end up doing eventually… I like that CTE