#Num error in Linked Excel file. Unable to track down why. (1 Viewer)

KV_access

New member
Local time
Today, 05:56
Joined
Mar 24, 2020
Messages
12
Hello,

I have posted a few times in the past week regarding different issues in my access database, but I am finally, hopefully, getting close in my finished result. Long story short I am attempting to create a access database which is linked to multiple excel files (which have the same format with the only difference being date). Additional linked excel files will be added periodically and all the files are Union Queried together and are eventually filtered through a search form to obtain totals.

The issue I am having is obtaining totals due to linked excel cells having the #Num error. I have attached an image with shows the issue below:

1585767332416.png


I have checked the cells in excel which contain #Num! in access, all are empty (they don't even contain "spaces") and they are all formatted as Currency as they should be. All this data was created the same way, so I am at a complete loss as to why access begin having issues with empty cells.

Is there anything else I should check? I sadly cannot attach the excel file(s) which contain this issue.

Is there any means to tell access to ignore or replace the #Num error with blank cells?

Any help is greatly appreciated. Thanks again for all the help this community has already given me!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:56
Joined
Feb 28, 2001
Messages
26,996
You might try the NZ function with an IIF, though that would mean that the resulting query would not allow updates. Any field you want to protect this way might need something similar to: NZ( [TheField], "" )
In English that says "If the field in question contains a null, display blanks." If that doesn't work then something else is going on that would require more research into specifics.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:56
Joined
Feb 19, 2013
Messages
16,553
this one of the problems with excel - it had a variant datatype (format is irrelevant in determining the datatype) and you have no control how the data is viewed from outside of excel - the datatype is determined by what is in columns in the first few rows. If the columns in these rows are all blank then text is assumed so I am a bit surprised by the 2nd and 3rd columns assuming these are the top rows.

For that reason it is always better to import a .csv. Suggest save your excel file as a .csv then open it using notepad (not excel) - see what is there and perhaps try to import the .csv

This may not be relevant to your situation but note that if you open a csv file with excel, then save the file as .xlsx (or even .csv), excel can change the data - particularly 'blanks'. So a blank value might be null, a zero length string or 'nothing'. Currency might be saved a text, etc
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:56
Joined
May 7, 2009
Messages
19,169
modify the underlying excel file.
on the Ribbon (excel), put on each Column the correct numeric format.
 

KV_access

New member
Local time
Today, 05:56
Joined
Mar 24, 2020
Messages
12
You might try the NZ function with an IIF, though that would mean that the resulting query would not allow updates. Any field you want to protect this way might need something similar to: NZ( [TheField], "" )
In English that says "If the field in question contains a null, display blanks." If that doesn't work then something else is going on that would require more research into specifics.
Thank you for the suggestion, I tried it and sadly it didn't seem to work, but I am not sure if I executed it correctly. This was my attempt in the SQL of my query:


IIF([Union Query Linked].[Mon reg payment] Is Null, 0, [Union Query Linked].[Mon reg payment])

FROM [Union Query Linked];


Was this the correct way to attempt it? The Union Query Linked is multiple excel files unioned together and Mon reg payment is just one of the many columns where the #Num errors appear.
 

KV_access

New member
Local time
Today, 05:56
Joined
Mar 24, 2020
Messages
12
this one of the problems with excel - it had a variant datatype (format is irrelevant in determining the datatype) and you have no control how the data is viewed from outside of excel - the datatype is determined by what is in columns in the first few rows. If the columns in these rows are all blank then text is assumed so I am a bit surprised by the 2nd and 3rd columns assuming these are the top rows.

For that reason it is always better to import a .csv. Suggest save your excel file as a .csv then open it using notepad (not excel) - see what is there and perhaps try to import the .csv

This may not be relevant to your situation but note that if you open a csv file with excel, then save the file as .xlsx (or even .csv), excel can change the data - particularly 'blanks'. So a blank value might be null, a zero length string or 'nothing'. Currency might be saved a text, etc

Thank you for the suggestions. I haven't attempted to import the csv files yet but I did save the excel as a csv to see if anything "appears" in the apparently blank cells. When I opened the csv in a text editor there is nothing in the cells which appear to be empty which makes the #Num error that much more confusing. The previous screenshot I attached was actually roughly 5000 rows down. The linked excel file seems to correctly determine the data type:
1585840369132.png


Please ignore the naming convention and spaces, I haven't got around to fixing it yet, but the datatype is correct and seems to correctly apply for roughly 5000 lines before it starts treating all blanks as a #Num error. Would it be possible to change all blanks to "0" currency values and possible fix it? If so, do you know how I may go about it in access versus having to change it in the excel file?

I may end up importing csv files, but that kinda defeats part of my goal in linking excel files so updates would be dynamically completed in the access database.
 

KV_access

New member
Local time
Today, 05:56
Joined
Mar 24, 2020
Messages
12
modify the underlying excel file.
on the Ribbon (excel), put on each Column the correct numeric format.
Hello, thank you for the suggestion. I have looked in excel and all the relevant columns are set to Currency, as seen here:
1585840776567.png

The linked excel file in access also finds the correction datatype as seen here:
1585840832342.png


So, I am still not sure why the #Num error is there. Is this where you were talking about changing it to the current numeric format or is there somewhere else I should look at changing it as well? Thank you.
 

bastanu

AWF VIP
Local time
Today, 02:56
Joined
Apr 13, 2010
Messages
1,401
Can you try to open Excel, find a block of few cells that show #Num in Access, select them and click the Delete button on the ribbon, save Excel and finally refresh the link in Access (in the Linked Table Manager). Do they still show the error? I found sometimes hidden characters in Excel that cause this kind of problem.

One solution could be to force all fields to have their data type as text (by linking the Excel files and unselecting the First Row has Field Names checkbox, then use queries in Access to reset the field names and the proper data types.

Cheers,
Vlad
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:56
Joined
Feb 28, 2001
Messages
26,996
I would have used a different format, perhaps.

Code:
You:   IIF([Union Query Linked].[Mon reg payment] Is Null, 0, [Union Query Linked].[Mon reg payment])

Me:   NZ([Union Query Linked].[Mon reg payment], 0)
 

Users who are viewing this thread

Top Bottom