Populate textbox based on a query

[poporacer]

I have a report that is bound to a query (qryReport) in this report I have a few textboxes that I need to get the data from another query (qryTop2Dates) I tried to use the DLookup function but I couldn't get it to work. :banghead: qryTop2Dates has the following Fields:
EMP_ID 'used as an employee ID Number
docType 'used for the type of training received..will be a number 1-4
docDate 'used for the date that training was issued
I need to get the most recent training for each employee and put that date in the textbox of the report. I used this in the Control Source :

=DLookUp("MAX(docDate)","qryTop2Dates","docType=1 AND qryTop2Dates.EMP_ID= [EMP_ID]")

This is giving me the MAX date of all the employees, not the MAX date for the EMP_ID in the report. So everyone is listed as having been trained on the same date. I am close, but not quite there. What am I missing?

 

[Isskint]

Try this;

=DLookUp("MAX(docDate)","qryTop2Dates","docType=1 AND qryTop2Dates.EMP_ID= " & [EMP_ID])

You could also use;


=DMax("docDate","qryTop2Dates","docType=1 AND qryTop2Dates.EMP_ID= " & [EMP_ID])

 

[poporacer]

Isskint,
Thanks, I ended up figuring it out..I also had the issue that EMP_ID is actually a text field so I had to put in a few parenthesis. But then when it worked, I found another error. I use the MAX to fill in the value in one text box and the MIN for another textbox. The problem is that if there is only one date in the query, both textboxes get filled with the same date, when one should be blank. I think I can solve this with an Iif statement and COUNT(*) but I can't get it to work. Here is what I have tried.

=DLookUp(Iif (COUNT(*) qryTop2Dates.docDate WHERE qryTop2Dates.docType = 1 AND qryTop2Dates.EMP_ID= [EMP_ID] >1, "MAX(docDate)","qryTop2Dates","docType=1 AND qryTop2Dates.EMP_ID ='" & [EMP_ID] & " ' ","")

And a few variations. So what I need is that if there is only one query result for training type 1 to return "", if there are 2 then return the result of the MAX function in the query. Any suggestions?

 

[Isskint]

The Iif() wants to go AROUND the Dcount(). EG somehting like this.

Iif(Dcount("docDate","qryTop2Dates","((docType = 1) AND (EMP_ID='" & [EMP_ID] & "')) > 1, DLookUp("MAX(docDate)","qryTop2Dates","docType=1 AND qryTop2Dates.EMP_ID= " & [EMP_ID]),"")

 

[poporacer]

I tried it and got a syntax error Missing Operator: I did modify it to take into account the text field. I used:

=Iif(Dcount("docDate","qryTop2Dates","((docType = 1) AND (EMP_ID='" & [EMP_ID] & "')) > 1, 
DLookUp("MAX(docDate)","qryTop2Dates","docType=1 AND qryTop2Dates.EMP_ID ='" & [EMP_ID] & " ' "),"")

Either way I received the syntax error. Did I miss something?

 

[Isskint]

Wow, took some time to find it:banghead:

1. you are missing the closing speech marks after the first EMP_ID = [EMP_ID] condition test.
2. You have put spaces around the second closing speech of the second EMP_ID = [EMP_ID]. (would not cause the error, just would not return data)
3. You are missing a couple of close parenthsis ssss

=Iif(Dcount("docDate","qryTop2Dates","((docType = 1) AND (EMP_ID='" & [EMP_ID] & "'[B][SIZE=4][COLOR=Red]!1![/COLOR][/SIZE][/B])) > 1,  DLookUp("MAX(docDate)","qryTop2Dates","docType=1 AND qryTop2Dates.EMP_ID ='" & [EMP_ID] & "[B][SIZE=4][COLOR=Red][SIZE=4]![/SIZE]2![/COLOR][/SIZE][/B] ' [B][SIZE=4][COLOR=Red][SIZE=4]![/SIZE]2![/COLOR][/SIZE][/B]"[B][SIZE=4][COLOR=Red][SIZE=4]![/SIZE]3![/COLOR][/SIZE][/B]),"")

Use this version;

=IIf((DCount("docDate","qryTop2Dates","((docType = 1) AND (EMP_ID = '" & [EMP_ID] & "'"))>1,DLookUp("MAX(docDate)","qryTop2Dates","((docType = 1) AND (EMP_ID ='" & [EMP_ID] & "'))"),"")

 

[poporacer]

Well, I tried it and I got #Error in the control....:banghead: (my turn) I attached the DB for you to see what I am talking about. One little glitch can be such a pain... I appreciate all your help!

 

[Isskint]

lol, like i said before, took some time to find it:banghead:

It is all in the end of the DCount() function. Missing 2 closing parenthesis WITHIN the DCount() criteria.

=IIf((DCount("docDate","qryTop2Dates","((docType = 1) AND (EMP_ID = '" & [EMP_ID] & "'[SIZE=4][B][COLOR=Red]))[/COLOR][/B][/SIZE]"))>1,DLookUp("MAX(docDate)","qryTop2Dates","((docType = 1) AND (EMP_ID ='" & [EMP_ID] & "'))"),"")

Correct criteria

=IIf((DCount("docDate","qryTop2Dates","((docType = 1) AND (EMP_ID = '" & [EMP_ID] & "'))"))>1,DLookUp("MAX(docDate)","qryTop2Dates","((docType = 1) AND (EMP_ID ='" & [EMP_ID] & "'))"),"")

Dont know why i missed that last time. When i encounter a large/complex/nested AGGREGATE SQL, i break it down into its component parts to ensure each bit works. The DCount() is the only bit to fail on its own.

 

[poporacer]

Thanks a million!