Re-run query with new date criteria, saving results: VBA or Macro?

[OB7]

Hello,

I have MS Access query with two date fields, and I wish to run the same query multiple times. Each time the query is run, there will be a different set of dates and the query will produce a numeric value that is related to those dates. I wish to save the dates, as well as the associated numeric value, in ?a multidimensional array? Is this possible with a macro (or some alternative method), or must I use VBA?

I have some programming experience, but none with VBA. Here is an algorithm:

1. Set initial Start Date
2. Set initial End Date
3. Run query and save (1) initial Start Date, (2) initial End Date, and (3) numeric query result ?in an array?

Begin Loop:
4. Update Start Date (add 7 days to previous Start Date)
5. Update End Date (add 7 days to previous End Date)
6. Run query and save (1) Start Date, (2) End Date, and (3) numeric query result ?in an array?
7. Return to 4, exit Loop when Start and End Dates exceed Final Start and End Dates

Thank you for any assistance!

[rpeare]

You should only create an array if you are going to use that array in code subsequent to your pseudocode, in other words that data will be lost if you close the database or, depending on your code, run another process.

Other than that what you're asking is pretty easy it's called a multi dimensional array

https://msdn.microsoft.com/en-us/lib...(v=vs.90).aspx

You can add 'rows' to your array then cycle through the array retrieving elements of the row as you go along.

also.. you wouldn't add 7 days to your start and end date you'd have something more like

dim dStartDate, dEndDate, dCurrEndDate as date

dStartDate = #1/1/2016#
dEndDate = #12/31/2016#

do while dstartdate < denddate
    dcurrenddate = dateadd("d", 6, dstartdate)
    debug.print dstartdate & " - " & dcurrenddate

    dstartdate = dateadd("d", 1, dcurrenddate)
loop

just to make sure you don't accidentally overlap date ranges.

[John_G]

Does the query produce only one row each time it is run, with only one value of interest (other than the dates)? If so, you should probably be using a function instead of a query. In VBA, you cannot run a select query and then examine/save the results.

What do you want to do with the numbers you get, and what do you mean by "save" in this context? An array is only temporary.

Can you provide some clarification, please.

[OB7]

Hi John_G,

The query result is only one field (one row, one column): an integer. However, the Start and End Dates are actually referred to within Where clauses in MS Access Design View (so they aren't even included in the query output):

MS Access Design View criteria:
Start Date (where) : < = dd/mm/yyyy (call this date X1)
End Date (where) : > dd/mm/yyyy or Is Null (call this date Y1)

When this query is run, call the resulting integer N1

Thus, I had hoped to produce a table that I could use in Excel to produce a bar chart:

X1 Y1 N1
X2 Y2 N2
X3 Y3 N3
....

[OB7]

Thank you, rpeare. I will look at the link that you provided! However, I will need to find a way to save the dates and the query results, as I will use those to produce a chart in Excel.

[rpeare]

nothing preventing you from writing the information out as you produce it rather than loading it into an array.