Dlookup in query displays error for short text data type

[ayub]

Dear sir / madam


i have two tables the first table is coa which has two fields accountid and accounthead and in accountid i have three rows 22102 , 22313, 22701/02 and in accounthead i have the information for these three travel,refreshment,stationaries. In the second table which is expensetable i have three fields Id,accountid and accounthead. in the accountid field i enter the accountid for example 22102 and using query i should get the related accounthead. i can get the first and the second code 22102 and 22313 accounthead results when its data type is set to number but for the third 22701/02 i do not get the result when i changed the data type to short text for the accountid field. My query expression was Dlookup(”accounthead”,”coa”,”accountid=”&[accountid]). ,i came to know that for short text data type some extra information has to be added but i was not able to do so. pls help in on this function.
best regards
ayub”

[Welshgasman]

If you use string criteria with a domain function you must surround it with single quote (or triple double quotes if the criteria could contain a single quote like O'Neill)
You can get by with single quotes.

[June7]

What extra information were you thinking of that you were not able to do?

Text field requires apostrophe delimiters for parameter.

Dlookup(”accounthead”, ”coa”, ”accountid='” & [accountid] & "'")

It worked for two values because there were no non-numeric characters. However, it is still a text type field so use the apostrophes.

[Micron]

First and foremost, you should not use DLookups in queries as a rule. It's inefficient but mostly it doesn't make sense when you can make the looked up field part of the query itself just by adding the table and/or field. Second, since you're using non alpha characters in your data, your field type has to be string. In that case, when you have to concatenate variables you must add quotes (single quote being the easiest). Such a lookup might then be Dlookup("accounthead","coa","accountid= ' " &[accountid] & " ' ")
I added spaces so that the quotes are easier to see. You would remove them.

I guess I should refresh my page when I've been away.

[June7]

But then you make a good point about use of DLookup in query.

ayub, so where is this query used?

[ayub]

But then you make a good point about use of DLookup in query.

ayub, so where is this query used?

Hi thanks for your feedback. iam from afghanistan and very new to microsoft access and gather the programming knowledge from internet. in reply to your question as i had mentioned before that i have one table by the name coapts which is chart of accounts with a aacountcode abduct accounthead fields. the second table is aardvark cash transaction which includes fields like voucher number,transaction date, supplier,particulars,aacountid,account head,receipt,payment and balance.
now based on the above table i do not get two field informations which is a accounthead and it should be automatically created and the balance which has to be a running balance and to get these information i design a query and use dlookup for finding the accouthead and running balance through a expression builder and the query will be saved as my cash transaction.
regards
ayub

note if you have some site information where i could increase my knowledge on access Sql and access vba coding for free then please provide me.

[ayub]

Dear micron
Thanks for the feedback

[June7]

Still not sure what purpose the query serves. Is it RecordSource for a form or report?

Running sum/balance is a common topic.

Running sum/balance on form is seldom useful and difficult to accomplish. Running balance should be fairly easy to accomplish in textbox on report where RunningSum property of textbox is available (not on form).