Union Query Logic 2

[Jimmy4559]

I have been struggling with my first union query. From the look of it, what I am trying to do isn’t straightforward.

I have a form [Title Page] which contains a combo box Assistant. This lists the names of all the shop assistants who worked during a certain period.

The list is extracted from the query qCombined by means of a further query called Menu_Assistants.

SELECT qCombined.Name
FROM qCombined
GROUP BY qCombined.Name;

The contents of the combo box needs to contain an “All” entry which I have created as follows:

SELECT Menu_Assistants.Name 
FROM Menu_Assistants
UNION 
SELECT '*' 
FROM Menu_Assistants;

The two queries above could no doubt be merged but this is my first union query. I then wish to use the selection made in a further query, the important part of which goes as follows:

SELECT qCombined.Name AS Details
FROM qCombined
WHERE (((qCombined.Name)=[Forms]![Title Page]![Assistant]));

The problem is, that whenever I select the ‘*’ entry I get nothing rather than everything. I have rewritten the query a variety of times using Like ‘*’ and “(All)” – which would be my preferred entry rather than an asterisk – but I appear to be missing something fundemental.

Where is my logic letting me down?

 

[hindsjl]

Try this:

SELECT qCombined.Name AS Details
FROM qCombined
WHERE qCombined.Name like ([Forms]![Title Page]![Assistant]);

When ([Forms]![Title Page]![Assistant]) is a valid name or ID, "Like" will give you the exact matches only. When ([Forms]![Title Page]![Assistant]) is '*', "Like" will match for all values except Null.

John

 

[Jimmy4559]

As always the answer was as straightforward as they come. It looks like my logic was okay, it was just my syntax that was wrong. That's the trouble when you rely on the Query Window to do the work for you.

Much appreciated.