Exporting Access Yes-No Fields

[Lainie]

So I have this Access query I've built that includes Yes-No fields. I need to export the list to Excel for review, editing, etc. I know from experience that the Yes-No fields will export into Excel as 0s and -1s. I don't want that. I want the Yeses to be little Xs and the Nos to be empty cells.

So far, I've thought of two ways around this, both annoying although not a huge amount of work:

1. In Access, before exporting, create another query, with formulas that convert the Yeses and Nos appropriately.

2. In Excel, after exporting, do a Search/Replace, replacing 0s with nothing and -1s with Xs. This one seems like less work.

Any other ideas?

[ganzfeld]

Originally Posted by Lainie

2. In Excel, after exporting, do a Search/Replace, replacing 0s with nothing and -1s with Xs. This one seems like less work.

This is what I'd do. You can write a little formula in Excel to do it but, unless I were going to be doing it dozens of times over the next few weeks, why bother.

[Lainie]

Yeah, I probably wouldn't bother writing a formula. Although I may, in fact, be doing it a number of times over the next few weeks or months, now that I think about it. I'll also have to take changes made in Excel by other users and somehow record them in the Access database.

It's an interesting project.I'm trying to determine appropriate user security levels in a new system, based on those same users' security levels in a legacy system. The challenge is that the security architecture of the two systems is radically different. We've also changed job structures and responsibilities recently, and the legacy system's security levels are still somewhat based on the old structures and responsibilities.

[Ducky]

I wonder if you can set up some sort of macro to convert the 0s and -1s. So when you import from Access you then can just highligiht the column then hit the macro button.

That may or may not be the same idea as the formula idea already mentioned.

I thought of that because I once did a spread sheet where I wanted the addresses all in Caps. I was able to find a code snippt for a macro to convert lowercase to uppercase. Every time after that I entered addresses in lower case then was able to just click the button to convert. Sure that was more work as I could have just hit the caps key but I generally have 3 different things going on on my computer and forget that I have the cap key on lol.

[Dactyl]

Originally Posted by Lainie

So I have this Access query I've built that includes Yes-No fields. I need to export the list to Excel for review, editing, etc. I know from experience that the Yes-No fields will export into Excel as 0s and -1s. I don't want that. I want the Yeses to be little Xs and the Nos to be empty cells.

So far, I've thought of two ways around this, both annoying although not a huge amount of work:

1. In Access, before exporting, create another query, with formulas that convert the Yeses and Nos appropriately.

2. In Excel, after exporting, do a Search/Replace, replacing 0s with nothing and -1s with Xs. This one seems like less work.

Any other ideas?

I would hide the -1 and 0 cells, add a new column next to it and put the following code into the new column:

=IF(A1=-1,"X","")

Where A1 is the cell reference for the imported information. You can then just copy this down as you would any other formula.

Example (without hiding the column):