Update Query from -1 to 0 (1 Viewer)

[SteveE]

Could anyone offer advise in the best way to update the below:
I have a 135000 line text file which I receive monthly, in the data there are 8 Yes/No fields which come across as -1 or 0. When I import the data they are the wrong way a-round my Yes are No and No are Yes this appears to be because of the way the data is presented ie "No 48 Hour Service Available 1/0 (1 = Not available)" I need to be able to update in either a query or function to swop around these values.

 

[Bilbo_Baggins_Esq]

Is it mandatory that you change the values?
You could just work with them in reverse...

otherwise, just write an update query which looks at the existing values of each field and updates to the opposite?

In the "update to:" of the query design, use this:
iif([field]=0,-1,0)

This assumes the ONLY possible valuse are indeed 0 and -1

 

[SteveE]

Thanks for the reply, I have to change the values as this data is re-subimtted to another 3rd party for import into their distribution programming. I can do the If / Then replace option, but once a fields value has been changed i.e. from Yes to No, then all the values of that field will = No, so how would I change only thoese values that WERE No to Yes ?

Steve

 

[Bilbo_Baggins_Esq]

The if / Then will look to check the existing value, if it is 0, it will make it -1. If the existing value is not 0, it must be -1, so then it will make it 0.

iif([field]=0,-1,0)

 

[SteveE]

OK fully understand now thanks for your patience

Steve

 

[krishan.chawla]

can anyone give me some examples of update query with OR & AND conditions and with more complex situations.


I want to update

if "A" column contains like "executive*" or "match*" and then check "B" column not like "fwp*" or "fwt*"

and then update "B" columns values to "FWT"

please guide me...

 

[Brianwarnock]

Your SQL will be

UPDATE Table1 SET Table1.B = "FWT"
WHERE (((Table1.A) Like "executive*" Or (Table1.A) Like "match*") AND ((Table1.B) Not Like "fwt*" And (Table1.B) Not Like "fwp*"));

To do this in the design grid select the two columns and code the relevent criteria in the relevent column.
switch to update query and code the "FWT" in the field update to of col B

Brian

 

[krishan.chawla]

Thanks a lot Brianwarnock . I applied the same and now my problem has been resolved..

thanks againg. Do u have any e-book of VBA writing with complete Elaboration and detailed examples.

I need it.. Please help me if you have. or any book on access or office module writing.

 

[gemma-the-husky]

personally i wouldnt change imported data - i would add new fields for what you need for your output and populate them accordingly

i just feel it is clearer to see what is going on, than if you modfiy imported data

 

[Brianwarnock]

Gemma has a point, although I would not draw a distinction beteen imported and any other data. I feel that nay mass updating must be done with careful thought, and a regression option, but some simple updates can be done in place.

For the the question posed in the original part of the thread I agree with Gemma, but Krishan's seems straight foreward enough to do in place.
Recently in another thread the poster was being given advice to change a text date to a proper date in situ. , what a potential nightmare scenario.

Brian

 

[gemma-the-husky]

i would tend not to change the imported data, because it ensures you have traceability - if you overrwrite imported data, just to reverse it, its not so bad - but if you selectively start changing some records its easy to lose track of the original position