can values in a table be used as criteria to filter a query

[Navy Ken]

Ok folks thanks for the help, Seriously.
Moving foward I just have to build an If, Then, Else. Marking this as solved.

 

[Navy Ken]

Ah, didn't realize that flagging it solved locked it out. that's rather inconvenient.

 

[The_Doc_Man]

Glad you found it, and yes, you are right. Spaces are important in concatenation. Less important when it is all math, but when it is text, those spaces are real speed bumps if you forget them. The reason is that the text parser that analyzes lines of VBA will use a space as a token delimiter i.e. so it can know when the previous item on the line is complete and it is ready for the next item on the line.

For the record, the correct term is concatenation but I sometimes call it substitution because of another language I used for my Navy job that used ampersands in the same way - but that O/S preferred the term substitution. Old habits die hard.

 

[Navy Ken]

Glad you found it, and yes, you are right. Spaces are important in concatenation. Less important when it is all math, but when it is text, those spaces are real speed bumps if you forget them. The reason is that the text parser that analyzes lines of VBA will use a space as a token delimiter i.e. so it can know when the previous item on the line is complete and it is ready for the next item on the line.

For the record, the correct term is concatenation but I sometimes call it substitution because of another language I used for my Navy job that used ampersands in the same way - but that O/S preferred the term substitution. Old habits die hard.

Can you explain to me why that code had to use concatenation to arrive at the answer? I am still a bit confused about that.

 

[The_Doc_Man]

Can you explain to me why that code had to use concatenation to arrive at the answer?

I can try.

Access has two parallel environments (at least two that we know of). There is the SQL environment where all queries are actually run. This is usually found in a child process of your main Access application running on your .ACCDx file. The main application is the graphic user interface (GUI) and where your VBA code, form & report definitions, and a few other things live. These are two different processes - a parent and a child - that talk to each other only in a tightly prescribed manner. When you run Windows Task Manager you can sometimes see the ">" symbol to the left of MSACCESS.EXE in the Processes tab. If you click that, you will see the child process.

Among other things the parent and child DON'T do is that they don't share memory with each other. This is a Windows requirement that is actually derived from the military's "Orange Book" that defined operating system security levels many years ago, and which has been superseded a few times by now. The Navy (among other services) mandated "C2" security on all computers they bought. One of the rules of C2 says that separate processes can only interact with each other through specific methods or data channels. The short form of that is "process independence."

OK, so the parent GUI has the forms in it, and the data you want - in the form of UserID and AreaID (or whatever their real names are) can be found in controls on those forms. BUT in order to run a query in the SQL environment managed by the JET or ACE database engine, you must get the data from the GUI into the SQL environment.

One of the "specific methods of interaction" allowed between the GUI and the SQL environment is that before you execute a query, you build it as a string. Well, the DCOUNT( ) function internally builds an SQL SELECT string.* And in order to build a selective query, you have to include criteria. You use concatenation to build a COMPLETE SQL string that you can then send it to ACE and get back a result.

Let's take a look at Gasman's earlier offering just for an example.

'   DCount( expression, domain, criteria )
    DCount("*", "AreaXEmployeeT", "[UserID] = " & Me.UserID & " AND [AreaID] = " & Me.AreaID )

So internally, DCount builds "SELECT COUNT( " & expression & " ) FROM " & domain & " WHERE " & criteria & " ;" and sends that to SQL. Which would give you this query:

SELECT COUNT(*) FROM AreaXEmployeeT WHERE [UserID] = whatever is in Me.UserID AND [AreaID] = whatever is in Me.AreaID ;

Now, just for final clarity, what that concatenation will ACTUALLY PRODUCE will probably look like this, if Me.UserID internally translates to 112 and Me.Area translates to 7 ...

SELECT COUNT(*) FROM AreaXEmployeeT WHERE [UserID] = 112 AND [AreaID] = 7 ;

The concatenation is required because you are building the string you will send to SQL/ACE with all values translated and ready to use when the SQL string gets to ACE. And here is where ACE gets caught by the C2 rule from earlier. ACE can't look into "Me.UserID" or "Me.AreaID" because they are in the separate memory of the parent process. Ace CAN see the workspace so COULD track down a reference like Forms!name-of-this-form.UserID - but in this context, concatenation involves less typing.

* Final note: The domain aggregate functions like DCount build that string, but they do more than just concatenate things. They also do error checking and other stuff to prevent the function from crashing the machine or the session. So it isn't QUITE as simple as I made it out to be.

The_Doc_Man EDITED the last SQL/DCount discussion for consistency with the original question.

 

[Navy Ken]

OK rather high level but I do get the idea. So the TLDR is it's the Navy's fault. LOL Story of my life.

 

[Gasman]

Ah, didn't realize that flagging it solved locked it out. that's rather inconvenient.

Marking it solved should not do that?

 

[Gasman]

Got it!
Believe it or not it was spaces. I needed spaces around the ampersands. Unbelieveable.

No you do not.
Better to do so to make it more readable however

? dcount("*","TestTransactions","description like '*"&"139566"&"'")
 5

 

[Navy Ken]

No you do not.
Better to do so to make it more readable however

? dcount("*","TestTransactions","description like '*"&"139566"&"'")
 5

Now that Doc explained it I sort of get what's going on, and spaces are fixed fixed it. That was the only change I made.

 

[The_Doc_Man]

In case anyone is wondering why the spaces are needed around ampersands in a concatenation statement, remember that the syntax for entering a hexadecimal constant is (e.g.) "&H00F012C2" - where there is no space between the & and the H. So there are cases where you can do without that space, but they mean something different and special to VBA.

 

[Gasman]

That is within the same string though, surely?

This is accepted and works

 ? dcount("*","TestTransactions","description like '*"&"H39566"&"'")
 0

This also works after I amended data to suit.

 ? dcount("*","TestTransactions","memo like '"&"H1*"&"'")
 1

 

[The_Doc_Man]

Gasman, the issue is that some cases of ampersand are ambiguous. The example you showed has quotes to resolve the ambiguity. Navy Ken uses spaces to resolve the ambiguity. Access's parser just doesn't like certain cases. Why do you think we need brackets around field or table names that include embedded spaces? To resolve the ambiguity!

 

[Pat Hartman]

@Navy Ken When you need to concatenate a string, the best option is to use a variable to hold it. That way debugging is easier when you run into syntax errors. Printing the variable into the immediate window will frequently allow you to see what Access is complaining about.