Strange dlookup behaviour

  • Thread starter Thread starter Ceebaby via AccessMonster.com
  • Start date Start date
C

Ceebaby via AccessMonster.com

Hi Folks

Can anyone tell me why the below dlookup works in a form created in Access
2000 but will not work with the same form imported into access 2003 but with
the default set to 2000

Me![Post Code] = DLookup("[PostCode]", "[QryWardLook]", "[LUAddress] = '"
& Me!LUAddress & "'")

The addresses I select have a post code but it will not return any value. All
reference libraries are the same on both with the exception of microsoft
office 11.

I cannot see where I am going wrong.
Thanks for any help in advance

--
Ceebaby

Trying to be great at Access

Message posted via AccessMonster.com
 
Don't see anything obvious (assuming that the form has a control named Post
Code and a control named LUAddress, but sometimes the inclusion of [ ]
characters in the first argument causes a few hiccups. Try this:

Me![Post Code] = DLookup("PostCode", "[QryWardLook]", "[LUAddress] = '"
& Me!LUAddress & "'")
 
Ken

What you posted works. You were right I had used a wrong field in my query,
as I have 2 similar sounding fieldnames. I just could not see the woods for
the trees.

Many thanks for your response though.
Have a good day.

Ceebaby
London
Don't see anything obvious (assuming that the form has a control named Post
Code and a control named LUAddress, but sometimes the inclusion of [ ]
characters in the first argument causes a few hiccups. Try this:

Me![Post Code] = DLookup("PostCode", "[QryWardLook]", "[LUAddress] = '"
& Me!LUAddress & "'")
Hi Folks
Click to expand...
[quoted text clipped - 14 lines]
I cannot see where I am going wrong.
Thanks for any help in advance
Click to expand...
Click to expand...

--
Ceebaby

Trying to be great at Access

Message posted via AccessMonster.com
 
Just a note, you can simplify the delimiters syntax by using
FORMS!formName!ControlName syntax inside the third argument of Dxxx
functions:



Me![Post Code] = DLookup("PostCode", "QryWardLook", "LUAddress =
FORMS!FormNameHere!LUAddress")


and it should work, without the use ' or " or # delimiters. That works for
Dxxx functions.



Vanderghast, Access MVP



cesima via AccessMonster.com said:
Ken

What you posted works. You were right I had used a wrong field in my
query,
as I have 2 similar sounding fieldnames. I just could not see the woods
for
the trees.

Many thanks for your response though.
Have a good day.

Ceebaby
London
Don't see anything obvious (assuming that the form has a control named
Post
Code and a control named LUAddress, but sometimes the inclusion of [ ]
characters in the first argument causes a few hiccups. Try this:

Me![Post Code] = DLookup("PostCode", "[QryWardLook]", "[LUAddress] =
'"
& Me!LUAddress & "'")
Hi Folks
Click to expand...
[quoted text clipped - 14 lines]
I cannot see where I am going wrong.
Thanks for any help in advance
Click to expand...
Click to expand...

--
Ceebaby

Trying to be great at Access

Message posted via AccessMonster.com
Click to expand...
 
Michel Walsh said:
Just a note, you can simplify the delimiters syntax by using
FORMS!formName!ControlName syntax inside the third argument of Dxxx
functions:



Me![Post Code] = DLookup("PostCode", "QryWardLook", "LUAddress =
FORMS!FormNameHere!LUAddress")


and it should work, without the use ' or " or # delimiters. That works
for Dxxx functions.
Click to expand...


This will work from a control's ControlSource, or from VBA code, or from a
Condition in a macro; but I don't believe it will work from a calculated
field in a query (especially not if you try to open a recordset to that
query in VBA code).
 
Indeed, it won't work if you (have to) use CurrentDb.

It will work with DoCmd, that includes a row source of list of a combo box;
a query open with DoCmd, opening a form based on the saved query, etc, but
NOT to open a recordset (based on CurrentDb.OpenRecordset(... ) ) or for
anything done through CurrentDb.


Vanderghast, Access MVP



Ken Snell (MVP) said:
Michel Walsh said:
Just a note, you can simplify the delimiters syntax by using
FORMS!formName!ControlName syntax inside the third argument of Dxxx
functions:



Me![Post Code] = DLookup("PostCode", "QryWardLook", "LUAddress =
FORMS!FormNameHere!LUAddress")


and it should work, without the use ' or " or # delimiters. That works
for Dxxx functions.
Click to expand...


This will work from a control's ControlSource, or from VBA code, or from a
Condition in a macro; but I don't believe it will work from a calculated
field in a query (especially not if you try to open a recordset to that
query in VBA code).
Click to expand...
 
Back
Top