Dlookup error

[glen.sheppard]

Good evening all,

I am really hoping someone will be able to steer me in the right direction with this query.



I have a table called ProductT this table has two fields relevant to this question. The first field is ProductName and the second field is CostPrice. I also have a table called PurchasesT this information has these two fields and a totals field. What I would like to do is set the Costprice in PurchasesT to auto populate. I have created a form from the table PurchasesT and this form has a lookup so that you can select the ProductName from the list. If i add =Dlookup("CostPrice","ProductT","ProductName="&[ProductName]) I get an error. I have made sure that the fields have the same strings eg CostPrice has currency flagged on both tables. I have even changed this to short text and number to see if it will pull the correct data.

Any help would be really appreciated.

Thank you in advance
Glen Sheppard

[RuralGuy]

You might start by trying =Dlookup("CostPrice","ProductT","ProductName= '" & [ProductName] & "'" )

[orange]

Glen,

As Allan has said -- check your syntax (quotes required around text values).
But my concern is with the use of CostPrice to auto-populate. I may have misunderstood your comment, sot I'd like to see a jpg of your tables and relationships to confirm.

A common mistake is to use the CostPrice in the Product table to determine the TotalPrice of an Order.
For example:

ProductT
--ProductId
--ProductName
--CostPrice

PurchasesT
--PurchaseID
--PurchaseDate
--ProductIDFK
--PurchaseQuantity

Select ProductT.ProductName
,PurchaseT.PurchaseDate
,(PurchaseT.PurchaseQuantity * ProductT.CostPrice) as TotalPrice
FROM ProductT Inner Join PurchaseT ON
ProductT.ProductId = PurchaseT.ProductIDFK

which will give a result such as

LaptopComputer Nov 10,2015 (3 * 900) ===> 3 laptops ($900/each) total $2700 Nov 10/2015.

Now suppose you change the CostPrice of that Product to $1000.

If you rerun the query on a previous Sale/Purchase you have changed the historical amount.
It will now show ($3000).

In your PurchasesT, include the Selling or AgreedTo Price, that way you will not lose historical info.

[glen.sheppard]

Thank you for the reply. I have tried the string mentioned above and get "The Expression you entered has an invalid string" I have attached screen shots. Thank you for your recommendations on not using a fixed cost price due to issues down the track with amendments to products cost and sales price. I am actually going to take your advice on this but I really would like to rectify this error. I must admit, my access skills are not at the level as my excel skills. I can build pretty much anything in Excel and use macros, v and h lookups, pivot tables, filters and sorts, conditional formatting etc but I want to start using access more. Trying to build a sales pipeline or inventory tracker in excel is not very realistic and lags with the vlookups and sorts required. I am hoping that there is only a slight bug in my dlookup. I have watched a few tutorials on them but just cannot get it to work.....

[orange]

I don't see anything wrong with the code adjustment that Allan suggested.
Are you using lookups at the table field level? If so, that can cause unexpected issues.
You may have to post a copy of your database for readers to see the real set up.

[glen.sheppard]

Thank you Allan and Orange,

It certainly is a strange one. I copied Allan's suggested dlookup to see if it will return the value. It does not show the error now however will not return the value. I thought it would be pretty straight forward as it is only a form pulling data from one table to populate the form and then using the lookup to reference the result within the field from a 2nd table. My objective was to auto populate the cost price in the PurchasesT table. I guess like a standard purchasing invoice where you would select the product, key in the quantity, have the cost price auto populate based on the product and then give you a total amount based on a calculation of quantity x cost price. I will ponder a way around this without using a dlookup or revert back to excel.

Cheers Glen

[orange]

Glen,

Post a copy of your database. I would have a field in PurchaseT called AgreedToPrice. I would populate it with
CostPrice from ProductT, or the sale/clearance Price actually used as appropriate.
Hang in--