DLookup Error

  • Thread starter Thread starter tgavin
  • Start date Start date
T

tgavin

I am trying to lookup a value and add it to a table because no matter how I
link the tables, they are not updatable. I am using the following line of
code and it is returning Type Mismatch. I figure I have something wrong in
how I wrote the criteria but I don't know what. Please help.

dblPriceToPurchase = DLookup("[dblPriceToPurchaseFactor]", "tblPricing",
"[intItem] = " & [intItemID] And "[intVendor] = " & Forms!frmPO![intVendor])

Thanks
Terri
 
Terri

I noticed that your expression was explicit about including "Forms!frmPO!"
for the Vendor, but not for the Item?

Have you verified that the syntax follows that described in Access HELP?

Note that the error message "type mismatch" means you are telling Access to
look for one type of value (e.g., "numeric"), when the data itself is
another (say, text).

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Terri

The secondary issue has to do with why your ?"linked tables"? are not
updateable.

How are you "linking" those tables?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Try this assuming intItem and intVendor are numeric:

dblPriceToPurchase = DLookup("[dblPriceToPurchaseFactor]", "tblPricing",
"[intItem] = " & [intItemID] & " And [intVendor] = " &
Forms!frmPO![intVendor])
 
Why post twice?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Thank you! I knew it had to be in the " and &'s but I couldn't find anything
with numaric multiple criteria to use as an example.

Again, Thanks!

Duane Hookom said:
Try this assuming intItem and intVendor are numeric:

dblPriceToPurchase = DLookup("[dblPriceToPurchaseFactor]", "tblPricing",
"[intItem] = " & [intItemID] & " And [intVendor] = " &
Forms!frmPO![intVendor])

--
Duane Hookom
Microsoft Access MVP


tgavin said:
I am trying to lookup a value and add it to a table because no matter how I
link the tables, they are not updatable. I am using the following line of
code and it is returning Type Mismatch. I figure I have something wrong in
how I wrote the criteria but I don't know what. Please help.

dblPriceToPurchase = DLookup("[dblPriceToPurchaseFactor]", "tblPricing",
"[intItem] = " & [intItemID] And "[intVendor] = " & Forms!frmPO![intVendor])

Thanks
Terri
Click to expand...
Click to expand...
 
Jeff,

I was explicit on the frmPo because it is on the Master form, all the other
fields are on the subfrom.

It is probably because it needs to link to 2 different tables (the Vendor
and the Item)to get the right record. At least that is the only thing I can
figure out.

I posted it twice because I couldn't see my original post after a half
hour. So I figured had done something wrong. Actualy, I am still not seeing
my first post.

Terri
Jeff Boyce said:
Terri

I noticed that your expression was explicit about including "Forms!frmPO!"
for the Vendor, but not for the Item?

Have you verified that the syntax follows that described in Access HELP?

Note that the error message "type mismatch" means you are telling Access to
look for one type of value (e.g., "numeric"), when the data itself is
another (say, text).

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

tgavin said:
I am trying to lookup a value and add it to a table because no matter how I
link the tables, they are not updatable. I am using the following line of
code and it is returning Type Mismatch. I figure I have something wrong in
how I wrote the criteria but I don't know what. Please help.

dblPriceToPurchase = DLookup("[dblPriceToPurchaseFactor]", "tblPricing",
"[intItem] = " & [intItemID] And "[intVendor] = " &
Forms!frmPO![intVendor])

Thanks
Terri
Click to expand...


.
Click to expand...
 
?There's a subform? I don't think I saw that in your original post ...

Check Access HELP for "expressions". When you refer to a control on a
subform, you have to tell Access to look on the main form, in the subform
control, for the control on the subform ... something like:

Forms!YourForm!YourSubformControlName.Form!YourControlOnSubformName

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

tgavin said:
Jeff,

I was explicit on the frmPo because it is on the Master form, all the
other
fields are on the subfrom.

It is probably because it needs to link to 2 different tables (the Vendor
and the Item)to get the right record. At least that is the only thing I
can
figure out.

I posted it twice because I couldn't see my original post after a half
hour. So I figured had done something wrong. Actualy, I am still not
seeing
my first post.

Terri
Jeff Boyce said:
Terri

I noticed that your expression was explicit about including
"Forms!frmPO!"
for the Vendor, but not for the Item?

Have you verified that the syntax follows that described in Access HELP?

Note that the error message "type mismatch" means you are telling Access
to
look for one type of value (e.g., "numeric"), when the data itself is
another (say, text).

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

tgavin said:
I am trying to lookup a value and add it to a table because no matter
how I
link the tables, they are not updatable. I am using the following line
of
code and it is returning Type Mismatch. I figure I have something wrong
in
how I wrote the criteria but I don't know what. Please help.

dblPriceToPurchase = DLookup("[dblPriceToPurchaseFactor]",
"tblPricing",
"[intItem] = " & [intItemID] And "[intVendor] = " &
Forms!frmPO![intVendor])

Thanks
Terri
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top