Reporting

**zmbd** · Sep 18 '12, 01:58 PM

A long shot for me; however, have you tried a cross tab query/report? You might be able to fit the columns as the headers and the hydrants as the rows? Or are you looking for a different format?

**TheSmileyCoder** · Sep 18 '12, 02:05 PM

It would make it alot easier for us to present help and for you to undersaid said help it we speak from the same context. Could you please show us your table layout (Field names and types) as well as a few example records?

**didacticone** · Sep 18 '12, 02:42 PM

[IMGnothumb]http://i4.photobucket. com/albums/y147/xmistertoolx/fields.jpg[/IMGnothumb]

example records is challenging because they vary.... for the asset_id its obviously just a number, the other fields are mostly ok, not ok, or yes or no... pretty basic.

**zmbd** · Sep 18 '12, 03:22 PM

Apparently, the firewalls here are blocking the image... I've tried to fix it so that is shows. Hopefully, TheSmileyCoder can see it :)

As for the records... just a simple CSV format would be good:
[field1],[field2],[field3],[field4]
1, some record info, Y, N
2, another record, N, N
3, YAR, Y, Y

Table format such as:
tbl_name
[field1] primary key, autonumber
[field2] text(50), required, no duplicates
[field3] boolean
[field4] boolean

-z

**didacticone** · Sep 18 '12, 04:07 PM

Ok ive attached the csv

Attached Files

table.txt (1.4 KB, 359 views)

**didacticone** · Sep 18 '12, 04:08 PM

http://i4.photobucket.com/albums/y14...olx/fields.jpg that is a link to the fields... perhaps you can see it then

**TheSmileyCoder** · Sep 18 '12, 06:22 PM

Weird I could see the image earlier, and now I can't. Odd.

One approach could be to have code that checks each field, and if "negative" adds text to a combined string, and the report then shows said combined string.

**TheSmileyCoder** · Sep 18 '12, 06:33 PM

Start by placing all the fields you want visible into your report. Then add in all the fields you want to combine onto your report and set their visibility to False. You can make them as small as you want and delete their labels too if required.

Finally add a textbox where you want the "summary" status to be printed on your report. In this case I callde it tb_Combined. Make sure it has its "CanGrow" property set to true.

Then in the reports Detail Format event:

Code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
   Dim strC As String
   
   If Me.Drainage & "" <> "OK" Then
      strC = strC & "Drainage: " & Me.DrainageComments & vbNewLine
   End If
   
   If Me.Drip & "" <> "OK" Then
      strC = strC & "Drip: " & Me.DripComments & vbNewLine
   End If
   
   'Add more field checks in here if needed
   If strC <> "" Then
      Me.tb_Combined = Mid(strC, 1, Len(strC) - 2) 'The -2 is to remove the last vbNewline.
      Else
      Me.tb_Combined = "OK" 'Nothing wrong at all.
      
   End If
   
   
End Sub

**didacticone** · Sep 19 '12, 12:57 AM

ok going to try this tomorrow... thanks for the help and i'll let you know how it goes. i really appreciate the help guys.

**didacticone** · Sep 19 '12, 04:01 PM

tried it and it works awesome! i have on extra question though. for the drainage category i have 2 options, "OK", "Drains Slowly", "Doesn't Drain". I want if it says "ok" or "Drains slowly" for it to be considered "ok"... if your following... i want to know if it drains slowly but as far as the report goes i want it to be considered ok and am not quite sure how to manipulate the code to get the required results.

**TheSmileyCoder** · Sep 19 '12, 07:25 PM

You have 2 possibilites either check that its different from both ok and Drains slowly, or check that it is equal to Doesn't drain.

Code:

'Example condition one
If Me.Drainage & "" <> "OK" AND me.Drainage & ""<>"Drains slowly" Then
'Example condition 2
IF ME.Drainage & ""="Doesn't Drain" Then

With that said, do you control the entry of data so that user(s) cannot put "not draining" or "Drain not working" and such?

A good part of UI design is ensuring uniformity and ensuring that users cannot take wrong actions (cause they inevitable do, and when they do, they blame you the developer)

**didacticone** · Sep 20 '12, 11:00 AM

i actually used your second example right after i posted the message and it worked perfectly. and yes users can only input the data a specific way. thanks so much for your help!

**TheSmileyCoder** · Sep 20 '12, 11:56 AM

Thats nice to hear, and I am glad to hear you know to limit your users to pre-defined entry values.