Parameters query Null Error Trouble .

[MISCurls]

I'm using a form to run a 2 parameters query on pharmacy data
The query is setup like this:
BrandName Generic Name Strength Quantity

[Forms]![F Drug Used within Dates]![BrandName] on the brand name(combobox) field
and

Between [Forms]![F Drug Used within Dates]![StartDate] And [Forms]![F Drug Used within Dates]![EndDate] on the date field

This is passed to a report which sums the quantity of drugs used between the two dates.

This works great until a drug from druglist does not contain data between the dates I get an error (#error) in the report for SumofQuantity field reflecting this.

I tried using iif(isnull([brand name]),"unknown",format([brand name],"@;""ZLS""")) Which returns everything that is not null which of course is incorrect. I'm looking to return a 0 in the Sum of Quantity field on the report if the date range on the brand name does not produce data. What can I do to replace that report error with a 0? I would appreciate any advice Thank you in advance.

 

[NewYorkFashionToGo]

I had a similar problem with something I was trying to achieve, I am still relatively new at access , I am beginning to grasp it however I am sure there are better ways to do it. Did you try from the design view of your query (right click) on the line that connects the two tables and then select join properties, There are a few options there that say show all records from this table. fool around with that and see what happens.

Also what I been learning is that if you have two tables with similar column names it confuses access and may give you an error, , Right click and build it straight from the tables using the expression builder (from the root table)not from query table.....

 

[Darrylles]

Hi Mis,

Try trapping this in your 'SumofQuantity' field on your report.

i.e. use
[tt]
iif(isnull(sumofquantity),0,sumofquantity)
[/tt]
in the controlsource property of the field.

ATB

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[MISCurls]

Darrylle
First, thanks for your reply! This worked great on the sumofquantity field -it did return a 0 for the null value however, the results for the sumofquantity that isnotnull is now summing incorrectly. A result for drug with 27 total is now showing as 2 and the sumofsumofquantity for the total drug of all forms is returning as -1. Any additional advice would be very much appreciated

 

[jsteph]

mis
Are you summing at the query level or report?

That is, is your query a 'group by' query--which means you're performing the sum in the query, then displaying on a report, or is your query a standard Select query and the group by/summing is done on the report? I say this because sumofquantity would be the default fieldname given if you're creating a group-by query.

Do you want all data by brandname--or do you want all data that has a valid link to druglis--then one big summ for all data not found on druglist?

And how is the drug list joined? I noticed that in your example there is a field BrandName, but in your IIF() you have Brand Name (with space). Does the raw data contian a brandname, and then this links to a druglist table keyed on brand name there?

Anyway, you should not get #error if you try to sum a Null value--this is standard stuff and the Sum function expects some nulls and won't choke on them.

You should also make the query a standar Select, if it is not already, and let the report do it's grouping/summing.
--Jim

 

[MISCurls]

Jim,
I would like the query to find all instances of the drug used between two specified dates. If the query produces no instance of the drug specified then it should return a 0 in the sumofquantity
I am summing the quantity in query and then summing the sumofquantity in the report because some of the drugs have different strengths so I would to have a total for all of the "BrandName" all together but I need subtotals for each strength in there as well.

This is the query sql statement:

SELECT DISTINCTROW [T Contents].[Brand Name], [T Contents].[Generic Drug Name], [T Contents].Strength, First([T Removed].Date) AS FirstOfDate, Sum([T Removed].Quantity) AS [Sum Of Quantity]
FROM [T Contents] INNER JOIN [T Removed] ON [T Contents].ID = [T Removed].[Drug Used]
GROUP BY [T Contents].[Brand Name], [T Contents].[Generic Drug Name], [T Contents].Strength
HAVING ((([T Contents].[Brand Name])=[Enter Brand Name]) AND ((First([T Removed].Date)) Between [enter start Date] And [enter End Date]));

I created a form for the user to input the parameters

Private Sub OK_Click()
Me.Visible = False
DoCmd.OpenQuery "Q List Drug within Dates", acViewNormal, acEdit
DoCmd.Close acForm, "F Drug within Dates"
DoCmd.OpenReport "R List Drug within Dates", acViewPreview
End Sub

 

[jsteph]

I would really suggest using a standard select query, then in the Report, group by Brand Name, then by Strength. This will total it correctly.


I'm not sure how your table's data is set up. If you could provide an example that would help. But it appears that you may want to create a sub-query (or table) listing just the drug names.

Then outer-join that to your existing query, so that it can create a 'dummy' row for those that are not found in the table with the data. For the dates--since you're doing an Outer Join, you'd need to add 'Or Is Null', since you want the drug to list but if you put criteria on the other table, then you sort of turn it into an Inner Join, defeating the purpose.
--Jim

Since the date would be in the table with data,

 

[MISCurls]

I took the suggestion of taking the math out of the query and running a report with a combination of the math applied in an nz function on the both the summary of the sum of quantity and grand total and an iif argument.
=iif(nz([Quantity])="","0",Sum([Quantity])) -works like a dream.
Thank you everyone for you advice.