Finding query result on form.

[davidjearly]

Hi,

Hope someone can help here.

I have a field on a form, 'Date of Last Review'. This form is based on a query storing the details of all the patient reviews. The two important columns in this query relating to this query are 'Client Code' and 'Review Date'.

Basically, there will be more than one entry for every client in the 'Client Code' column, corresponding to the last time a review was completed for them, the date of which is stored in the 'Review Date' column.

Now, on my form, is there a way to link this field, 'Date of Last Review' to the query so that it finds the latest entry in the 'Review Date' column for the particular client who's record is currently active on my form?

I hope I've explained that appropriately.

Would appreciate any help.

Thanks!

 

[pbaldy]

If you simply want to display the date, you can use a DMax:

http://www.mvps.org/access/general/gen0018.htm

 

[davidjearly]

Thanks for your prompt reply.

Yes, I simply want to display the most recent date, but only for the client who is active on my form. The query contains data for many different clients.

Could you give me an example of how you would do this on the form. I presume it involves setting the field's control, but not sure exactly how to tell it what I would like it to do.

Thanks again.

 

[pbaldy]

The link I posted shows the syntax for referring to the form for the criteria.

 

[davidjearly]

Tried using this, but to no avail:

=DMax("[Review Date]","qryReviews","Criteria = #" & [Forms]![Patient Details]![Client Code] & "#")

 

[davidjearly]

The link I posted shows the syntax for referring to the form for the criteria.

I tried to follow the link in that post, but I get '#ERROR' displayed in the field on my form. My syntax is in the post above.

 

[pbaldy]

Is your client code field really named "Criteria"? And does it have a date data type, as you are treating it? The syntax is based on the field in the criteria, not the field being looked up.

 

[davidjearly]

Thanks for your perseverance, but I'm still not having much luck:

=DMax("[Review Date]", "qryReviews", "[Client Code] = '" & forms![Patient Details]![Client Code] & "")

Query name = qryReviews
Column name in query = Review Date & Client Code
Field name on form = Client Code
Form name = Patient Details

Any further ideas?

Thanks.

 

[pbaldy]

I assume client code is a text value, so try

=DMax("[Review Date]", "qryReviews", "[Client Code] = '" & forms![Patient Details]![Client Code] & "'")

 

[davidjearly]

pbaldy - many thanks that works really well!

On a side note, there was some additional functionality I was hoping to achieve with this. I have another field on my form just below the 'Date of Last Review' called 'Time since last review'.

Is there I could make this cell show the time since this client was last reviewed (preferably in days, but any unit would be fine)?

Many thanks for your help with this!

David

 

[davidjearly]

Got it!

For reference, I used

=DateDiff("d",Now(),[Date of Last Review])

 

[davidjearly]

Ok, I spoke too soon. That isn't working the way I would like it to. It is displaying a negative in front of the number, and I would like it to just show the number of days that have elapsed since the last review date without symbols.

 

[pbaldy]

Try reversing the last 2 arguments.

 

[davidjearly]

Yeah, I realised that after an embarrasingly long time.

The only thing is, if I change the last review date in my forms, it takes closing the form window and re-opening it for this to update.

Is there any way to improve that?

Thanks.

 

[pbaldy]

You should be able to requery the form or control, as appropriate.

 

[davidjearly]

I've placed the Me.Requery command in the 'On Enter' and 'On Got Focus' of the text box, but I still have to manually click the box to get it to update.

My form has tabs and I would like it to refresh whenever the tab that my 'Date of Last Review' text box is swtiched to.

Thanks.

 

[pbaldy]

I would probably have put it in the after update event of the control where you would be changing the date. If that doesn't work for you, I'll have to poke around for the event to use for the tab.

 

[davidjearly]

Yeah thats what I thought too. Unfortunately it still doesn't work.

Would appreciate any other ideas.