By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,453 Members | 3,183 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,453 IT Pros & Developers. It's quick & easy.

Dlookup String Problem

P: 5
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:
Expand|Select|Wrap|Line Numbers
  1. 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
Aug 21 '17 #1

✓ answered by 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.

Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,494
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.
Aug 22 '17 #2

PhilOfWalton
Expert 100+
P: 1,430
@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
Expand|Select|Wrap|Line Numbers
  1. HostAmount: DLookUp("Amount","tblCardHost","AuthorisationCode = " & MyField)
  2.  
Where MyField is numeric or

Expand|Select|Wrap|Line Numbers
  1. HostAmount: DLookUp("Amount","tblCardHost","AuthorisationCode = " & Chr$(34) & MyField & Chr$(34))
  2.  
Where MyField is Text

There are alternatives to using Chr$(34), but the look messy (depending on the font.
Expand|Select|Wrap|Line Numbers
  1. HostAmount: DLookUp("Amount","tblCardHost","AuthorisationCode = '" & MyField & "'")
  2.  
Phil
Aug 22 '17 #3

NeoPa
Expert Mod 15k+
P: 31,494
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.
Aug 22 '17 #4

Post your reply

Sign in to post your reply or Sign up for a free account.