Report based on Cross Tab Query 1

[LarryDeLaruelle]

I have a report based on a cross tab query that tracks the administration of several medications. Periodically, one or more of the medications will not have been administered during the report period. When that happens no value is returned in the cross tab for that med.

The report is set up with bound objects to the cross tab and when the cross tab does not contain a value for the bound object, I get this message:

"The Microsoft Jet database engine does not recognize '{the med name)' as a valid field name or expression."

Is there a way to trap for this message, temporarily remove the offending bound object/s and complete the report? I'm doing this manually now but an automated process would enable me to put this out for the nurses to run the report.

Any suggestions on how I can do this?

Thanks.

 

[Guest_imported]

can you incorporate the folowing in your query

Nz("offending field","Value_to_display_if_null&quot

 

[LarryDeLaruelle]

Thanks for the response John.

Unfortunately, that won't work since the cross tab is based on a select query where the med types criteria are defined. If one of the meds is not in the result the Nz function won't have any effect on the select results or the cross tab.

I really think what I need is a trap in the reports On Open or On Format event to test that the cross tab results contain each of the bound objects defined in the report's detail band. If not, I would then want to temporarily remove (enable = false?; visible = false?) that object.

What I haven't been able to figure out is how to structure the code to perform the test or what action I need to take to disable the object.

 

[Guest_imported]

Larry, you have me hooked. What does your crosstab query display when you open it and view the results on one of the troublesome records? Another question is regarding the bound objects on the report, what type of object is it and does it have a record source property?
If I can't help, I certainly will keep track of this thread and learn somthing!

John

 

[LarryDeLaruelle]

John:

Thanks for the response.

The select query looks for current clients in our residential treatment program who have been prescribed one or more of six specific medication types. The crosstab query then summarizes that information into row/column format for a report distributed to the nursing staff: Row = Resident Name; Columns = Medication Type (Date last administered as the detail for the Med Columns).

When I first set up the report each medications were being administered to at least one resident so the report was based on the values returned from the cross tab. Now I find that one of the medications is infrequently used and, thus, no date values are returned from the queries for that med. As a result, when the report opens it finds no data corresponding to the bound object for that med and returns the error message above.

I'm pretty sure that what I need to do is to write some code either in the On Open or On Format event that looks for the presence of each of the med types relating to the bound objects and then enables/disables the bound objects as necessary.

What I'm not sure of is how to refer to the fields/values from the cross tab and how to handle the absence of any one of them.

I hope this makes the problem a little clearer and that you or one of the other gurus on this site can give me a push in the right direction.

Thanks again.

 

[jimmythegeek]

Larry,

Please explain the structure of the table(s) that is/are used in the inital Select query.
How do you know the last date for each client's medication use for each medication?
You may be able to do this without a crosstab, but I need to know how you have the table(s) set up to retrieve the dates that are currently populating your crosstab query. [sig]<p>Jim Lunde<br><a href=mailto:compugeeks@hotmail.com>compugeeks@hotmail.com</a><br><a href=

http://www.cgeek.com>CompuGEEKS</a><br>Custom

Application Development[/sig]

 

[LarryDeLaruelle]

The report is based on a cross tab query that is based on a select query.

The select query uses five tables from our tracking data base (created by an outside programmer): Client; ClientExam; Exam; ClientLocation; Location. The last two are used only for sorting and grouping the report.

Client is the main table and contains, as you would expect, information on each resident current and past.

ClientExam stores infomation on a variety of medical issues: Height; weight; IQ test results (not medical but its here); various innoculations; and, what I am interested in, various medications. The table is set up as ExamID (Autonumber), ClientID (foreign key), ExamID (foreign key), Results (text for Height, weight, etc), ExamDate (of exam or innoculation or administration of medication) and Notes. Every time a resident's height or weight is taken or a shot is given the data are entered into the ClientExam table with ExamID coming from a lookup based on the Exam table.

Exam is, essentially, a Lookup table that provides the description of the various Exams.

The select query pulls Location from the ClientLocation/Location tables, Name from the Client table, ExamID and ExamDate from the ClientExam table and Exam Name from the Exam table. Results are ordered by (Location), Client Name, ExamID, ExamDate. The query is limited to current residents and only those ExamIDs of the medications necessary for the report.

The crosstab query is based on the select query and is organized as follows:

Field Row Total Row Crosstab Row
Location Group By Row Heading
Name Group By Row Heading
Exam Name Group By Column Heading
Exam Date Last Value

When the report was created at least one resident was receiving at least one of each of the medications in question. At this point no current resident is receiving Lamictal (the offending med) but this could change with the next admission. The report is set up with each of the seven medications in the detail band (based on the original results of the crosstab query).

Now, since the crosstab does not return a column for Lamictal, I receive the message &quot;. . . does not recognize &quot;Lamictal&quot; as a valid field name or expression.&quot;

Probably that was a lot more detail than necessary but you did asked for it. (I hope this is what you were asking for.)

As I said above, what I think I need is the syntax for referencing the results of the query in the Report Open or Format events to determine if Lamictal is in the results. And then some idea of what to do with that information to prevent the error from aborting the report.

I have been trying a number of things but haven't met with anything that works.

I really appreciate your help Jim, it's been invaluable. [sig]<p>Larry De Laruelle<br><a href=mailto:larry1de@yahoo.com>larry1de@yahoo.com</a><br><a href= > </a><br> [/sig]

 

[jimmythegeek]

Could you possibly create a blank database, and import the tables, queries, and report(s) that make up this issue, and send it to me. I am a bit confused. I don't believe you will be able to do it the way you imagined, but I guess I need to see it to understand valid my thoughts. [sig]<p>Jim Lunde<br><a href=mailto:compugeeks@hotmail.com>compugeeks@hotmail.com</a><br><a href=

http://www.cgeek.com>CompuGEEKS</a><br>Custom

Application Development[/sig]

 

[Quehay]

Forgive my jumping in where much better minds are at work on the problem. What values are reported in the medication column? If it's a number, e.g. mls administered or a yes/no for &quot;Received Thorazine, etc.&quot; why not have a default value for the field that says &quot;Not administered,&quot; &quot;O ml,&quot; or &quot;No,&quot; etc., so there'll be a value to report in all cases. Maybe this could cut the Gordian knot. . . [sig][/sig]

 

[LarryDeLaruelle]

Quehay:

I'm pulling the last date on which the medication was administered with Resident Name as the row, Med Type as column and Date as value/last (where date has been sorted in ascending order).

Is there a way using the reports's On Open or On Format event to test the values being passed by the query?

What I would like to do is check all of the medications being tracked and if one or more is not present in the query result, to disable or remove it's control temporarily from the report.

Any suggestions? [sig]<p>Larry De Laruelle<br><a href=mailto:larry1de@yahoo.com>larry1de@yahoo.com</a><br><a href= > </a><br> [/sig]

 

[pdupreez]

Hi

I am getting a similar problem, and it is as a result of some fieldnames dissapearing from the results from the crosstab query when no data is present for that specific type of medicine. I have to still however find out how to indirectly (dynamically?) add the fieldnames when I set up the field columns in the report's detail section.

Any help will be appreciated.

Pieter

 

[Nancy2]

Have not thoroughly read all the preceding posts but, for the last respondee - pdepreez :

Have you tried using the column heading query property in the crosstab query feeding the report -- if you know all the potential fields just identify them in as medicine1, medicine2 - then they will all be available in the report with just null values showing for those not needed.

 

[67peshawar294]

Larry,
I am a beginner at the VB stuff but I have been using Access since it first came out in 92. I Had a crosstab that gave me the same error. I added,
IIf(IsNull([fieldname]),0,[fieldname])
to the textbox and have had no further trouble.
Jim

 

[67peshawar294]

Larry,
Sorry, I forgot to add that mine was the sum of three different fields so the entire statement is
=IIf(IsNull([field1]),0,[field1])+IIf(IsNull([field2]),0,[field2])+IIf(IsNull([field3]),0,[field3])
jim