Handling Missing Fields in Raw/Imported Data

[dransfield]

This is what I want my database to do:

Import some raw data (about boats) in the form of an Excel
spreadsheet. Some of the fields imported will be superfluous - no
problem - they'll be ignored. Some will be essential - no problem - if
they're not there it isn't going to work.

But some of them will be 'optional'. Eg. I really need to know
(roughly) how big a boat is so I need to have either a "Boat Type"
field OR a "Boat Name" field. (I could have both, in which case I'll
just use the name and look up the exact length in a table).

So my current problem is to work out how to use a field if its there,
but how to avoid an error message if its not.
I've tried writing a UDF but the query still asks for the missing
field.
Can anyone help?
TIA
Dz

 

[dransfield]

So my current problem is to work out how to use a field if its there,
but how to avoid an error message if its not.

Reading this back through I realise I need to make this clearer:

I'm trying to handle an expected Field not being there, not just an
entry in a field for certain records, I mean a whole column of info
not being in the source data, not even the column heading.

Dz

 

[Jeff Boyce]

So, how would YOU know the field was missing? How would you advise an
assistant to check to see if the field was missing?

Before you can handle "what to do about it", you have to be able to
determine "it".

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[dransfield]

So, how would YOU know the field was missing? How would you advise an
assistant to check to see if the field was missing?

Before you can handle "what to do about it", you have to be able to
determine "it".

I know the field names I'm looking for, eg. "Boat Name" and "Boat
Type". I need to calculate an output based on boat length. I can get
the boat length by looking up Boat Name or Boat Type in a table. If
both fields are available in the imported Excel raw data then I'll use
Boat Name as its more accurate, but I need the import process to
handle either of these fields being absent.

Sorry I'm probably missing the point of your question, but in answer:
Myself or an assistant would know if one was missing by looking for
these two columns. My problem is that I can't work out how to make
Access use a column if its there, but not give an error message if its
not there. I've tried writing a UDF which basically says "if
[BoatType] is error then display 'Absent Field" otherwise [Boat Type]"

Dz

 

[Jeff Boyce]

No, you didn't miss the point. While it is blindingly obvious to a human
that something we expect isn't there, Access has to be told how to tell.

If this were mine, I'd run the import process with a dummy set of data that
WAS missing the fields, then note the error message and number.

Then I'd add error handling to the procedure that says, in essense, "if the
error is this one, keep on going, otherwise, ..."

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

So, how would YOU know the field was missing? How would you advise an
assistant to check to see if the field was missing?

Before you can handle "what to do about it", you have to be able to
determine "it".

I know the field names I'm looking for, eg. "Boat Name" and "Boat
Type". I need to calculate an output based on boat length. I can get
the boat length by looking up Boat Name or Boat Type in a table. If
both fields are available in the imported Excel raw data then I'll use
Boat Name as its more accurate, but I need the import process to
handle either of these fields being absent.

Sorry I'm probably missing the point of your question, but in answer:
Myself or an assistant would know if one was missing by looking for
these two columns. My problem is that I can't work out how to make
Access use a column if its there, but not give an error message if its
not there. I've tried writing a UDF which basically says "if
[BoatType] is error then display 'Absent Field" otherwise [Boat Type]"

Dz