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 Scott & Mimmo , I will work on the information provided tonight and I will give a feedback tomorrow.

Regards

Chris

Answer 2

This goes back to things I discuss in my blog on Displaying Related data.

tblAccounts is your Account table. It contains information about each Account. So there is one record per account. The AccountID is you primary key and should be used as a Foreign key in any table where you want to include data about the account. You should NOT repeat AccountCode or AccountName in any table as that is redundant data. 

NO, you do NOT want a table that looks like:

AccountCode  AccountName   AccountCartegory Debit  Credit  Total

That is a QUERY. 

So you have your tblAccounts and then you have a Transactions table that should look something like this:

tblTransactions

TransactionID (PK Autonumber)

AccountID (FK)

TransDate

Debit

Credit

(personally I would have a single amount field and a field to indicate whether its a debit or credit). 

Now to get the output you want you use a query:

SELECT AccountCode, Accountname, AccountCategory, TransDate, Debit, Credit

FROM tblAccounts, tblTransactions INNER JOIN ON tblTransactions.AccountID = tblAccounts.AccountID

ORDER BY AccountCode, Transdate;

I believe we have been trying to explain this to you over several threads. This is how relational databases work, how that are to be designed. And this is not an unusual design, in fact, its fairly standard.

DLookups have their place, but this is not it. This is a fairly simple query.

Answer 3

Kindly see my accounts table below:

tblAccounts

AccountID  = PK

AccountCode =  unique (not duplicable) but text example ( 40500125 as code controlled manually ) this is so because users may have certain account standards for revenue can start may be from 500100100, expenses can start may be from 400100100 ,etc.

AccountName = Text

AccountType = A combo from account category

I do not understand the columns you are talking about because the above table has all what you are suggesting, that is why I want to use Dlookup function to return some missing data in the TB

Kindly note that, the AccountCode appear as a combo or a lookup to all tables that require selecting an account code as you may be aware that the rules of normalization dictates that data must be stored only in one place not so many places to avoid redundant or duplication. 

I want to have a tb that looks like below:

AccountCode  AccountName   AccountCartegory Debit  Credit  Total

Regards

Chris

Answer 4

This is in a query, why use a Dlookup. Why not add tblAccounts joined on AccountCode and then add the Accountname as a column. This is NOT a proper use for a DLookup.

1. You have 2 options. Either add the Transaction date using WHERE on the Group By row instead of Group By. Or create a query filtered by Transaction data and use that query in your Group By query.
2. Again this is done with Joins. One function of queries is to pull in all your tables joined on key fields and then pull the columns you need to display.

Answer 5

Hi,

try with:

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

Ciao Mimmo