Calculation in Form using field in Table

[nigelh]

Hi All,

Coming back to access after a long break and I'm sure this is a simple one.

I have a table (tblPriceList) and a form (frmPriceCalc), the form is used to calculate prices. I need to perorm the following calculation:-

txtCPU * [tblPriceList.Unit Cost(£)] where type = Memory

At the moment I am doing the simple thing and hard coding the unit cost, this is not ideal as the price will change often and I only want to change it once in the table.

Regards,

Nigel

 

[JamesMcS]

Try

txtCPU * dlookup("[Unit Cost (£)]","tblPriceList","Type='Memory'")

 

[nigelh]

Thanks for reply James,

I was thinking along those lines but presumed I was getting the syntax incorrect.

I have entered the following in the control source of txtCalc:-

=[txtCPU]*DLookUp("[Unit Cost (£)]","tblPriceList","Type='IntelMemory'")

This results in #Name? appearing in the txtCalc field on the form.

Is there a way of debugging this to see what is happening?

Thanks again,

Nigel

 

[JamesMcS]

Whoops - I think it's "[tblPriceList]","[Type]='Memory'")

 

[nigelh]

Hi James,

=[txtCPU]*DLookUp("[Unit Cost (£)]","[tblPriceList]","[Type]='IntelMemory'")

Same result.

Nigel

 

[vbaInet]

Create a table to hold the Type against the Unit Price. Add the table to your query and link it to your other table via Type. Perform the calculation based on the Unit Price returned in the new table.

 

[JamesMcS]

That'd work too. VBA, what's wrong with the syntax on my dlookup suggestion? Can't figure it out...

 

[vbaInet]

It depends on what the OP means by "Same result". He/she hasn't explained what they actually mean.

 

[JamesMcS]

Getting #Name? as the result of the expression, I think...

 

[vbaInet]

[Unit Cost (£)] or [Type] isn't a field in [tblPriceList].

 

[JamesMcS]

That's what I figured, but I can never remember what has to be []'d. If field names are right, I think it should be

=[txtCPU]*DLookUp("[Unit Cost (£)]","tblPriceList","[Type]='IntelMemory'")

i.e. no [] around the domain. I thought....

 

[vbaInet]

I think it would return #Error if this was the case. The square brackets in the domain is optional.

 

[JamesMcS]

Ah yes, of course.... so something to do with field names....

 

[nigelh]

Thanks for your thoughts on this.

James is correct in that I am getting #Name? error still.

VBA, Unit Cost(£) and Type are both fields in tblPriceList and Memory only appears in Type once, i.e the unit cost is already held against the type.

Regards,

Nigel

 

[JamesMcS]

It must be the txtCPU part of the expression then. Does the statement work without this?

 

[nigelh]

Same error without the txtCPU which certainly suggests a problem with the field names.

 

[nigelh]

My sincerest of apologies. James your first solution was correct. The code had a space between 'cost & (£)' the field does not. All working now. Thanks James & VBA for your help.

 

[JamesMcS]

Jolly good then