Dlookup Function

[josros60]

I have form access with field G/L and a field Account which is the name of the G/L account comes from table Chart of Accounts.

I want to type the GL on the textbox automatically get the name in the Account display on the textbox Account on the Contact Details form.

the Formula i have I am using the following fields: GL and Account from table Chart of Accounts and at the end GL which the text box in Form Contact Details.

I have this one but doesnt' work:

=DLookUp("GL","Chart of Accounts","GL = " & [Forms]![Contact Details]![GL])

thanks.

 

[sneuberg]

I've found that I always need to put table names with spaces in brackets in dlookups and although this may sound weird you don't have to concatenate in full references so try:

=DLookUp("GL","[Chart of Accounts]","GL = [Forms]![Contact Details]![GL]")

 

[plog]

Agree with sneuberg about spaces--its actually best to avoid them altogether for this reason. You also need to escape your comparison. When you compare text, you need to put quote marks around the text you are inputting:

=DLookUp("[GL]","[Chart of Accounts]","GL = '" &[Forms]![Contact Details]![GL] & "'")

 

[sneuberg]

Agree with sneuberg about spaces--its actually best to avoid them altogether for this reason. You also need to escape your comparison. When you compare text, you need to put quote marks around the text you are inputting:

=DLookUp("[GL]","[Chart of Accounts]","GL = '" &[Forms]![Contact Details]![GL] & "'")

I guess you don't believe me about not have to concatenate in full references. I didn't believe it myself when I think it was Galixiom mentioned it. I suggest you try it sometime

 

[josros60]

thanks.

I tried both but I get an #error display on the G/L name textbox

Is my mistake didn't explain well,

Here it is what i want to accomplish:

I have form Contact Details
with the With the text boxes:

1. G/L from Contacts table
2. G/L Name from Contacts table as well

so I want when i type G/L number like 6040 to display the account name in on the G/L Name text box, so i want dlookup function to get the G/L name from the chart of Accounts table.

Char of Accounts table and Contacts table has the same fields names G/L and G/L name.

thanks

 

[sneuberg]

Could you post a screen shot of the Chart of Accounts table in design view?

 

[josros60]

Here it's the screenshot of Chart of Accounts.

thanks.

 

[sneuberg]

This should be right if it doesn't work check the spelling of the form and text box in this.

=DLookUp("[Account]","[Chart of Accounts]","[Chart of Accounts].[GL] = [Forms]![Contact Details]![GL]")

Note this won't display anything or will be #Name until the control GL has a valid number in it.

Actually it would be better if you added the Account field to the form's record source and then just make it the control source of this text box

 

[plog]

I guess you don't believe me about not have to concatenate in full references. I didn't believe it myself when I think it was Galixiom mentioned it. I suggest you try it sometime

Your saying that you don't have to add those quote marks even if GL is text? Because its a parameter it automatically works? I always thought you did no matter what. Learn something new every day, thanks.

 

[sneuberg]

Your saying that you don't have to add those quote marks even if GL is text? Because its a parameter it automatically works? I always thought you did no matter what. Learn something new every day, thanks.

Yes, Same thing works for text, numbers and dates. Apparently Access figures it out.

 

[josros60]

Thank you.

I tried but doesn't work.

on the form i have G/L text box from the Contacts table and I want to be able when type that number display account name on text box G/L from the contact table so i want the dlookup to look that number on the "Chart of Accounts" table.

thanks.

 

[plog]

Remove the criteria portion and just get it to work:

=DLookUp("[Account]","[Chart of Accounts]")

If that doesn't work, weve narrowed the problem to either the field or table. If it does work, put back in criteria, but don't reference the form--hard code a value. Once that works, then put the form reference back in.