How to segregate a single value from a query in MS Access

0
votes

My apologies but I am a bit lost in the world of Microsoft Access. So I currently have a query that returns a column of values. The output looks like this:

SMABranchCode Code1 Code2 Code3 etc.

This query then gets moved to a form text box. To do this, I have used Control Source like so: =IIf(IsNull(DLookUp("SMABranchCode","PWS_SM_QUERY"))," ","X")

This works "Ok"; however there are going to be probably 30+ more text boxes going horizontally next to this one all running the same query. Above them is a label with the Code. So basically when it spits out the data it looks like this (as long as there is an entry for that code):

Code 1 | Code 2 | Code 3

X | X | X

The problem is, I either need to go and create a query for each of these text boxes so that it only pulls a single entry for each Code OR I can hopefully change up my Control Source entry (=IIf(IsNull(DLookUp("SMABranchCode","PWS_SM_QUERY"))," ","X")) to include criteria so that each text box has its own Criteria, so that from the one original query, each Code (1,2,3) only displays its information.

I have tried changing DLookUp to have criteria which has looked like this: =IIf(IsNull(DLookUp("SMABranchCode","PWS_SM_QUERY","SMABranchCode = Code1"))," ","X")

However that gives me an #Error! in the boxes I apply it too. I'm hoping maybe someone can help me figure out a solution since my brain is turning to mush over this.

Thank you

1
ms-access
Is this on a form or report? Exactly what are you trying to accomplish? Domain aggregate functions can perform slowly on form and report objects. - June7
There is almost certainly a much better way to go about all this, but for any meaningful help you have to go a step back and show your table structure and what you hope to accomplice. - SunKnight0

1 Answers

0
votes

I presume SMABranchCode is a text type field. Criteria values for text fields must be delimited with apostrophes, date/time field values would use #.

"SMABranchCode = 'Code1'"