Dlookup #error


T

Thrava

I can't figure this out and its driving me nuts.

in the same select query that I'm trying to used the
DLookup Function, I also have a caculated field called
"Diff_M_ID".

I have a table created that I want to use as a lookup
table. The table is called "Tbl_Lookup_MYr_BucketCode".

No matter what I do, the Dlookup below gives me #Error for
that column when the Query is run.

LookupTest: DLookUp("[Translate into
month]","[Tbl_Lookup_MYr_BucketCode]","[Tbl_Lookup_MYr_Buck
etCode].[Calc Diff Month ID]='"&[Diff_M_ID]&"'")


If I replace the last part of the function so the criteria
is based a number (to test the function) it works fine as
seen below.

LookupTest: DLookUp("[Translate into
month]","[Tbl_Lookup_MYr_BucketCode]","[Tbl_Lookup_MYr_Buck
etCode].[Calc Diff Month ID]=-3")

Can anyone help with this please? why when I use the
field [Diff_M_ID] instead of a number, like -3, it doesn't
work?

thanks
 
Ad

Advertisements

D

Dan Artuso

Hi,
You really have the solution in your post. When you substitute the number, do you
enclose it in quotes? No, so don't enclose your criteria in quotes either.
="&[Diff_M_ID])
 
T

Thrava

Thank you.
You're right, it works now :O)
So when do you use singel quote and when do you use double?

-----Original Message-----
Hi,
You really have the solution in your post. When you substitute the number, do you
enclose it in quotes? No, so don't enclose your criteria in quotes either.
="&[Diff_M_ID])
--
HTH
Dan Artuso, Access MVP


"Thrava" <[email protected]> wrote in
message news:[email protected]
I can't figure this out and its driving me nuts.

in the same select query that I'm trying to used the
DLookup Function, I also have a caculated field called
"Diff_M_ID".

I have a table created that I want to use as a lookup
table. The table is called "Tbl_Lookup_MYr_BucketCode".

No matter what I do, the Dlookup below gives me #Error for
that column when the Query is run.

LookupTest: DLookUp("[Translate into
month]","[Tbl_Lookup_MYr_BucketCode]","[Tbl_Lookup_MYr_Buck
etCode].[Calc Diff Month ID]='"&[Diff_M_ID]&"'")


If I replace the last part of the function so the criteria
is based a number (to test the function) it works fine as
seen below.

LookupTest: DLookUp("[Translate into
month]","[Tbl_Lookup_MYr_BucketCode]","[Tbl_Lookup_MYr_Buck
etCode].[Calc Diff Month ID]=-3")

Can anyone help with this please? why when I use the
field [Diff_M_ID] instead of a number, like -3, it doesn't
work?

thanks


.
 
Ad

Advertisements

D

Dan Artuso

Hi,
The basic rules are:
Strings have to be delimited with quotes
Numbers need no delimiter
Dates are delimited with #

I pretty much have always used double quotes, using the Replace function
to double up the occurence of any single quotes within the string itself.

Dan O'Neil, for example, will not work correctly if you delimit it with single quotes.
What you need it to look like is:
'Dan O''Neil'
because 2 single quotes within a string evaluate to one single quote and it they do not
interfere with your delimiters.


--
HTH
Dan Artuso, Access MVP


Thrava said:
Thank you.
You're right, it works now :O)
So when do you use singel quote and when do you use double?

-----Original Message-----
Hi,
You really have the solution in your post. When you substitute the number, do you
enclose it in quotes? No, so don't enclose your criteria in quotes either.
="&[Diff_M_ID])
--
HTH
Dan Artuso, Access MVP


"Thrava" <[email protected]> wrote in
message news:[email protected]
I can't figure this out and its driving me nuts.

in the same select query that I'm trying to used the
DLookup Function, I also have a caculated field called
"Diff_M_ID".

I have a table created that I want to use as a lookup
table. The table is called "Tbl_Lookup_MYr_BucketCode".

No matter what I do, the Dlookup below gives me #Error for
that column when the Query is run.

LookupTest: DLookUp("[Translate into
month]","[Tbl_Lookup_MYr_BucketCode]","[Tbl_Lookup_MYr_Buck
etCode].[Calc Diff Month ID]='"&[Diff_M_ID]&"'")


If I replace the last part of the function so the criteria
is based a number (to test the function) it works fine as
seen below.

LookupTest: DLookUp("[Translate into
month]","[Tbl_Lookup_MYr_BucketCode]","[Tbl_Lookup_MYr_Buck
etCode].[Calc Diff Month ID]=-3")

Can anyone help with this please? why when I use the
field [Diff_M_ID] instead of a number, like -3, it doesn't
work?

thanks


.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

DLookup and Nz 0
Dlookup 7
Dlookup error 4
DLookUp for an Alphanumeric Record 3
using dlookup in a query 2
DLookUp 7
dlookup 2
DLookUp 1

Top