Update Field 2 depending on Field 1 value

[kevinnaomi]

I have a database which contains a table with 2 fields.
Field 1 and Field 2.

I want to have field 2 update depending on the value in field 1.

e.g.
Field 1 is an IP address 11.22.33.44
I want Field 2 to be 33 (this is from 33 in field 1
If Field 1 is 11.22.34.55, then Field 2 should be 3.

Can this be done?

 

[KenReay]

Hi

You say

Field 1 is an IP address 11.22.33.44
I want Field 2 to be 33 (this is from 33 in field 1
If Field 1 is 11.22.34.55, then Field 2 should be 3.

should the last line say

If Field 1 is 11.22.34.55, then Field 2 should be 34.

In brief the answer is yes, with a bit of VBA code you could do this

but why?

You can derive field2 from field1 "on the fly" in a query using string manipulation functions such as left() right() mid() instr() correct database design would do it that way

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[PHV]

Anyway, as we are in the Access VBA forum, have a look at the Split function (ac2k or above).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kevinnaomi]

KenReay,
I have no idea how I should be doing it..
If your suggestion is the better way, then I`m in for that.
Will this way automatically update field 2, or do I have to run an update occassionally?
Any hints on how to code this would be appreciated.

Regards.

 

[KenReay]

The whole point of doing it "my" way is that there is no field2, therefore no need to update it, you make a query based upon the table containing field1, in that query you make a field field2 like so field2:mid(field1,7,2) or if you are using Access2k or later you could use Split as suggested by PHV. See help for syntax of that and the other functions I mentioned

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK