If query results are null ...

J

Joshua McLemore

Hello,

I am trying to design a macro to use with a combo box that will run a query
and if those results are null, will run a different query and display those
results instead within the combo box.

I have the queries set up and working, but now I just need to get the "use
this unless blank, then use that" working.

Thanks for any help.

Joshua
 
S

Steve Schapel

Joshua,

When you say "those results are null", do you mean if there are no
records returned by the query?

When you say "display those results within the combo box", do you mean
the list of items that are shown in the combobox drop-down list? In
which case we are talking about the Row Source of the combobox?
 
J

Joshua McLemore

Steve,

Thanks for your post. You are correct that I meant 'no records returned by
query' when I said those results are null.

When I said display those results within the combo box, I was referring to
row source being the result of the first query, with results, or the second
query results if the first query did not return anything.

My apologies for the lack of clarity!

Thanks.

Joshua
 
J

Joshua McLemore

If this helps ... this is where I'm at on the statement:

SWITCH(Iff tblCrew_Event_Assignments.Name, tblCrew_Event_Assignments.[Call
Number]
FROM tblCrew_Room_Assignments, tblCrew_Event_Assignments
GROUP BY tblCrew_Event_Assignments.Name, tblCrew_Event_Assignments.[Call
Number], tblCrew_Event_Assignments.[Event Name],
tblCrew_Event_Assignments.Position
HAVING (((tblCrew_Event_Assignments.[Event Name])=[Forms]![frm All
Events]![Event Name]) AND
((tblCrew_Event_Assignments.Position)=[Forms]![frmEvent_Part1_Details]![Audio
Position 1 Description Combo]));
=IS NOT NULL
SELECT tblCrew_Event_Assignments.Name, tblCrew_Event_Assignments.[Call Number]
FROM tblCrew_Room_Assignments, tblCrew_Event_Assignments
GROUP BY tblCrew_Event_Assignments.Name, tblCrew_Event_Assignments.[Call
Number], tblCrew_Event_Assignments.[Event Name],
tblCrew_Event_Assignments.Position
HAVING (((tblCrew_Event_Assignments.[Event Name])=[Forms]![frm All
Events]![Event Name]) AND
((tblCrew_Event_Assignments.Position)=[Forms]![frmEvent_Part1_Details]![Audio
Position 1 Description Combo]));)
(Iff tblCrew_Event_Assignments.Name, tblCrew_Event_Assignments.[Call Number]
FROM tblCrew_Room_Assignments, tblCrew_Event_Assignments
GROUP BY tblCrew_Event_Assignments.Name, tblCrew_Event_Assignments.[Call
Number], tblCrew_Event_Assignments.[Event Name],
tblCrew_Event_Assignments.Position
HAVING (((tblCrew_Event_Assignments.[Event Name])=[Forms]![frm All
Events]![Event Name]) AND
((tblCrew_Event_Assignments.Position)=[Forms]![frmEvent_Part1_Details]![Audio
Position 1 Description Combo]));
=IS NULL
THEN
SELECT tblCrew_Room_Assignments.Name, tblCrew_Room_Assignments.[Call Number]
FROM tblCrew_Room_Assignments
GROUP BY tblCrew_Room_Assignments.Name, tblCrew_Room_Assignments.[Call
Number], tblCrew_Room_Assignments.[Room Name],
tblCrew_Room_Assignments.Position
HAVING (((tblCrew_Room_Assignments.[Room Name])=[Forms]![frm All
Events]![Part 1 Location]) AND
((tblCrew_Room_Assignments.Position)=[Forms]![frmEvent_Part1_Details]![Audio
Position 1 Description Combo]));)


Obviously this is wrong, but hopefully this helps to show you what I'm
trying to do.

Thanks.

Joshua
 
S

Steve Schapel

Jushua,

Assuming these two queries are actual saved queries in your database,
you can use a SetValue actions, with Conditions, in your macro, like this:

Condition: DCount("*","NameOfYourFirstQuery")>0
Action: SetValue
Item: Forms]![NameOfForm]![NameOfCombobox].[RowSource]
Expression: NameOfYourFirstQuery

Condition: ...
Action: StopMacro

Condition: <blank>
Action: SetValue
Item: Forms]![NameOfForm]![NameOfCombobox].[RowSource]
Expression: NameOfYourSecondQuery

Hope that makes sense and will point you in the right direction.
 
J

Joshua McLemore

Steve,

I quickly tried the macro and couldn’t get it to work. Beyond that, I have
about 35 of the fields per form and 7 forms which would all require their own
macro doing it this way. So using a macro for each would be a lot of macros!
Thanks so much for this though.

I have been working on a statement that I’m sticking in the combo’s row
source that isn’t quite right. But I’m trying to use the two queries that are
both working great.


1st query with conditions
SELECT (qryFindbyEvent_Matching) IF ((tblCrew_Event_Assignments.Name) Is Not
Null) AND ((tblCrew_Event_Assignments.[Event Name])=[Forms]![frm All
Events]![Event Name]) AND
((tblCrew_Event_Assignments.Position)=[Forms]![frmEvent_Part1_Details]![Audio
Position 1 Description Combo]));ELSE (qryFindbyRoom_Matching);
2nd query

Is this just not practical or is my noobness at SQL just shining through?

Thanks again.

--
Joshua


Steve Schapel said:
Jushua,

Assuming these two queries are actual saved queries in your database,
you can use a SetValue actions, with Conditions, in your macro, like this:

Condition: DCount("*","NameOfYourFirstQuery")>0
Action: SetValue
Item: Forms]![NameOfForm]![NameOfCombobox].[RowSource]
Expression: NameOfYourFirstQuery

Condition: ...
Action: StopMacro

Condition: <blank>
Action: SetValue
Item: Forms]![NameOfForm]![NameOfCombobox].[RowSource]
Expression: NameOfYourSecondQuery

Hope that makes sense and will point you in the right direction.

--
Steve Schapel, Microsoft Access MVP

Joshua said:
If this helps ... this is where I'm at on the statement:

SWITCH(Iff tblCrew_Event_Assignments.Name, tblCrew_Event_Assignments.[Call
Number]
FROM tblCrew_Room_Assignments, tblCrew_Event_Assignments
GROUP BY tblCrew_Event_Assignments.Name, tblCrew_Event_Assignments.[Call
Number], tblCrew_Event_Assignments.[Event Name],
tblCrew_Event_Assignments.Position
HAVING (((tblCrew_Event_Assignments.[Event Name])=[Forms]![frm All
Events]![Event Name]) AND
((tblCrew_Event_Assignments.Position)=[Forms]![frmEvent_Part1_Details]![Audio
Position 1 Description Combo]));
=IS NOT NULL
SELECT tblCrew_Event_Assignments.Name, tblCrew_Event_Assignments.[Call Number]
FROM tblCrew_Room_Assignments, tblCrew_Event_Assignments
GROUP BY tblCrew_Event_Assignments.Name, tblCrew_Event_Assignments.[Call
Number], tblCrew_Event_Assignments.[Event Name],
tblCrew_Event_Assignments.Position
HAVING (((tblCrew_Event_Assignments.[Event Name])=[Forms]![frm All
Events]![Event Name]) AND
((tblCrew_Event_Assignments.Position)=[Forms]![frmEvent_Part1_Details]![Audio
Position 1 Description Combo]));)
(Iff tblCrew_Event_Assignments.Name, tblCrew_Event_Assignments.[Call Number]
FROM tblCrew_Room_Assignments, tblCrew_Event_Assignments
GROUP BY tblCrew_Event_Assignments.Name, tblCrew_Event_Assignments.[Call
Number], tblCrew_Event_Assignments.[Event Name],
tblCrew_Event_Assignments.Position
HAVING (((tblCrew_Event_Assignments.[Event Name])=[Forms]![frm All
Events]![Event Name]) AND
((tblCrew_Event_Assignments.Position)=[Forms]![frmEvent_Part1_Details]![Audio
Position 1 Description Combo]));
=IS NULL
THEN
SELECT tblCrew_Room_Assignments.Name, tblCrew_Room_Assignments.[Call Number]
FROM tblCrew_Room_Assignments
GROUP BY tblCrew_Room_Assignments.Name, tblCrew_Room_Assignments.[Call
Number], tblCrew_Room_Assignments.[Room Name],
tblCrew_Room_Assignments.Position
HAVING (((tblCrew_Room_Assignments.[Room Name])=[Forms]![frm All
Events]![Part 1 Location]) AND
((tblCrew_Room_Assignments.Position)=[Forms]![frmEvent_Part1_Details]![Audio
Position 1 Description Combo]));)


Obviously this is wrong, but hopefully this helps to show you what I'm
trying to do.
Click to expand...
Click to expand...
 
J

Joshua McLemore

Just FYI for anyone with a similar issue in the future ...

To do this I ended up creating two queries. First was a union query:

SELECT 1 as Preference, tblCrew_Event_Matching.Name,
tblCrew_Event_Matching.[Event Call Order]

FROM tblCrew_Event_Matching

WHERE (tblCrew_Event_Matching.[Event Call Order]) Is NOT Null

AND tblCrew_Event_Matching.[Event Name]=[Forms]![frm All Events]![Event Name]

AND tblCrew_Event_Matching.[Event
Position]=[Forms]![frmEvent_Part1_Details]![Audio Position 1 Description
Combo]

UNION SELECT 2 as Preference, tblCrew_Event_Matching.Name,
tblCrew_Event_Matching.[Event Call Order]

FROM tblCrew_Event_Matching

WHERE tblCrew_Event_Matching.[Room Name]=[Forms]![frm All Events]![Part 1
Location]

AND tblCrew_Event_Matching.[Room
Position]=[Forms]![frmEvent_Part1_Details]![Audio Position 1 Description
Combo];


And the second was a query that I stuck in the row source of the combo box:

SELECT tblCrew_Event_Matching.Name AS Expr1, tblCrew_Event_Matching.[Event
Call Order] AS Expr2
FROM qryEventMatching_Part1_Audio1
WHERE (((qryEventMatching_Part1_Audio1.Preference)=(SELECT MIN(Preference)
FROM qryEventMatching_Part1_Audio1)))
ORDER BY tblCrew_Event_Matching.[Event Call Order];


Thanks for your help with this Steve.
--
Joshua


Joshua McLemore said:
Steve,

I quickly tried the macro and couldn’t get it to work. Beyond that, I have
about 35 of the fields per form and 7 forms which would all require their own
macro doing it this way. So using a macro for each would be a lot of macros!
Thanks so much for this though.

I have been working on a statement that I’m sticking in the combo’s row
source that isn’t quite right. But I’m trying to use the two queries that are
both working great.


1st query with conditions
SELECT (qryFindbyEvent_Matching) IF ((tblCrew_Event_Assignments.Name) Is Not
Null) AND ((tblCrew_Event_Assignments.[Event Name])=[Forms]![frm All
Events]![Event Name]) AND
((tblCrew_Event_Assignments.Position)=[Forms]![frmEvent_Part1_Details]![Audio
Position 1 Description Combo]));ELSE (qryFindbyRoom_Matching);
2nd query

Is this just not practical or is my noobness at SQL just shining through?

Thanks again.

--
Joshua


Steve Schapel said:
Jushua,

Assuming these two queries are actual saved queries in your database,
you can use a SetValue actions, with Conditions, in your macro, like this:

Condition: DCount("*","NameOfYourFirstQuery")>0
Action: SetValue
Item: Forms]![NameOfForm]![NameOfCombobox].[RowSource]
Expression: NameOfYourFirstQuery

Condition: ...
Action: StopMacro

Condition: <blank>
Action: SetValue
Item: Forms]![NameOfForm]![NameOfCombobox].[RowSource]
Expression: NameOfYourSecondQuery

Hope that makes sense and will point you in the right direction.

--
Steve Schapel, Microsoft Access MVP

Joshua said:
If this helps ... this is where I'm at on the statement:

SWITCH(Iff tblCrew_Event_Assignments.Name, tblCrew_Event_Assignments.[Call
Number]
FROM tblCrew_Room_Assignments, tblCrew_Event_Assignments
GROUP BY tblCrew_Event_Assignments.Name, tblCrew_Event_Assignments.[Call
Number], tblCrew_Event_Assignments.[Event Name],
tblCrew_Event_Assignments.Position
HAVING (((tblCrew_Event_Assignments.[Event Name])=[Forms]![frm All
Events]![Event Name]) AND
((tblCrew_Event_Assignments.Position)=[Forms]![frmEvent_Part1_Details]![Audio
Position 1 Description Combo]));
=IS NOT NULL
SELECT tblCrew_Event_Assignments.Name, tblCrew_Event_Assignments.[Call Number]
FROM tblCrew_Room_Assignments, tblCrew_Event_Assignments
GROUP BY tblCrew_Event_Assignments.Name, tblCrew_Event_Assignments.[Call
Number], tblCrew_Event_Assignments.[Event Name],
tblCrew_Event_Assignments.Position
HAVING (((tblCrew_Event_Assignments.[Event Name])=[Forms]![frm All
Events]![Event Name]) AND
((tblCrew_Event_Assignments.Position)=[Forms]![frmEvent_Part1_Details]![Audio
Position 1 Description Combo]));)
(Iff tblCrew_Event_Assignments.Name, tblCrew_Event_Assignments.[Call Number]
FROM tblCrew_Room_Assignments, tblCrew_Event_Assignments
GROUP BY tblCrew_Event_Assignments.Name, tblCrew_Event_Assignments.[Call
Number], tblCrew_Event_Assignments.[Event Name],
tblCrew_Event_Assignments.Position
HAVING (((tblCrew_Event_Assignments.[Event Name])=[Forms]![frm All
Events]![Event Name]) AND
((tblCrew_Event_Assignments.Position)=[Forms]![frmEvent_Part1_Details]![Audio
Position 1 Description Combo]));
=IS NULL
THEN
SELECT tblCrew_Room_Assignments.Name, tblCrew_Room_Assignments.[Call Number]
FROM tblCrew_Room_Assignments
GROUP BY tblCrew_Room_Assignments.Name, tblCrew_Room_Assignments.[Call
Number], tblCrew_Room_Assignments.[Room Name],
tblCrew_Room_Assignments.Position
HAVING (((tblCrew_Room_Assignments.[Room Name])=[Forms]![frm All
Events]![Part 1 Location]) AND
((tblCrew_Room_Assignments.Position)=[Forms]![frmEvent_Part1_Details]![Audio
Position 1 Description Combo]));)


Obviously this is wrong, but hopefully this helps to show you what I'm
trying to do.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access Determine what query to run based on a checkbox 0
Excel Lotto - How to find duplicate 6-number groups from result database? 2
Show query results in form 5
Run macro if query is not null 24
Condition If, Then, Else logic 6
using If,then, else statement in a macro 1
Controlling flow of actions 2
Re: Pass field value selected in Combo Box to SQL Query 1

Top