DLOOKUP in Query not working

  • Thread starter Thread starter Santa-D
  • Start date Start date
S

Santa-D

Hi all,

I'm running a query which generates a report for me on vehicles.
Within the report I have a field which consists of an account code
called [COSTCODE]
What I want to do is retrieve the Performance Centre from the
[COSTCODE] and do a lookup from the Chart of Accounts Table and obtain
the Full Description from the Performance Centre Table.

This is what I have done so far.

PCode: DLookUp("[PCDesc]","[Seg 3 - Perf
Centre]","[PCCode]=LEFT([COSTCODE],4)")

I've even tried creating a field called CCODE which specifically did
the LEFT([COSTCODE],4) but on both ocassions I obtained the following
error.

The expression you entered as a query parameter produced this error:
'Microsoft Office Access can't find the name 'COSTCODE' you entered in
the expression'

Can anyone tell me why this isn't working and how I get it to work
please?
 
Santa-D said:
Hi all,

I'm running a query which generates a report for me on vehicles.
Within the report I have a field which consists of an account code
called [COSTCODE]
What I want to do is retrieve the Performance Centre from the
[COSTCODE] and do a lookup from the Chart of Accounts Table and obtain
the Full Description from the Performance Centre Table.
Click to expand...

Have you tried something like this?

PCode: DLookUp("[PCDesc]","[Seg 3 - Perf Centre]","[PCCode]='" &
LEFT([COSTCODE],4) & "'")

Tom Lake
 
Have you tried something like this?
PCode: DLookUp("[PCDesc]","[Seg 3 - Perf Centre]","[PCCode]='" &
LEFT([COSTCODE],4) & "'")
Click to expand...

It runs the query, however the result is #Error

I tried having a field called PCC:LEFT([COSTCODE],4) and PCode:
DLookUp("[PCDesc]","[Seg 3 - Perf Centre]","[PCCode]='" & [PCC] & "'")

this resulted in #Error

I then converted PCC:CDbl(LEFT([COSTCODE],4) as the table [Seg 3 - Perf
Centre] primary key [PCCode] is a double, however this didn't work
either.

I tried removing the [] from the DLOOKUP function around the Seg 3 -
Perf Centre but that didn't work either. #Error

Yet one of the forms I've built has the following function which works.

=IIf([Forms]![frm-Vehicle-Search]![qry-vehicle-search
subform].[Form]![CLNTCODE]="DCS","[ " & Mid([COSTCODE],1,4) & " ] - " &
DLookUp("[PCDesc]","[Seg 3 - Perf Centre]","MID(
Forms![frm-Vehicle-Search]![qry-vehicle-search subform].Form![COSTCODE]
,1,4)= [PCCode] "),"[ " & Mid([COSTCODE],3,4) & " ] - " &
DLookUp("[PCDesc]","[Seg 3 - Perf Centre]","MID(
Forms![frm-Vehicle-Search]![qry-vehicle-search subform].Form![COSTCODE]
,3,4)= [PCCode] "))
 
I just tried replicating the same code for the forms with the
following:

PCode: DLookUp("[PCDesc]","[Seg 3 - Perf Centre]","MID([COSTCODE],1,4)
= [PCCode] ")

and I get the same result. Can't find [COSTCODE].
 
I got it to work.

A friend of mine noted that I'm trying to convert a string into a
string. He suggested to use the following.

PCode: DLookUp("[PCDesc]","Seg 3 - Perf Centre","[PCCode]=" &
Mid([COSTCODE],1,4))

and it worked.
 
Back
Top