validation rule for a text field

[Laoballer]

I have a table with a text field that I would like to create a rule
for. The format of the field is like this
(100-199 kV, 200-299 kV, 300-399 kV, etc.)
I would like it so that records that don't match the format don't get
imported to the table.

Thanks,

 

[Pete D.]

Not sure how many of these you have but I sometimes make a lookup table to
solve this problem. Depending on how much work and table size I might also
just do it in Excel first and import the values. I'm not saying base your
tables on the lookup table but just create one for the query you use to fix
the data. Import the data raw into a table and then use a query to update
field values to what you want. Between functions native to Access and VBA
you can really clean up your data. Also I would normalize what you have by
moving them apart more. fldLowValue, fldHighValue, fldUnitSize or something
like that. Just thinking out loud here, others may have better idea.

 

[Gina Whipp]

Laoballer,

If it were me I would write my own Import Specification. However, you could
also bring them all in then write a Delete query to remove the 'offending'
records.

 

[Laoballer]

Laoballer,

If it were me I would write my own Import Specification.  However, you could
also bring them all in then write a Delete query to remove the 'offending'
records.

I do use an import specification to import a tab delimited with first
row as header. Does the specification file reside within the access
database? So whoever has the database will be able to access the
specification file.

 

[Gina Whipp]

Yes it does go where ever the database goes.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

Laoballer,

If it were me I would write my own Import Specification. However, you
could
also bring them all in then write a Delete query to remove the 'offending'
records.

I do use an import specification to import a tab delimited with first
row as header. Does the specification file reside within the access
database? So whoever has the database will be able to access the
specification file.

 

[Gina Whipp]

I don't know if the one available from within Access allows filters which is
why I would write my own. I only use the one from within Access if it's an
all or nothing deal.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

Laoballer,

If it were me I would write my own Import Specification. However, you
could
also bring them all in then write a Delete query to remove the 'offending'
records.

I do use an import specification to import a tab delimited with first
row as header. Does the specification file reside within the access
database? So whoever has the database will be able to access the
specification file.

 

[Laoballer]

I don't know if the one available from within Access allows filters whichis
why I would write my own.  I only use the one from within Access if it's an
all or nothing deal.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors





I do use an import specification to import a tab delimited with first
row as header.  Does the specification file reside within the access
database?  So whoever has the database will be able to access the
specification file.

how do I write my own schema file in access? I can't find where to do
that anywhere.

Thanks

 

[Gina Whipp]

I found this http://support.microsoft.com/default.aspx?scid=kb;en-us;155512,
also you might find something here http://www.bigresource.com/tracker.html

But something that may be easier... Import the file into a 'dummy' table
then run a query against the dummy table into the 'real' table. The query
would contain the data you want to keep (or a one field table) which would
give you the same thing.
--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

I don't know if the one available from within Access allows filters which
is
why I would write my own. I only use the one from within Access if it's an
all or nothing deal.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors





I do use an import specification to import a tab delimited with first
row as header. Does the specification file reside within the access
database? So whoever has the database will be able to access the
specification file.

how do I write my own schema file in access? I can't find where to do
that anywhere.

Thanks

 

[Laoballer]

I found thishttp://support.microsoft.com/default.aspx?scid=kb;en-us;155512,
also you might find something herehttp://www.bigresource.com/tracker.html

  But something that may be easier...  Import the file into a 'dummy'table
then run a query against the dummy table into the 'real' table.  The query
would contain the data you want to keep (or a one field table) which would
give you the same thing.
--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors





how do I write my own schema file in access? I can't find where to do
that anywhere.

Thanks

I'm noticing something unusual about validation rules, I have a field
where the only values you can enter in that field are AC,ACO,DC,DCO
I'm importing tab delimited text files and when I do, the rows that
contains something other then AC,ACO,DC,DCO in that field doesn't get
imported. Is there a way where I can specify rules where if the field
doesn't meet the validation rule for that field then leave that field
blank but import the other fields in that record?

Thanks,
Vilet

 

[Gina Whipp]

No, unfortunately, validating the field does have it's side effects.

Another thought you could remove the validation, import all the records, run
an update query to remove all data on THAT field that does not fit. Similar
to using a dummy table but with no dummy table, only a table with the
desired data.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

I found
thishttp://support.microsoft.com/default.aspx?scid=kb;en-us;155512,
also you might find something herehttp://www.bigresource.com/tracker.html

But something that may be easier... Import the file into a 'dummy' table
then run a query against the dummy table into the 'real' table. The query
would contain the data you want to keep (or a one field table) which would
give you the same thing.
--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors





how do I write my own schema file in access? I can't find where to do
that anywhere.

Thanks

I'm noticing something unusual about validation rules, I have a field
where the only values you can enter in that field are AC,ACO,DC,DCO
I'm importing tab delimited text files and when I do, the rows that
contains something other then AC,ACO,DC,DCO in that field doesn't get
imported. Is there a way where I can specify rules where if the field
doesn't meet the validation rule for that field then leave that field
blank but import the other fields in that record?

Thanks,
Vilet