DLOOKUP function

[tariq1]

Hi,



I have an Employees Form, with fields including:

1) EmployeeNumber
2) FirstName
3) Notes

I am learning the DLOOKUP function and am trying to retrieve the FirstName within the Notes field.

There are 10 records within the Form:
1) EmpoyeeNumber = 27495 FirstName = Justine
2) EmployeeNumber = 28404 FirstName = Raymond

And within the Controlsource of the Notes field I have used
=DLookUp("FirstName","Employees","EmployeeNumber = [EmployeeNumber] ")

For 1) Notes = Justine which is correct BUT
2) Notes = Justine which is incorrect as this should retrieve Raymond

Thank you for your assistance

[Bob Fitz]

Why are you duplicating the data in the notes field?

[tariq1]

Hi Bob,

Thabk you for your response
I am learning the DLOOKUP functin so am just testing this out with something simple, thats all...

Tariq

[Bob Fitz]

Try:
=DLookUp("FirstName","Employees","EmployeeNumber = " & [EmployeeNumber])

[Missinglinq]

And since this is a learning exercise, a couple of things:

First, Bob's syntax is correct if 'EmployeeNumber' is defined as a Number Datatype, which your initial post would seem to suggest.

But if 'EmployeeNumber' were to be defined as a Text Datatype, the correct syntax would be

=DLookUp("FirstName","Employees","[EmployeeNumber] ='" & [EmployeeNumber] & "'")

Secondly, that 'digits' and 'numbers' should not be confused. Data like Employee Numbers, Serial Numbers, Social Security Numbers, etc., when only containing digits, can be defined as Text or Number Datatypes. Which you elect to use is up to you. The rule of thumb that I use is that if the data will not be used in performing math, it's Text, otherwise, it's a Number. But that's an individual decision. The only I'd define a non-math, all-digits Field as a Number would be if it were to be used as a Primary Key.

Linq ;0)>

[uncletreetrunk]

I had a DLookUp related question yesterday. pbaldy provided this link. It has proper syntax for various data types.

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

Hope it helps