Importing data from a txt file and input masks

[Liz J]

Hi

I have a table that I have set up and I want the Customer ID to have an
input mask of LLL0000 (I think that is 3 mandatory letters followed by 4
mandatory numbers).

I set up the input mask and then I need to import data to the table from a
txt file. I am using the import text wizard for this that I find in File,
Get external data.

The data comes into the table fine but I need it to trap any Customer ID
that does not conform to the input mask and it is not doing so.

How can I set an input mask (or validation) that will trap any Customer IDs
that do not follow the correct pattern?

TIA

Liz

 

[Douglas J. Steele]

Input masks only work with forms, not importing data.

You'll have to write a query that goes against the data and identifies any
invalid data.

 

[Liz J]

Thanks for the reply Doug, much appreciated.

Is there nothing I can do in the validation setup that would trap this?

I need this for an assessment my students are doing at school and the sort
of query you mention would, I think, be out of their range.

TIA

Liz

 

[Douglas J. Steele]

The only validations that occur during import check that the values are
correct for the data type, and that they don't violate referential integrity
(if set).

It's a fairly simple query to check for invalid values, though:

WHERE CustomerID Not Like "[a-z][a-z][a-z][0-9][0-9][0-9][0-9]"

 

[Liz J]

I imported my data and the record with the incorrect CustomerID was present.
(FREO149 instead of FRE0149). I then set up a query on the customer table
with the criteria you suggested and ran the query but the record was not
found. The query returned no records.

Am I being particularly daft tonight?

Liz

The only validations that occur during import check that the values are
correct for the data type, and that they don't violate referential
integrity (if set).

It's a fairly simple query to check for invalid values, though:

WHERE CustomerID Not Like "[a-z][a-z][a-z][0-9][0-9][0-9][0-9]"


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks for the reply Doug, much appreciated.

Is there nothing I can do in the validation setup that would trap this?

I need this for an assessment my students are doing at school and the
sort of query you mention would, I think, be out of their range.

TIA

Liz

 

[Douglas J. Steele]

Couldn't tell you whether you're being daft <g>.

Double-check that you typed the WHERE statement correctly: I just tested on
your sample data, and that criteria returned FREO149 as not being
acceptable.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I imported my data and the record with the incorrect CustomerID was
present. (FREO149 instead of FRE0149). I then set up a query on the
customer table with the criteria you suggested and ran the query but the
record was not found. The query returned no records.

Am I being particularly daft tonight?

Liz

The only validations that occur during import check that the values are
correct for the data type, and that they don't violate referential
integrity (if set).

It's a fairly simple query to check for invalid values, though:

WHERE CustomerID Not Like "[a-z][a-z][a-z][0-9][0-9][0-9][0-9]"


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks for the reply Doug, much appreciated.

Is there nothing I can do in the validation setup that would trap this?

I need this for an assessment my students are doing at school and the
sort of query you mention would, I think, be out of their range.

TIA

Liz


Input masks only work with forms, not importing data.

You'll have to write a query that goes against the data and identifies
any invalid data.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


"Liz J" <thejordanschangethistoanatsignclara.net> wrote in message
Hi

I have a table that I have set up and I want the Customer ID to have
an input mask of LLL0000 (I think that is 3 mandatory letters followed
by 4 mandatory numbers).

I set up the input mask and then I need to import data to the table
from a txt file. I am using the import text wizard for this that I
find in File, Get external data.

The data comes into the table fine but I need it to trap any Customer
ID that does not conform to the input mask and it is not doing so.

How can I set an input mask (or validation) that will trap any
Customer IDs that do not follow the correct pattern?

TIA

Liz

 

[Liz J]

Hi Doug
Many thanks, that worked well. I think I left the WHERE in even though I
was putting it in the criteria box in the query design.

thanks for your help, much appreciated.

liz