Solved

How Can I Loop Through Table and Get Data (Numbers) and then Format in a SQL Table

Posted on 2013-05-14
29
209 Views
Last Modified: 2014-02-18
I have the following query/SQL Statement in MS Access (see code below).

Every day a new value (date) is added to the table (record source).  I want to convert this to VBA and use a SQL Statement instead of the query.

Using VBA how do I loop through the table (record source) and have it pull the new date and add to the query grid (sql statement), and have it include the If(IsNull . . . ) formatting statement.

Also, when a new month starts, the dates start over (so for example the sample code below is for May), once June comes around the dates start over with 06/01, etc., so to start out for a new month, there is less data (dates) in the grid, so it needs to adjust.

This is the SQL View of the MS Access Query:

SELECT DISTINCT [tblPreFinal II - NEW].[tblPreFinal I - NEW_Contract Number], 
[tblPreFinal II - NEW].State, 
IIf(IsNull([Denied/Cancelled/Duplicate]+[CMS Auto Enrolls]+[Group Enrollments]+[TOTAL Active/Open_]),0,[Denied/Cancelled/Duplicate]+[CMS Auto Enrolls]+[Group Enrollments]+[TOTAL Active/Open_]) AS [Grand Total], 
IIf(IsNull([Denied/Cancelled/Duplicate]),0,[Denied/Cancelled/Duplicate]) AS Denied_Canc_Dup, IIf(IsNull([CMS Auto Enrolls]),0,[CMS Auto Enrolls]) AS CMS_Auto_Enrolls, 
IIf(IsNull([Group Enrollments]),0,[Group Enrollments]) AS Group_Enrollments, 
[tblPreFinal II - NEW].[TOTAL Active/Open_], [tblPreFinal II - NEW].[TOTAL OCTOBER Active/Open], 
[tblPreFinal II - NEW].[TOTAL NOVEMBER Active/Open], [tblPreFinal II - NEW].[TOTAL DECEMBER Active/Open], 
[tblPreFinal II - NEW].[TOTAL JANUARY Active/Open], [tblPreFinal II - NEW].[TOTAL FEBRUARY Active/Open], 
[tblPreFinal II - NEW].[TOTAL MARCH Active/Open], [tblPreFinal II - NEW].[TOTAL APRIL Active/Open], 
[tblPreFinal II - NEW].[TOTAL MAY Active/Open], 
IIf(IsNull([05/01]),0,[05/01]) AS [05/01_], IIf(IsNull([05/02]),0,[05/02]) AS [05/02_], 
IIf(IsNull([05/03]),0,[05/03]) AS [05/03_], IIf(IsNull([05/04]),0,[05/04]) AS [05/04_], 
IIf(IsNull([05/05]),0,[05/05]) AS [05/05_], IIf(IsNull([05/06]),0,[05/06]) AS [05/06_], 
IIf(IsNull([05/07]),0,[05/07]) AS [05/07_], IIf(IsNull([05/08]),0,[05/08]) AS [05/08_], 
IIf(IsNull([05/09]),0,[05/09]) AS [05/09_], IIf(IsNull([05/10]),0,[05/10]) AS [05/10_], 
IIf(IsNull([05/11]),0,[05/11]) AS [05/11_], IIf(IsNull([05/12]),0,[05/12]) AS [05/12_], 
IIf(IsNull([05/13]),0,[05/13]) AS [05/13_], IIf(IsNull([After_Count]),0,[After_Count]) AS [After Count], 
[tblPreFinal II - NEW].[TOTAL MAY Active/Open_]
FROM [tblPreFinal II - NEW]
ORDER BY [tblPreFinal II - NEW].[tblPreFinal I - NEW_Contract Number];

Open in new window



You will need to scroll over to the right to see all the code.

Hope this is making sense.  I tried to word it the best I could.

Thanks,

gdunn59
0
Comment
Question by:gdunn59
  • 11
  • 10
  • 5
  • +1
29 Comments
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 39166422
Do you actually have fields called 05/08, etc?  That is not a good idea.  Is a crosstab query involved?  What do you mean by pulling the dates?  More information is needed.
0
 

Author Comment

by:gdunn59
ID: 39166436
Yes, there is a crosstab, that is why the fields are actually like 05/01, 05/02 etc.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39167034
by "new dates" do you mean "new columns"?
SELECT DISTINCT
      [tblPreFinal II - NEW].[tblPreFinal I - NEW_Contract Number]
    , [tblPreFinal II - NEW].STATE
    , IIf(IsNull([Denied/Cancelled/Duplicate] + [CMS Auto Enrolls] + [Group Enrollments] + [TOTAL Active/Open_]), 0, [Denied/Cancelled/Duplicate] + [CMS Auto Enrolls] + [Group Enrollments] + [TOTAL Active/Open_]) AS [Grand Total]
    , IIf(IsNull([Denied/Cancelled/Duplicate]), 0, [Denied/Cancelled/Duplicate]) AS Denied_Canc_Dup
    , IIf(IsNull([CMS Auto Enrolls]), 0, [CMS Auto Enrolls]) AS CMS_Auto_Enrolls
    , IIf(IsNull([Group Enrollments]), 0, [Group Enrollments]) AS Group_Enrollments
    , [tblPreFinal II - NEW].[TOTAL Active/Open_]
    , [tblPreFinal II - NEW].[TOTAL OCTOBER Active/Open]
    , [tblPreFinal II - NEW].[TOTAL NOVEMBER Active/Open]
    , [tblPreFinal II - NEW].[TOTAL DECEMBER Active/Open]
    , [tblPreFinal II - NEW].[TOTAL JANUARY Active/Open]
    , [tblPreFinal II - NEW].[TOTAL FEBRUARY Active/Open]
    , [tblPreFinal II - NEW].[TOTAL MARCH Active/Open]
    , [tblPreFinal II - NEW].[TOTAL APRIL Active/Open]
    , [tblPreFinal II - NEW].[TOTAL MAY Active/Open]
    , IIf(IsNull([05/01]), 0, [05/01]) AS [05/01_]
    , IIf(IsNull([05/02]), 0, [05/02]) AS [05/02_]
    , IIf(IsNull([05/03]), 0, [05/03]) AS [05/03_]
    , IIf(IsNull([05/04]), 0, [05/04]) AS [05/04_]
    , IIf(IsNull([05/05]), 0, [05/05]) AS [05/05_]
    , IIf(IsNull([05/06]), 0, [05/06]) AS [05/06_]
    , IIf(IsNull([05/07]), 0, [05/07]) AS [05/07_]
    , IIf(IsNull([05/08]), 0, [05/08]) AS [05/08_]
    , IIf(IsNull([05/09]), 0, [05/09]) AS [05/09_]
    , IIf(IsNull([05/10]), 0, [05/10]) AS [05/10_]
    , IIf(IsNull([05/11]), 0, [05/11]) AS [05/11_]
    , IIf(IsNull([05/12]), 0, [05/12]) AS [05/12_]
    , IIf(IsNull([05/13]), 0, [05/13]) AS [05/13_]
    
 /* you want "new dates" (columns) */   
    
    , IIf(IsNull([After_Count]), 0, [After_Count]) AS [After Count]
    , [tblPreFinal II - NEW].[TOTAL MAY Active/Open_]
FROM [tblPreFinal II - NEW]
ORDER BY [tblPreFinal II - NEW].[tblPreFinal I - NEW_Contract Number];

Open in new window

and what happens to "old dates"?
ps: reformatting courtesy of  http://poorsql.com/ (its a url, not a comment on the code)
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39167037
mm, now I look at what I posted and it doesn't seem sensible - oh well at least one can read the query now.
0
 

Author Comment

by:gdunn59
ID: 39176138
The new dates that need to be added will always be one date prior.  What I mean by new dates, is for example today is 5/17, 5/16 is the new date/column that needs to be added.  The old dates are still included.  So every day a new date/column is added.  Then when a new month comes, the dates start over.  For example if today is 6/02, then the dates start over with 6/01 (since this is the previous date), and so then the results just contain a column 6/01.

Thanks,
gdunn59
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39176371
so you did mean columns, I see

not sure you should change the underlying SQL query each day
why not extend the same sort of logic that applies to months? (which are "recycled")
so 31 day columns, but only display those where day number <= today's day number

excluding month number from the headings would make life easier also.
0
 

Author Comment

by:gdunn59
ID: 39191976
PortletPaul:

Your solution sounds like it would work, but I'm not sure how to go about it (write the SQL).  Can you show me?

Thanks,
gdunn59
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39227956
@gdunn59
I'm not that familiar with access so I'm not able to offer a solution

All I can really say is that I'd want to redesign it so I didn't have to amend the query
(easy to say, I know) .
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39228306
given I raised an idea that I can't really fulfill, perhaps if I suggested "dynamic sql" as a way forward (not that I like it, it's just that you have a design that requires frequent change to a query).

It seems you may have some options for this and these links may provide some ideas:

http://www.access-programmers.co.uk/forums/showthread.php?t=151383

http://stackoverflow.com/questions/133922/how-do-you-execute-a-dynamic-sql-query-against-ms-access-2003-via-vba

I came across these by searching for
create dynamic sql in access
at google
0
 
LVL 26

Expert Comment

by:Nick67
ID: 39229606
Looking at what PortletPaul posted, if it is corrected and works on your side, it isn't that hard to get VBA to write up the dynamic SQL string based on today's date.  It also isn't that hard to use VBA to sub that string into a query
Dim someSQL as string
Dim TheAlterableQuery as QueryDef
Dim db as database

set db = CurrentDb
set TheAlterableQuery  as db.QueryDefs("TheNameOfSomeQueryYouWantToAlter")

someSQL = "Some code that loops and builds up a correctly formatted SQL string"
TheAlterableQuery.SQL = someSQL

Open in new window

and then you do stuff with your newly tweaked query
DoCmd.OpenQuery "SomeQueryName", acViewPreview

The fun of course is "Some code that loops and builds up a correctly formatted SQL string"
With something complex like that, I'll break it down farther than someSQL
Dim MySelect as String
Dim MyFrom as String
Dim MyWhere as String
Dim MyOrderBy as String

someSQL = MySelect & MyFrom & MyWhere & MyOrderBy

Open in new window

The idea is to break it into chunks that a MsgBox will display as you debug, and to be able to put those pieces of the SQL that don't change as copy-and-paste from your query into your code ONCE and bug-free.  Then you've got to build your loops to create the dynamic parts

This chunk
    , [tblPreFinal II - NEW].[TOTAL OCTOBER Active/Open]
    , [tblPreFinal II - NEW].[TOTAL NOVEMBER Active/Open]
    , [tblPreFinal II - NEW].[TOTAL DECEMBER Active/Open]
    , [tblPreFinal II - NEW].[TOTAL JANUARY Active/Open]
    , [tblPreFinal II - NEW].[TOTAL FEBRUARY Active/Open]
    , [tblPreFinal II - NEW].[TOTAL MARCH Active/Open]
    , [tblPreFinal II - NEW].[TOTAL APRIL Active/Open]
    , [tblPreFinal II - NEW].[TOTAL MAY Active/Open]

will be built with Month(Now), which returns an integer with today's Month
MonthName(SomeIntegerBetweenOneAndTwelve) returns a string with the full month's name
A couple of loops (if October is the starting point) and you are off to the races for that chunk.  The wrap-over from December to January and testing for that will be a bit of an annoyance, but nothing fierce

This chunk

    , IIf(IsNull([05/01]), 0, [05/01]) AS [05/01_]
    , IIf(IsNull([05/02]), 0, [05/02]) AS [05/02_]
    , IIf(IsNull([05/03]), 0, [05/03]) AS [05/03_]
    , IIf(IsNull([05/04]), 0, [05/04]) AS [05/04_]
    , IIf(IsNull([05/05]), 0, [05/05]) AS [05/05_]
    , IIf(IsNull([05/06]), 0, [05/06]) AS [05/06_]
    , IIf(IsNull([05/07]), 0, [05/07]) AS [05/07_]
    , IIf(IsNull([05/08]), 0, [05/08]) AS [05/08_]
    , IIf(IsNull([05/09]), 0, [05/09]) AS [05/09_]
    , IIf(IsNull([05/10]), 0, [05/10]) AS [05/10_]
    , IIf(IsNull([05/11]), 0, [05/11]) AS [05/11_]
    , IIf(IsNull([05/12]), 0, [05/12]) AS [05/12_]
    , IIf(IsNull([05/13]), 0, [05/13]) AS [05/13_]


will be built with Day(now) which will return an integer with the day of the month it is today.
You already know how to get the month from today -- Month(now).  The rest is a loop
for x = 1 to Day(now)
    Myselect = myselect & "  , IIf(IsNull([" & month(now) & "/" & x & "]), 0, [" & month(now) & "/" & x & "]) AS [" & month(now) & "/" & x & "_]"

next x

Open in new window

Lots of kitschy debugging to get it right -- but not technically that tough.  It just string concatenation.

Using the SQL property will complete overwrite the SQL of the existing query
TheAlterableQuery.SQL = someSQL
It's a handy thing for things like SQL Server passthrough queries.

Remember .SQL = someString-- completely overwrites the existing SQL -- permanent changes!  So don't muck with a query you want to keep as you test!

Nick67
0
 
LVL 26

Expert Comment

by:Nick67
ID: 39230207
This ought to be close
Private Sub LotsOfFun()
Dim someSQL As String
Dim TheAlterableQuery As QueryDef
Dim db As Database
Dim MySelect As String
Dim MyFrom As String
Dim MyWhere As String
Dim MyOrderBy As String
Dim TheMonth As Integer
Dim TheFormattedMonth As String
Dim TheDay As Integer
Dim TheFormattedDay As String
Dim ThisMonthName As String
Dim x As Integer
Dim TheField As String
Dim TheAlias As String

TheMonth = Month(Now)
TheDay = Day(Now)

If TheMonth < 10 Then
    TheFormattedMonth = "0" & TheMonth
Else
    TheFormattedMonth = CStr(TheMonth)
End If


MySelect = "SELECT DISTINCT [tblPreFinal II - NEW].[tblPreFinal I - NEW_Contract Number],"
MySelect = MySelect & "[tblPreFinal II - NEW].State,"
MySelect = MySelect & "IIf(IsNull([Denied/Cancelled/Duplicate]+[CMS Auto Enrolls]+[Group Enrollments]+[TOTAL Active/Open_]),0,[Denied/Cancelled/Duplicate]+[CMS Auto Enrolls]+[Group Enrollments]+[TOTAL Active/Open_]) AS [Grand Total],"
MySelect = MySelect & "IIf(IsNull([Denied/Cancelled/Duplicate]),0,[Denied/Cancelled/Duplicate]) AS Denied_Canc_Dup, IIf(IsNull([CMS Auto Enrolls]),0,[CMS Auto Enrolls]) AS CMS_Auto_Enrolls,"
MySelect = MySelect & "IIf(IsNull([Group Enrollments]),0,[Group Enrollments]) AS Group_Enrollments,"
MySelect = MySelect & "[tblPreFinal II - NEW].[TOTAL Active/Open_],"

Select Case True
    Case TheMonth > 9
        MySelect = MySelect & "[tblPreFinal II - NEW].[TOTAL OCTOBER Active/Open],"
        For x = 11 To TheMonth
            MySelect = MySelect & "[tblPreFinal II - NEW].[TOTAL " & MonthName(x) & " Active/Open],"
        Next x
    Case Else
        For x = 10 To 12
            MySelect = MySelect & "[tblPreFinal II - NEW].[TOTAL " & MonthName(x) & " Active/Open],"
        Next x
        For x = 1 To TheMonth
            MySelect = MySelect & "[tblPreFinal II - NEW].[TOTAL " & MonthName(x) & " Active/Open],"
        Next x
End Select

For x = 1 To TheDay
    If x < 10 Then
        TheFormattedDay = "0" & x
    Else
        TheFormattedDay = CStr(x)
    End If
    TheField = TheFormattedMonth & "/" & TheFormattedDay
    TheAlias = TheField & "_"
    TheField = "[" & TheField & "]"
    TheAlias = "[" & TheAlias & "]"
    MySelect = MySelect & "IIf(IsNull(" & TheField & "),0," & TheField & ") AS " & TheAlias & ","
Next x
MySelect = MySelect & "IIf(IsNull([After_Count]),0,[After_Count]) AS [After Count],"

x = TheMonth
MySelect = MySelect & "[tblPreFinal II - NEW].[TOTAL " & MonthName(x) & " Active/Open] "


MyFrom = "FROM [tblPreFinal II - NEW] "
MyWhere = ""
MyOrderBy = "ORDER BY [tblPreFinal II - NEW].[tblPreFinal I - NEW_Contract Number];"

someSQL = MySelect & MyFrom & MyWhere & MyOrderBy
Set db = CurrentDb
Set TheAlterableQuery = db.QueryDefs("TheNameOfSomeQueryYouWantToAlter")
TheAlterableQuery.SQL = someSQL

End Sub

Open in new window

0
 

Author Comment

by:gdunn59
ID: 39273261
Nick67:

I've tried the code from your last posting, and it works to a certain extent.  It puts the correct information in the Query Grid, but the Table Data Source for the Query isn't correct.  So although it shows the June data in the Query Grid, the Table Data Source in the Query still shows the dates for May.

I have attached a spreadsheets showing the Steps 1-3 that compile the final results for a the current Month.  These spreadsheets contain the data for May and how it should look for the final query/results.  Since the attachments are for May, I would need the next month (June) to have a column added after "TOTAL May Active/Open" to show "TOTAL June Active/Open" and this column needs to contain the Totals for June.  So to date, the grid would show the new column "TOTAL June Active/Open", and the individual date columns for 6/01, 6/02, 6/03 . . . . 6/23 (through 6/23, but not 6/24 because it is always up to the previous date from the current date).

These steps need to be repeated each time the next Month starts.

Thanks,
gdunn59
This-is-the-2nd-Step-to-creating.docx
This-is-the-Last-Step-to-creatin.xlsx
This-is-what-the-Final-Results-l.docx
This-is-the-1st-Step-to-creating.docx
0
 
LVL 26

Expert Comment

by:Nick67
ID: 39273337
So, we then have some problem in the what month the code thinks it is.
Columns O:AS are for the full month of May--and yet that should only be possible on 31-May
Are they data from May or June?

This bit of the code
TheMonth = Month(Now)
TheDay = Day(Now)

If TheMonth < 10 Then
    TheFormattedMonth = "0" & TheMonth
Else
    TheFormattedMonth = CStr(TheMonth)
End If

Open in new window


Should resolve out (today) as
TheMonth = 6
TheDay = 24
TheFormattedMonth = "06"

Does it?
Step through it and msgbox it

The column aliases come from here
    TheField = TheFormattedMonth & "/" & TheFormattedDay
    TheAlias = TheField & "_"
    TheField = "[" & TheField & "]"
    TheAlias = "[" & TheAlias & "]"
If they are not right then somehow, they are not getting the correct day in

Hey!
One should NEVER name fields and controls after reserved words
(hangs head in shame) but I have named fields 'Date' which screwed with things

What do these return
TheMonth = Month(Now)
TheDay = Day(Now)

Open in new window

0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:gdunn59
ID: 39273702
Nick67:

The day should be Day(Now)-1.  Because the last day in the report should be the previous day's data.
0
 

Author Comment

by:gdunn59
ID: 39275253
Nick67:

Since the dates for the Month are populated in the "STEP 11: qryCurrent_Month - NEW" query, wouldn't I need to use this query in your code to populate the current Month's data?

Here is the SQL Code for the above-referenced query:
SELECT [STEP 02aa: qryPromised_Apps (Active - Crosstab) -CurrMth 2012_NW].* AS [STEP 02aa] INTO tblCurrent_Month_NEW

FROM [STEP 02aa: qryPromised_Apps (Active - Crosstab) -CurrMth 2012_NW]

WHERE ((([STEP 02aa: qryPromised_Apps (Active - Crosstab) -CurrMth 2012_NW].[Contract Number]) Is Not Null));

Open in new window


Thanks,
gdunn59
0
 
LVL 26

Expert Comment

by:Nick67
ID: 39275356
The day should be Day(Now)-1.  Because the last day in the report should be the previous day's data.

Ok, so we'll need some code to detect the first day of the month, and roll back, and to detect January 1st to roll back (or you'll never work on New Year's day?)

But first things first.
What month does the code think it is today?
And what day?
And then what results return?

As for it grabbing the right data, it pulls from here
MyFrom = "FROM [tblPreFinal II - NEW] "

Open in new window

So, if tblPreFinal II - NEW is a parameterized query or temptable of some sort that is getting its data set by an outside date source, then that makes sense
0
 
LVL 26

Expert Comment

by:Nick67
ID: 39275409
Your original query only made mention of one source
FROM [tblPreFinal II - NEW]

Open in new window

and it was a question about how to dynamically change the field alias names of the query
Every day a new value (date) is added to the table (record source).

I assumed that you wanted to rework the query based off of today.  Perhaps you want to enter a desired date into a control? or as a public variable?  But, in the original SQL you posted all the data was only coming from a single source.

Nick67
0
 

Author Comment

by:gdunn59
ID: 39275432
Nick67:

The code thinks the Month is June (which is correct), and the code thinks the day is the 24th (because it is always needs to capture the prior day's data).

The results returned are data for 6/1 through 6/24/2013.

Prior to it pulling data from "tblPrefinal II - New", the query that populates the monthly dates is a "Make Table Query", and the table that is created from this query is included in the Step 2 Query ("STEP 13: qryPromised_Apps (Crosstab for Rpt) -PreFinal II - NEW") that I included in my ID: 39273261 posting.

Thanks,
gdunn59
0
 
LVL 26

Expert Comment

by:Nick67
ID: 39275521
Ok,
So if the code has the right month and day then it's time to step through
For x = 1 To TheDay
    If x < 10 Then
        TheFormattedDay = "0" & x
    Else
        TheFormattedDay = CStr(x)
    End If
    TheField = TheFormattedMonth & "/" & TheFormattedDay
    TheAlias = TheField & "_"
    TheField = "[" & TheField & "]"
    TheAlias = "[" & TheAlias & "]"
    MySelect = MySelect & "IIf(IsNull(" & TheField & "),0," & TheField & ") AS " & TheAlias & ","
Next x

Open in new window


because that's the bit that is giving each of their aliases.
I have no idea how, if the code gets the month and day right, that it can be getting the field aliases wrong, or how it could be pulling data from days larger than today's day
0
 

Author Comment

by:gdunn59
ID: 39275620
Nick67:

The query that I originally referenced does have the monthly dates, as well as other fields.  As I mentioned above, the monthly dates are added to the query referenced in my initial posting, by way of the STEP 1 query (which I included in my ID: 39273261 posting).

So I need to get the monthly dates updated in the STEP 1 query (which results are included in the original query that I provided, so that it will be populated in the query in the initial posting.

Sorry if I wasn't clear initially, and if this is not making sense.  I'm trying to explain it the best I can.

How would I go about that?

Thanks,
gdunn59
0
 
LVL 26

Expert Comment

by:Nick67
ID: 39275729
This bit
Select Case True
    Case TheMonth > 9
        MySelect = MySelect & "[tblPreFinal II - NEW].[TOTAL OCTOBER Active/Open],"
        For x = 11 To TheMonth
            MySelect = MySelect & "[tblPreFinal II - NEW].[TOTAL " & MonthName(x) & " Active/Open],"
        Next x
    Case Else
        For x = 10 To 12
            MySelect = MySelect & "[tblPreFinal II - NEW].[TOTAL " & MonthName(x) & " Active/Open],"
        Next x
        For x = 1 To TheMonth
            MySelect = MySelect & "[tblPreFinal II - NEW].[TOTAL " & MonthName(x) & " Active/Open],"
        Next x
End Select

Open in new window


does the month total aliases.  It assumes you wanted October first, looks after cases where you are in Oct, Nov, or Dec, and then the new year.  All it is doing is getting data that is already in tblPreFinal II - NEW.

Nothing in the code I've posted does a thing about getting data into THAT table, it all about pulling what it assumes is there out
0
 

Author Comment

by:gdunn59
ID: 39275983
Nick67:

So what I need is, in the end I do need what you've provided, but before it gets to that point I need to get the latest month's dates in the Table.

Where do we go from here?  

Thanks,
gdunn59
0
 
LVL 26

Expert Comment

by:Nick67
ID: 39276019
Well,
You've got an example of how VBA can be used to build an insert the SQL text of a query into a QueryDef object.

You now have a second QueryDef object that you want to screw around with.
The principals are the same: look at how the query changes iteratively over time and build VBA loops to write the changes, instead of manually going into the query in the GUI and doing it.

Take a stab at it.
If you can't make it play, since it in many ways is a different question than this one, post a related question and get some more eyes on it :)
0
 

Author Comment

by:gdunn59
ID: 39276028
Nick67:

Herein, I've included the SQL View of the very first query where the monthly dates are gathered (added on to the query each time a new month starts - keeping in mind 1 day prior to current date.  So for instance, on June 2nd, I compiled data starting for June, and the only date included in the report is June 1st, then on June 3rd, I add on to the June data adding June 2nd, etc. etc.).

Here is the SQL Code for the first query:
SELECT DISTINCT
report1.ID, 
report1.[Member First Name],
report1.[Member Last Name],
report1.[Contract Number],
tblContract_States.State AS [State(s)],
report1.[Employee Type],
tblContract_States.Region,
report1.[Member Current Status],
report1.[Operations Receipt Date],
report1.[Application Effective Date],
report1.[Enrollment Application Receipt Date],
Format(CDate(Left([Operations Receipt Date],4) & "-" & Right(Left([Operations Receipt Date],6),2) & "-" & Right([Operations Receipt Date],2)),"mm/dd") AS [Operations Receipt_Date],
report1.[Enrollment Source],
report1.[Enrollment Application Receipt Date],
Format(CDate(Left([Enrollment Application Receipt Date],4) & "-" & Right(Left([Enrollment Application Receipt Date],6),2) & "-" & Right([Enrollment Application Receipt Date],2)),"mm/dd") AS Enrollment_Application_Receipt_Date,
IIf([Enrollment Application Receipt Date] Like "201110*","OCT",
IIf([Enrollment Application Receipt Date] Like "201111*","NOV",
IIf([Enrollment Application Receipt Date] Like "201112*","DEC",
IIf([Enrollment Application Receipt Date] Like "201201*","JAN",
IIf([Enrollment Application Receipt Date] Like "201202*","FEB",
IIf([Enrollment Application Receipt Date] Like "201203*","MAR",
IIf([Enrollment Application Receipt Date] Like "201204*","APR",
IIf([Enrollment Application Receipt Date] Like "201205*","MAY",
IIf([Enrollment Application Receipt Date] Like "201206*","JUN",
IIf([Enrollment Application Receipt Date] Like "201207*","JUL",
IIf([Enrollment Application Receipt Date] Like "201208*","AUG",
IIf([Enrollment Application Receipt Date] Like "201209*","SEP",
IIf([Enrollment Application Receipt Date] Like "201210*","OCT_NEW",
IIf([Enrollment Application Receipt Date] Like "201211*","NOV_NEW","")))))))))))))) AS [Month],
IIf([Enrollment Application Receipt Date] Like "201210*","OCT_NEW",
IIf([Enrollment Application Receipt Date] Like "201211*","NOV_NEW",
IIf([Enrollment Application Receipt Date] Like "201212*","DEC_NEW",
IIf([Enrollment Application Receipt Date] Like "201301*","JAN_NEW",
IIf([Enrollment Application Receipt Date] Like "201302*","FEB_NEW",
IIf([Enrollment Application Receipt Date] Like "201303*","MAR_NEW",
IIf([Enrollment Application Receipt Date] Like "201304*","APR_NEW",
IIf([Enrollment Application Receipt Date] Like "201305*","MAY_NEW","")))))))) AS Month2, 
report1.[Completed Information Returned Receipt Date]
FROM report1 LEFT JOIN tblContract_States ON report1.[Contract Number] = tblContract_States.[Contract Number]
WHERE (((report1.[Member Current Status]) Not In ("Cancel","Closed","Denied"))
AND ((report1.[Application Effective Date])>="20130101")
AND ((report1.[Enrollment Application Receipt Date])>="201210*"
And (report1.[Enrollment Application Receipt Date])<Format(Now(),"yyyymmdd"))
AND ((report1.[Enrollment Source]) Not Like "*cms*"
And (report1.[Enrollment Source]) Not Like "wgs*"))
ORDER BY report1.[Member Last Name], report1.[Contract Number], report1.[Operations Receipt Date];

Open in new window


So the code that I included is from the May process.  As you can see the last date pulled from the IIF Statements is "May" (IIf([Enrollment Application Receipt Date] Like "201305*","MAY_NEW","")))))))) AS Month2).  For June the criteria for the field [Enrollment Application Receipt Date] would be Like "201306*", and the June criteria for the field [Month2] where the IIF Statement is would be "JUNE_NEW".

Hope this is making sense.


Thanks,
gdunn59
0
 
LVL 26

Expert Comment

by:Nick67
ID: 39276096
This is the only bit of that query that is dynamic, right
IIf([Enrollment Application Receipt Date] Like "201110*","OCT",
IIf([Enrollment Application Receipt Date] Like "201111*","NOV",
IIf([Enrollment Application Receipt Date] Like "201112*","DEC",
IIf([Enrollment Application Receipt Date] Like "201201*","JAN",
IIf([Enrollment Application Receipt Date] Like "201202*","FEB",
IIf([Enrollment Application Receipt Date] Like "201203*","MAR",
IIf([Enrollment Application Receipt Date] Like "201204*","APR",
IIf([Enrollment Application Receipt Date] Like "201205*","MAY",
IIf([Enrollment Application Receipt Date] Like "201206*","JUN",
IIf([Enrollment Application Receipt Date] Like "201207*","JUL",
IIf([Enrollment Application Receipt Date] Like "201208*","AUG",
IIf([Enrollment Application Receipt Date] Like "201209*","SEP",
IIf([Enrollment Application Receipt Date] Like "201210*","OCT_NEW",
IIf([Enrollment Application Receipt Date] Like "201211*","NOV_NEW","")))))))))))))) AS [Month],
IIf([Enrollment Application Receipt Date] Like "201210*","OCT_NEW",
IIf([Enrollment Application Receipt Date] Like "201211*","NOV_NEW",
IIf([Enrollment Application Receipt Date] Like "201212*","DEC_NEW",
IIf([Enrollment Application Receipt Date] Like "201301*","JAN_NEW",
IIf([Enrollment Application Receipt Date] Like "201302*","FEB_NEW",
IIf([Enrollment Application Receipt Date] Like "201303*","MAR_NEW",
IIf([Enrollment Application Receipt Date] Like "201304*","APR_NEW",
IIf([Enrollment Application Receipt Date] Like "201305*","MAY_NEW","")))))))) AS Month2, 

Open in new window


so you'll need to detect the year and the month and build a 'select case true + for x = ' structure like in the previous code that loops through and builds the
Like "201304*","APR_NEW",
bits dynamically

Different bits in it than this, but this structure is the guts of it
Select Case True
    Case TheMonth > 9
        MySelect = MySelect & "[tblPreFinal II - NEW].[TOTAL OCTOBER Active/Open],"
        For x = 11 To TheMonth
            MySelect = MySelect & "[tblPreFinal II - NEW].[TOTAL " & MonthName(x) & " Active/Open],"
        Next x
    Case Else
        For x = 10 To 12
            MySelect = MySelect & "[tblPreFinal II - NEW].[TOTAL " & MonthName(x) & " Active/Open],"
        Next x
        For x = 1 To TheMonth
            MySelect = MySelect & "[tblPreFinal II - NEW].[TOTAL " & MonthName(x) & " Active/Open],"
        Next x
End Select

Open in new window


Take a stab at building the code, and then post it.
The logic will be looking for what year it is, and then if the month is >Oct, pulling from one calendar year earlier, and if the month is <Oct pulling from two calendar years previous for the 'IIf([Enrollment Application Receipt Date] Like "201110*","OCT",' bits and rolling ahead months and years until today is reached.
0
 

Author Comment

by:gdunn59
ID: 39276689
Nick67:

I've never done a dynamic SQL statement, and really am confused as to where to begin in getting what I want.

If I close out this question and award you the 500 points for the initial question, and open another one, would you be able to assist me further?

I'm up against a timeline and really don't have any time to spare to try and figure this out right now.

It would be greatly appreciated.

Thanks,

gdunn59
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
ID: 39278900
Sigh.

It is always dangerous to cut-and-paste code you don't understand.
I've never done a dynamic SQL statement, and really am confused as to where to begin in getting what I want.

What I posted was an example, using your stuff, of creating a dynamic SQL Statement.
You build a string that is the SQL, and then you use the SQL property of a QueryDef to permanently alter the SQL of the query you were previously hand editing.

Looking hard at your new query, you don't need dynamic SQL there, you need to whack those IIF statements with a stick.  Attached is a sample.  Look at Query1.  I've broken the string date into its constituent pieces, put them back together in an ISO date format, coerced it to a date and then assigned MyMonth or MyNewMonth based on a comparison of the date to ~Halloween.

The query needs to change just once a year as you roll into November.

Anytime you are nesting more than one IIF statement, you gotta ask yourself 'Isn't there a better way to do this?"
UglyDates.mdb
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now