Dlookup String Problem

[swhaba]

I am trying to use a simple style for DlookUp Function as a value for a calculated column HostAmount in a select query on other table as following:

HostAmount: DLookUp("Amount","tblCardHost","AuthorisationCode =' 182800'")

But access is responding an error message indicates while running this select query.

that Access can not find the name of 'AuthorisationCode' you entered in the expression.

Please help ASAP,

Thanks
Shereef Wahba

[NeoPa]

Using DLookUp() is certainly not to be advised within SQL if it's possible to avoid - and it's hard to think of a situation where it's not.

On the other hand, the message is telling you that your table [tblCardHost] has no field with the exact name [AuthorisationCode]. It may be worth you checking the spelling of the field in the table.

[PhilOfWalton]

@Neopa

I agree completely with your views on DLookup, but I have used it, albeit reluctantly, when the joins make a query not updateable.

@swahaba
Firstly Neopa's advice is very sound, but if you do have to use DLookup then what is 18280?

The normal DLookup syntax is

HostAmount: DLookUp("Amount","tblCardHost","AuthorisationCode = " & MyField)
 

Where MyField is numeric or

HostAmount: DLookUp("Amount","tblCardHost","AuthorisationCode = " & Chr$(34) & MyField & Chr$(34))
 

Where MyField is Text

There are alternatives to using Chr$(34), but the look messy (depending on the font.

HostAmount: DLookUp("Amount","tblCardHost","AuthorisationCode = '" & MyField & "'")
 

Phil

[NeoPa]

You're quite right Phil. It was difficult to bring anything to mind yesterday but that's certainly one instance where it could be used. Even in that case though, a sub-query would probably be a better solution. Certainly I wouldn't want to give it out as an absolute, but the understanding that it should be avoided where possible I think is perfectly on script.