Solved

How to create a dynamic query builder

Posted on 2009-04-14
131
914 Views
Last Modified: 2013-11-28
I am trying to determine the best way to go about creating the code necessary to allow a user form to generate custom queries.  I have thought of several approaches and none seem very efficient.

At least initially, the queries will all be based on the same table.  So it is just a matter of defining what fields to include and what criteria for each field.

On the form, I am using radio buttons, check boxes and text boxes.
0
Comment
Question by:pgerman
  • 72
  • 52
  • 5
  • +2
131 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 24138397
see this link

How to use the query by form (QBF) technique in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;en-us;304428
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24138439
I guess the way I would start is read the table properties to get a list of fieldnames. From this I would populate a listbox (selectable fields)
Have another listbox which contains the fields the user wants then a create button.
This will either create a new query each time or if you dont want to do that, just keep modifying an existing query


0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24138487
A simple code example

STEP 1

Say the two listboxes you have are called this   lstSelectableFields and lstSelectedFields. These two listboxes will be of type Value List as we are specifying the values

This is how you would populate lstSelectableFields

    Dim db As DAO.Database
    Dim tbl As DAO.TableDef
    Dim i As Integer
   
    lstSelectableFields.RowSource = ""
    Set db = CurrentDb
    Set tbl = db.TableDefs("mytable")
    For i = 0 To tbl.Fields.Count - 1
        If lstSelectableFields.RowSource <> "" Then lstSelectableFields.RowSource = lstSelectableFields.RowSource & ";"
        lstSelectableFields.RowSource = lstSelectableFields.RowSource & tbl.Fields(i).Name
    Next i

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24138508
oh, in my typing I didnt see cap's post. I havent seen it but it may answer your question.

I will put on hold the rest of my code sample, unless you feel it will be useful to you and you want me to

0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24138539
Assuming you have each field name and a check box/radio button to determine if you want to use that field and then a text box for the criteria, I would proceed like this.
Name each radio button the same as the field name.  Start to build your SQL by cycling through the controls on the form and check to see if it is a radio button and see if it is selected for use. (SELECT [USERID], [NAME],...)
Name the text boxes the same as each field name.  continue building your SQL by cycling though the controls onthe form and check to see if it is text box and then create the where clause based on the text fields. (WHERE [USERID] = 'D3984D' AND [NAME] = 'JOE')
This would take some code to do but I would go this route.
0
 

Author Comment

by:pgerman
ID: 24138837
masterjojobinks,

That seems closest to what I had in mind, but I still don't quite understand how the SQL will be generated each time.

My basic set up is I'm giving the user the option of including or excluding 5 fields.  The table has many more fields, but they aren't relevant to what is trying to be generated from the query.  The selection of whether or not to include will ne made through the checking of a radio button.  If the radio button is checked, then I want the query to then look for criteria for that field.  For the most part I am just using check boxes for the criteria.  So for example, I have a region radio box and below it four check boxes one for each region (WHEM, EMEA, etc.).  If the user checks the radio box for region, I would want the SQL to select that field and then use a WHERE clause for what ever regions are checked off.
0
 

Author Comment

by:pgerman
ID: 24138917
When you say 'start cyclying through your controls' is this something that can be done each time new criteria is entered?
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24138928
I am with Rockiroads, I did not see Caps post or Rockiroads for that matter; my route will take some explaining; make and probably and example to help you develop your own code.  Assuming you have evaluated Rockiroads and Capricorns options  I will continue, first come first crack courtesy.
0
 

Author Comment

by:pgerman
ID: 24138976
I have looked over their responses.  Your response seems most inline with what I am tryin to do.  I would really appreciate further assistence
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24138978
masterjobbinks, my approach was also going to be slow, hence I was going to do each step one post at a time. Plan was to have two listboxes and then to further develop that with filters.
But pgerman likes your approach so I think you should go with it.
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24139030
To answer the last question Pgerman, I would wait to build the SQL until the user is done selecting everything.  At the bottom of the form have command button that will build the sql and then open a report or form.
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24139070
Are you going to want the user to selection multiple fields as part of the search?  You indicated that the user will select a radio button for region.  I assume there will be radio buttons for each field.  Will these buttons be grouped in an option group (meaning only one can be selected) or will they be individual (meaning the user can select all 5 buttons or any combination there off)?
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24139108
masterjojobinks, you got it well under control, so I will unsubscribe. You dont need my services here. Good luck!
0
 

Author Comment

by:pgerman
ID: 24139132
Yes the user will be able to select all five, or any combination. The radio button will simply indicate if the field should be included at all.
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24139166
Are there always only going to be 5 regions to choose from for the search?  If not, I would suggest going to a list box with each choice available.  This way in the future you will not have to change the form when another region is added to the data (or any of the other four fields change in size).
0
 

Author Comment

by:pgerman
ID: 24139501
These are the choices available to the user:
Include time period & corresponding reveunes? (Radio button)
If yes:
Choose time period:  ( The table has about 3 years of months as field names with the corresponding revenues for those months going down throughout the records)
      preset or custom? (radio buttons)
           If preset, choose from drop down (for example Q1 2008)
            If custom, set range by selecting from drop downs. From  [Month] & [Year] through [Month]
            & [Year].
                   Set criteria for time period: (Select check boxes)
                          Large (>5mm for the time period)
                          Medium(between 1 and 5 mm for the time period)
                          Small(<1mm for the time period)
                          Other
                               If other, enter a minimum revenue value and a maximum into textboxes

Include segment field? (radio button)
If Yes:
choose what segments to include (set criteria): (select checkboxes)
Asset managers
Banks
Central Banks
etc. (13 total segment choices)

Include hierarchy level, or whether or not to show customer names and at what level, (radio button)
If Yes:
Select what field to choose (in this case they are all separate fields) (check boxes)
Ultimate Parent Name
Customer Name
Group Account number

Include a specific sub product specification? (Radio button)
If Yes:
Set Subproduct criteria by selecting check boxes:
Full Discretionary
Exclusives only
etc. (5 total options for sub product)

Include Region field? (Radio button)
If Yes:
Set region criteria by selecting check boxes
WHEM
EMEA
ASIA
LATAM
                         
0
 

Author Comment

by:pgerman
ID: 24139592
Is the basis for this going to be a bunch of IF statements, for example if radiobox1 = true then add this [ ...] to the SQL string, etc.?
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24139630
There will be some if statements to be sure, but I plan a single subroutine to be called to build the where portion of the SQL.  It will be called multiple times and build it up nice and pretty with as little as code as possible.  That is of course one reason, a list box would be easier to maintain in the future.  We can do check boxes too and the will not care the number of check boxes (assuming they are named correctly :>)
0
 

Author Comment

by:pgerman
ID: 24139795
alright sounds good.  So I guess i should start by getting everything like the check boxes, textboxes and radio buttons named right?
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24139907
Yes, but depending on how you name them will affect how the code is written ... I will have a sample for you in just a little bit, to follow which should help considerably.
0
 

Author Comment

by:pgerman
ID: 24139944
ok great, let me know if that long post I submitted made sense. or if I need to specify anything
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24140289
This is what I have at first pass.  It provide the direction I was talking about.  From this you should be able to attapt to each different criteria.  if you look at the immediate window after pushing the command button you will see the sql statement that has been created.  as you can see the example only provides two check boxes per radio button.  The code will work for as many as you want, as long as the check boxes and radio buttons are named correctly.
Radio button name syntax 'OB_<fieldname>'
Each check box name syntax for this perticular radio button '<fieldname>_CB_<criteria value>'

the code used these to match up the correct check box with the correct radio button and build the correct sql.
Digest this while I am at lunch.
db6.mdb
0
 

Author Comment

by:pgerman
ID: 24140371
masterjojobinks,

Can you email this file to me, I am un able to directly open it from the site from my work computer.

peter.c.german@jpmorgan.com

many thanks
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24140705
Emailed the file.

The only two that need more work is: Include time period & corresponding reveunes  (radio button)
 and Include hierarchy level, or whether or not to show customer names and at what level, (radio button)

The others are covered in the example.  The example assumes that each criteria is going to be text and based on your one long reply they are.  when you being mixing and matching datatypes the code has to account count it.

Questions about these other two...
0
 

Author Comment

by:pgerman
ID: 24140734
I am going to work with it for a little and see if I can get some things working.  I will plan on getting back to you with how everything is going by 5PM NY time today.
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24140741
The easier of the two:

Include hierarchy level, or whether or not to show customer names and at what level, (radio button)
If Yes:
Select what field to choose (in this case they are all separate fields) (check boxes)
Ultimate Parent Name
Customer Name
Group Account number

Are these just a set of fields that can be chosen?
user selects HierarchyLevel then can select any or all of the three fields you see here?
Is hierarchy level a field or just a grouping title?
0
 

Author Comment

by:pgerman
ID: 24140765
hierarchy level is just a grouping title. Ultimate Parent Name, Customer Name and Group Account Number refer to field names that can be chosen, 0 or all can be selected
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24140783
Include time period & corresponding reveunes? (Radio button)  If yes:
Choose time period:  ( The table has about 3 years of months as field names with the corresponding revenues for those months going down throughout the records)
      preset or custom? (radio buttons)
           If preset, choose from drop down (for example Q1 2008)
            If custom, set range by selecting from drop downs. From  [Month] & [Year] through [Month]
            & [Year].
                   Set criteria for time period: (Select check boxes)
                          Large (>5mm for the time period)
                          Medium(between 1 and 5 mm for the time period)
                          Small(<1mm for the time period)
                          Other  - If other, enter a minimum revenue value and a maximum into textboxes

This table has Q1 2007, Q2 2007 ... and so on as field names?
Do you want each of these field names to be displayed in the sql?
0
 

Author Comment

by:pgerman
ID: 24140912
The table has individual months for field names and a few aggregate fields like Sum of 2007, 2008 and 2009 YTD, which add up the individual months.  So if the user selected Q12007, the sql would need to include fields JAN 07, FEB 07 and MAR 07.

I'm not sure what you mean by 'displayed in SQL'
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24140958
You answered my question ...  
another though ... the criteria, do you want it applied to each field (jan 07, Feb 07, ...)?
What kind of data is in these fields? Text or numbers?
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24140984
Hierachy is solved by limiting the users options with event code and just using radio buttons.  I did add a small check to ensure that each radio button has OB as the first two charactors of the name so I could skip some radio buttons like hierarchy.  
BTW, what type of data is the Group account number, because the code only accounts for text fields?
0
 

Author Comment

by:pgerman
ID: 24141268
Group Account number is text.

I am a little confused with how to proceed.

Do I need to set each check box equal to a default value, in the properties of the check boxes?

Also I do not understand the significance of the numbers in the code, like 4, -1, etc.  Also do not know what 'Len' is.
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24141326
I am working on the solution to  time period.  Let me finish that and I will add copious amounts of comments to the code to help.

Len is a built in function that defines the character length of a variable.  I am using it in conjunction with left, right, and mid functions which parse a piece of text into different pieces.  This is where the field names and criteria are coming from.  If you have more questions about these put your curser on LEN in the Code screen and press F1, help will give you a lot of info about them.
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24141611
The criteria for these date fields, do you want it applied to each field (jan 07, Feb 07, ...)?
What kind of data is in these fields? Text or numbers?
0
 

Author Comment

by:pgerman
ID: 24141760
I am sending you a sample db, with any sensitive data taken out.  There are three forms, an intro, custom and existing.  Right now we are focusing on the custom form.  Also check out the set up of the masterdata table.  This should answer some questions.
0
 

Author Comment

by:pgerman
ID: 24141865
Let me know what you think of the db. I have brought in your code.  I am a little stuck on what to do next
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24141889
If you don't mind I will just make the modifications to your DB and then explain what I did.
0
 

Author Comment

by:pgerman
ID: 24141966
ok np.  Thank you
0
 

Author Comment

by:pgerman
ID: 24142064
Just a few things to keep in mind:

The Classifications (Large, Medium, Small and Other) all have definitions.  Large = >5000000, Medium = >1000000 AND <5000000, Small = <1000000. Other relies on the text boxes.  These would be the criteria for the revenue/ month fields.

I am focusing on the 'Preview Custom Report' Button for now.  This is what will pull up the query.

Group Account # is 'CAS Acct #' in the masterdata table.

Region is 'UltP Geo Region Code'

Also once we get this figured out, is there a way to assign a unique report number to each query and then create a repository of all queries ran, which can be refered back to by the user, by maybe entering the report number, which they will be provided upon running the query?
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24142098
yes we can do that.  I need to also know which of these catagories line up with which fields.  You gave me group account # and region, what about Sub product and Segment
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24142112
also the Customer Name and Ultimate Parent Name
0
 

Author Comment

by:pgerman
ID: 24142188
Sub Product on Form = 'Sub Prod Name' field in masterdata table

Segment on Form = 'UltP Segment' in masterdata table

Customer Name on Form = 'Cust Name' in masterdata table

Ultimate Parent Name on Form = 'UltP Name' in masterdata table
0
 

Author Comment

by:pgerman
ID: 24142276
I just realized that I deleted all the tables that the drop down menus on the form refer to.  For the time period choices there was a table for each year and in each table there was one column listing each individual month for the year as well as Q1, Q2, Q3, Q4 and full year.  Selecting Q1 would have to select the first three months of the year.

Under the custom area for time period, there are two tables that are linked to the four drop downs. One for each possible year 2007-2009 and one for each possible month.
0
 

Author Comment

by:pgerman
ID: 24142463
Is there anything I can do in the meantime to prepare the existing code?
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24142502
I hope to have this finished in the next hour, then I can expain to you.
0
 

Author Comment

by:pgerman
ID: 24142534
ok great. I really appreciate it.  I definitely want to make sure I understand everything
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24142561
We can make sure of that tomorrow.  Nothing is really difficult here (if you are used to doing it), if you want you can study up using the help on the word CONTROLS (for help in understanding the collections)
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24142941
Ok, need some clarification on the classifications.  
When looking for the revenue data do you want to see all data with
Jan07 >=5000000 and Feb07 >= 5000000 and Mar07 >= 5000000
that equates to seeing all customers with revenue above 5mil for those three months (first qtr)

or
Jan07 >=5000000 or Feb07 >= 5000000 or Mar07 >= 5000000
that equates to seeing all customers with revenue above 5mil for either three months (first qtr)

0
 

Author Comment

by:pgerman
ID: 24143012
Actually this is a good point.  I need to rethink the classifications a bit.  Originally the classifications were set to define clients for an entire year.  Therefore I should break it down to setting 'Large' to describe clients that generate more than or equal to 416666.6667 in a month.  (this is 5mm/12). So for if a quarter is selected, the Large threshold would be 3 * 416666.6667 = 1250000.  

Accordingly, Medium would be defined as any client generating between 1mm and 5mm for the year, or between 83333.3333 and 416666.6667 for the month or between 250000 and 1250000 for the quarter.

Small would be less than 83333.3333 for the month, less than 250000 for the quarter and less than 1mm for the year.
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24143116
So does that mean you don't want to see each months revenues; just a summary based on what the user selects?
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24143124
Still have several things to do, but I have to run for the night.  I am going to email the DB to you just so you can play with it a little.  We need a report to open this with.  also the segment and sub product check boxes have to be renamed to match the first two in the stack for them to work.
0
 
LVL 2

Expert Comment

by:uaexpert
ID: 24145860
What development tool do you use? Is it MS Access only or you used something like VB.NET and Access database?

You can try to use EasyQuery library: http://devtools.korzh.com/eq/

They have several editions for different platforms including ActiveX (which should be suitable for Access Forms I think) and .NET controls (in case you are developing Windows Forms application or ASP.NET web-site).
To see this solution at work take a look at the demo page: http://www.easyquerydemo.com
0
 

Author Comment

by:pgerman
ID: 24146129
Thanks again for your help.  I took a look at it.  It seems like there are some undefined variables.  Before I make to many changes, I will wait for your response.
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24147027
You need to add the Microsoft DAO 3.6 Object Library reference to fix the undefined variables.
0
 

Author Comment

by:pgerman
ID: 24147834
Ok. I will try that.  I will touch base with you at around 4PM NY time.
0
 

Author Comment

by:pgerman
ID: 24158863
masterjojobinks,

Sorry for not getting back to you yesterday, I had to jump to another project.  Have you made any additional updates to the db?
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24158884
I am working on it now. Specificly the choose time period and revenue.
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24163254
Here is something I am relatively happy with.  Look through it and feel free to ask any questions you might have about the code.
You will see that I renamed most of the controls on the form so I could utilize there name.  Others still have to changed to match the query criteria for each box and also naming convention

Option Compare Database

Option Explicit
 

Private Sub Form_Open(Cancel As Integer)
 

    'load the combo boxes with possible months

    Call LoadComboBoxes

    'set the combo boxes, check boxes, and text fields to locked

    'until the specific group button is pressed

    Me.PresetTimePeriod_CmBox_2007.Locked = True

    Me.PresetTimePeriod_CmBox_2008.Locked = True

    Me.PresetTimePeriod_CmBox_2009.Locked = True

    Me.FromMonth.Locked = True

    Me.FromYear.Locked = True

    Me.ToMonth.Locked = True

    Me.ToYear.Locked = True

    Me.Large_Criteria_Time_Period.Locked = True

    Me.Medium_Criteria_Time_Period.Locked = True

    Me.Small_Criteria_Time_Period.Locked = True

    Me.Other_Criteria_Time_Period.Locked = True

    Me.TxBox_Other_Min.Locked = True

    Me.TxBox_Other_Max.Locked = True

End Sub

Function LoadComboBoxes()

'used to load the fromMonth FromYear ToMonth ToYear

'combo boxes based on the table data
 

    Dim db As DAO.Database

    Dim tbl As DAO.TableDef

    Dim fld As Field

    Dim dteTemp As Date

    Dim strQtr As String

    Dim blnFound As Boolean

    Dim strTblName As String

    Dim strCreateSQL As String

    Dim strWriteSQL As String

    

    'create a temporary table which will hold all available data for the combo boxes

    'this data is created based on the masterdata field names

    strTblName = "tmptblComboBoxData"

    Call EraseTable(strTblName)

    'this statement create the needed sql to create the table

    strCreateSQL = "CREATE TABLE " & strTblName & " (" & _

        "FieldName TEXT(25), " & _

        "FieldDate Date, " & _

        "FieldMonth Integer, " & _

        "FieldYear Integer, " & _

        "FieldQtr TEXT(25))"

    CurrentDb.Execute (strCreateSQL)

    strTblName = "MasterData"

    'cycle through all the fields in the strTblName (MasterData) table to create the needed

    'data for the combo boxes

    Set db = CurrentDb

    Set tbl = db.TableDefs(strTblName)

    For Each fld In tbl.Fields

        'if the field name is a valid date

        If IsDate(Left(fld.Name, 2) & "-01-" & _

                Right(fld.Name, 2)) = True Then

            'assigne the combined value to a variable for use

            'data format is assumed <Month><YEAR> with leading zero (0107,0207,0307...)

            dteTemp = DateValue(Left(fld.Name, 2) & "-01-" & _

                Right(fld.Name, 2))

            'establish which quarter the date ends up in.

            'assumes fiscal year starts in jan

            Select Case Month(dteTemp)

                Case 1, 2, 3

                    strQtr = "1Q " & Year(dteTemp)

                Case 4, 5, 6

                    strQtr = "2Q " & Year(dteTemp)

                Case 7, 8, 9

                    strQtr = "3Q " & Year(dteTemp)

                Case 10, 11, 12

                    strQtr = "4Q " & Year(dteTemp)

            End Select

            'create the needed sql to append the data to the temporary table

            strWriteSQL = "INSERT INTO tmptblComboBoxData ( FieldName, FieldDate, FieldMonth, FieldYear, FieldQtr ) " & _

                            "SELECT '" & fld.Name & _

                            "', #" & dteTemp & _

                            "#, " & Left(fld.Name, 2) & _

                            ", " & Right(fld.Name, 2) & _

                            ", '" & strQtr & "';"

            CurrentDb.Execute (strWriteSQL)

        End If

    Next fld

End Function
 
 

Private Sub Preview_Cstm_Rpt_Btn_Click()

    Dim db As DAO.Database

    Dim qry As DAO.QueryDef

    Dim frm As Form

    Dim ctl As Control

    Dim ctlTmp As Control

    Dim prp As Property

    Dim varItm As Variant

    Dim strTblName As String

    Dim strSelectSQL As String

    Dim strWhereSQL As String

    Dim strSQL As String

    Dim dteStart As Date

    Dim dteEnd As Date

    

    'set up a sub routine to ensure the user has filled in the needed

    'fields to execute the query builder

    If blnCheckForm = True Then

        'these variables will be used to help establish the sql statement

        strTblName = "MasterData"

        strSelectSQL = "SELECT "

        strWhereSQL = "WHERE "

        strSQL = "FROM " & strTblName & " "

        'set the value of the form with the correct form name

        Set frm = Forms![CRA_rpt_bldr_custom1]

        'cycle through each control on the form

        For Each ctl In frm.Controls

            'find the specific control which is an option button. Each of the groups

            'have an option button that must be pressed to get to the needed criteria

            If ctl.Properties("ControlType") = acOptionButton Then

                'some of the option buttons are not fields to add to the sql but are only

                'group headings.  if the control name does not start with OB then it

                'is not a field to be added only a group heading

                If ctl.Value = True And Left(ctl.Name, 2) = "OB" Then

                    'add the found control to the select statement

                    Call BuildSelect(strSelectSQL, ctl)

                    'add the found controls to the where statement

                    Call BuildWhere(strWhereSQL, frm, ctl)

                Else

                    'if the custom time period is selected

                    If ctl.Name = "CustomTimePeriod" And ctl.Value = True Then

                        dteStart = DateValue(Me.FromMonth & "-1-" & Me.FromYear)

                        dteEnd = DateValue(Me.ToMonth & "-1-" & Me.ToYear)

                        'then add the needed field names to the select and where statement

                        Call GetFieldNames(strTblName, strSelectSQL, strWhereSQL, dteStart, _

                                            dteEnd, frm, GetCriteriaValue(frm))

                    End If

                    'if the preset time period is selected

                    If ctl.Name = "PresetTimePeriod" And ctl.Value = True Then

                        'cycle through the controls on the form again to find the right ones

                        For Each ctlTmp In frm.Controls

                            'if the control matches then pull then

                            If Left(ctlTmp.Name, Len(ctl.Name)) = ctl.Name And ctlTmp.Name <> ctl.Name Then

                                If Nz(ctlTmp.Column(0)) <> "" Then

                                    'populate the needed variables to get the field names and criteria

                                    dteStart = DateValue(ctlTmp.Column(1))

                                    dteEnd = DateValue(ctlTmp.Column(2))

                                    'then add the needed field names to the select and where statement

                                     Call GetFieldNames(strTblName, strSelectSQL, strWhereSQL, dteStart, _

                                                         dteEnd, frm, GetCriteriaValue(frm))

                                End If

                            End If

                        Next ctlTmp

                    End If

                End If

            End If

        Next ctl

        'make sure something was selected and clean up the select statement

        If Len(strSelectSQL) > 7 Then

            strSelectSQL = Left(strSelectSQL, Len(strSelectSQL) - 2) & " "

        Else

            strSelectSQL = strSelectSQL & "* "

        End If

        strSQL = strSelectSQL & strSQL

        'make sure something was selected and clean up the where statement

        If Len(strWhereSQL) > 8 Then

            strSQL = strSQL & Left(strWhereSQL, Len(strWhereSQL) - 5) & ";"

        Else

            strSQL = Left(strSQL, Len(strSQL) - 1) & ";"

        End If

    End If

    Debug.Print strSQL

    Set db = CurrentDb

    EraseQuery ("tmpqrySQLBuilder")

    db.CreateQueryDef "tmpqrySQLBuilder", strSQL

    DoCmd.OpenQuery "tmpqrySQLBuilder"

End Sub

Function GetCriteriaValue(frm As Form) As String

    'this function is used to find the correct criteria based on the user

    'input from the by classifications section of the form

    'the definitions of the size is stored in the tblClassificationsDefined table

    

    Dim ctlTemp As Control

    Dim blnFound As Boolean

    Dim rsRead As DAO.Recordset

    Dim strReadSQL As String

        

    'loop through all the controls to find the correct set of check boxes

    For Each ctlTemp In frm.Controls

        'if the control matches the criteria_time_period

        If Right(ctlTemp.Properties("Name"), 20) = "Criteria_Time_Period" Then

            'if the control is selected

            If ctlTemp.Value = True Then

                'this sql statement gets the data for the specific size client the user choose

                strReadSQL = "SELECT tblClassificationsDefined.* " & _

                                "FROM tblClassificationsDefined " & _

                                "WHERE (((tblClassificationsDefined.CName)='" & _

                                        ctlTemp.Properties("Name") & "'));"

                'this statement establishes a record set in memory which can be gone through

                'using this code below.  this recordset can be manipulated as well (though this is not)

                Set rsRead = CurrentDb.OpenRecordset(strReadSQL)

                    'make sure something was returned in the sql statement

                    If rsRead.RecordCount > 0 Then

                        'these if statements are set up based on the way the table is established

                        'if the lower bound of the criteria is not blank

                        If Nz(rsRead("CNumberSmall")) <> "" Then

                            'if the upper bound of the criteria is not blank

                            If Nz(rsRead("CNumberBig")) <> "" Then

                                'the code assumes the criteria is between the upper and lower

                                GetCriteriaValue = "Between " & rsRead("CNumberSmall") & _

                                    " AND " & rsRead("CNumberBig")

                            Else

                                'if the upper bound is blank

                                'the code assumes the criteria is greather or equal to the small number

                                GetCriteriaValue = ">= " & rsRead("CNumberSmall")

                            End If

                        Else

                            'if the lowe bound is blank and

                            'if the upper bound of the criteria is not blank

                            If Nz(rsRead("CNumberBig")) <> "" Then

                                'the code assumes the criteria is less than or equal to the big number

                                GetCriteriaValue = "<= " & rsRead("CNumberBig")

                            Else

                                'if both boxes are empty something is wrong

                                MsgBox "Sorry, nothing found to match selected criteria in the tblClassificationsDefined table", vbCritical

                            End If

                        End If

                    Else ' check to see if the user selected other values

                        If Left(ctlTemp.Properties("Name"), 5) = "Other" Then

                            GetCriteriaValue = "Between " & Me.TxBox_Other_Min & _

                                    " AND " & Me.TxBox_Other_Max

                        Else

                            'if both boxes are empty something is wrong

                            MsgBox "Sorry, nothing found to match selected criteria in the tblClassificationsDefined table", vbCritical

                        End If

                    End If

                rsRead.Close

            End If

        End If

    Next ctlTemp

End Function

Function GetFieldNames(strTblName As String, strSelectSQL As String, strWhereSQL As String, dteStart As Date, dteEnd As Date, frm As Form, strValue As String)

    'this function goes through the strTblName and looks for any field

    'which will qualify as a date field

    

    Dim db As DAO.Database

    Dim tbl As DAO.TableDef

    Dim fld As Field

    Dim dteTemp As Date

    Dim blnFound As Boolean

    

    blnFound = False

    'add the needed open perethecies to the sql

    strWhereSQL = strWhereSQL & "("

    'establish the variables need to access the fields collection

    Set db = CurrentDb

    Set tbl = db.TableDefs(strTblName)

    'establish a loop through the fields of this table strTblName

    For Each fld In tbl.Fields

        'if the field falls into the criteria of a date then <month><Year> with leading zero (0107,0207,0307...)

        If IsDate(Left(fld.Name, 2) & "-01-" & Right(fld.Name, 2)) = True Then

            'assigne the field name a real date based on the first day of the month

            dteTemp = DateValue(Left(fld.Name, 2) & "-01-" & _

                Right(fld.Name, 2))

            'if the field name real date falls equal to or between the passed in start and dend date then

            If dteTemp >= dteStart And dteTemp <= dteEnd Then

                'build the segment for the select statement

                Call BuildSelectTimePeriod(strSelectSQL, fld.Name)

                'build the segment for the where statement

                Call BuildWhereTimePeriod(strWhereSQL, frm, fld, strValue)

                blnFound = True

            End If

        End If

    Next fld

    'clean up the sql as needed

    If blnFound = True Then

        strWhereSQL = Left(strWhereSQL, Len(strWhereSQL) - 4) & ") AND "

    Else

        strWhereSQL = Left(strWhereSQL, Len(strWhereSQL) - 1)

    End If

End Function

Function BuildSelectTimePeriod(strSQL As String, strVal As String)

    'used to build the required select portion of the sql statement for the time

    'period specific fields

    strSQL = strSQL & "[" & strVal & "], "

End Function

Function BuildWhereTimePeriod(strSQL As String, frm As Form, fld As Field, strValue As String)

    'used to build the required where segment for the sql statement for the time

    'period specific fields

    strSQL = strSQL & "[" & fld.Name & "] " & strValue & _

    " AND " 'by changing this and to an or the query is changed from each fielding matching to

            'criteria to any field matching the criteria

End Function

Function BuildSelect(strSQL As String, ctl As Control)

    'used to build the required select portion of the sql statement

    strSQL = strSQL & "[" & Mid(ctl.Name, 4, Len(ctl.Name) - 3) & "], "

End Function

Function BuildWhere(strSQL As String, frm As Form, ctl As Control)

    'used to build the required where segment for the sql statement

    

    Dim ctlTemp As Control

    Dim blnFound As Boolean

    

    blnFound = False

    strSQL = strSQL & "("

    

    'loop through all the controls to find the correct set of check boxes

    For Each ctlTemp In frm.Controls

        'make sure the criteria selections on the form are check boxes and name correctly

        If ctlTemp.Properties("ControlType") = acCheckBox Then

            'if the control name contains an under score continue

            If InStr(1, ctlTemp.Properties("name"), "_") > 0 Then

                'if the first part of the control is named the same as the group name continue

                If Left(ctlTemp.Properties("name"), InStr(1, ctlTemp.Properties("name"), "_") - 1) _

                    = Mid(ctl.Name, 4, Len(ctl.Name) - 3) Then

                    'if the correct control is checked then add it to the where clause

                    If ctlTemp.Value = True Then

                        strSQL = strSQL & "[" & Left(ctlTemp.Name, InStr(1, ctlTemp.Name, "_") - 1) & _

                            "] = '" & Right(ctlTemp.Name, Len(ctlTemp.Name) - InStrRev(ctlTemp.Name, "_")) & "' OR "

                        blnFound = True

                    End If

                End If

            End If

        End If

    Next ctlTemp

    'clean up the sql as needed

    If blnFound = True Then

        strSQL = Left(strSQL, Len(strSQL) - 4) & ") AND "

    Else

        strSQL = Left(strSQL, Len(strSQL) - 1)

    End If

End Function

Function blnCheckForm() As Boolean

    'write code to ensure the user has entered all the data needed to run the sql builder

    'specificly make sure the time period has four valid entries.

    blnCheckForm = True

End Function

Private Sub Hierarchy_AfterUpdate()

    'lock the needed controls so the user can not select them without the group

    If Me.Hierarchy = True Then

        Me.OB_UltP_Name.Locked = False

        Me.OB_Cust_Name.Locked = False

        Me.OB_CAS_ACCT_NUMBER.Locked = False

        Me.OB_UltP_Name = False

        Me.OB_Cust_Name = False

        Me.OB_CAS_ACCT_NUMBER = False

    Else

        Me.OB_UltP_Name.Locked = True

        Me.OB_Cust_Name.Locked = True

        Me.OB_CAS_ACCT_NUMBER.Locked = True

        Me.OB_UltP_Name = Null

        Me.OB_Cust_Name = Null

        Me.OB_CAS_ACCT_NUMBER = Null

    End If

End Sub

Private Sub CustomTimePeriod_AfterUpdate()

    'lock the needed controls so the user can not select them without the group

    If Me.CustomTimePeriod = True Then

        Me.FromMonth.Locked = False

        Me.FromMonth = ""

        Me.FromYear.Locked = False

        Me.FromYear = ""

        Me.ToMonth.Locked = False

        Me.ToMonth = ""

        Me.ToYear.Locked = False

        Me.ToYear = ""

        Me.PresetTimePeriod = False

        Call PresetTimePeriod_AfterUpdate

        Me.Large_Criteria_Time_Period.Locked = False

        Me.Large_Criteria_Time_Period = False

        Me.Medium_Criteria_Time_Period.Locked = False

        Me.Medium_Criteria_Time_Period = False

        Me.Small_Criteria_Time_Period.Locked = False

        Me.Small_Criteria_Time_Period = False

        Me.Other_Criteria_Time_Period.Locked = False

        Me.Other_Criteria_Time_Period = False

    Else

        Me.FromMonth.Locked = True

        Me.FromMonth = ""

        Me.FromYear.Locked = True

        Me.FromYear = ""

        Me.ToMonth.Locked = True

        Me.ToMonth = ""

        Me.ToYear.Locked = True

        Me.ToYear = ""

        Me.Large_Criteria_Time_Period.Locked = True

        Me.Large_Criteria_Time_Period = False

        Me.Medium_Criteria_Time_Period.Locked = True

        Me.Medium_Criteria_Time_Period = False

        Me.Small_Criteria_Time_Period.Locked = True

        Me.Small_Criteria_Time_Period = False

        Me.Other_Criteria_Time_Period.Locked = True

        Me.Other_Criteria_Time_Period = False

    End If

End Sub

Private Sub PresetTimePeriod_AfterUpdate()

    'lock the needed controls so the user can not select them without the group

    If Me.PresetTimePeriod = True Then

        Me.PresetTimePeriod_CmBox_2007.Locked = False

        Me.PresetTimePeriod_CmBox_2007 = ""

        Me.PresetTimePeriod_CmBox_2008.Locked = False

        Me.PresetTimePeriod_CmBox_2008 = ""

        Me.PresetTimePeriod_CmBox_2009.Locked = False

        Me.PresetTimePeriod_CmBox_2009 = ""

        Me.CustomTimePeriod = False

        Call CustomTimePeriod_AfterUpdate

        Me.Large_Criteria_Time_Period.Locked = False

        Me.Large_Criteria_Time_Period = False

        Me.Medium_Criteria_Time_Period.Locked = False

        Me.Medium_Criteria_Time_Period = False

        Me.Small_Criteria_Time_Period.Locked = False

        Me.Small_Criteria_Time_Period = False

        Me.Other_Criteria_Time_Period.Locked = False

        Me.Other_Criteria_Time_Period = False

    Else

        Me.PresetTimePeriod_CmBox_2007.Locked = True

        Me.PresetTimePeriod_CmBox_2007 = ""

        Me.PresetTimePeriod_CmBox_2008.Locked = True

        Me.PresetTimePeriod_CmBox_2008 = ""

        Me.PresetTimePeriod_CmBox_2009.Locked = True

        Me.PresetTimePeriod_CmBox_2009 = ""

        Me.Large_Criteria_Time_Period.Locked = True

        Me.Large_Criteria_Time_Period = False

        Me.Medium_Criteria_Time_Period.Locked = True

        Me.Medium_Criteria_Time_Period = False

        Me.Small_Criteria_Time_Period.Locked = True

        Me.Small_Criteria_Time_Period = False

        Me.Other_Criteria_Time_Period.Locked = True

        Me.Other_Criteria_Time_Period = False

    End If

End Sub
 

Private Sub ToMonth_AfterUpdate()

    'make sure the date values selected by the user are correct

    If Nz(Me.ToMonth) <> "" And Nz(Me.ToYear) <> "" And Nz(Me.FromMonth) <> "" And _

            Nz(Me.FromYear) <> "" Then

        'make sure the from date is less than or equal to the to date

        If DateValue("01-" & Me.ToMonth & "-" & Me.ToYear) < DateValue("01-" & Me.FromMonth & _

                "-" & Me.FromYear) Then

            MsgBox "To date selection must be later or equal to from date.", vbCritical

        End If

    End If

End Sub
 

Private Sub ToYear_AfterUpdate()

    'make sure the date values selected by the user are correct

    If Nz(Me.ToMonth) <> "" And Nz(Me.ToYear) <> "" And Nz(Me.FromMonth) <> "" And _

            Nz(Me.FromYear) <> "" Then

        'make sure the from date is less than or equal to the to date

        If DateValue("01-" & Me.ToMonth & "-" & Me.ToYear) < DateValue("01-" & Me.FromMonth & _

                "-" & Me.FromYear) Then

            MsgBox "To date selection must be later or equal to from date.", vbCritical

        End If

    End If

End Sub

Private Sub FromMonth_AfterUpdate()

    'make sure the date values selected by the user are correct

    If Nz(Me.FromMonth) <> "" And Nz(Me.FromYear) <> "" And Nz(Me.FromMonth) <> "" And _

            Nz(Me.FromYear) <> "" Then

        'make sure the from date is less than or equal to the to date

        If DateValue("01-" & Me.FromMonth & "-" & Me.FromYear) < DateValue("01-" & Me.FromMonth & _

                "-" & Me.FromYear) Then

            MsgBox "From date selection must be later or equal From from date.", vbCritical

        End If

    End If

End Sub
 

Private Sub FromYear_AfterUpdate()

    'make sure the date values selected by the user are correct

    If Nz(Me.FromMonth) <> "" And Nz(Me.FromYear) <> "" And Nz(Me.FromMonth) <> "" And _

            Nz(Me.FromYear) <> "" Then

        'make sure the from date is less than or equal to the to date

        If DateValue("01-" & Me.FromMonth & "-" & Me.FromYear) < DateValue("01-" & Me.FromMonth & _

                "-" & Me.FromYear) Then

            MsgBox "From date selection must be later or equal From from date.", vbCritical

        End If

    End If

End Sub
 

Private Sub Large_Criteria_Time_Period_AfterUpdate()

    'lock the needed controls so the user can not select them without the group

    If Me.Large_Criteria_Time_Period = True Then

        Me.Medium_Criteria_Time_Period = False

        Me.Small_Criteria_Time_Period = False

        Me.Other_Criteria_Time_Period = False

        Call Other_Criteria_Time_Period_AfterUpdate

    End If

End Sub
 

Private Sub Medium_Criteria_Time_Period_AfterUpdate()

    'lock the needed controls so the user can not select them without the group

    If Me.Medium_Criteria_Time_Period = True Then

        Me.Large_Criteria_Time_Period = False

        Me.Small_Criteria_Time_Period = False

        Me.Other_Criteria_Time_Period = False

        Call Other_Criteria_Time_Period_AfterUpdate

    End If

End Sub
 

Private Sub Small_Criteria_Time_Period_AfterUpdate()

    'lock the needed controls so the user can not select them without the group

    If Me.Small_Criteria_Time_Period = True Then

        Me.Large_Criteria_Time_Period = False

        Me.Medium_Criteria_Time_Period = False

        Me.Other_Criteria_Time_Period = False

        Call Other_Criteria_Time_Period_AfterUpdate

    End If

End Sub
 

Private Sub Other_Criteria_Time_Period_AfterUpdate()

    'lock the needed controls so the user can not select them without the group

    If Me.Other_Criteria_Time_Period = True Then

        Me.Large_Criteria_Time_Period = False

        Me.Medium_Criteria_Time_Period = False

        Me.Small_Criteria_Time_Period = False

        Me.TxBox_Other_Max.Locked = False

        Me.TxBox_Other_Max = ""

        Me.TxBox_Other_Min.Locked = False

        Me.TxBox_Other_Min = ""

    Else

        Me.TxBox_Other_Max.Locked = True

        Me.TxBox_Other_Max = ""

        Me.TxBox_Other_Min.Locked = True

        Me.TxBox_Other_Min = ""

    End If

End Sub

Private Sub OB_Sub_Prod_Name_AfterUpdate()

    'used to lock the check boxes for this group

    If Me.OB_Sub_Prod_Name = True Then

        Call LockControls(Me.Form, "Sub Prod Name", False)

    Else

        Call LockControls(Me.Form, "Sub Prod Name", True)

    End If

End Sub
 

Private Sub OB_UltP_Geo_Region_Code_AfterUpdate()

    'used to lock the check boxes for this group

    If Me.OB_UltP_Geo_Region_Code = True Then

        Call LockControls(Me.Form, "UltP Geo Region Code", False)

    Else

        Call LockControls(Me.Form, "UltP Geo Region Code", True)

    End If

End Sub
 

Private Sub OB_UltP_Segment_AfterUpdate()

    'used to lock the check boxes for this group

    If Me.OB_UltP_Segment = True Then

        Call LockControls(Me.Form, "UltP Segment", False)

    Else

        Call LockControls(Me.Form, "UltP Segment", True)

    End If

End Sub
 

Function LockControls(frm As Form, strCtlName As String, blnValue As Boolean)

    'lock or unlock all controls passed through

    Dim ctl As Control

    'cycle through each control on the form

    For Each ctl In frm.Controls

        If Left(ctl.Properties(1), Len(strCtlName)) = strCtlName Then

            ctl.Locked = blnValue

            ctl = False

        End If

    Next ctl

End Function

Open in new window

0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24163297
If you get an error saying the type mismatch on the line 'For Each fld in tbl.Fields' then make sure your references are list like the attached screen print.

References.JPG
0
 

Author Comment

by:pgerman
ID: 24225398
I have just gotten back to working on the db
I am getting the error:
"The expression On Open you entered as the event property setting produced the following error: Procedure declaration does not match description of event or procedure having the same name."

I think this has to do with the Form_open code
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24225431
When you get the error hit debug and let me know which line it is happening on.
The only thing I can think of is you have two on open routines.
0
 

Author Comment

by:pgerman
ID: 24225964
It doesn't even let me choose debug, it just shows a message box with the error, only option is 'ok' then it closes out
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24225994
When the message comes up hit <ctrl><break>
When you hit ok you should get a debug option.
0
 

Author Comment

by:pgerman
ID: 24226043
its still just closing the msg box and doing nothing
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24226096
Can you email a copy of DB for me to look at?
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24226371
Strange...
First thing you need to do is add the 'Microsoft DAO 3.6 Object 2.1 Library' to your references as shown on posting
Date:04/16/09 05:18 PM

The error you are getting seems to be caused by the Preview_Cstm_Rpt_Btn_DBLClick() event on the preview custom reports button.  The only thing I could do to get it to work was change the event to a click event.
So...
The second thing you need to do is change the event property of the Preview_Cstm_Rpt_Btn from a double click to a single click.  Then copy the code into the single click routine and delete the double click routine.

If case you are wondering how I figured out it was this perticular routine ... I opened the VBA code window and compiled the code.  This shows you all errors that happen and where, just like debug.

0
 

Author Comment

by:pgerman
ID: 24242698
Now its saying the sub or function 'Erasetable' is not defined.  Is there another reference I need to check?
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24242766
Oh, sorry forgot about adding that function.  Add this code in a module and that will go away along with another error you will get about at erasequery function.

Function EraseTable(TableName As String)

    

    'Delete the table specified

    If CheckTable(TableName) = True Then DoCmd.DeleteObject acTable, TableName

End Function
 

Function CheckTable(TableName As String) As Boolean

On Error GoTo subexit:

 

    If CurrentDb.TableDefs(TableName).Name = TableName Then

        CheckTable = True

    End If

 

subexit:

End Function
 

Function EraseQuery(QueryName As String)

    

    'Delete the Query specified

    If CheckQuery(QueryName) = True Then DoCmd.DeleteObject acQuery, QueryName

End Function
 

Function CheckQuery(QueryName As String) As Boolean

On Error GoTo subexit:

 

    If CurrentDb.QueryDefs(QueryName).Name = QueryName Then

        CheckQuery = True

    End If

 

subexit:

End Function

Open in new window

0
 

Author Comment

by:pgerman
ID: 24242942
I realized I needed the module with this function.  I brought that in.  Now I'm runnning into a runtime error type 13 mismatch. on the line 'For Each fld In tbl.Fileds'
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24242965
If you look back at the previous posting on 04/16/09 05:18 PM, it addresses that problem.
0
 

Author Comment

by:pgerman
ID: 24243043
oh ok. I did not realize the order of the references libraries was an issue
0
 

Author Comment

by:pgerman
ID: 24243078
What do you think my next steps should be in getting this thing to work?  I have renamed all objects on the form to coprrespond with the naming conventions
0
 

Author Comment

by:pgerman
ID: 24243238
right now, the form loads fine, then if nothing is checked, the preview custom report button pulls up the master table.  It seems as though the classification check boxes are locked, but if I select one of the other option buttons and try to click the preview report button, I get a run time error '5'  Invalid procedure call or argument on line:
"If Left(ctlTemp.Properties("name"), InStr(1, ctlTemp.Properties("name"), "_") - 1) = Mid(ctl.Name, 4, Len(ctl.Name) - 3) Then"
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24243249
If you have done that, the next step would be to assign the query which is created to a report.
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24243528
I can only get an error when selecting custom.
This is because a check routine needs to be written to ensure the form is filled out correctly.  Example is when custom time period is choosen the combo boxes for month and year must be filled out before the preview button is selected.  
The only thing I can think of that would cause your error is if it cannot find the option bubble or check box you selected or the check box is named wrong.  In this perticular line is checking to see if the first part of the control(option bubble for each group) is named the same as the field(check boxes).
Can you tell me exactly what you are selecteding to get the error?
0
 

Author Comment

by:pgerman
ID: 24243716
Initially I tried to copy and paste the code into my form function by function.  Now I just imported the form directly and renamed my old one and it seems to be working.

There are some modifications I need to make.

For the preset time period, I want the user to be able to select any individual month or quarter for each of the years, or the entire year.

What might get tricky is defining the classifications as small, medium or large.  If just one month is selected, then large clients will be defined as those who generated more than $416.6667k.  If a quarter is selected, large clients will be >1.25mm and if the full year is selected it will be back to >5mm.  If custom time period is selected, the user will be forced to define an 'other' classification.  I would also like to have the 'show all' option in which no classification setting is required.

Another issue is with the date field names.  The filed names are based on a particular reporting system and are not the actual time periods.  So 0209 is actually January data.  In the table, the field will still be 0209, but to the user it will be January.  Is there a way to deal with this?
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24244215
Lets see... I can point you in the right direction...
First, the preset time period  is defined by the LoadComboBoxes function.  In this function you can add code to include month or year.  Basicly it creates a temporary table which contains the lable (what the user sees) and then a start date and end date.  So for a specific month the data would be March, 3/1/09, 3/31/09.

Second, the classifications can be redefined base on other input, it would just have to be code that way in the that would have to be coded in the GetCriteriaValue function.  The classifications are defined in a table tblClassificationsDefined and this table could hold more values or you could select different values based on user input (like qtr, month, or year).

Third,  the best way to deal with this would be to keep the coding the way it is (for the most part) and just subtract or add a month as needed.
0
 

Author Comment

by:pgerman
ID: 24246027
I'm having some problems changing the code to allow for other options in the preset time period drop down menus
0
 

Author Comment

by:pgerman
ID: 24246056
I am trying this:
  Select Case Month(dteTemp)
                Case 1, 2, 3
                    strQtr = "1Q " & Year(dteTemp)
                Case 4, 5, 6
                    strQtr = "2Q " & Year(dteTemp)
                Case 7, 8, 9
                    strQtr = "3Q " & Year(dteTemp)
                Case 10, 11, 12
                    strQtr = "4Q " & Year(dteTemp)
             
           ' End Select
           
           ' Select Case Month(dteTemp)
                Case 1
                    strQtr = "Jan " & Year(dteTemp)
                Case 2
                    strQtr = "Feb " & Year(dteTemp)
                Case 3
                    strQtr = "Mar " & Year(dteTemp)
                Case 4
                    strQtr = "Apr " & Year(dteTemp)
                Case 5
                    strQtr = "May " & Year(dteTemp)
                Case 6
                    strQtr = "Jun " & Year(dteTemp)
                Case 7
                    strQtr = "Jul " & Year(dteTemp)
                Case 8
                    strQtr = "Aug " & Year(dteTemp)
                Case 9
                    strQtr = "Sep " & Year(dteTemp)
                Case 10
                    strQtr = "Oct " & Year(dteTemp)
                Case 11
                    strQtr = "Nov " & Year(dteTemp)
                Case 12
                    strQtr = "Dec " & Year(dteTemp)
                   
            'End Select
           
           ' Select Case Month(dteTemp)
                Case 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12
                    strQtr = "Full Year " & Year(dteTemp)
            End Select

But it is just picking up on the first part, so it still only displays the quarters
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24246133
you are on the right track.  I think what you missing is writing that data to the table after it determines an action.
Add this after each end select.  That gets you most of the way there.  The segment that writes the data for each month will have to modified a little to grab the last day of the month as well.
            'create the needed sql to append the data to the temporary table

            strWriteSQL = "INSERT INTO tmptblComboBoxData ( FieldName, FieldDate, FieldMonth, FieldYear, FieldQtr ) " & _

                            "SELECT '" & fld.Name & _

                            "', #" & dteTemp & _

                            "#, " & Left(fld.Name, 2) & _

                            ", " & Right(fld.Name, 2) & _

                            ", '" & strQtr & "';"

            CurrentDb.Execute (strWriteSQL)

Open in new window

0
 

Author Comment

by:pgerman
ID: 24246192
ok I got it to work.  Now how about sorting the list in a logical order, like Jan - Dec, then 1Q-4Q then Full Year.  Right now it is sorting alphabetically
0
 

Author Comment

by:pgerman
ID: 24246549
Also, each query needs to display the sum of revenues in a given time period by client.  So I guess I now need to add 'Sum( ) ' to the select clauses and also another function to build the 'group by' clause. I'm a bit stuck as to how to start with this.
0
 

Author Comment

by:pgerman
ID: 24249431
I was able to figure out adding the group by clause.  my next step is creating a total field and only showing the total when a specific quarter, time range or full year is selected.  Currently if full year is selected, the query returns 12 columns, I want it to return one.  I will also need to redefine the classifications of small medium and large.  I think I am making good progress with this.  I have some questions, but I will post another sample of my db in a bit to confirm I am on the right track
0
 

Author Comment

by:pgerman
ID: 24249783
I realized that I probably need a HAVING clause rather than a where clause. I am having trouble breaking up the first part of the having clause which restates the sum  and should look like:
HAVING(Sum([0209]+[0109])
and the second part which would add on any criteria looking like:
AND [UltP Segment] = 'AM'

I have attached another sample db so you can get an idea of where I am.

Thank you so much again for your help
PGsampleDB042809.zip
0
 

Author Comment

by:pgerman
ID: 24250116
Please refer to this attachment.  I have made some changes, but still stuck.
PGsampleDB042809.zip
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24251058
Let me make sure I understand what you want.  You want the values from each field totaled based on the criteria choosen by the user (qtr, year, month).  You don't need a sum you just need to add the fields together creating a total field.
Look at the attached copy of the database.
I took the code back to where it was the last time I uploaded it and added the code needed to do the following:
I created another field that is generated automaticly based on the preset criteria or custom criteria.
Also I added a couple of fields to the combo boxes so it sorts the way you want it.

One thing.  Before you zip up and upload your database make sure you have compacted and repaired the database.  The last upload was 73.1M.  In reality the db size is about 600K.

PG-Sample-db-042809.mdb
0
 

Author Comment

by:pgerman
ID: 24251190
Can you email me the latest db? I am unable to pull it off the site at this work station
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24251217
Yep, the email is away
0
 

Author Comment

by:pgerman
ID: 24251298
Wow, that looks great.  You are very good at this stuff.  One thing though, If 'Full year' is selected, I only want Full year field to show, accordingly if an individual quarter is selected I would only want Quarter Total field to show. Same for any time period that is set by a custom selection Jan 2008 through Feb 2009 would return only only field, the total for the time period.
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24251361
Two options.  You can only use the fields you want in the report or we can eliminate adding each field to the select statement.
At this point, the easiest is the second option:
To do the second option all you have to do is comment out this line of code in the getFieldNames function
Call BuildSelectTimePeriod(strSelectSQL, fld.Name)

0
 

Author Comment

by:pgerman
ID: 24251647
I am now getting a "Run-time error '0': Reserved Error"
on this line
If Nz(ctlTmp.Column(0)) <> "" Then
0
 

Author Comment

by:pgerman
ID: 24252191
Ok disregard my last comment.  Had to just create a fresh db and import what I needed.  but the query is still returning more than one record per client.  I want it to only return one, to do this you wouldn't include the entire year's fields in the select or WHERE clauses.  Any thoughts on this?
0
 

Author Comment

by:pgerman
ID: 24252375
Some how I need to build queries that look like this:

SELECT Sum([0108]+[0208]+[0308]+[0408]+[0508]+[0608]+[0708]+[0808]+[0908]+[1008]+[1108]+[1208]) AS [Full Year 2008], MasterData.[UltP Segment], MasterData.[UltP Name]
FROM MasterData
GROUP BY MasterData.[UltP Segment], MasterData.[UltP Name]
HAVING (((Sum([0108]+[0208]+[0308]+[0408]+[0508]+[0608]+[0708]+[0808]+[0908]+[1008]+[1108]+[1208]))<>False) AND ((MasterData.[UltP Segment])='AM'));
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24252418
I guess I was assuming that each record in the master database was for a client.  Here I go assuming again, Cust ID indicates each client?  The way this sql is built, only the fields that are selected are included in the query.  Are you wanting to include a standard set of fields and also include those fields selected.  Example, if I only choose a preset time, the only field I get is that preset time.  Also the criteria is against each field not the sum of the field.  Where should the criteria be?
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24252548
Don't worry all is not lost.  We will just have to break this into two pieces.  Getting the fields wanted, then limiting the data.  I am working on it now.
0
 

Author Comment

by:pgerman
ID: 24252596
If I select UltP, I only want UltP to show. I think the criteria should apply to the sum of the fields
0
 

Author Comment

by:pgerman
ID: 24252622
Basically the unique identifier is a combination of CAS Acct # and Feeder ID.  There can be multiple CAS Acct #s for each Cust ID and multiple Cust IDs for each UltP
0
 

Author Comment

by:pgerman
ID: 24252650
I think I solved part of the problem by replacing 'AND' with '+' in the 'BuildWhereTimePeriod' function, but i still need to figure out how to apply a 'group by' clause
0
 

Author Comment

by:pgerman
ID: 24253076
Also, if you get a chance, do you see what I am trying to do based on the latest design of my form?

I added a specific client section.  So here, the user would enter the name of the UltP and the query would return only data for that client.  I put in a button that will pull up a list of the UltPs so the user will be able to just copy and paste from that list into the textboxes.

Also in the second box on the form (upper right quadrant) I added 'show all' which appears twice and added 'other' an additional time.  The idea here is that the user can select from large medium or small if they are choosing a preset time period, but if they have chosen custom, they are only able to define an 'other' revenue range.  Selecting 'Show all' will simply not add any criteria to the revenue field, but I want it to be so that if it is selected, you can't select Large medium or small.

One small thing: once the user clicks in the 'other' check box, the cursor should move to the max and min entry boxes and blink extra visibly.
0
 

Author Comment

by:pgerman
ID: 24253749
how are we looking?

I don't want to make extensive changes if you are working on the same things, but if you re unable to look at it anymore today, let me know and i will continue trying different ideas.
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24253843
We are fine.  The query is fixed and works fine.  I am working on adding the additional things you added to the form.  
0
 

Author Comment

by:pgerman
ID: 24253868
awesome, you have been such a big help, I wish there was some way I could help you in return.
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24254011
Question,
You added other and all for custom and you added all for preset.
The way it is coded night now, the user selects either custom or preset on the left and whatever they chose for classification applies to preset or custom (depending on what they choose).

So do you need classification for custom by its self?
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24254455
I went back and re-read one of your previous postings.  We have two options,  we can control the form and to esure the user does not select large, medium or small when dealing with custom, or we can add the two buttons.  I would much rather just control the form rather than change the code.
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24254775
Here is the database with the new changes.  look at custom form 2
Emailed as well
PG-Sample-db-042809.mdb
0
 

Author Comment

by:pgerman
ID: 24255227
I am getting a syntax error on the group by clause
0
 

Author Comment

by:pgerman
ID: 24255244
strGroupBySQL = Left(strGroupBySQL, Len(strGroupBySQL) - 7) & " "

I think I got it.  I just needed to switch the 2 to a 7 in the above line
0
 

Author Comment

by:pgerman
ID: 24255419
now running into an error with the Where/Having clause, it says there is an extra paren, cant figure out where
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24259535
Let me know what you are doing to get this error.  I can not get any errors to happen.
0
 

Author Comment

by:pgerman
ID: 24259989
Ok, I did a bunch of testing and did some clean up, mostly all working now which is great.  Now it seems like one of the last problems is when I try to select multiple preset time periods (like 2007 full year, 2008 full year and 2009 full year at the same time) and set the class to large.  Its giving me a runtime error 3075.  I think I just need to get a space after the and and before the parenthesis in one part.  You should see what I mean if you select multiple preset full years, large and ultimate parent name, then view custom report.

Also, what do you think the best way is for me to go about re defining the classifications small, medium and large to be more specific?  If you click on the button on the form below s m & l it explains how I am trying to define them.
PG-Latest-Sample-042909.mdb
0
 

Author Comment

by:pgerman
ID: 24261477
I fixed the problem I mentioned above, except for the redefining of the classes
0
 

Author Comment

by:pgerman
ID: 24261781
Also, one more thing I am trying to add:  the capability for the query to detect lost clients.  A lost client would be defined as one that had revenue at any point in a set time period and then had zero for the remainder of the time period.  So if the user selected Jan 2009 - March 2009, a lost client would be one that had revenue in Jan and Feb, or Juset one of the two, but not in the last, or last two months of the quarter.

Do you think this is possible?
0
 

Author Comment

by:pgerman
ID: 24261843
If you are going to make any changes to the db, please make them to this latest attached db, it has most of the errors that were popping up taken care of.  Also I have made some other updates to the form and main menu
latest-sample2.zip
0
 

Author Comment

by:pgerman
ID: 24263184
I have done more test queries and it seems like the problem at this point is just getting the puncuation correct for the sql statements.  For example for some queries an extra parentesis may be needed, but not in an other, so if I put it into the code it fixes some queries but then messes up others.

0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24271008
I am out of pocket yesterday and today.  I will get back with you tomorrow.
0
 

Author Comment

by:pgerman
ID: 24271197
awesome thank you
0
 

Author Comment

by:pgerman
ID: 24277469
I believe I have fixed all the glitches with the punctuation of the sql for all different combinations of criteria an fields.  I will post so you can have a look.  As for the lost clients query, I think I have a plan for it. (see below)

All other thoughts about what it will take to complete this db at this time:

I will add a check box to the main form that will be similar to 'other' and 'show all'.  If the lost box is checked then the query will have to execute all the given criteria on the sheet just like any other query, but the results will just be lost clients during the specified time period.  Lost will for now simply be defined as any client that has 0 revenue in the last month of the time period selected.  So if you selected preset, full year 2008, lost, ultp name, you should get any client that had zero revenue as of december 2008, but if you selected a custom time period (say Jan 2008 to March 2009), lost and ultp name and one of the clients that had zero rev in dec 08 picked up again in q1 09, the client would no longer be considered lost.  The one problem is that we also wouldnt consider a client that had 0 during the entire time period lost, so the query should first eliminate all records with all 0 rev from its considerations of potential new clients.

But actually if we just defined an 'other' revenue class and set the minimum to anything above $0.00, this should handle the issue.

So if we just add the lost check box and add that if checked, it gets added to the where clause that the end month must equal 0, the queries should then return still a sum of the time period, so even though lost in the time period, the query would show what ever revenue the client had until it was lost which would indicate the total impact to revenue in the next time period.

On thing that I would need to add would be having the option of showing all months in the time period, like we did at first.  This would allow the user to see exactly what month the client dropped off.  If we have this as an option, I assume we could have it as an option for even if lost is not selected.
Maybe this could be done by innputting two more option buttons next to 'choose the time period' on the form.  The options would be:  Detail(show all months in the time period) and summary ( give the total for the time period)

Then when that is figured out, I just need to keep testing and testing to make sure very combination works.  Some little things that should be easy like locking segment, region and sub product criteria selections, so that they cannot be checked unless their corresponding field selector option button is selected.
PG-Sample-Latest-043009.mdb
0
 

Author Comment

by:pgerman
ID: 24278103
I just realized another error coming up.  If you don't set a revenue range by selecting other, large, medium or small and simply click show all, or nothing, it gives a 3075 error
0
 

Author Comment

by:pgerman
ID: 24278211
there are others as well, i can't seem to fix all of them
0
 

Author Comment

by:pgerman
ID: 24282589
I assume we might just have to get back to this early next week?
0
 

Author Comment

by:pgerman
ID: 24304576
I fixed all the errors mentioned above and now am looking to change/fix the db in this order:

1. Have the option of selecting a WHERE clause, even if the field option button isn't selected.

2. The summary and detail options.

3. The lost clients query as mentioned above in a previous post.


#1 This would allow the user to select criteria for a field that would not show in the query, the logic of a where clause.  Right now on the main form if you don't select the field in the 3rd box, you can not set criteria for that field in the 4th box.  I want to change this so that if the user doesnt select the field in the third box, they can still set criteria for the field, there just won't be any grouping at that level so the criteria will be combined together and grouped at the other level selected.

#2 The summary and detail options.  Basically right now the form is in summary mode.  We discussed this before and agreed that it would be best to have one column of data as the sum of the time period selected and not show every individual month of the time period selected.  If detail mode is selected, I want the queries to return all the individual months and a total column.  I beleive the detail mode will be necessary for the lost clients query.
0
 

Author Comment

by:pgerman
ID: 24304625
Here is the latest
PG-Sample-Latest-050509.mdb
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24308060
To address the first one, I would include a check box to each field option and allow the user to select whether to show the field or not.  Then you could opt to exclude that field form the building of the select statement.  If the check box is named as some direvitive of the option box then it becomes simple to check for.

To address the second one, I would do simply check to see of the detail button is checked and then include the fields as part of the select statement.

Do you need help doing these changes?
0
 

Author Comment

by:pgerman
ID: 24310113
I got everything working and figured out, except for the lost clients query function.  Also I have noticed that if you leave the query open and click away from it, it appears lost to the user and if you then try to run the query again, it gives an error.  So I need to error trap for that.  Is there away to say IF query is open then close query?  I would need to add this in on Form_Open 9the reset button would use it) and also on the view custom report button, if by chance the user just clicks away from the query window.

Here is my latest db
PG-Sample-DB-Latest-050509.mdb
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24314523
The query problem cannot be fixed, directly.  The only reason I wrote the code to a query was to show that it worked.  The SQL that is created should be written to a report or form.  At that point the form or report could be controled.  The query is not designed for the user to see.  Also I noticed that you are asking the user to save the query as New1 or whatever.  I would take and write the sql statement to a table and allow the user to enter a name for the query.  Write this data to a table then you can write an interface to allow the user to select a saved query based on the the name they entered.  Then use this sql to open the report and form.

As for the lost clients query,  If I understand what you want, all you must do is change the where statement to include any client with a zero balance during your time period.
0
 

Author Comment

by:pgerman
ID: 24315836
I don't quite follow.  What do you mean the query was not meant for the user to see?  Also in other words there is no ' if open, then close' funtionality?

As for the lost clients query, it would need to show those clients that have 0 for the last month of the time period selected, not the whole period
0
 

Author Comment

by:pgerman
ID: 24316013
Ok I think I now remember you mentioning that you saw the query that would be generated as a precursor to a report, but I think just getting the query is enough, because basiclly the purpose of the program is to generate a bunch of different queries, not necessarily all worth creating a 'pretty' report for.  I was assuming the user could just copy and paste the data from the query they built using the form if they wanted.  I guess my next step could be having it exported to excel for them by hitting a button.  Is that what youre thinking?
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24316215
That would be the next step in my mind.  In stead of opening the qry, just have it export to excel and the user can view the data in excel.
0
 

Author Comment

by:pgerman
ID: 24316335
ok how do you recommend implementing that?
0
 

Author Comment

by:pgerman
ID: 24316409
actually how about we just open the query results on a form, which will always be on top.  It may also be useful to have the ability to have multiple reports open at once for comparison reasons
0
 
LVL 8

Accepted Solution

by:
masterjojobinks earned 500 total points
ID: 24316866
You could do that, open the form in a datasheet view with either your header or footer having buttons on it to export, print, whatever.
The issue with this is you don't know what fields you will have in the query, meaning you have to change the fields on the form to match your query.  If you need help with this issue, I would post another question.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
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…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

757 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

18 Experts available now in Live!

Get 1:1 Help Now