validation rule

[Laoballer]

is there a way I can set a rule in an access field where if a field is
entered or imported with a certain value, the value stored is actually
an arbitrary value? For example

if the user enters or imports 1 the data that gets stored is actually
100-199
2 entered stores 200-299
3 entered stores 300-299.

I would like to do this using rules because data will be imported from
a text file.

The reason I would like to do this is because some users send in files
that contains either a range 200-299 which is the correct data while
others will send in 2 which is the ID for 200-299.

I hope I'm stating my question clearly enough.

Thanks,

 

[Ken Sheridan]

For imported data execute an update query after the import:

UPDATE [YourTable]
SET [YourField] =
SWITCH(LEN([YourField]) > 1, [YourField],
[YourField] = "1","100-199",
[YourField] = "2","200-299",
[YourField] = "3","300-399");

For data entry put the following in the AfterUpdate event procedure of the
control bound to the field:

[YourField] = _
Switch(Len([YourField]) > 1, [YourField], _
[YourField] = "1","100-199", _
[YourField] = "2","200-299", _
[YourField] = "3","300-399")

Ken Sheridan
Stafford, England

 

[Laoballer]

For imported data execute an update query after the import:

UPDATE [YourTable]
SET [YourField] =
SWITCH(LEN([YourField]) > 1, [YourField],
[YourField] = "1","100-199",
[YourField] = "2","200-299",
[YourField] = "3","300-399");

For data entry put the following in the AfterUpdate event procedure of the
control bound to the field:

[YourField] = _
    Switch(Len([YourField]) > 1, [YourField], _
    [YourField] = "1","100-199", _
    [YourField] = "2","200-299", _
    [YourField] = "3","300-399")

Ken Sheridan
Stafford, England

is there a way I can set a rule in an access field where if a field is
entered or imported with a certain value, the value stored is actually
an arbitrary value?  For example

if the user enters or imports 1 the data that gets stored is actually
100-199
2 entered stores 200-299
3 entered stores 300-299.

I would like to do this using rules because data will be imported from
a text file.

The reason I would like to do this is because some users send in files
that contains either a range 200-299 which is the correct data while
others will send in 2 which is the ID for 200-299.

I hope I'm stating my question clearly enough.

Thanks,

I tried your suggestion but reversing the way fields are changed. if
fields contain 100-199 it gets changed to 1 and if it contains 200-299
it gets changed to 2. The fields that contains the values I want
change do change but fields that already contain the correct data gets
deleted. This is the query I used.

UPDATE

SET [field] =
SWITCH(
[field] = "200-299 kV","1",
[field] = "300-399 kV","2",
[field] = "500-599 kV","3",
[field] = "600-799 kV", "4");

 

[Ken Sheridan]

You can update only those rows where the value ends with 'kV' with:

UPDATE

SET [field] =
SWITCH(
[field] = "200-299 kV","1",
[field] = "300-399 kV","2",
[field] = "500-599 kV","3",
[field] = "600-799 kV", "4")
WHERE RIGHT([field],2) ="kV";

I assume that the single character values are values of a foreign key
referencing the primary key of another table with one row per range – if not
they should be as it allows referential integrity to be enforced and excludes
potential update anomalies. Rather than calling the Switch function in the
query you could join the tables:

UPDATE

INNER JOIN [table_of_ranges]
ON [table_of_ranges].[range] =

.[field]
SET

.[field] = [table_of_ranges].[field];

where range is the column in the referenced table table_of_ranges with the
values 200-299 kV etc. Only those rows with matching values will be joined,
so as the join is on the foreign key to the non-key text column of
table_of_ranges in this case, only those rows in table with the incorrect
values will be updated. This is a far better approach as it does not require
you to hard-code data in the SQL statement. Hard-coding data in SQL or VBA
code is always best avoided as you have to maintain the code or SQL if, for
instance, the parameters of the ranges are changed.

Ken Sheridan
Stafford, England

For imported data execute an update query after the import:

UPDATE [YourTable]
SET [YourField] =
SWITCH(LEN([YourField]) > 1, [YourField],
[YourField] = "1","100-199",
[YourField] = "2","200-299",
[YourField] = "3","300-399");

For data entry put the following in the AfterUpdate event procedure of the
control bound to the field:

[YourField] = _
Switch(Len([YourField]) > 1, [YourField], _
[YourField] = "1","100-199", _
[YourField] = "2","200-299", _
[YourField] = "3","300-399")

Ken Sheridan
Stafford, England

is there a way I can set a rule in an access field where if a field is
entered or imported with a certain value, the value stored is actually
an arbitrary value? For example

if the user enters or imports 1 the data that gets stored is actually
100-199
2 entered stores 200-299
3 entered stores 300-299.

I would like to do this using rules because data will be imported from
a text file.

The reason I would like to do this is because some users send in files
that contains either a range 200-299 which is the correct data while
others will send in 2 which is the ID for 200-299.

I hope I'm stating my question clearly enough.

Thanks,

I tried your suggestion but reversing the way fields are changed. if
fields contain 100-199 it gets changed to 1 and if it contains 200-299
it gets changed to 2. The fields that contains the values I want
change do change but fields that already contain the correct data gets
deleted. This is the query I used.

UPDATE

SET [field] =
SWITCH(
[field] = "200-299 kV","1",
[field] = "300-399 kV","2",
[field] = "500-599 kV","3",
[field] = "600-799 kV", "4");