pgerman
asked on
How to create a dynamic query builder
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.
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.
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
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
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.RowSou
Set db = CurrentDb
Set tbl = db.TableDefs("mytable")
For i = 0 To tbl.Fields.Count - 1
If lstSelectableFields.RowSou
lstSelectableFields.RowSou
Next i
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.RowSou
Set db = CurrentDb
Set tbl = db.TableDefs("mytable")
For i = 0 To tbl.Fields.Count - 1
If lstSelectableFields.RowSou
lstSelectableFields.RowSou
Next i
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
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
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.
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.
ASKER
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.
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.
ASKER
When you say 'start cyclying through your controls' is this something that can be done each time new criteria is entered?
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.
ASKER
I have looked over their responses. Your response seems most inline with what I am tryin to do. I would really appreciate further assistence
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.
But pgerman likes your approach so I think you should go with it.
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.
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)?
masterjojobinks, you got it well under control, so I will unsubscribe. You dont need my services here. Good luck!
ASKER
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.
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).
ASKER
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
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
ASKER
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.?
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 :>)
ASKER
alright sounds good. So I guess i should start by getting everything like the check boxes, textboxes and radio buttons named right?
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.
ASKER
ok great, let me know if that long post I submitted made sense. or if I need to specify anything
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
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
ASKER
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.co
many thanks
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.co
many thanks
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...
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...
ASKER
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.
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?
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?
ASKER
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
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?
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?
ASKER
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'
I'm not sure what you mean by 'displayed in SQL'
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?
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?
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?
BTW, what type of data is the Group account number, because the code only accounts for text fields?
ASKER
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.
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.
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.
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.
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?
What kind of data is in these fields? Text or numbers?
ASKER
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.
ASKER
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
If you don't mind I will just make the modifications to your DB and then explain what I did.
ASKER
ok np. Thank you
ASKER
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?
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?
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
also the Customer Name and Ultimate Parent Name
ASKER
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
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
ASKER
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.
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.
ASKER
Is there anything I can do in the meantime to prepare the existing code?
I hope to have this finished in the next hour, then I can expain to you.
ASKER
ok great. I really appreciate it. I definitely want to make sure I understand everything
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)
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)
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)
ASKER
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.
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.
So does that mean you don't want to see each months revenues; just a summary based on what the user selects?
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.
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
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
ASKER
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.
You need to add the Microsoft DAO 3.6 Object Library reference to fix the undefined variables.
ASKER
Ok. I will try that. I will touch base with you at around 4PM NY time.
ASKER
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?
Sorry for not getting back to you yesterday, I had to jump to another project. Have you made any additional updates to the db?
I am working on it now. Specificly the choose time period and revenue.
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
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
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
References.JPG
ASKER
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
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
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.
The only thing I can think of is you have two on open routines.
ASKER
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
When the message comes up hit <ctrl><break>
When you hit ok you should get a debug option.
When you hit ok you should get a debug option.
ASKER
its still just closing the msg box and doing nothing
Can you email a copy of DB for me to look at?
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_DBLCl
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.
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_DBLCl
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.
ASKER
Now its saying the sub or function 'Erasetable' is not defined. Is there another reference I need to check?
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 FunctionASKER
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'
If you look back at the previous posting on 04/16/09 05:18 PM, it addresses that problem.
ASKER
oh ok. I did not realize the order of the references libraries was an issue
ASKER
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
ASKER
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("n
"If Left(ctlTemp.Properties("n
If you have done that, the next step would be to assign the query which is created to a report.
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?
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?
ASKER
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?
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?
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.
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.
ASKER
I'm having some problems changing the code to allow for other options in the preset time period drop down menus
ASKER
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
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
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.
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)ASKER
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
ASKER
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.
ASKER
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
ASKER
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
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
ASKER
Please refer to this attachment. I have made some changes, but still stuck.
PGsampleDB042809.zip
PGsampleDB042809.zip
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
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
ASKER
Can you email me the latest db? I am unable to pull it off the site at this work station
Yep, the email is away
ASKER
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.
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(strS
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(strS
ASKER
I am now getting a "Run-time error '0': Reserved Error"
on this line
If Nz(ctlTmp.Column(0)) <> "" Then
on this line
If Nz(ctlTmp.Column(0)) <> "" Then
ASKER
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?
ASKER
Some how I need to build queries that look like this:
SELECT Sum([0108]+[0208]+[0308]+[
FROM MasterData
GROUP BY MasterData.[UltP Segment], MasterData.[UltP Name]
HAVING (((Sum([0108]+[0208]+[0308
SELECT Sum([0108]+[0208]+[0308]+[
FROM MasterData
GROUP BY MasterData.[UltP Segment], MasterData.[UltP Name]
HAVING (((Sum([0108]+[0208]+[0308
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?
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.
ASKER
If I select UltP, I only want UltP to show. I think the criteria should apply to the sum of the fields
ASKER
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
ASKER
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
ASKER
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.
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.
ASKER
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.
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.
We are fine. The query is fixed and works fine. I am working on adding the additional things you added to the form.
ASKER
awesome, you have been such a big help, I wish there was some way I could help you in return.
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?
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?
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.
Here is the database with the new changes. look at custom form 2
Emailed as well
PG-Sample-db-042809.mdb
Emailed as well
PG-Sample-db-042809.mdb
ASKER
I am getting a syntax error on the group by clause
ASKER
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
I think I got it. I just needed to switch the 2 to a 7 in the above line
ASKER
now running into an error with the Where/Having clause, it says there is an extra paren, cant figure out where
Let me know what you are doing to get this error. I can not get any errors to happen.
ASKER
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
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
ASKER
I fixed the problem I mentioned above, except for the redefining of the classes
ASKER
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?
Do you think this is possible?
ASKER
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
latest-sample2.zip
ASKER
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.
I am out of pocket yesterday and today. I will get back with you tomorrow.
ASKER
awesome thank you
ASKER
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
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
ASKER
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
ASKER
there are others as well, i can't seem to fix all of them
ASKER
I assume we might just have to get back to this early next week?
ASKER
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.
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.
ASKER
Here is the latest
PG-Sample-Latest-050509.mdb
PG-Sample-Latest-050509.mdb
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?
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?
ASKER
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
Here is my latest db
PG-Sample-DB-Latest-050509.mdb
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.
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.
ASKER
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
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
ASKER
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?
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.
ASKER
ok how do you recommend implementing that?
ASKER
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
How to use the query by form (QBF) technique in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;en-us;304428