how do i programmatically apply validation to a EXISTING table of

[Guest]

Hi,

havent used access for ages, am trying to write a macro or query which will
allow me to apply some simple field length tests to fields and then indicate
somehow which records are over length. I want it to validate the whole table
in one sweep, rather than field by field and I need to be able to see and
edit all the invalid records, with something indicating which field was
invalid.

The column headers will be the same every time so I can include them in the
validation rules - i.e. URN < 13 (URN must be less than 13 so display all
records containing URN >12 and indicate that the URN is wrong somewhere -
presumably in another field).

I can do all this in excel easily but am not at all familiar with access so
dont know where to start or how VBA works differently in that to Excel

 

[Guest]

Add an output field to the design view grid like this --
Expr1: Len([URN])
Sort it descinding.

 

[John Spencer]

Add a field to the grid

Field: BadURN: IIF(Len([URN])>12,"XXX",Null)
Criteria (line 1): is Not Null
Criteria (line 2): <<Leave Blank>>

You can do that for multiple fields, but you will have to stair step the
criteria so that you are doing an OR for each criteria

Field: BadOther: IIF(Len([OtherField])>12,"XXX",Null)
Criteria (line 1): <<leave blank>>
Criteria (line 2): is Not Null

Etcetera.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[John W. Vinson]

Hi,

havent used access for ages, am trying to write a macro or query which will
allow me to apply some simple field length tests to fields and then indicate
somehow which records are over length. I want it to validate the whole table
in one sweep, rather than field by field and I need to be able to see and
edit all the invalid records, with something indicating which field was
invalid.

The column headers will be the same every time so I can include them in the
validation rules - i.e. URN < 13 (URN must be less than 13 so display all
records containing URN >12 and indicate that the URN is wrong somewhere -
presumably in another field).

I can do all this in excel easily but am not at all familiar with access so
dont know where to start or how VBA works differently in that to Excel

Neither VBA nor a macro is needed; you can do this in a Query.

Create a query based on your table. Select whichever fields you want to edit
(so you can see the field's value); next to each field in the query grid put a
calculated field

URNLen: Len([URN])

and similarly for the other fields that you want to check.

Put a criterion

12

on the first Criteria row under this field; put similar criteria under all the
other Len fields, on *separate rows* of the query grid so that it will use OR
logic, returning the record if any one of the criteria is true.


John W. Vinson [MVP]

 

[Guest]

Hello all, thanks for the answers.

The problem I have is that I dont know how to use a prewritten query to
validate the data on a table since you cant append to a query. And I cant
append to a table because that wont let me put in len() as a column.

I mean, without having to add columns containing the length of each column
in the table each time I want to validate data, how can I quickly apply the
validation rules to an exisiting table of data? As I say, field names will be
standard and the validation rules will be standard.

Please help either explain how I can apply the validation without using
macros as has been suggested in one go, or how to construct a macro to add
the columns to a table programmatically.

Thanks,

hope that makes sense.

Hi,

havent used access for ages, am trying to write a macro or query which will
allow me to apply some simple field length tests to fields and then indicate
somehow which records are over length. I want it to validate the whole table
in one sweep, rather than field by field and I need to be able to see and
edit all the invalid records, with something indicating which field was
invalid.

The column headers will be the same every time so I can include them in the
validation rules - i.e. URN < 13 (URN must be less than 13 so display all
records containing URN >12 and indicate that the URN is wrong somewhere -
presumably in another field).

I can do all this in excel easily but am not at all familiar with access so
dont know where to start or how VBA works differently in that to Excel

Neither VBA nor a macro is needed; you can do this in a Query.

Create a query based on your table. Select whichever fields you want to edit
(so you can see the field's value); next to each field in the query grid put a
calculated field

URNLen: Len([URN])

and similarly for the other fields that you want to check.

Put a criterion

12

on the first Criteria row under this field; put similar criteria under all the
other Len fields, on *separate rows* of the query grid so that it will use OR
logic, returning the record if any one of the criteria is true.


John W. Vinson [MVP]

 

[John W. Vinson]

Hello all, thanks for the answers.

The problem I have is that I dont know how to use a prewritten query to
validate the data on a table since you cant append to a query. And I cant
append to a table because that wont let me put in len() as a column.

I mean, without having to add columns containing the length of each column
in the table each time I want to validate data, how can I quickly apply the
validation rules to an exisiting table of data? As I say, field names will be
standard and the validation rules will be standard.

Please help either explain how I can apply the validation without using
macros as has been suggested in one go, or how to construct a macro to add
the columns to a table programmatically.

Thanks,

hope that makes sense.

I'm sorry, no, it doesn't make sense to me!

Of course you can append to a query. Standard practice.
There's no need to append to a table.

If the fieldnames and validation rules are standard, just create a Query with
the calculated fields for the length of the fields and validation rules on
those calculated values. Open the query. It will show you the records with
errors. You HAVE to have a query with Len() functions in it in order to do
this at all because that's what you're trying to validate!

John W. Vinson [MVP]