Reporting hell (1 Viewer)

Kelly Napier · Mar 6, 2018

Can someone please help me...

This is what I want - but it doesn't work - can someone tell me how to do it properly please??

=Sum([Diagnosis]="Anxiety")+([Diagnosis2]="Anxiety")+([Diagnosis3]="Anxiety")+([Diagnosis4]="Anxiety")*-1

Minty · Mar 6, 2018

Try removing the Sum - it's an aggregate function and your don't appear to be aggregating it, simply performing a calculation of sorts...

plog · Mar 6, 2018

=Sum([Diagnosis]="Anxiety")+([Diagnosis2]="Anxiety")+([Diagnosis3]="Anxiety")+([Diagnosis4]="Anxiety")*-1

How about you tell us what you are trying to do. I mean, there's a lot wrong in that expression -- more than just removing a Sum will fix.

You've essentially got a big math problem:

A + B + C - D

But each element is a comparison not a number (X="Value") so you are essentially adding a bunch of True/False values together--which makes little sense--but isn't the proper way to do a comparison anyway (you need an IIf to do that). Then for some reason you threw in a Sum on just the first element.

What's the big picture here?

Minty · Mar 6, 2018

I think the OP is simply trying to work out if the patient definitely has Anxiety by using the True + True + True + True type of logic.

However as you have pointed out, there seems to a fundamental data storeage issue, there shouldn't be a field for diagnosis 4,3,2,1 etc...

Kelly Napier · Mar 6, 2018

Ok, so up to four diagnoses per client... and the look up fields are the same. So anxiety could be listed in any four....

I tried taking the Sum out - but it doesn't even generate a number in the report.....

Kelly Napier · Mar 6, 2018

each field
Diagnosis, Diagnosis2, Diagnosis3, Diagnosis4
has the same look up field with a list of diagnosis.... each client can have up to four.

When totting up I don't want to have to put a sum of each field for each look up entry - so I'd like the one sum to capture all the "anxiety" in each field and count them.

Sorry - I'm self taught and don't get the terminology very well...

pbaldy · Mar 6, 2018

Where do you have it? It wouldn't work in the page footer, but could in the report or group footers.

Mark_ · Mar 6, 2018

Minty, either that or Kelly has great anxiety, thus the "Reporting Hell"...

@ Kelly, Where are you trying to do this? I am guessing in a group footer on a report, but please let us know.

Mark_ · Mar 6, 2018

Kelly Napier said:
Ok, so up to four diagnoses per client... and the look up fields are the same. So anxiety could be listed in any four....

I tried taking the Sum out - but it doesn't even generate a number in the report.....

If there is more than one diagnosis, this should be in a child file. Most any time that you have "One of X" situations you will want to save that information in a child record and establish a One to Many relationship.

plog · Mar 6, 2018

The report isn't a great place to do a lot of logic--checking for values then conditionally adding them. Instead this should be probably be done in a query.

However, even before you get there, you need to fix your table structure. Instead of 4 fields in 1 record for diagnosi, you should have 4 records in a table like so:

PatientID, DiagnosisNumber, Diagnosis
1123, 1, Anxiety
1123, 2, Broken Arm
1123, 3, Heart Attack

In a database you should deal with data vertically, and not horizontally. With the table illustrated above it becomes easy to build a query to find specific diagnosi.

Perhaps you can post sample data from your table and then based on that sample data what you want your report to look like.

Kelly Napier · Mar 7, 2018

Not being rude by not replying - just having to get a report out - so I'll have to come back to this in a couple of days if that's ok? Sorry... I really appreciate your help so far and will definitely come back to it.

arnelgp · Mar 7, 2018

use the Reports Load Event, Print Event and ReportFooter Format Event.

Code:

Option Compare Database
Option Explicit

Dim TotalAnxiety As Integer


Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
    TotalAnxiety = TotalAnxiety + _
                    (([Diagnosis] & "" = "anxiety") + ([Diagnosis2] & "" = "anxiety") + _
                    ([Diagnosis3] & "" = "anxiety") + ([Diagnosis4] & "" = "anxiety")) * -1

End Sub

Private Sub Report_Load()
    TotalAnxiety = 0
End Sub

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
    Me.yourUnboundTextbox = TotalAnxiety
End Sub

Reporting hell (1 Viewer)

Kelly Napier

Registered User.

Minty

AWF VIP

plog

Banishment Pending

Minty

AWF VIP

Kelly Napier

Registered User.

Kelly Napier

Registered User.

pbaldy

Wino Moderator

Mark_

Longboard on the internet

Mark_

Longboard on the internet

plog

Banishment Pending

Kelly Napier

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

Similar threads

Users who are viewing this thread