Data mismatch when importing from Excel

[bradp1979]

I have a form online, that, when filled out, is sent to me in an Excel spreadsheet. The columns of the Excel Spreadsheet match up with the columns of a table in my DB. I plan to import the data from Excel into my DB, but several columns in the table on the DB have checkboxes, which correspond to a form(frmEmployee). The online form has the same checkboxes. However, the Excel spreadsheet does not. Instead, if the individual checked the box online, the spreadsheet indicates it with a "1".



Can I tell the database, when it's reading that information for import, to check the corresponding box if there's a "1" in the Spreadsheet?

Maybe something like "IIf (IsNotNull([fieldname]), yes)" or something like that?

[June7]

'fraid not - not with the import wizard. Access True is -1, anything else means False.

Can you set a link to the spreadsheet? If so, can build a query of the linked sheet that converts the 1 to -1 and base an INSERT SELECT sql action on that.

Otherwise, probably better clean up the spreadsheet before import.

[bradp1979]

It'd be easy to clean the spreadsheet before import. I can just do a find and replace, and replace all "1" with -1. It'd be the only instance of a number.

So when doing the import, if it sees -1, it'll fill in the checkbox?

[ssanfu]

Access True is -1, anything else means False.

In Excel, Microsoft has defined a constant TRUE as 1 and in Access, TRUE is defined as -1. In both Excel and Access the constant FALSE is defined as 0.

In reality, FALSE = 0 and Not FALSE = TRUE.
So that means FALSE = 0 and any other number, positive or negative, is TRUE.

I have built forms and reports using the above to show if an option is selected/available or not.

See attached dB...

[June7]

Okay, guess I had it backwards. In which case 1 should import as True but you say that is not happening? Or have you not tested import yet?