Results 1 to 11 of 11
  1. #1
    stalk is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    143

    looping fields with out null values to generate a report

    I have a table with 40 fields like pid, date_received, hdate,ph, and 20 other fields. These 20 other filed names ends with _ct. For ex: A_ct, B_ct,C_ct. Now my question is I have to generate a report based of the drop down pid selected. And I only want to include the _ct fields whose value is >=0.



    How can I programmatically generate a report with pid, date_received, hdate,ph and only _ct fields with >= 0 value. ( zero should be included ). Fields with null values should not be on the report.

    Thank you so much

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    It would require code to loop the fields. The issue highlights a normalization problem. Typically fields like that would be records in a related table, where Nulls could easily be excluded via query criteria.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    stalk is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    143
    How can I filter using query?

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You can't with your current setup. You get the whole record, then have to deal with potential null values in fields. If they were records instead of fields, it would be easy:

    Item Value
    a_ct 123
    b_ct 456
    c_ct Null

    In fact normally c_ct probably wouldn't even have a record.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    stalk is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    143
    Using array can I loop each field value and store them in temporary table. Then use this temporary table to generate the report? not sure how to use arrays

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'd fix the design and store the data in a normalized related table. Failing that, a UNION query could "normalize" the fields, though that would make the data vertical rather than horizontal (which is normally how this type of thing would be displayed). Leaving all as is, I'd have a report with 20 hidden labels/textboxes for these values, with code in the open event to loop the record source and set the label captions/textbox control sources for the appropriate fields, and making them visible. It sounds complicated because a database isn't meant to store data in fields like that. Research normalization.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I agree with everything pbaldy has suggested to you. Without knowing why you're storing your data in a non-normalized structure it's standard advice to clean it up to make your query process more friendly.

    Having said that, if you are attempting to find records based on one of these fourty fields and you do not have the option of changing your data you could try concatenating a string in both your query and your criteria

    i.e.

    in your query have the field

    SearchCT: a_ct & "~" & b_ct & "~" & c_ct & "~" & ... etc.

    then build a criteria from your form the same way (I would tend to build this on the form then just reference the field on the form in your criteria)

    criteria for the same form assuming your form is named MYFORM with fields labeled a_ct, b_ct, etc, just like your table structure

    Criteria: forms!MYFORM!a_ct & "~" & forms!MYFORM!b_ct & "~" & forms!MYFORM!c_ct & "~" ..... etc.

    you'd just have to account for the possibility of a space being in the fields on your form which would screw things up on null value items in your table. This would be extremely ugly to build and you may have to do it in batches of 10 fields because your statement may get too long. As baldy has pointed out non -normalized structures are not great to work with but you can make them work if you have no other option.

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    An alternative might be to concatenate the fields to one long string. Likely require a custom function to exclude the Null fields.

    Problem I see with compressing the data by any approach is the label headers will not be consistent. Values in each 'column' will not all be from the same field. So how would you want to label this data?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    hah, we're thinking the same thing june, that's why I forced a tilde into the string to separate fields

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I don't think criteria is the problem with the existing data, you're always pulling a single record with the specified pid. The problem is that each record will have a varying number of these 20 fields filled out, and the goal is to only display those fields. I suppose a function could be an option, looping through and returning a string with field names and values.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Build the sql in vba omitting the null fields, assign it to a querydef then open the query as a pdf?
    Not much control over how it looks (images, headers, etc) but should take care of the problem of controls with no data.

    @stalk: typical Access report has controls that are always there. As noted, they could be made invisible if empty via code, or perhaps if you want to put conditional formatting on 20 (40?) controls. Likely there'd be gaps because of the invisible ones. Building a report on the fly would be a horrendous undertaking. Better to normalize as it would hurt far less and you're likely to run into more trouble like this as you go if you simply create work-arounds.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 12-04-2013, 01:55 PM
  2. Replies: 3
    Last Post: 01-20-2012, 04:46 PM
  3. Importing numeric fields with null values from XML
    By hcorvallis in forum Import/Export Data
    Replies: 1
    Last Post: 10-20-2011, 08:09 AM
  4. Access: Updating Null Fields with values
    By bmaz in forum Queries
    Replies: 2
    Last Post: 10-11-2011, 05:18 PM
  5. Hide Fields With Null Values
    By jay in forum Reports
    Replies: 6
    Last Post: 09-22-2011, 07:23 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums