Exporting yes/no to excel

[stuart_adair]

Hello all,
I've got a database running which has some text and some yes/no tick boxes. I want to export a repot to an excel file.
I've created the report and have a control button on a form that sends the report to the file. All works wonderfully apart from the fact that none of the yes/no fields are exported to excel.

Can anyone help ?
Thanks

Stu

 

[dkinley]

One option is based the report on a query if it is not. Then on your yes/no fields (column in the QBE), replace it with ...

x: IFF([Yes/NoFieldName] = -1, "Yes", "No")

Where x is the name you want the column to be in Excel. This will place a text value into your spreadsheet.

-dK

 

[Brianwarnock]

They should export with the values TRUE FALSE, or atleast they do from a query.

Brian

Edit He's right tho' they don't export from a report.!

 

[stuart_adair]

I'm making some progress and can get the data to export as -1 for yes adn 0 for no. As we want to use the data in excel -1 is no use. Is there any way I can change -1 for +1 ?

Stu

 

[dkinley]

One option is based the report on a query if it is not. Then on your yes/no fields (column in the QBE), replace it with ...

x: IFF([Yes/NoFieldName] = -1, "Yes", "No")

Where x is the name you want the column to be in Excel. This will place a text value into your spreadsheet.

-dK

Use ...

x: IFF([Yes/NoFieldName] = -1, "+1", "0")

-dK

 

[stuart_adair]

Thanks for the reply but I'm not an expert user and am not sure where to use this code.
Stu

 

[dkinley]

In the query builder. The fields have columns.

For more information on queries, look at ...

http://www.techonthenet.com/access/index.php

-dK

 

[stuart_adair]

Another approach, running this after update on the field called source_web

Gives me the error message Sub not defined.

Can someone tell me what I've done wrong ?
Stu



Private Sub Source_Web_AfterUpdate()
iff [Source_Web] = -1, 1, 0
End Sub

 

[dkinley]

Looking at it, you don't have any parenthesis for the IIF function. It seems you are making your own yes/no type? I don't think you can store a positive 1 in a yes/no field since Access isn't built that way.

-dK

 

[stuart_adair]

I think your right. I keep coming up against barriers when I try to store a + number. Another way I could do it would be to have a second field, X and say if source_web=-1 then set x to 1.

I know this code isnt right but hopefully you can follow my logic enough to guide me.
Thanks for your patience with an idiot.

Private Sub Source_Web_AfterUpdate()
iff ([Source_Web] = -1, [x]=1, [x]=0)
End Sub

 

[dkinley]

That looks about right .... perhaps with a

iff ([Source_Web] = -1, Me.[x]=1, Me.[x]=0)

However, just as a note, you are storing data that is already stored. The modification of a query I proposed would automatically do this for you on the export without the need of excess data storage.

Try this out for size ...

1) Create a select query. (Help: http://www.techonthenet.com/access/index.php)
2) Drag every field in the table into the query.
3) Modify the yes/no columns. In the column with "Source_Web" in it, copy and paste the following over it.

Source Web: IFF([Source_Web] = -1, 1, 0)

4) Save the query. Let's call it qryExportToExcel
5) Create a button on a form.
6) Put the following code behind the button.

    Dim sQryName As String
    Dim strFilter As String
 
    sQryName = "qryExportToExcel"
    DoCmd.OutputTo acQuery, sQryName , acFormatXLS

7) Click the button, it should prompt you on where to save the .xls file.
8) Check for accuracy, etc ....

-dK

 

[Brianwarnock]

Why not in your query or report have a field
Newfld: oldfield*-1

Brian

 

[stuart_adair]

Thank you everyone for your advice and I'll let you know how I get on

 

[dkinley]

Why not in your query or report have a field
Newfld: oldfield*-1

Brian

Oh man ... clever. Very good option!

Just wondering if a * - 1 is more optimized than an IFF or vice-versa. I guess the real question considering is .. does it really matter?

Either way, very nice tip - very compact!

-dK

 

[Brianwarnock]

Thanks DK , that makes me feel good, but I think that I just happened along when the requirements were clear and didn't get side tracked into fighting bush fires.

Brian