Export with Conditional 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

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.

 

[Kipp Woodard]

It is not clear what you are asking.

Can you post the basic Select statement, and then explain how the two output
sets would be different?

 

[Musa via AccessMonster.com]

HI,

The select statement is too long to include in the post, however, here is the
basic principle on which it is based. There are several variables that are
programmed to be manadatory, meaning they must meet certain criteria within
the access database before being exported into an excel file. If they do not
meet these criteria they are still housed within the access database, they
just do not get exported to the external excel file and then sent to me.

I am trying to come up with code that would allow these mandatory variables
to hinge on one variable-Refused. If Refused=1 meaning that someone refuses
to answer any further questions, then the mandatory variables can be null
(meaning no information needs to be entered into them to export) If Refused=0
meaning that someone agrees to answer further questions, then the prior
conditions must be met in order for that file to export. This way I can safe
guard against incomplete data in the export file.

I hope this helps to clear up the confusion, I appreciate you taking the time
to help.

It is not clear what you are asking.

Can you post the basic Select statement, and then explain how the two output
sets would be different?

Hello,

[quoted text clipped - 38 lines]

VBA code ?
I didn't think SQL supported IF THEN ELSE statements in MS ACCESS.

 

[Kipp Woodard]

So, is it correct to say that you are always going to export the same
columns, but sometimes you will force the value of certain columns to Null
base on the value of Refused?

If so, then you will do it collumn-by-column.

In the query designer, the Current_Living column would look like this:

CurLiving: IIF(refused_tx = 1, Null, [current_living])

In this example you are adding a calculated column named CurLiving. The
value of this calculated column will be Null if Refused_tx = 1, otherwise it
will be the value of the actual column named current_living.

HI,

The select statement is too long to include in the post, however, here is the
basic principle on which it is based. There are several variables that are
programmed to be manadatory, meaning they must meet certain criteria within
the access database before being exported into an excel file. If they do not
meet these criteria they are still housed within the access database, they
just do not get exported to the external excel file and then sent to me.

I am trying to come up with code that would allow these mandatory variables
to hinge on one variable-Refused. If Refused=1 meaning that someone refuses
to answer any further questions, then the mandatory variables can be null
(meaning no information needs to be entered into them to export) If Refused=0
meaning that someone agrees to answer further questions, then the prior
conditions must be met in order for that file to export. This way I can safe
guard against incomplete data in the export file.

I hope this helps to clear up the confusion, I appreciate you taking the time
to help.

It is not clear what you are asking.

Can you post the basic Select statement, and then explain how the two output
sets would be different?

Hello,

[quoted text clipped - 38 lines]

VBA code ?
I didn't think SQL supported IF THEN ELSE statements in MS ACCESS.

 

[Musa via AccessMonster.com]

I have added the columns like you suggested, however, I am getting 3 records
that should not be in the Export. The common theme these 3 records have 1)
Refused TX does not = 1 , 2) Current Living = Is not Null 3) Issues = 0
and 4) Outcomes = 0

I think the "0" in the Issues and Outcomes columns are being recognized as
Not Null ( an actual value).
How do I correct for this ?

This is the code of have below :
Issues: IIf([FUAF_refused]=1,0,[NumOfIssues])

The 2 columns should = 12 , in order to be Exported.

Thanks

So, is it correct to say that you are always going to export the same
columns, but sometimes you will force the value of certain columns to Null
base on the value of Refused?

If so, then you will do it collumn-by-column.

In the query designer, the Current_Living column would look like this:

CurLiving: IIF(refused_tx = 1, Null, [current_living])

In this example you are adding a calculated column named CurLiving. The
value of this calculated column will be Null if Refused_tx = 1, otherwise it
will be the value of the actual column named current_living.

HI,

[quoted text clipped - 26 lines]