Dlookup #error

[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

 

[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])

 

[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

.

 

[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

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

.