dlookup to replace a one field subform (1 Viewer)

[lightray]

Thought this would be a good solution, but having trouble with the syntax.

=DLookUp("[StartDate]","[tblJobHistory]","Me.[EmployeeID] =" & tblJobHistory!EmployeeID & " AND Min([tblJobHistory]![StartDate])")

I am getting the dreaded #Name? can I use the Min command like this?

Any help appreciated

 

[Moniker]

The #Name is coming in play because it's looking for a field in your table called "Me.EmployeeID" -- you have the field reference and the table reference reversed in the criteria clause.

Assuming EmployeeID is a numeric data format, this should work:

=DLookUp("[StartDate]","[tblJobHistory]","[EmployeeID] =" & Me.[EmployeeID] & " AND Min([StartDate])")

If EmployeeID is a text format, change that to this:

=DLookUp("[StartDate]","[tblJobHistory]","[EmployeeID] ='" & Me.[EmployeeID] & "' AND Min([StartDate])")

Also note that it's bad form (and goes against normalization) to name your controls the same thing as your fields. You've done that here with "EmployeeID" being called the same thing on the form as it is in the table "tblJobHistory".

~Moniker

 

[lightray]

EmployeeID is indeed a numeric field, I have implemented your suggestion and the #Name? doesn't go away.
I removed the Me. from [EmployeeID] and got #Error, if that is of any help
Any other suggestions?

 

[Dreamweaver]

I would put Min([StartDate]) Into a query with the employeeID then use the query in the Dlookup.

mick

 

[lightray]

Thanks, I was working on an idea like that since I had the query for the original subform, but currently still no success.
New code:
=DLookUp("[FirstJob]","[qryFirstJob]","Me.[EmployeeID] =" [qryFirstJob].[EmployeeID])

I've also tried switching the Me.[EmployeeID] =" [qryFirstJob].[EmployeeID] around, still coming up with #Name?
any further thoughts?

 

[unclejoe]

Hi,

Please refer to Moniker post.

=DLookUp("[StartDate]","[tblJobHistory]","[EmployeeID] =" & Me.[EmployeeID] & " AND Min([StartDate])")

Try the "Me.EmployeeID" without the square brackets. Access is saying that the control is missing or misnamed.(if you named the control as "EmployeeID")

First try it out without the "& " AND Min([StartDate])")", and if it works then, add it on the second try.

Thanks, I was working on an idea like that since I had the query for the original subform, but currently still no success.
New code:
=DLookUp("[FirstJob]","[qryFirstJob]","Me.[EmployeeID] =" [qryFirstJob].[EmployeeID])

I've also tried switching the Me.[EmployeeID] =" [qryFirstJob].[EmployeeID] around, still coming up with #Name?
any further thoughts?

 

[lightray]

Workable solution

Thanks to all who responded. Decided to go with a lookup of the query, which already had the Min() values in it, so the final solution was:

=DLookUp("[FirstJob]","[qryFirstJob]","[EmployeeID] =" [EmployeeID])

in the Control Source of an Unbound text box.

UncleJoe: I think removing the Me. helped this time, thanks