DLookup doesn't work in 2007

[AnnPhil]

I have several DLookups in my 2003 version that work fine, but when i upgrade the db to 2007 they do not work. Is the syntex different in 2007?

Here is just an example of one of them used in a form.

=DLookUp("[Name]","[FMLA_Yearly List]","[FMLA_ID]=[Forms]![FMLA Log]![FMLA_ID]")

I search other threads and i get a couple different syntex, but none of them work.

Thanks in advance for any help

 

[pbaldy]

The syntax should be the same, but I'd try the syntax here:

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

 

[AnnPhil]

I tried looking at that link before and still got error message says invalid syntax. This time i cut and pasted the names into the example and still no go

DLookup("Name ", " FMLA_Yearly List ", " FMLA_ID = " & forms! FMLA Log! FMLA_ID)

 

[pbaldy]

If that's a direct cut/paste, there are spaces in there that are either in error or need to be bracketed. What are the exact names, and what is the data type of FMLA_ID?

 

[AnnPhil]

"Name" is a text field, "FMLA_Yearly List" is a query, "FMLA_ID" is a number field

I tried taking out the spaces where i thought they should be taken out and still no go.

=DLookup("Name", "FMLA_Yearly List", "FMLA_ID = " & forms! FMLA Log! FMLA_ID)

 

[SOS]

Name is an Access Reserved Word and needs to be in square brackets (and in reality it should not be used as a field or object name at all).

 

[pbaldy]

And you still have spaces in there. Try

=DLookup("Name", "FMLA_Yearly List", "FMLA_ID = " & forms!FMLA Log!FMLA_ID)

 

[SOS]

And you still have spaces in there. Try

=DLookup("Name", "FMLA_Yearly List", "FMLA_ID = " & forms!FMLA Log!FMLA_ID)

And better still this:

=DLookup("[Name]", "FMLA_Yearly List", "FMLA_ID = " & forms!FMLA Log!FMLA_ID)

 

[AnnPhil]

Thanks SO MUCH everyone, i finally got it to work! I took both your advise and had to add more brackets in order for it to work

Here is what worked

=DLookUp("[Name]","[FMLA_Yearly List]","[FMLA_ID] = " & [Forms]![FMLA Log]![FMLA_ID])

Which is what i had in 2003 but with the criteria area written differently, now i hope it works on the other two i have yet to change

 

[pbaldy]

And better still this:

=DLookup("[Name]", "FMLA_Yearly List", "FMLA_ID = " & forms!FMLA Log!FMLA_ID)

I agree that Name is a reserved word that shouldn't be used, but I don't think it caused a problem here. A brief test resulted in no error in a test db:

?dlookup("name","table1","searchtext = 'blah'")
paul

 

[SOS]

And we've had posters here before who have used it and when the brackets were added it worked just fine. But the main lesson is to NOT use NAME as an object or field name.

 

[AnnPhil]

Lesson learned. They say the lessons learned the hard way are the ones you remember most often. Thanks again !!!!

 

[The_Doc_Man]

Actually, I prefer the Ambrose Bierce definition of experience.

Experience is that which allows us to recognize our mistakes when we make them again.