Data mismatch caused by null in record

  • Thread starter Thread starter KARL DEWEY
  • Start date Start date
K

KARL DEWEY

I thought I read a cure for this but have forgotten what to do.

Macro open form displays 'Data type mismatch in criteria expression.'
Action failt showing error code 2950.

I have a null record that causes this. The null record is created by people
starting new record and not entering anything.

I probably could run delete query before opening form to remove null records
but there must be a better way.
 
Your form is based on either a table or a query. Have it based on a
query, and simply use "not null" for the criteria in any field that will
be null in this condition. You probably should still have something to
delete the empy records if they will tend to accumulate, but this way
they won't screw up your open form.
 
KARL said:
I thought I read a cure for this but have forgotten what to do.

Macro open form displays 'Data type mismatch in criteria expression.'
Action failt showing error code 2950.

I have a null record that causes this. The null record is created by people
starting new record and not entering anything.
Click to expand...


There is an inconsistency in your supposition about the
"null" records. A record will not be saved unless something
is set in at least one field. Using Undo or Esc key will
return a dirtied new record back to a record that does not
need to be saved.

I conclude that your "null" records actually have some data
somewhere. Either the users enterd something or you have
code somewhere that set a field to something. In the former
case, you should use the form's BeforeUpdate event to check
for valid values in the required fields or set those fields
Required property to Yes to prevent the junk records from
being saved.

In the latter case, you should find the code that dirties
new records and move it somewhere that only happens when a
record really has good data.
 
You are correct the record is not truly null as it has an autonumber primary
key.
I will use the Is Not Null on the query and then on close of database check
for 'nulls' and run a delete query.
 
KARL said:
You are correct the record is not truly null as it has an autonumber primary
key.
I will use the Is Not Null on the query and then on close of database check
for 'nulls' and run a delete query.
Click to expand...


Still not valid reasoning. An autonumber is just a fancy
default value so an autonumber field is not set until
something is entered into some other field.

Rather than bulding a kludge to deal with junk records, you
should put the effort into preventing them from being saved.
 
Back
Top