Update on field based on another

[iankerry]

Hi

I have inherited a massive excel spreadsheet which i have taken into access.

one field called category, has text in it e.g. "Sussex; Shropshire; Promoter;".

I have created three tick boxes called sussex and shropshire and promoter.

I have been playing with an update query but havn't has success. I want to search the category field and if "sussex" is found in the field then the new sussex field, should be true.

Can anyone help?

Thanks

Ian

 

[Brianwarnock]

The Update to field will be

IIf(InStr([category],"sussex")>0,True,False)

Brian

 

[iankerry]

Hi Brian

thanks for responding so quickly - that solutino seems to work.

As I have to do about 15 of these (which i can do of course) I just wondered if there was a way of looping through the records searching for different text each time in order to update the appropriate fields?

cheers
ian

 

[Brianwarnock]

I must assume that all of the 15 fields that you intend to update are different, therefore do it all in 1 query.

Brian

 

[iankerry]

Ah but i dont have a clue how to make the code you gave for one, loop through all the other answers.

but no worries i'll do it one by one.

thanks for your answer
ian

 

[Brianwarnock]

You drag all of the fields to be updated onto the design grid and place the relevent update info into each update to field

Your SQL ends uplooking like
UPDATE Table1 SET Table1.sussextickbox = IIf(InStr([category],"sussex")>0,True,False), Table1.surreytickbox = IIf(InStr([category],"surrey")>0,True,False) etc etc;

Brian

 

[iankerry]

thanks very much Brian.

 

[Brianwarnock]

No problem, sorry for delay in response lunch got in the way.

Brian

 

[iankerry]

Ah lunch, yes i really should stop...