DLookup Pulling wrong Data

Question

Hi

1. I have the following Dlookup function which is pulling one account name for all 130 accounts, kindly see  where the mistake is:

LedgerName:Dlookup(“ [AccountName]”,”[tblaccounts]”,”[AccountCode]=  [AccountCode]”]

For all 130 accounts it is pasting other expenses account name instead of the unique accounts names. The reason why I want to use the Dlookup function at TB level is because the UNION QUERY is CONVERTING my accountcodes to PRIMARY KEY just because they are populated through a COMBO BOX, hence the need to reverse back to the meaningful account code and account name.

1. I want also to incorporate the transaction date within the query where the above Dlookup is sited so that I can use the parameter form to filter the correct transactions by date for my Trial Balance, but each time I use group by in the select query design, the dates are causing all the transactions NOT to be summed or totalled by account code. As long as the transaction dates are not the same then the query cannot total the field to one consolidate figure, you may be aware that in the Trial balance only the totals are required regardless of different transaction dates. Any suggestion on this??????????
2. Hidden text box control on a form, the other time one suggested that I create a hidden text box control that can be used as reference in point 1 above. Now what I’m supposed to do ?

. Did you mean to create a new unbound form that will hold that hidden text control? Then how is it referenced at query level?

. Do I have to create a hidden control for all the forms which have accounts Code combos and then how do I reference them at query level , I have no problem referencing this at form and report level because it is just a question of opening the form or report data property and insert a code like ( =[AccountCode].Column ) this will give me an account name ,the problem comes at query level , how is it done ? Can some demonstrate??????????

Regards

Chris

Answer 1

Thank you so much, Ciao Mimmo answered DLookup question very well , I even went further to sort out the opening balances by using the same concept, but with Dsum by insert it on the report header directly , it also works well as long as the parameters on the form are linked within the Dsum, and so no need for a query to get just the opening balance of anything, it is very simple for novices like us.

Many thanks to the community and happy Xmas to Scott & Ken, you people, you have built me from ground zero.

Regards

Chris