Dlookup Error doing my head in

[CSP12345]

Hi all,
I suspect this ha been covered but I have tried everything and searched every were please help

Ok I have created a query "query1"
In query1 I have a field "Assest Number" "BatteryType" that is just pulling information direct from a table "3 Yearly Cost"
and in another field "BatteryCost" that I want to look up the cost of the battery from a table "BatteryTb" field "BatteryCost1"

My Expression is this BatteryCost: DLookUp("BatteryCost1","BatteryTb","Batterytype1 =" & [BatteryType])

The Error it is returning is The object doesnt contain the automation object "GP1272"
GP1272 is a battery type that is displaying in the query filed "BatteryType"


Any help please, Im good with Excel but new to Access and the cross over is doing my head in

 

[Norie]

If 'BatteryType1' is a text field you need to enclose the criteria value in quotes.

BatteryCost: DLookUp("BatteryCost1","BatteryTb","Batterytype1 ='" & [BatteryType] & "'")

 

[CSP12345]

If 'BatteryType1' is a text field you need to enclose the criteria value in quotes.

BatteryCost: DLookUp("BatteryCost1","BatteryTb","Batterytype1 ='" & [BatteryType] & "'")

Mate that worked a treat I am so great full. Just 1 more question since the BatteryCost1 is a currency how do I make the query result display as a currency?

 

[Norie]

You can set the Format of the field in the query to Currency.

To do that select the field, right click, select Properties... and set the Format property in the property sheet.

 

[CSP12345]

You can set the Format of the field in the query to Currency.

To do that select the field, right click, select Properties... and set the Format property in the property sheet.

The format of the table is set to currency but the result in the query returns it as a text eg.

BatteryTb

BatteryType1 BatteryCost1

GP1234 $1.00


Query1

BatteryType BatteryCost

GP1234 1.00

 

[Norie]

Did you try changing the format of the field/expression BatteryCost in the query?

 

[CSP12345]

Did you try changing the format of the field/expression BatteryCost in the query?

I just have blanks in the format.?

 

[Norie]

Sorry I don't follow.

In the Format field of the property sheet there should be a dropdown offering various options, including Currency, for formatting.

 

[CSP12345]

Im afraid not for some reason. To be on the same wave length this is in the Query property options for the field right?

 

[Norie]

No it's in the Field Properties of the field, the Query Properties are, for welll the query.