Counting with a query to give results on report

[trackmedic]

I have a query that pulls up information on employees when they receive warning notices. I would like the query to give me some type of warning (report, email, etc.) when an employee has three or more notices.

Can someone help me with this?

- Thanks in advance!

 

[boblarson]

A query doesn't give warnings as a query has no events to generate warnings. You need to bind the query to a FORM and use the form's EVENTS to be able to test for certain values and then display a message based on that value.

 

[trackmedic]

??

Could it be bound to a report? for instance the report I have, once populated, may have 1000 employees. but, i do not understand about binding to a form. When a person gets a warning notice, I click a yes/no box on a form, the query looks for "yes" results and then list the names.

i guess I can put a box on the report to look at the results? I am lost!!

- Thanks

 

[boblarson]

Yes, you can bind a query to a report and then you can use a text box with either Conditional Formatting, or something like this in the controlsource:

=IIf([YourFieldNameHere]>=3, "Warning Notice","")

That is assuming your YourFieldNameHere is the aggregate count of notices for the employee.

 

[trackmedic]

Thanks Bob - One more if you do not mind!

You are always a great help and I have learned much from you.

One thing I have problems with and I have still not figured it out.

I have a date box on a form. I would like to place an unbound box on a form with a formula to look at that date box and tell me if it is over 30 days behind.

I would like the box to be blank and if the date in the other date box is over 30 days, I would like it to say "DRILL BEHIND". can you help me with this?

 

[boblarson]

If you are comparing to today's date it would be simply:

=IIf(DateAdd("d",30,[YourDateFieldHere])<Date(),"DRILL BEHIND","")

I believe that should do it. So if, after adding 30 days to the date, the date returned is less than today it will return "DRILL BEHIND" and if not it will return nothing.

 

[trackmedic]

hi,

I am comparing between two different date fiels, not todays date. One is the date of inspection and the other is the date of drills. I used the following

=DateDiff("d",[Date],[Last Fire Drill])

This gives me a number as a negative. I would like the answer to be blank unless it is over 30 days old, then kick out the message "drill behind".

We almost got it!

 

[boblarson]

First of all, if your date field is named Date then change it immediately. Date is a reserved word in Access and you will only cause yourself much grief if you continue to use it.

Second, put you should be able to use the same logic I used before but just putting the other date in where I used Date(). Put the date that should have happened first in the first slot and the one afterwards in the second.

 

[trackmedic]

ok, one more time and I will quit bothering you!

This is what you sent

=IIf(DateAdd("d",30,[YourDateFieldHere])<Date(),"DRILL BEHIND","")

The date of the report is placed in by hand, it is in a field called "mainDate"

I want to compare the fire drill "last fire drill" field to the "mainDate" field to get the results

I tried what you said and I get some funky thing that says Name# or something to that affect.

- Andre'

 

[ajetrumpet]

Medic,

Questions that you can answer which may help here:

1) Is your form bound to a table?
2) Usually when people use the word "field" when talking about forms, they mean "a control that is bound to a table field". Are you In-Sync with this?
3) There is nothing wrong with the syntax given:

=IIf(DateAdd("d",30,[YourDateFieldHere])<Date(),"DRILL BEHIND","")

...so if, when you put that in the ControlSource line, you get:

some funky thing that says Name# or something to that affect.

...then the problem is not with the help that has been provided.

Do you have a file to post? Or a snapshot or something?

<Edit>

Medic,

Might look at this link as well, from Microsoft...

http://office.microsoft.com/en-us/access/HA011814471033.aspx?pid=CL100570041033

 

[boblarson]

So, make sure your text box name and field name are not the same. Also, you can try either:

=IIf(DateAdd("d",30,[last fire drill])<[mainDate],"DRILL BEHIND","")

or

=IIf(DateDiff("d",[last fire drill],[mainDate])>=30, "DRILL BEHIND","")