473,320 Members | 1,930 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Is it possible to hide fields in a query based on criteria?

53
Hi,

I have a monthly sales query that sums monthly sales by product (each month has its own field from January last year until December of the current year). A user of this table will be prompted to select a beginning date and an ending date and the sales for each month are calculated based on the dates given (say if the dates inputted are 7/18/2006 and 7/18/2007, the field for January-June 2006 will sum to 0 for all products and July 2006-July 2007 will have some value). Is it possible to hide the fields that sum to 0? So if the months are not within the date range selected, the fields for those months would not be shown.

I don't know how to describe my knowledge of VBA exactly. I have been learning on the go. I wrote code for a query by form so a user would be able to select criteria and an order (4 levels) and it works perfect. I like to think that if I see a code I can figure out what each element is doing, but I'm still a beginner.

Any help will be appreciated.

Thanks in advance,

Raza
Jul 18 '07 #1
11 9141
ADezii
8,834 Expert 8TB
Hi,

I have a monthly sales query that sums monthly sales by product (each month has its own field from January last year until December of the current year). A user of this table will be prompted to select a beginning date and an ending date and the sales for each month are calculated based on the dates given (say if the dates inputted are 7/18/2006 and 7/18/2007, the field for January-June 2006 will sum to 0 for all products and July 2006-July 2007 will have some value). Is it possible to hide the fields that sum to 0? So if the months are not within the date range selected, the fields for those months would not be shown.

I don't know how to describe my knowledge of VBA exactly. I have been learning on the go. I wrote code for a query by form so a user would be able to select criteria and an order (4 levels) and it works perfect. I like to think that if I see a code I can figure out what each element is doing, but I'm still a beginner.

Any help will be appreciated.

Thanks in advance,

Raza
SQL is really not my area, but you would probably have to dynamically create a QueryDef (Query) Object, and built the SQL Statement only to show those Months that are explicitly included within the Range. This would be a little tricky. I suggest being patient and see if more qualidfied SQL Experts have a different, and more practical, approach.
Jul 19 '07 #2
RZ15
53
SQL is really not my area, but you would probably have to dynamically create a QueryDef (Query) Object, and built the SQL Statement only to show those Months that are explicitly included within the Range. This would be a little tricky. I suggest being patient and see if more qualidfied SQL Experts have a different, and more practical, approach.
Yeah, I have been toying around with the SQL stuff, but have not been successful so far.

If anyone can help or post any ideas, i will appreciate it.
Jul 19 '07 #3
RZ15
53
Sorry for the bump, but I really need help with this. I'm going to continue to tackle this today, but it would help me a lot and save me a lot of time if someone can at least point me in the right direction.
Jul 19 '07 #4
RZ15
53
:( i'm getting desparate.
Jul 19 '07 #5
:( i'm getting desparate.
If the report is based on a query, in the criteria row of the date field of the query use

Between [Enter Start Date] and [Enter End Date]

this will ask the user for the same information as you described and only pull the data for the specified time period.

Otherwise, if you need the records to show but be blank instead of 0, in the field properties in the report, under "Data" tab, in the "Control Source", use an IIf statement, such as

IIf ([valuefield] = 0,"",[valuefield])

This will basically make an empty field result if the value is 0.

Hope this helps
Jul 19 '07 #6
RZ15
53
If the report is based on a query, in the criteria row of the date field of the query use

Between [Enter Start Date] and [Enter End Date]

this will ask the user for the same information as you described and only pull the data for the specified time period.

Otherwise, if you need the records to show but be blank instead of 0, in the field properties in the report, under "Data" tab, in the "Control Source", use an IIf statement, such as

IIf ([valuefield] = 0,"",[valuefield])

This will basically make an empty field result if the value is 0.

Hope this helps
Thanks for your help, but I'm afraid my issue is a little more complex. It's not that I want the fields that will be 0 for all records to be blank, it's that I want those fields to go away entirely. So since I have fields for each month of this year and last year, I don't want the field January to show at all if it's not within the date range...I just want all the relevant fields to shift over and the blank fields to go away. This is why I was trying to do this in a query instead of a report.
Jul 19 '07 #7
mlcampeau
296 Expert 100+
Thanks for your help, but I'm afraid my issue is a little more complex. It's not that I want the fields that will be 0 for all records to be blank, it's that I want those fields to go away entirely. So since I have fields for each month of this year and last year, I don't want the field January to show at all if it's not within the date range...I just want all the relevant fields to shift over and the blank fields to go away. This is why I was trying to do this in a query instead of a report.
It sounds like you are just wanting data within the specified date range to show up. Trainer's first solution will help with that. Here's an example from a query that I have used to produce a report showing performance reviews between a specified date range:

Expand|Select|Wrap|Line Numbers
  1. SELECT CDate([Enter the beginning performance review date]) AS BegDate, CDate([Enter the end performance review date]) AS EndDate
  2. FROM tablename
  3. WHERE (((TABLE.FIELD)>=CDate([Enter the beginning performance review date]) AND (TABLE.FIELD)<=CDate([Enter the end performance review date])))
Please keep in mind that I have removed all irrelevant fields, just to show you how this goal can be accomplished. In the Where clause, where it says "Table.Field", this would be the date field that you are comparing the date range to. I hope this helps!
Jul 19 '07 #8
ADezii
8,834 Expert 8TB
Thanks for your help, but I'm afraid my issue is a little more complex. It's not that I want the fields that will be 0 for all records to be blank, it's that I want those fields to go away entirely. So since I have fields for each month of this year and last year, I don't want the field January to show at all if it's not within the date range...I just want all the relevant fields to shift over and the blank fields to go away. This is why I was trying to do this in a query instead of a report.
Here is a partial solution in Beta Testing (LOL) and it is a little tricky to follow. The following code opens a Recordset based on a Query named qryTest. This would parallel your original Query in which certain Fields are returning all Zeros (0s). It examines the 1st Record in the Recordset and sees which Field(s) contain a 0. If the Field does not contain a 0, the Field Name is added to an Array whose elements are sequentially numbered. After looping through the Fields Collection, this Array now contains either a Field Name indicating a Non-Zero value in the Field or an Empty String (0). Each element of the Array is now filtered and only those elements containing Field Names are concatenated to a SQL String which will eventually be used to recreate your original Query but with only those Fields that do not return Zeros. The test Recordset contained 7 Fields, 4 of which returned Zeros interspersed in Field order, and the other 3 Fields having valid entries were FirstName, LastName and MI. The sample code and OUTPUT representing a partial SQL String is listed below for you to see. I did not spend too much time on it, it is RAW, and a little confusing to follow, but I have tested it and it is functional. Look at it closely for awhile and let me know what you think:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database, MyRS As DAO.Recordset
  2. Dim intNoOfFields As Integer, intCounter As Integer, strSQL As String
  3. Dim strSQLInclude() As String, intArrayElement As Integer
  4.  
  5. Set MyDB = CurrentDb()
  6. Set MyRS = MyDB.OpenRecordset("qryTest", dbOpenSnapshot)
  7.  
  8. 'Keep Collection and Array Indexes in sync
  9. intNoOfFields = MyRS.Fields.Count
  10. 'Array to hold Field Names to include in New SQL String
  11. ReDim strSQLInclude(0 To intNoOfFields - 1)     'MAXIMUM
  12.  
  13. MyRS.MoveFirst      'Move to 1st Record - essentially a Test Record
  14. intArrayElement = 0     'initialize the 1st Array Element
  15.  
  16. 'Check ALL Fields in the 1st Record to see if there is a Zero (0) in any Field.
  17. 'If there isn't, include the Field Name in the strSQLInclude() Array
  18. For intCounter = 0 To intNoOfFields - 1
  19.   If MyRS.Fields(intCounter).Value <> 0 Then
  20.     strSQLInclude(intArrayElement) = MyRS.Fields(intCounter).Name
  21.     intArrayElement = intArrayElement + 1       'keep elements in sequence
  22.   Else
  23.   End If
  24. Next
  25.  
  26. strSQL = "SELECT "
  27.  
  28. For intCounter = LBound(strSQLInclude) To UBound(strSQLInclude)
  29.   If Len(strSQLInclude(intCounter)) > 0 Then
  30.     strSQL = strSQL & strSQLInclude(intCounter) & ", "
  31.   End If
  32. Next
  33.  
  34. strSQL = Left$(strSQL, Len(strSQL) - 2) & " FROM tblEmployee"
  35. Debug.Print strSQL
  36. MyRS.Close
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. SELECT FirstName, LastName, MI FROM tblEmployee
Jul 20 '07 #9
RZ15
53
Here is a partial solution in Beta Testing (LOL) and it is a little tricky to follow. The following code opens a Recordset based on a Query named qryTest. This would parallel your original Query in which certain Fields are returning all Zeros (0s). It examines the 1st Record in the Recordset and sees which Field(s) contain a 0. If the Field does not contain a 0, the Field Name is added to an Array whose elements are sequentially numbered. After looping through the Fields Collection, this Array now contains either a Field Name indicating a Non-Zero value in the Field or an Empty String (0). Each element of the Array is now filtered and only those elements containing Field Names are concatenated to a SQL String which will eventually be used to recreate your original Query but with only those Fields that do not return Zeros. The test Recordset contained 7 Fields, 4 of which returned Zeros interspersed in Field order, and the other 3 Fields having valid entries were FirstName, LastName and MI. The sample code and OUTPUT representing a partial SQL String is listed below for you to see. I did not spend too much time on it, it is RAW, and a little confusing to follow, but I have tested it and it is functional. Look at it closely for awhile and let me know what you think:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database, MyRS As DAO.Recordset
  2. Dim intNoOfFields As Integer, intCounter As Integer, strSQL As String
  3. Dim strSQLInclude() As String, intArrayElement As Integer
  4.  
  5. Set MyDB = CurrentDb()
  6. Set MyRS = MyDB.OpenRecordset("qryTest", dbOpenSnapshot)
  7.  
  8. 'Keep Collection and Array Indexes in sync
  9. intNoOfFields = MyRS.Fields.Count
  10. 'Array to hold Field Names to include in New SQL String
  11. ReDim strSQLInclude(0 To intNoOfFields - 1)     'MAXIMUM
  12.  
  13. MyRS.MoveFirst      'Move to 1st Record - essentially a Test Record
  14. intArrayElement = 0     'initialize the 1st Array Element
  15.  
  16. 'Check ALL Fields in the 1st Record to see if there is a Zero (0) in any Field.
  17. 'If there isn't, include the Field Name in the strSQLInclude() Array
  18. For intCounter = 0 To intNoOfFields - 1
  19.   If MyRS.Fields(intCounter).Value <> 0 Then
  20.     strSQLInclude(intArrayElement) = MyRS.Fields(intCounter).Name
  21.     intArrayElement = intArrayElement + 1       'keep elements in sequence
  22.   Else
  23.   End If
  24. Next
  25.  
  26. strSQL = "SELECT "
  27.  
  28. For intCounter = LBound(strSQLInclude) To UBound(strSQLInclude)
  29.   If Len(strSQLInclude(intCounter)) > 0 Then
  30.     strSQL = strSQL & strSQLInclude(intCounter) & ", "
  31.   End If
  32. Next
  33.  
  34. strSQL = Left$(strSQL, Len(strSQL) - 2) & " FROM tblEmployee"
  35. Debug.Print strSQL
  36. MyRS.Close
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. SELECT FirstName, LastName, MI FROM tblEmployee
Thank you, I am going to try to do this now and I'll let you know how it goes.

As for the poster before you, sorry if I was not clear but that is not the problem I am having. My query will calculate the monthly sales properly for the given date range. There is nothing wrong with my query. It's just that when certain dates are entered and some months that are represented by FIELDS end up being 0 for all records since the month is not within the date range. My goal is to hide those fields entirely, based on the given dates (I don't want fields showing up that have 0s for all records).
Jul 20 '07 #10
ADezii
8,834 Expert 8TB
Thank you, I am going to try to do this now and I'll let you know how it goes.

As for the poster before you, sorry if I was not clear but that is not the problem I am having. My query will calculate the monthly sales properly for the given date range. There is nothing wrong with my query. It's just that when certain dates are entered and some months that are represented by FIELDS end up being 0 for all records since the month is not within the date range. My goal is to hide those fields entirely, based on the given dates (I don't want fields showing up that have 0s for all records).
Keep in mind that the SQL String in the OUTPUT section is only partially complete. You must add the exact Date Range as specified in the Original Query to the SQL String or else any results will be meaningless.
Jul 20 '07 #11
RZ15
53
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database, MyRS As DAO.Recordset
  2. Dim intNoOfFields As Integer, intCounter As Integer, strSQL As String
  3. Dim strSQLInclude() As String, intArrayElement As Integer
  4.  
  5. Set MyDB = CurrentDb()
  6. Set MyRS = MyDB.OpenRecordset("qryTest", dbOpenSnapshot)
  7.  
  8. 'Keep Collection and Array Indexes in sync
  9. intNoOfFields = MyRS.Fields.Count
  10. 'Array to hold Field Names to include in New SQL String
  11. ReDim strSQLInclude(0 To intNoOfFields - 1)     'MAXIMUM
  12.  
  13. MyRS.MoveFirst      'Move to 1st Record - essentially a Test Record
  14. intArrayElement = 0     'initialize the 1st Array Element
  15.  
  16. 'Check ALL Fields in the 1st Record to see if there is a Zero (0) in any Field.
  17. 'If there isn't, include the Field Name in the strSQLInclude() Array
  18. For intCounter = 0 To intNoOfFields - 1
  19.   If MyRS.Fields(intCounter).Value <> 0 Then
  20.     strSQLInclude(intArrayElement) = MyRS.Fields(intCounter).Name
  21.     intArrayElement = intArrayElement + 1       'keep elements in sequence
  22.   Else
  23.   End If
  24. Next
  25.  
  26. strSQL = "SELECT "
  27.  
  28. For intCounter = LBound(strSQLInclude) To UBound(strSQLInclude)
  29.   If Len(strSQLInclude(intCounter)) > 0 Then
  30.     strSQL = strSQL & strSQLInclude(intCounter) & ", "
  31.   End If
  32. Next
  33.  
  34. strSQL = Left$(strSQL, Len(strSQL) - 2) & " FROM tblEmployee"
  35. Debug.Print strSQL
  36. MyRS.Close
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. SELECT FirstName, LastName, MI FROM tblEmployee
I tried this and nothing seemed to happen (and I did activate Microsoft DAO 3.6 Object Library in the References, though I am not familiar with it). There was no error, it's just that it started to load and then it stopped and nothing happened.

I didn't know exactly where to put the code, so I just put it in a form and put it on the On Click event of an OK button. I removed the parameters from my query and ran it and nothing happens.

Here is how I modified your code to fit mine (I only changed the names of the query/table):

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command0_Click()
  2. Dim MyDB As DAO.Database, MyRS As DAO.Recordset
  3. Dim intNoOfFields As Integer, intCounter As Integer, strSQL As String
  4. Dim strSQLInclude() As String, intArrayElement As Integer
  5.  
  6. Set MyDB = CurrentDb()
  7. Set MyRS = MyDB.OpenRecordset("z1", dbOpenSnapshot)
  8.  
  9. 'Keep Collection and Array Indexes in sync
  10. intNoOfFields = MyRS.Fields.Count
  11. 'Array to hold Field Names to include in New SQL String
  12. ReDim strSQLInclude(0 To intNoOfFields - 1)     'MAXIMUM
  13.  
  14. MyRS.MoveFirst      'Move to 1st Record - essentially a Test Record
  15. intArrayElement = 0     'initialize the 1st Array Element
  16.  
  17. 'Check ALL Fields in the 1st Record to see if there is a Zero (0) in any Field.
  18. 'If there isn't, include the Field Name in the strSQLInclude() Array
  19. For intCounter = 0 To intNoOfFields - 1
  20.   If MyRS.Fields(intCounter).Value <> 0 Then
  21.     strSQLInclude(intArrayElement) = MyRS.Fields(intCounter).Name
  22.     intArrayElement = intArrayElement + 1       'keep elements in sequence
  23.   Else
  24.   End If
  25. Next
  26.  
  27. strSQL = "SELECT "
  28.  
  29. For intCounter = LBound(strSQLInclude) To UBound(strSQLInclude)
  30.   If Len(strSQLInclude(intCounter)) > 0 Then
  31.     strSQL = strSQL & strSQLInclude(intCounter) & ", "
  32.   End If
  33. Next
  34.  
  35. strSQL = Left$(strSQL, Len(strSQL) - 2) & " FROM dbo_INIDP"
  36. Debug.Print strSQL
  37. MyRS.Close
  38. End Sub
As you can see, i only changed the names of the queries/tables.

I also tried your code in a dummy-DB in which i made a table with one record. 4 columns had 0's and 3 had some text in them. I created a query pulling all fields and then the form with the OK button again to put the code in. It still didn't result in anything.

What am I doing wrong?
Jul 20 '07 #12

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: nick_faye | last post by:
hi, hope someone can help me. i am a newbie in creating queries and i want to create a query wherein i only get entries from my table where values of fields 2, 3 and 4 are not zeros. for...
7
by: nospam | last post by:
Am I trying to do something that is impossible with access 97? I want to write an address book application. I have exported (from a Casio BOSS organiser) all my contact information and it is...
5
by: Megan | last post by:
Hi everybody- I'm helping a friend with a music database. She has an old one and is creating a new one. She wants to compare records and fields in the old database with records and fields in the...
3
by: Pea | last post by:
Is it possible to get the average in a query where I have multiple criteria? Here's an abbreviated example of the query in design view: Fields: USERID DATE TIME ID_TYPE...
1
by: Matthew | last post by:
Hey, I have built a form that has certain combo and text boxes on it, which a user specifies his criteria and then clicks on a search button to run a query based on that criteria. To build to...
3
by: Jan Szymczuk | last post by:
I am trying to create a query that will show me who is phoning who in an organisation from available Telephone Billing information. I am creating a MSAccess 2000 database with a few few tables, two...
6
by: Brian | last post by:
Hello, Basically, I'm running a query on a form's activation, and I'd like to have the results of the query be placed into other fields on the same form automatically. Does anybody know how...
2
by: fstenoughsnoopy | last post by:
I have a customer order database and I need to pull a customers information, ie first name, last name, address, city, state, zip, phone, etc, into the oder table. i don't know how to go about...
2
by: missnaughton | last post by:
Hi I'm designing a query for our hockey coach to print out players' names to stick on game sheets. I could sort the query results based on Jersey number, but the problem is the 2 goalie names...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.