DLookup gets value from wrong record! 
Author Message
 DLookup gets value from wrong record!

Is there a known problem with DLookup that causes it to sometimes retrieve a
field from a different record than it is supposed to look at?

My situation: I have a table (Materials) with fields MaterialID, Material,
and Price. Two such records could look like:

1, Widget, $4.50
2, Gadget, $895.00

If then I run the following code to look up the price for Gadget, it
retreives another value, perhaps the $4.50, or it could be another value
that is about two or three records before or after it:

strLookup = DLookup("[Price]","Materials","[Material] = """ &
varVariantWithGadgetAsItsValue & """")



Sat, 29 Dec 2001 03:00:00 GMT  
 DLookup gets value from wrong record!
Try it with a single quote, not a double quote:

strLookup = DLookup("[Price]","Materials","[Material] = "'" &
varVariantWithGadgetAsItsValue & "'")

Quote:

> Is there a known problem with DLookup that causes it to sometimes retrieve a
> field from a different record than it is supposed to look at?

> My situation: I have a table (Materials) with fields MaterialID, Material,
> and Price. Two such records could look like:

> 1, Widget, $4.50
> 2, Gadget, $895.00

> If then I run the following code to look up the price for Gadget, it
> retreives another value, perhaps the $4.50, or it could be another value
> that is about two or three records before or after it:

> strLookup = DLookup("[Price]","Materials","[Material] = """ &
> varVariantWithGadgetAsItsValue & """")



Sat, 29 Dec 2001 03:00:00 GMT  
 DLookup gets value from wrong record!
Thanks, but that won't work because the double-double-quote evaluates to a
single double-quote, and thus VBA wants to see a third double-quote at the
end. And, if my original were wrong, then you'd expect an error or a null
value, neither of which I get. Anyway, here is a better version of my code
that you can look at:

  Dim varLookup As Variant, strCriteria As String

  strCriteria = "[Description] = "" & Me.Description & """
  varLookup = DLookup("[Price]", "tblMaterials", strCriteria)

Therefore, if Me.Description = "Widget" then strCriteria will be exactly as
appears in the following line:
[Description] = "Widget"
This is confirmed when I look at the value of strCriteria in the debug
process.

Is it possible that my table is corrupt?


Quote:
> Try it with a single quote, not a double quote:

> strLookup = DLookup("[Price]","Materials","[Material] = "'" &
> varVariantWithGadgetAsItsValue & "'")


> > Is there a known problem with DLookup that causes it to sometimes
retrieve a
> > field from a different record than it is supposed to look at?

> > My situation: I have a table (Materials) with fields MaterialID,
Material,
> > and Price. Two such records could look like:

> > 1, Widget, $4.50
> > 2, Gadget, $895.00

> > If then I run the following code to look up the price for Gadget, it
> > retreives another value, perhaps the $4.50, or it could be another value
> > that is about two or three records before or after it:

> > strLookup = DLookup("[Price]","Materials","[Material] = """ &
> > varVariantWithGadgetAsItsValue & """")



Sat, 29 Dec 2001 03:00:00 GMT  
 DLookup gets value from wrong record!
Darn, I made an error in the last message. I meant to say:

Therefore, if Me.Description = "Widget" then strCriteria will be exactly as
appears in the following line:
[Price] = "Widget"


Quote:
> Thanks, but that won't work because the double-double-quote evaluates to a
> single double-quote, and thus VBA wants to see a third double-quote at the
> end. And, if my original were wrong, then you'd expect an error or a null
> value, neither of which I get. Anyway, here is a better version of my code
> that you can look at:

>   Dim varLookup As Variant, strCriteria As String

>   strCriteria = "[Description] = "" & Me.Description & """
>   varLookup = DLookup("[Price]", "tblMaterials", strCriteria)

> Therefore, if Me.Description = "Widget" then strCriteria will be exactly
as
> appears in the following line:
> [Description] = "Widget"
> This is confirmed when I look at the value of strCriteria in the debug
> process.

> Is it possible that my table is corrupt?



> > Try it with a single quote, not a double quote:

> > strLookup = DLookup("[Price]","Materials","[Material] = "'" &
> > varVariantWithGadgetAsItsValue & "'")


> > > Is there a known problem with DLookup that causes it to sometimes
> retrieve a
> > > field from a different record than it is supposed to look at?

> > > My situation: I have a table (Materials) with fields MaterialID,
> Material,
> > > and Price. Two such records could look like:

> > > 1, Widget, $4.50
> > > 2, Gadget, $895.00

> > > If then I run the following code to look up the price for Gadget, it
> > > retreives another value, perhaps the $4.50, or it could be another
value
> > > that is about two or three records before or after it:

> > > strLookup = DLookup("[Price]","Materials","[Material] = """ &
> > > varVariantWithGadgetAsItsValue & """")



Sat, 29 Dec 2001 03:00:00 GMT  
 DLookup gets value from wrong record!
Ok, ok, 1000 apologies, and whip me with a wet noodle. Let's stop the thread
right here because I see more errors that are making this too confusing.
Sorry.

I'll start a new thread when I'm good and ready.

<Sigh>

Quote:
> Darn, I made an error in the last message. I meant to say:

> Therefore, if Me.Description = "Widget" then strCriteria will be exactly
as
> appears in the following line:
> [Price] = "Widget"



> > Thanks, but that won't work because the double-double-quote evaluates to
a
> > single double-quote, and thus VBA wants to see a third double-quote at
the
> > end. And, if my original were wrong, then you'd expect an error or a
null
> > value, neither of which I get. Anyway, here is a better version of my
code
> > that you can look at:

> >   Dim varLookup As Variant, strCriteria As String

> >   strCriteria = "[Description] = "" & Me.Description & """
> >   varLookup = DLookup("[Price]", "tblMaterials", strCriteria)

> > Therefore, if Me.Description = "Widget" then strCriteria will be exactly
> as
> > appears in the following line:
> > [Description] = "Widget"
> > This is confirmed when I look at the value of strCriteria in the debug
> > process.

> > Is it possible that my table is corrupt?



> > > Try it with a single quote, not a double quote:

> > > strLookup = DLookup("[Price]","Materials","[Material] = "'" &
> > > varVariantWithGadgetAsItsValue & "'")


> > > > Is there a known problem with DLookup that causes it to sometimes
> > retrieve a
> > > > field from a different record than it is supposed to look at?

> > > > My situation: I have a table (Materials) with fields MaterialID,
> > Material,
> > > > and Price. Two such records could look like:

> > > > 1, Widget, $4.50
> > > > 2, Gadget, $895.00

> > > > If then I run the following code to look up the price for Gadget, it
> > > > retreives another value, perhaps the $4.50, or it could be another
> value
> > > > that is about two or three records before or after it:

> > > > strLookup = DLookup("[Price]","Materials","[Material] = """ &
> > > > varVariantWithGadgetAsItsValue & """")



Sat, 29 Dec 2001 03:00:00 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Getting the autonumber value of a new record

2. URGENT: Getting Values from First/Last Record of a Group

3. Value for previous month in DlookUp

4. getting R G B values from a long value

5. Getting records from recordset (between Nth and Mth records)

6. I am getting duplicate records, I mean everything is duplicate even Access record number

7. How to set default value of combo box to value of record in database

8. Spliting a record into many records based on its value

9. Replacing The Text String Value In A Record With That From A Previous Record

10. Record Values from other Records

11. Update a record using a previous records value

12. Combo Box Problem- getting wrong data returned

 

 
Powered by phpBB® Forum Software