How to use Variables? For auto populating date range automatically each month

[Russellh]

Each month I want to pull billing records just for that month. Commercial accounts are always billed last each month but the date always varies. Currently I am having to enter the between dates by looking up a commercial account to see when it was billed the previous month (beginning date) and when it was billed in the current month ( add 1 day to it so it is included in query END DATE).

How would I automate that?



Each account has a bill month year and then a bill date as fields. So if I want May's records I need how would I pull the bill date field for april as beginning date and I need to pull the bill date for May and add 1 day to it for my end date. What would the syntax look like?

[A S MANN]

Not quit Clear,
In query filter Criteria Use Between #01/04/2014# To 01/05/2014# to fetch records of may month + 1 day.
You can also type date in form From Date To Date and Filer Query.
Hope this will Clear air.

[ItsMe]

Build a WHERE clause within your query that uses the BETWEEN operator. In Access 2010 it is super easy. Use the grid at the bottom of the query builder window, in the Criteria field. Start by typing the word BETWEEN.

Between 1-1-2010 AND 12-31-2014

The above criteria can be made Dynamic by referencing control(s) on a form and or other field(s) within the query.

[Russellh]

Build a WHERE clause within your query that uses the BETWEEN operator. In Access 2010 it is super easy. Use the grid at the bottom of the query builder window, in the Criteria field. Start by typing the word BETWEEN.

Between 1-1-2010 AND 12-31-2014

The above criteria can be made Dynamic by referencing control(s) on a form and or other field(s) within the query.

I don't think I am making the problem clear. I don't want to have to enter a date and it will change from day to day or month to month so entering a between will fail the purpose. I would like for it to always the current day's date or read a date from a field and add 1 to it and use that date. But if it reads a date and adds 1 it will have to be like:

I want all the records will billmonth May and between "Record x's bill date on Bill month April plus 1" and "may bill date plus 1".
Set up with variable or some method to always chose a starting date that will be the bill date for a record x last month and an ending date that will be the same records May Bill Date plus 1 day (so it captures that record)

That way nothing ever has to be entered and it can always just be ran. Seeing as the accounts arent billed on the same days each month the range of dates I need all records for changes each month. But I could base the range off of an account that is always the last billed each month. So if I do a > last months bill date and < this months bill date plus 1 i get the range I want. But isnt just this one record I want. I want all the records for all accounts during that range of dates.
Let me know if that isnt clear. And thanks again for all the help.

[ItsMe]

You can cause your parameter to be dynamic by replacing the dates with references to fields within the query and or controls within an open form.

[June7]

As ItsMe said, use a form for input of filter criteria into UNBOUND controls and query references the controls. The controls can be set with a default value such as Date() and Date()+1 or some other expression.

I don't use dynamic parameterized queries. I would use a form or report and apply filter criteria to the form/report when it opens:

DoCmd.OpenReport "report name", , , "[datefield] BETWEEN #" & Me.tbxStart & "# AND #" & Me.tbxEnd & "#"

[rpeare]

Maybe I'm misreading this but the OP seems to want to maintain a history of billing dates such that he can run his billing from the day after the last billing date to the current date.

If that's the case you could do 1 of two things:

1. Add a field to your existing detail table something like DATE_BILLED, then update that field when you run your bills. Then you could look for any record newer than your last bill date (or basically any record that doesn't have a bill date at all)

2. Create a table that tracks your billing periods. This is a bit more clunky but still manageable