Dynamically select months

[Ben_Entrew]

Hi all,

I got the following issue:

There's a table containing all sales quantities for each month, looks like:

RepMonth Quantity
012013 55
022013 4654
...
112013 4645
122013 778


Now the User should decide how many months he wants to see.
Therefore I tried this:

Actuals is the month the User defines :

Public Sub TEST()
 Dim Feld1() As Integer
 Dim x As Integer
 Dim I As Integer
 Dim t As String
 'x = actuals - 1
 
 ReDim Feld1(actuals)      
    For I = 1 To actuals   
        Feld1(I) = I    
    t = Feld1(I) & year
 If Len(t) = 5 Then
 t = 0 & t
 Else
 End If
 
 Debug.Print t

    Next I

End Sub

Can I use this somehow in an SQL statement?
Instead of Select RepMonth,Quantity FROM TABLE_TEST
WHERE RepMonth IN ('012013','022013','032013')

I want to keep this Where statement variable.

Thanks in advance.

Regards,
Ben

 

[plog]

You essentially need to do a calculation on your month field, but your data doesn't lend itself to that because its a string. My suggestion is to convert RepMonth to a date, then convert your data to the first day of the month. 012013 becomes 1/1/2013, 022013 becomes 2/1/2013. Doing that allows you to use the built-in date functions (http://www.techonthenet.com/access/functions/) and use dates (and calculated dates) in your WHERE clause.


You posted random code, mentioned that users will be pulling this data but didn't say how. Do you have a form they interface with? How do they say they want 5 months of data?

 

[Ben_Entrew]

Yes I created a form, where the User can type in the count of months he wants to see.

This Reporting_Month format appears also in many other tables here.

Therefore I wouldn't like to change it, if not absolutely neccessary.

 

[plog]

I would create a query then to convert your ReportMonth to an actual date (using the CDate function: http://www.techonthenet.com/access/functions/datatype/cdate.php), then use the number of months they want in the WHERE clause.

 

[Ben_Entrew]

Hi all,

can I use a collection for this issue?
I thought about this:

Got one table called ACTUALS containing the column Reporting_Month.
When I create a query calles Reporting_Months showing me the Reporting_Month and the Sum of sold items.
Now I have sth. like this:
012013 4442
022013 3245
..
122013 21321


How can I assign these Reporting_Months into a collection variable?

I tried this:

Public Sub TEST()
Dim colRecordsets As Collection
Dim rs As DAO.Recordset

Set rs = colRecordsets("Reporting_Months")

End Sub

Something is still wrong

The next step would be if the USER tells the program he wants to use the first three months from the table ACTUALS.
I could shrink the collection variable to the first three indexes....

Do you think this is the right way???

Regards,
Ben

 

[plog]

No I do not think this is the right way. My first post is the right way; my second post is a convenient hack to accomodate your data. Your method is an inconvenient hack to accomodate your data that isn't fully formed yet.

Let's say you get your method to work. How are you going to know what is the 'next' month? At some point you are going to need to order them, correct? Here's how your 2013/2014 data will be ordered:

012013
012014
022013
022014
...
...
122013
122014

That's how strings get ordered. You're not going down the right path.