Reject imported data based on an input mask

[Guest]

I'm trying to reject any data imported from a text file that doesn't match an
input mask. I'd also like to see the rejected records in a separate table.
I did an append query into a copy of the table structure with the input mask
applied, but it appended all records regardless.
I achieved this for data rejected with a validation rule and then did an
find unmatched query.
Ideas?

 

[John Nurick]

Input masks only apply to keyboard input. If possible, modify your
validation rules so they only accept things that would fit the mask.

If the validation is too complicated to implement using Access-style
validation rules, there are various possible approaches, including

1) Mug up Jet SQL's DDL (Data Description Language), in particular the
CONSTRAINT clause. In skilled hands this can do things that can't be
done using the validation rule syntax; if you search these newsgroups
for
"jamie collins" OR onedaywhen constraint
you'll find examples.

2) Create a VBA custom function that examines its input and returns True
or False depending on whether it would fit the mask. Use this function
in a criterion in your append query. See
http://www.mvps.org/access/modules/mdl0063.htm for a function that can
be used in this way.

 

[Guest]

Thanks.
I used a LIKE validation and it worked. DDL and VBA seem a bit too tricky
for me.
Jim