Text field to Yes/No

[CuriousSeabeeEA1]

Greetings,


I am a novice developer tasked with converting an Excel document into an AccessDB.
Goal: Convert Text Field containing “X” to “Yes/No” field.
The “X” was utilized to annotate the member(record) has completed that item.

E.g.
Has member completed training?
“X” = yes “ ” = no

I wish to convert the “X” (yes) “ “ (no) method of tracking data to a check box “Yes/No” field.

[Minty]

Access stores yes no fields as 0 (false) or -1 (true)

So in your table replace X with -1 and everything else with 0, then convert to a yes no data type.or import accordingly.

[CuriousSeabeeEA1]

I am working with over 500 records and 24 yes no questions per record.
In order to convert to -1 0
Excel function: If This”X” than “-1”; IfThis”” than “0”?

[Minty]

In Excel I would use

=IF(A1 ="x",-1,0)

Replacing A1 with the source cell

Or you could simply Press ctrl & H and do a global replace on whole cell contents in the range?

[Welshgasman]

You would not hold 24 boolean fields for the questions in one record in Access (as you do in Excel)?
So you could make the change as you 'normalize' the data?
Each question should be it's own record.

[Micron]

As soon as I read post 2 I thought the data layout would be the typical problem posed by Excel users and it looks like that is the case. @Curious, I suggest you read up on db normalization (or you will likely be here often because of the lack) and consider this as well
http://allenbrowne.com/NoYesNo.html

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Then there's must-reads on bad words to use, naming conventions, table lookup fields, multi value field, autonumbers if you want to make your journey less painful. I have links for those if you want them.

[CuriousSeabeeEA1]

Thank you all for the information!