Access query trap #num error

Hi!

Is there a way to trap #num errors in a query? I tried iserror(), but it doesn't catch #num.

Thanks!
LVL 10
etech0Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Not really.  You need to resolve why that is occurring.

mx
0
etech0Author Commented:
Here's why it's occurring:

I have a linked excel table. The query in questions takes that entire table, and appends it to an access table.

In that excel file, there is a column of dates. Some of them come up as 00-00-0000 00:00:00, which should mean null. The linked table in Access shows #num in those columns. I don't need the append query to append those 'dates', but the entire query fails, and I think that's why.

Any ideas?
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Try applying some criteria to trap that date value  in the append query.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Maybe like:

WHERE Val([YourField]) >0
0
etech0Author Commented:
var([fieldname]) returns #num, because the field is saying #Num!
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
I see.
Well, I have a LOT of import stuff going on here at work.  What I do is First ... import the xls file into a 'local' table - where every field is Text ... then process from the.

Is 00-00-0000 00:00:00 the actually value in the Excel file?

Problem is ... that is not a valid date
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
And possibly instead of a append query, do a make table query to get it onto a local Access table first - the process from that.
0
etech0Author Commented:
It is the value in Excel, and it comes like that downloaded from our web database. I guess it means Null.

I can try a temp table and see what happens.

Thanks!
0
Rey Obrero (Capricorn1)Commented:
try a where clause like this

where [fieldName] & "" <>""
0
etech0Author Commented:
I don't want a where clause, because for those rows I want to import everything else, just not the column in question.

So I change the field in the query to:

fieldname_fixed: fieldname & ""

but it still shows #num!
0
Rey Obrero (Capricorn1)Commented:
ok try this

fieldname_fixed: iif([fieldname] & ""="",#1/1/1900#,[fieldName])
0
etech0Author Commented:
STILL shows #Num! Wow!
0
Rey Obrero (Capricorn1)Commented:
can we see the excel file and the query you are using?
0
etech0Author Commented:
The excel file has data that can't be posted, but I'll give you a screenshot of the problem area.
screenshot

Here's the query (at this point): (It's a little long, but the only spot you need to look at - I think - is

IIf([last_change_date] & ""="",#1/1/1900#,[last_change_date]) AS last_change_date_fixed

The whole thing:

INSERT INTO isdataitemvendor ( item_vendor_id, as400_id, item_id, status, last_status, sku, sku2, sku_suffix, static_cost, real_cost, future_cost, future_list, future_cost_effective_date, creation_user, creation_date, last_change_user, last_change_date, price_year, minimum_order, distribution_number, vendor_unit_measure, list_price, del, location, default_vendor_warehouse, price_vendor, real_mfg, division, free_freight, free_freight_note, width, to_update, weight, ruleset_id )
SELECT ISdataItemVendor_linked.item_vendor_id, ISdataItemVendor_linked.as400_id, ISdataItemVendor_linked.item_id, ISdataItemVendor_linked.status, ISdataItemVendor_linked.last_status, ISdataItemVendor_linked.sku, ISdataItemVendor_linked.sku2, ISdataItemVendor_linked.sku_suffix, ISdataItemVendor_linked.static_cost, ISdataItemVendor_linked.real_cost, ISdataItemVendor_linked.future_cost, ISdataItemVendor_linked.future_list, ISdataItemVendor_linked.future_cost_effective_date, ISdataItemVendor_linked.creation_user, ISdataItemVendor_linked.creation_date, ISdataItemVendor_linked.last_change_user, IIf([last_change_date] & ""="",#1/1/1900#,[last_change_date]) AS last_change_date_fixed, ISdataItemVendor_linked.price_year, ISdataItemVendor_linked.minimum_order, ISdataItemVendor_linked.distribution_number, ISdataItemVendor_linked.vendor_unit_measure, ISdataItemVendor_linked.list_price, ISdataItemVendor_linked.del, ISdataItemVendor_linked.location, ISdataItemVendor_linked.default_vendor_warehouse, ISdataItemVendor_linked.price_vendor, ISdataItemVendor_linked.real_mfg, ISdataItemVendor_linked.division, ISdataItemVendor_linked.free_freight, ISdataItemVendor_linked.free_freight_note, ISdataItemVendor_linked.width, ISdataItemVendor_linked.to_update, ISdataItemVendor_linked.weight, ISdataItemVendor_linked.ruleset_id
FROM ISdataItemVendor_linked;
0
Rey Obrero (Capricorn1)Commented:
try this

IIf(left([last_change_date],4="0000"),#1/1/1900#,[last_change_date]) AS last_change_date_fixed
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Not sure you're gonna get around the #Num issue.  
As a test, try a MakeTable query - and see what Access does with those values ...

mx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rey Obrero (Capricorn1)Commented:
i can see that the column name is "future_cost_effective_date"  is this correct?
0
etech0Author Commented:
that's the column name.
0
etech0Author Commented:
IIf(left([last_change_date],4="0000"),#1/1/1900#,[last_change_date]) AS last_change_date_fixed

returned a #error on every for

But....
a make table query just ignored the problem fields. And then, I can base the update query on the temp table.

Guess I'm going to add a step to the process. But it's worth it!
0
Rey Obrero (Capricorn1)Commented:
<that's the column name. >

then why are we using  "[last_change_date]" ?
0
etech0Author Commented:
Whoops - I guess I took a screenshot of the wrong column! They both have the same problem, so I figured I'd deal with one at a time.

Regardless, a make table query and then an append query fixes both issues.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
This may not be an option for you, but ... we get this kind of crap often from IT in Excel 'reports' - especially when ramping up on a new report.  Basically, we tell them: "Unacceptable - FIX IT - don't send junk@
0
etech0Author Commented:
Probably more trouble than it's worth, but I'll keep it in mind.
0
etech0Author Commented:
Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.