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)


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