Replies

I am using a query (qryLine1) with the date selected based on the date of incident, Floor and an expression for floor which counts the number of entries by floor between dates. Three fields to select.

I am using one stored query, I don’t want to write a different query for each box when all I need to do is to change the filter for the different floor. I don’t know how to make an SQL string. I can write the query and view the SQL but how do I move that to the report? There will be 88 boxes on the report when done and I don’t want to make a query for each one. I just believe access has a way to change the filter option alone for the text boxes in the report but I don’t have any idea on how to do it. Maybe I am wrong.

SELECT Count(DateSerial(Year(Date()),Month(Date()-1),0)) AS Numbers
FROM tblViolation
WHERE (((tblViolation.[Date of Incident]) Between DateSerial(Year(Date()),Month(Date()-1),0) And DateSerial(Year(Date()),Month(Date())-1,0)))
GROUP BY tblViolation.Floor
HAVING (((tblViolation.Floor)=”13″))
ORDER BY tblViolation.Floor DESC;

I can copy and paste this into the data control source but it only lists the first line and gives me an error: Extra ) in query expression…

I have counted, carefully, the brackets and there are the same number of opening and closing, plus I copied the expression straight from the SQL view and pasted it in.

I am not a pro at this but I think I can answer this for you.

To subtract two days you need to enclose the dates in a bracket, [Return Date]-[Ship Date].

The second is done by using sort, I presume you are using a query, in the Sort row set it to Ascending/Descending and set a filter for the month only.

Yes, that did it.

Thank You vVery Much.

I do not know what the functions do in Excel and rely on trial and error.

That is almost it. Is there a function that reverses this and counts NonBlanks?

I’m sorry, I was not clear, all the cells will either be empty or will have a name in them. The text is the name.

Yes, I need to list the staff that are sick, on vacation, etc. If there is a name in the column they are out for the reason and I need a count of these. But if there is no name listed then I don’t want to count that box.

Thanks, That did it.

JAM

Thank You so much. I couldn’t see the forest for the trees with this. It worked of course.

Jail Administrator Medical

I am not sure if this is what you mean, but if you haven’t done so you might try going to Tools – Options – Edit/Find and uncheck in the Confirm Box the “Record Changes” Option.

Jail Administrator Medical

I see, It seems to work now, but what is the Case statement.

When the data is accepted from the form and the user clicks on the close button I want it to run. But with what you sent me I think I am able to get it done. Thank You.

JAM

This is very helpful, and I even understand what the code is doing, but how would I go about clicking more than one box and having the reports print out on close of the record instead of ever time I click, or unclick, the field?

Thank You very much. This does exactly what I need for the Printing of the Yes and No.

JAM

Go to the Table in Design mode. Change the Data Type to Text.

Make sure the Field Size, in the big box below, is at least 10 to allow for the 5 zip, the “-” dash and the Plus 4.

JAM

As I see this it would obliviate the need for the queries I now use. I think.

What I have now are the following tables, I have simplified the previous for clairties sake.

tblInmates (it is a jail) which have Autonumber, Name, Location, Date of Birth, Date of Arrest, Allergies, Doctor, Arresting agency and Flag field.

tblMed which have ID (link to inmate, Med Name, Dose, Frequency of administration, Start Date, length of order, Stop Date, Discontinue and refill flags.

Do I need to make a tblCondition for the eight reportable conditions and give them an ID? Looks easy enough as there are only the eight.

How does the tblConditionMedicaine come in? Do I list the individual medications with the conditions they are used to treat? Would this just be two numbers in a table (ConditionID and MedicaineID) side by side? Would there be duplicates in the medicationID field for the multiple uses for the medications?

tblMedicaine I already have (called tblFormulary). How do I link the tables to generate the report?

Could you send a small example if possible? I am a visual person and the descriptions are sometimes difficult to follow.

Thank You for your time and effort, I know I am fairly new at this but there is no one here that even knows how to get started with Access and I am it by default. The jail has over 2400 inmates and about half of them are on one or more medications so it is important to us here to get the information as automated as possible.

JAM

What I do now with the query is to look-up everyone who takes certain medications, if they take the psych medications, I print a report quarterly listing their personal information and the medications they are taking. The same for the other reportable classes of medications. (Asthma, pregnancy, high blood pressure, psych, diabetes, seizure, stomach and medical are the reportable conditions.)

My present query looks for the medications and if found it flags a Yes/No field on the patients main information table. I have a report that gathers all the information and prints the reports. Then sets the flag back to No. This is all the Yes/No field does is to act as a flag for printing reports.

What I am trying to do is to list the medications that are now in the condition field of the query in a table instead. This way I could update the table as medications for the different conditions are added or deleted without going into the query and editing the condition field. If a new diabetic medication is approved and added to our formulary, which happens frequently, I could just add a medication to the bottom of the Cdiabetic table and it would then be included in those medications we must report on and the query would be updated easily. Now I must edit the condition field of the query and I have run into very long conditions that Access is saying are too complex, (too long I think as some conditions have very many medicaions used to treat them.)

Is there a simpler way to do this? I am trying to make it as simple as possible and thought setting a list in a table would be the easiest way to list all the medications.

Some medications are used in the treatment of many different conditions, an example would be Depakote which is used to treat seizures, depression, Bi-Polar (Manic Depressive) and anger. It would then fall into two different reportable categories.

I hope this is clear and if I am on the wrong track please point me in the right direction.

Jail Administrator Medical
JAM