EXPORT with IF Statement

[Musa via AccessMonster.com]

Hello,

I would like to EXPORT data based on a query, however, the query has to be
capable of exporting in either case based on one variable "refused_tx" ( 1 =
true and 0= False).
My problem is trying to find a way to place this code in the query and have
it successfully export from this query (SEE Export Code Below). Perhaps SQL .
.?

The basic construct is this using : IF -THEN -ELSE statement
IF refused_tx = 1 THEN
current_living = IS NULL
issues = 0
services = 0
outcome = 0
fname = IS NULL
lname = IS NULL
complete_dt = IS NULL
ELSE IF
refused_tx = 0 THEN
current_living = IS NOT NULL
issues = 12
services = >0
outcome = >0
fname = IS NOT NULL
lname = IS NOT NULL
complete_dt = IS NOT NULL
END IF

Here is the EXPORT code:

Private Sub CMDexport_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Followupexp",
"C:\MYDATA" & Year(Date) & Format(Month(Date), "00") & Format(Day(Date), "00")

MsgBox ("You have sucessfully exported MY DATA to your C: Drive")
End Sub

Thanks

 

[Michael J. Strickland]

Assuming "refused_tx" is a field you can make a query with one set of
conditions in the first row and the other set of conditions in the
second row. In this case the query will select both sets of records.

To use 2 different sets of query criteria based on the value of the
refused_tx" field, I think you need 2 separate queries.



If "refused_tx" is a variable which doesn't change for each record, and
you want to use the two different condition sets based on its value,
then I think you need to create a different query (in code) based on its
value.

For example:

'----------------------------
Dim qry As QueryDef
Dim strSQL as string
Dim strQueryName as string
Dim strTableName as String

If refused_tx = 1 Then
strSQL = "SELECT * FROM " & "[" & strTableName & "]" & " WHERE _ "
& " current_living is Null " _
& " And issues = 0" _
& ..."


Else
strSQL = "SELECT * FROM " & "[" & strTableName & "]" & " WHERE _ "
& " current_living is Not Null " _
& " And issues = 12" _
& ..."

Endif

Set qry = dbs.CreateQueryDef(strQueryName, strSQL)
'---------------------------------

I have not tested this code and you will have to add the other
conditions
in the ... sections.

 

[Musa via AccessMonster.com]

Will this code work in SQL ? Or, does it need to be placed somewhere in the
VBA code ?
I didn't think SQL supported IF THEN ELSE statements in MS ACCESS.

Assuming "refused_tx" is a field you can make a query with one set of
conditions in the first row and the other set of conditions in the
second row. In this case the query will select both sets of records.

To use 2 different sets of query criteria based on the value of the
refused_tx" field, I think you need 2 separate queries.

If "refused_tx" is a variable which doesn't change for each record, and
you want to use the two different condition sets based on its value,
then I think you need to create a different query (in code) based on its
value.

For example:

'----------------------------
Dim qry As QueryDef
Dim strSQL as string
Dim strQueryName as string
Dim strTableName as String

If refused_tx = 1 Then
strSQL = "SELECT * FROM " & "[" & strTableName & "]" & " WHERE _ "
& " current_living is Null " _
& " And issues = 0" _
& ..."

Else
strSQL = "SELECT * FROM " & "[" & strTableName & "]" & " WHERE _ "
& " current_living is Not Null " _
& " And issues = 12" _
& ..."

Endif

Set qry = dbs.CreateQueryDef(strQueryName, strSQL)
'---------------------------------

I have not tested this code and you will have to add the other
conditions
in the ... sections.

Hello,

[quoted text clipped - 41 lines]

Thanks