Dlookup Error doing my head in

CSP12345

Board Regular
Joined
Oct 1, 2014
Messages
52
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
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,835
Office Version
  1. 365
Platform
  1. Windows
If 'BatteryType1' is a text field you need to enclose the criteria value in quotes.

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

CSP12345

Board Regular
Joined
Oct 1, 2014
Messages
52
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,835
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Oct 1, 2014
Messages
52

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
75,835
Office Version
  1. 365
Platform
  1. Windows
Did you try changing the format of the field/expression BatteryCost in the query?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,835
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Oct 1, 2014
Messages
52
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,835
Office Version
  1. 365
Platform
  1. Windows
No it's in the Field Properties of the field, the Query Properties are, for welll the query.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,549
Messages
5,529,470
Members
409,884
Latest member
Msinmath
Top