DLookup exhausted all attempts (1 Viewer)

[lightray]

Hi Everyone!, appreciate if I could get some expert help.
A little background: I have an Employee form/table that links to a JobHistory subform/table. The subform shows relevant details for the Emps current Position/Job by way of the EndDate of the JobHistory not being complete (Null). This is acheived by way of a CurrentJob query. One of the details of the current job is the Division and accompanying DivisionID. I want to store the DivisionID into a field on the Employee Table.
I have spent most of today researching Post's, in lots of forums, for the use of DLookup. while It took we hours to get my head around, and I had success on simple models.
...And here's the rub, I'd like to know if I can do the following and test for the Null EndDate to get the DivisionID?

my code goes like this and returns an invalid result:

=DLookUp("[DivisionID]","tblJobHistory","[EmployeeID] = ' " & [EmployeeID] & " And " & [EndDate] & "Is Null" & " ' ")

I'm sure the end bit's not right, but I'm losing focus. It's loaded in the control source property of an unbound text box (at the moment)

 

[gemma-the-husky]

You have formatted your where statement of the dlookup incorrectly, I think

In the first part of the clause is employeeid numeric or text

if numeric you want just

"[EmployeeID] = " & [EmployeeID]

if its text you want

"[EmployeeID] = " & chr(34) & [EmployeeID] & chr(34)

and for the rest you just want

" And [EndDate] Is Null"), as you are not using any variables.

if you have problems dim a string
eg dim conditionstrg as string
set conditionstrg = your conditionstatement
and msgbox it first to see if it looks right.
then just use conditionstrg as the condition in the dlookup

 

[gemma-the-husky]

by the way, dlookup will only return 1 value even if there are several matches that satisfy the criteria

 

[gemma-the-husky]

A further observation - the explanation of the dlookup where clause is perhaps a bit misleading, you don't have to put the whole where clause in quotes - in your case its more like, you need to assemble a correctly syntaxed clause, like

[EmployeeID] = 4 and [datecheeck] is null

by mixing strings and variables, but variables have to be wrapped in a certain way ie numbers can be used as is, text has to be surrounded by quotes - use chr(34) - (the quote mark) for simplicity, as you can't just use the quote mark directly and dates must be surrounded by # symbol

but you get this by

"[employeeid] = " & variable_returning_4 & " and [datecheck] is null'

the dlookup call turns the whole statement into a single string itself

I hope thats a bit clearer

 

[lightray]

Should only be one match per employeeID

Many thanks Gemma-the-Husky, You are right about the first part! EmployeeID is Numeric! I was too focused on the EndDate not to see it, and now I realise most of the examples I looked at dealt with a text lookup first.
I will post back the results.

Q: There should only be one record per EmployeeID that has a Null EndDate, even if there are multiple JobHistory Records with Null EndDates each one will be for a unique EmployeeID, so this will still work Yes?

Thanks again

 

[gemma-the-husky]

yes, it should work - the observation was that there may be multiple recordfs, and you will on;y retrieve one (probably the first, but maybe a random one)

 

[lightray]

Yep! that hit the spot ...
Thanks gemma-the-husky this lesson has given me a much better understanding of the dlookup function. Final coding solution was:
=DLookUp("[DivisionID]","tblJobHistory","[EmployeeID] =" & [EmployeeID] & " And [EndDate] Is Null")