SQL newbie needs help creating a query that builds a conditional statement

[SGT68]

I'm inserting a new field into a large table and the value of that field is going to be either TRUE or FALSE depending on a value of an adjacent field that already exists... The new field defaults to FALSE but there are several hundred records where it needs to TRUE.

Can i get SQL to handle this for me? I'm need something that goes like:

SELECT * FROM mytable and if mytable.fieldX = 'this_value' THEN mytable.newfield = 'TRUE';

Help would be much appreciated.

 

[KenHigg]

I would suggest not adding the field and derive the data in the format you need on the fly in your app...

 

[SGT68]

I would suggest not adding the field and derive the data in the format you need on the fly in your app...

Thanks Ken, but i have just managed to solve it by sticking in an IIF() to the Access Query Designer.

 

[KenHigg]

Cool - Glad you have it working. How will you populate this for new records and how will you make sure the two fields stayed sync'd up?

 

[SGT68]

Cool - Glad you have it working. How will you populate this for new records and how will you make sure the two fields stayed sync'd up?

I'm doing a facelift on someones old database. There was a field that accepted Y or N or blank in a text field. I'm replacing that field with a YesOrNo field which on the form will have a check box, simply cos i prefer check boxes. So the old text field is going to be deleted once its Y or N values have been passed as TRUE or FALSE into the new field. (I found out that Yes/No check box fields hold their raw data as TRUE or FALSE).

So i created the query with the IIF(),

ListAddressYN: IIf([ListAddress]='Y','TRUE')

ran it, then cut and pasted the ListAddressYN column from the query into the table.

 

[KenHigg]

Cool - sounds like a good plan, sorry that I mis-understood what you were doing

 

[Frothingslosh]

I'm doing a facelift on someones old database. There was a field that accepted Y or N or blank in a text field. I'm replacing that field with a YesOrNo field which on the form will have a check box, simply cos i prefer check boxes. So the old text field is going to be deleted once its Y or N values have been passed as TRUE or FALSE into the new field. (I found out that Yes/No check box fields hold their raw data as TRUE or FALSE).

So i created the query with the IIF(),

ListAddressYN: IIf([ListAddress]='Y','TRUE')

ran it, then cut and pasted the ListAddressYN column from the query into the table.

Actually, believe it or not, boolean fields hold their data as either 0 (for FALSE/NO), or -1 (for TRUE in Access). I've seen a few pieces of code around here that used that particular fact for some creative condition and error checking.

 

[SGT68]

Actually, believe it or not, boolean fields hold their data as either 0 (for FALSE/NO), or -1 (for TRUE in Access). I've seen a few pieces of code around here that used that particular fact for some creative condition and error checking.

Well that makes sense, will bear in mind . Thanks

 

[SGT68]

Cool - sounds like a good plan, sorry that I mis-understood what you were doing

I did not explain that the old field was to be deleted, no need for apol.. Your subsequent questions meant you were thinking like a programmer!