IF ELSEIF ELSE Statements in Access

[nameuser321]

I have a particular query that requires me to INNER JOIN when a records EXIST in a a query but not JOIN if they don't. I was wondering if this was possible by writing it in SQL or if I was forced to write it in VBA. If VBA is required how would I pull the resulting record set into excel. I know how to do this by just calling the query, but not if the query is run from an internal module in access.

Code should look something like:

IF condition
SELECT *
FROM table1
INNER JOIN table2
ON table1.a = table2.a
Else
SELECT *
FROM table1

Thank you in advance for assistance.

[ranman256]

I would do a DLOOKUP to see if the record exists, if so, do a TransferSpreadsheet
this would be a 1 line macro using the condition field with:
not IsNull(Dlookup(fld,tbl,where)
then the action field = TransferSpreadsheet

or
2 lines in a vba function/sub.

[HiTechCoach]

I would use a saved query even with your VBA code.

There are two ways I would handle this:

1) First choice and very easy to implement is to use a single query with an outer join.

This may also help: Microsoft Access Outer Join Query: Finding All Records in One Table but Not Another and Creating "Not In" Queries (Click Here) (http://www.fmsinc.com/MicrosoftAccess/query/outer-join/)


The same query would then for both conditions.


2) Requires some VBA coding using the QueryDef() collection. I would still use a single saved query. I would use VBA code to modify the SQL SELECT statement in the same query.

Both methods uses a single saved query. This allow all the other steps in the process to not have to be modified.

FWIW: When exporting to a Excel worksheet, I find that only exporting tables have less issues. In your case I would create a new query based on the above saved query. This new query would be an append or make table query to save the records into a table. The append or make table query could all be done in VBA code. The table would then be exported to excel.

[XopieX20]

Try this


IF condition
SELECT *
FROM table1
INNER JOIN table2
ON table1.a = table2.a
End Code
ElseIf
SELECT *
FROM table1
End Code