Check if a value is legal



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)

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
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));

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question