Linking To .XLS: Some Dates = "#Num!" ??

  • Thread starter Thread starter PeteCresswell
  • Start date Start date
P

PeteCresswell

I'm linking to a .XLS that contains, among other things, an
"IssueDate" column.

Looking directly at the spreadsheet, dates look a-ok and the column is
formatted "Date".

Looking at the same sheet through an MS Access link, most of the dates
are OK, but some are rendered as "#Num!".

The link has DataType=Text.

I tried typing a perfectly good date into one of the problem rows - on
the sheet - but it still comes through the link as "#Num!".

Most come through OK, but some come through as "#Num!".


So far, "MS Access Issue".... right?

But here's the kicker:

I open up the problem .XLS.

I locate one of the problem rows.

I delete all of the 9,000+ rows above it, leaving only 70-some rows.

I close the .XLS.

Now the problem row renders a-ok through the MS Access link.

I'm thinking something's goofy with the absolute cell - which is now
buried down there in the "Empty" portion of the .XLS.

Anybody been here?
 
I'm thinking something's goofy with the absolute cell - which is now
buried down there in the "Empty" portion of the .XLS.
Click to expand...

FWIW, I tried saving the problem .XLS as .CSV and re-linking.

Result was that out of 115 problem rows, all except 6 now render the
date properly.

The six? Still haven't figured anything out..... but the fact that
six are still bad makes me wonder if the good guys were just an
artifact of something I don't know about.
 
Oh yes, at least once a week.

There are dates and there are date-lookalike texts.

Select the whole IssueDate column in Excel and remove all alignment. Make
the column too wide for its purpose.

Real dates will right align. Text, no-dates causing the errors, will left
align. You may have to reformat those cells and re-enter the date values,
and maybe even worse operations, text formatting can be incredibly
persuasive, to fix it.

HTH. Best wishes Harald
 
Select the whole IssueDate column in Excel and remove all alignment. Make
the column too wide for its purpose.
Click to expand...

I don't see a way to do that.

Format | Cells | Alignment | Horizontal doesn't offer up a "none"
option in my Excel.

Only General, Left, Center.... and so-forth.

Am I missing something?

Or is "General" = "none"
 
Real dates will right align. Text, no-dates causing the errors, will left
align.
Click to expand...

Going on the "General" being "no alignment" assumption, I do see that
some dates right-align and others left-align.

But it seems backwards: the problem cells are right-aligned and the
good guys are left-aligned.

??
 
They are toggle buttons. CLick Center, it centers the selected cells and
lights itself up. Click Center again and alignment is removed. Light is also
gone.

HTH. Best wishes Harald




Select the whole IssueDate column in Excel and remove all alignment. Make
the column too wide for its purpose.
Click to expand...

I don't see a way to do that.

Format | Cells | Alignment | Horizontal doesn't offer up a "none"
option in my Excel.

Only General, Left, Center.... and so-forth.

Am I missing something?

Or is "General" = "none"
 
Real dates will right align. Text, no-dates causing the errors, will
left
align.
Click to expand...

Going on the "General" being "no alignment" assumption, I do see that
some dates right-align and others left-align.

But it seems backwards: the problem cells are right-aligned and the
good guys are left-aligned.

??


This means that Jet is grabbing the text values (left aligned) properly
and the date (numeric) values are erroring out. See my post in the
Access thread.
 
Another way to see what cells are real dates and what cells are just text that
look like dates.

Select the range (entire column????)

Give that range a nice unambiguous date format that isn't used.
Maybe: mmmm dd, yyyy

Look through the range for the cells that don't look like that format. Those
are the offending cells. Reenter the date for those cells.

After all the text dates are fixed, reformat the range the way you like
(mm/dd/yyyy, maybe???).
 
They are toggle buttons. CLick Center, it centers the selected cells and
lights itself up. Click Center again and alignment is removed. Light is also
gone.

HTH. Best wishes Harald
Click to expand...

FWIW: I now see that some of the dates go one way and some the other.

From that, I'm extrapolating that some of the data is goofy: i.e. not
really "date".

So I did an end run - gleaned from somebody else's experience:
----------------------------------------------------------
Sub AddSpace()
Dim cell As Object
Dim k As Long

For Each cell In Selection
cell.Value = " " & cell.Value
cell.Value = Right(cell.Value, Len(cell.Value) - 1)
k = k + 1
Next

MsgBox k & " Cells updated", vbInformation, "Done"
End Sub
 
Back
Top