Change value

[mischa]

I would like to know how I can achieve the following (and if it is achievable by only using queries or vba has to be used).

I have an append query, one of the fields (lets call it 'A')it appends is a value.
The value of A is 0 or -1.

When A is -1 and B (which is a string) complies to a prespecified string (only the last part of the string has to comply). For example from the string 'autumn to spring' only 'to spring' has to be met.

If these conditions are met I would like that the query changes the value of A to -2.

Thanks in advance !!

 

[namliam]

Should be quite easy to do with an update query... and an update query alone, no VBA needed.

 

[pr2-eugin]

mischa, this sounds to me like a calculated String. There is no need to store this information in Column B. Instead Create a new table (say tblSomething) then add two columns to it. negID (Number, PK), someDesc (Text). So your table will be.

tblSomething
negID    someDesc
-1       Summer To Autumn
-2       Autumn To Winter
0        Winter To Monsoon

When you need to display the Description, all you need to do is JOIN the tblSomething with the actual table that has column A.

 

[mischa]

Should be quite easy to do with an update query... and an update query alone, no VBA needed.

Didn't think of that option:banghead:, but I can run it after my append query. I'll give it a try. Hope it works.

mischa, this sounds to me like a calculated String. There is no need to store this information in Column B. Instead Create a new table (say tblSomething) then add two columns to it. negID (Number, PK), someDesc (Text). So your table will be.

This would not be possible because in my database I have an almost infinite amount of options which could stand before the 'to'. I used 'to spring' as an example but this is not applicable to my situation.

 

[pr2-eugin]

This would not be possible because in my database I have an almost infinite amount of options which could stand before the 'to'. I used 'to spring' as an example but this is not applicable to my situation.

So you will be writing Infinite amount of UPDATE queries? Storing values in tables and using them is what databases are all about ! If you want to hard code them and run every time a new value pops in, its just an hassle ! Good Luck !

 

[mischa]

So you will be writing Infinite amount of UPDATE queries? Storing values in tables and using them is what databases are all about ! If you want to hard code them and run every time a new value pops in, its just an hassle ! Good Luck !

No that would be a to big of a hassle.
I just used a criteria that filters all and used it in the update query.
It now works perfectly.

Thank you Namliam and Paul!