IIF Statement

[eckert1961]

Hello,

In my Access 2003 database I have the following IIF statement in one of the
modules.

DoCmd.OpenQuery IIf(ans = vbYes, "qryGetMWF", "qryGetWF")

This works fine but I want to change it so that a 2nd query is run in both
conditions.

If True I want the following 2 queries run.

qryGetMWF and qryTrainingDatesMWF

If False.

qryGetWF and qryTrainingDatesWF

How does the IIF statement need to be changed? Thanks.

 

[Dirk Goldgar]

Hello,

In my Access 2003 database I have the following IIF statement in one of
the
modules.

DoCmd.OpenQuery IIf(ans = vbYes, "qryGetMWF", "qryGetWF")

This works fine but I want to change it so that a 2nd query is run in both
conditions.

If True I want the following 2 queries run.

qryGetMWF and qryTrainingDatesMWF

If False.

qryGetWF and qryTrainingDatesWF

How does the IIF statement need to be changed? Thanks.

Why not use an If ... Then ... Else block? As in:

If ans = vbYes Then
DoCmd.OpenQuery "qryGetMWF"
DoCmd.OpenQuery "qryTrainingDatesMWF"
Else
DoCmd.OpenQuery "qryGetWF "
DoCmd.OpenQuery "qryTrainingDatesWF"
End If

Given the query names you're using, in this case you can abstract it further
if you really want to, like this:

Dim strWhat As String

strWhat = IIf(ans = vbYes, "MWF", "WF")

DoCmd.OpenQuery "qryGet" & strWhat
DoCmd.OpenQuery "qryTrainingDates" & strWhat

But I'm not convince you gain all that much in doing so.

 

[eckert1961]

Hi Dirk,

I used your 1st suggestion and it works but I discovered an issue with my
2nd query that's executed in both conditions.

Both the qryTrainingDatesMWF and qryTrainingDatesWF are append queries. The
results are appended to tblTrainingDates. If I run the query multiple times
for the same month I will get duplicate entries in the table. Here is the SQL
statement from 1 of these queries.

INSERT INTO tblTrainingDatesWF ( pkClassDayID, fkClassesID, ClassDay,
EventLabel, [Month], [Year] )
SELECT tblClassDays.pkClassDayID, tblClassDays.fkClassesID,
tblClassDays.ClassDay, GetLabel([fkClassesID],[ClassDay]) AS EventLabel,
Month([ClassDay]) AS [Month], Year([ClassDay]) AS [Year]
FROM tblClassDays
WHERE (((GetLabel([fkClassesID],[ClassDay]))<>"Wknd" And
(GetLabel([fkClassesID],[ClassDay]))<>"Holiday" And
(GetLabel([fkClassesID],[ClassDay]))<>"Summer") AND
((Month([ClassDay]))=Month([Forms]![Attendance Form]![txtStartDate])) AND
((Year([ClassDay]))=Year([Forms]![Attendance Form]![txtStartDate])))
ORDER BY tblClassDays.pkClassDayID, Month([ClassDay]), Year([ClassDay]);

What would I need to change so that no duplicate entries are appended to the
table? Thanks.

 

[eckert1961]

I figured it out myself. I realized that I had the indexed field for the
ClassDay field name in tblTrainingDatesWF set to NO. Changing it to Yes(No
Duplicates) resolved the issue.
--
Regards,
Chris

Hi Dirk,

I used your 1st suggestion and it works but I discovered an issue with my
2nd query that's executed in both conditions.

Both the qryTrainingDatesMWF and qryTrainingDatesWF are append queries. The
results are appended to tblTrainingDates. If I run the query multiple times
for the same month I will get duplicate entries in the table. Here is the SQL
statement from 1 of these queries.

INSERT INTO tblTrainingDatesWF ( pkClassDayID, fkClassesID, ClassDay,
EventLabel, [Month], [Year] )
SELECT tblClassDays.pkClassDayID, tblClassDays.fkClassesID,
tblClassDays.ClassDay, GetLabel([fkClassesID],[ClassDay]) AS EventLabel,
Month([ClassDay]) AS [Month], Year([ClassDay]) AS [Year]
FROM tblClassDays
WHERE (((GetLabel([fkClassesID],[ClassDay]))<>"Wknd" And
(GetLabel([fkClassesID],[ClassDay]))<>"Holiday" And
(GetLabel([fkClassesID],[ClassDay]))<>"Summer") AND
((Month([ClassDay]))=Month([Forms]![Attendance Form]![txtStartDate])) AND
((Year([ClassDay]))=Year([Forms]![Attendance Form]![txtStartDate])))
ORDER BY tblClassDays.pkClassDayID, Month([ClassDay]), Year([ClassDay]);

What would I need to change so that no duplicate entries are appended to the
table? Thanks.
--
Regards,
Chris

Why not use an If ... Then ... Else block? As in:

If ans = vbYes Then
DoCmd.OpenQuery "qryGetMWF"
DoCmd.OpenQuery "qryTrainingDatesMWF"
Else
DoCmd.OpenQuery "qryGetWF "
DoCmd.OpenQuery "qryTrainingDatesWF"
End If

Given the query names you're using, in this case you can abstract it further
if you really want to, like this:

Dim strWhat As String

strWhat = IIf(ans = vbYes, "MWF", "WF")

DoCmd.OpenQuery "qryGet" & strWhat
DoCmd.OpenQuery "qryTrainingDates" & strWhat

But I'm not convince you gain all that much in doing so.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)