Help with DLookUp

[focosi]

I have 2 forms ("X" and "Y") linked by field ID.
I have a query called "Z" counting records in "Y" that have a common ID.


I would like to add a text field on form "X" showing the result in the field "W" of the query "Z" for the current ID.
I have put in control source of such text field the following :

=DLookUp([CountOfW], [Z], "ID=" & Me.ID)

I receive the error #Name? in the text field in form view.
What's wrong in my syntax ?

[apr pillai]

Change [Z] to "Z"

=DLookUp([CountOfW], "Z", "ID=" & Me.ID)

[rpeare]

=DLookUp("[CountOfW]", "Z", "ID=" & Me.ID)

might have to play with the " marks around the criteria section of the dlookup, I didn't test.

[focosi]

Actually I just discovered that in most non-US laptops the commas should be replaced by semicolons (sort of system settings) in order for the syntax to be accepted ! So the right one for me is :

=DLookUp("[CountOfW]";"Z";"[ID] = ...")

It works when I put a number in place of ..., but not when I place a call to current ID on form X. I have tried

=DLookUp("[CountOfW]";"Z";"[ID] = Forms!X![ID]")
and
=DLookUp("[CountOfW]";"Z";"[ID] = Me.[ID]")

but none of them works. Any suggestion ?

P.S. : ID is an autonumber field, so no single quotes should be needed in my opinion...

[ketbdnetbp]

You could try...

=DLookUp("[CountOfW]";"Z";"[ID]=" & Forms!X![ID])

instead of

=DLookUp("[CountOfW]";"Z";"[ID] = Forms!X![ID]")

Just a thought.

Jim

[focosi]

You could try...

=DLookUp("[CountOfW]";"Z";"[ID]=" & Forms!X![ID])

instead of

=DLookUp("[CountOfW]";"Z";"[ID] = Forms!X![ID]")

Just a thought.

Jim

still not working ... blank values... very frustrating....

[boblarson]

Make sure that there are no controls on this form which have the same name as any of the fields being used here. If there are, rename the controls. When using formulas none of the controls being referred to can have the same name as the fields they are bound to or it gives an error.

[ketbdnetbp]

Just as a test, try running this code somewhere on Form "X" and see what happens...

dim lngID as Long
dim lngID1 as Long
lngID = Me.[ID]
lngID1 = [Forms]![X]![ID]
MsgBox "First Value: " & lngID
MsgBox "Second Value: " & lngID1

[rpeare]

=DLookUp("[CountOfW]", "Z", "ID=" & Me.ID & ")

Forgot the closing & " mark

[focosi]

thanks for help I actually discovered that it was a matter of using ; instead of , on non-US PC.
Further, blank values were due to the fact that my query didn't include [ID] field from form "X". Now everything works. Thanks for help!

[boblarson]

=DLookUp("[CountOfW]", "Z", "ID=" & Me.ID & ")

Forgot the closing & " mark

Actually rpeare, there should NOT be a closing " at the end. If ID is not text then it would be:

=DLookUp("[CountOfW]", "Z", "ID=" & Me.ID)

or if needing the semi-colons as stated:

=DLookUp("[CountOfW]"; "Z"; "ID=" & Me.ID)

The extra quote at the end should generate an error.