?
Solved

Fill date field with last date

Posted on 2014-12-09
7
Medium Priority
?
178 Views
Last Modified: 2014-12-11
I have a form which has a date field named "Date of Previous Visit".  For the control source I'm trying to use:

=[qryLastPatientVisit]![Start Date]

But it doesn't like that.  Note that the query is a query that returns just the last record, not using DLast because I've learned that won't work.  Instead it is a select query returning just one record in descending order.

What is wrong with my control source?

I also tried:

Me.txtDateOfPreviousVisit = Queries!qryLastPatientVisit.[Start Date]

in the oncurrent event of the form but it didn't like that either.
0
Comment
Question by:SteveL13
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40490012
Something like this should work if you have the PatientID on the form (and the query really does return the last date for each patient...)

Dlookup("[Start Date]","qryLastPatientVisit","PatientID=" & me.PatientID)
0
 
LVL 9

Expert Comment

by:macarrillo1
ID: 40490017
If I understand what you are doing;

The problem is that just because you reference it does not mean that it is running.  Your form is open and has an active connection to the table or query, but has not opened the query qryLastPatientVisit. So your form will either have to reference it as part of your data source or perhaps you will need to run a macro to run it and retrieve the value.

Essentually you are trying to call a source that is completely unrelated to the form you have open.

You could resolve this by using the on current event to run the query and store the value into a variable and then use the variable to populate the field.
0
 

Author Comment

by:SteveL13
ID: 40490058
Trying this in the oncurrent event of the form but Getting #Name in the field on the form

Me.txtDateOfPreviousVisit = DLookup("[Start Date]", "qryLastPatientVisit", "PatientID =" & Me.cboPatientID)

Maybe because PatientID is a text field?
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40490476
Perhaps, ...
Me.txtDateOfPreviousVisit = DLookup("[Start Date]", "qryLastPatientVisit", "PatientID =" & "'" & Me.cboPatientID & "'")

But basic troubleshooting will tell you to try a hard coded value to test this  (Before ever adding it to a form current event;)

something like this :
msgbox DLookup("[Start Date]", "qryLastPatientVisit", "PatientID =" & "'" & 57 & "'")
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40490493
Or this:
msgbox DLookup("[Start Date]", "qryLastPatientVisit", "PatientID ='57'")
0
 
LVL 51

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 40490676
You could use DMax and skip the query:

=DMax("[Start Date]","tblPatientVisit","PatientID = '" & [cboPatientID] & "'")

or:

=DMax("[Start Date]","tblPatientVisit","PatientID = '" & Forms!frmYourFormName![cboPatientID] & "'")

or you could modify the rowsource for cboPatientID to include  StartDate (create a join to qryLastPatientVisit) and have a column for this. Then, if that column is the second, it would be:

=[cboPatientID].Column(1)

/gustav
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40491914
Please investigate Gustav's post above...
My post just used what you had already

Dmax is typically better at getting the "Last" value
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question