Check if a value is legal

[atledreier]

I have some fields I need to check against another table.

The scenario:

My users have been inputting data in my tables through queries up
until now. i've realized I'm going to need a few lookuptables to
verify their input, but there's some 10.000+ records that needs to be
checked. So I've made the lookup tables but I thought I'd make it a
little easier for my users to verify their data with a query that
returns records with faults.

I've made a query that returns al the NULL values and a continuous
form that mark the empty but required fields with yellow background
through autoformat. I'd love to have the invalid entries marked with
red text. I think i can handle that if i get a query that returns the
invalid records.

 

[Tom van Stiphout]

On Tue, 4 May 2010 01:33:12 -0700 (PDT), atledreier

The basic idea for such query is:
select * from myTable
where myField not in (select myField from myLookupTable)

-Tom.
Microsoft Access MVP

 

[Bruce Meneghin]

This will get you the invalid records. You'll need extra to figure out which
fields to turn red.

Table1 table you are looking for invalid records
Field1
Field2
Field3
lookup1 table with valid values for Table1.Field1
lookup2 table with valid values for Table1.Field2
lookup3 table with valid values for Table1.Field3

SELECT Table1.Field1, Table1.Field2, Table1.Field3
FROM ((Table1 LEFT JOIN lookup1 ON Table1.Field1 = lookup1.Field1) LEFT JOIN
lookup2 ON Table1.Field2 = lookup2.Field1) LEFT JOIN lookup3 ON Table1.Field3
= lookup3.Field1
WHERE (([lookup1]![Field1] Is Null) OR ([lookup2]![Field1] Is Null) OR
([lookup3]![Field1] Is Null));