[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4542
  • Last Modified:

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!
0
etech0
Asked:
etech0
  • 11
  • 7
  • 6
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
Try applying some criteria to trap that date value  in the append query.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Maybe like:

WHERE Val([YourField]) >0
0
 
etech0Author Commented:
var([fieldname]) returns #num, because the field is saying #Num!
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
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
 
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 MVP, Access and Data Platform)Commented:
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 11
  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now