Help With Time Formatting

[reo4ua]

Hi,

I have a 2013 access db that is pulling info from a 2013 excel sheet that is updated frequently. I had to do some digging to find out how to parse and convert data from one cell into multiple cells so that I could properly query the info in access - all date and time info is in one cell AND the date is in European format - this is what I had to split and fix.

The 2nd time I added new info to the spreadsheet (via import from a 3rd party windows program), I didn't realize that I couldn't just cut and paste the parse/convert formulas into the new cells so I had to go back to the drawing board and figure it out again. Regrettably, the method I used the 2nd time didn't match the first and I think this might be where access is having trouble. I'm not sure why the difference in formatting in excel - word wrap maybe?

How do I get rid of the #NUM ! in access and get it to show the data I know is there?

I've included a few screenshots via the link below.

https://photos.app.goo.gl/vhhSxPssgjWD3bzH3

 

[ranman256]

#NUM is where Access is confused on what XL has in a field.
If you want to import a field that is a string, but sometimes its a number (with no chars), i solved this by
running a macro in XL to convert every record to text (via the single quote)

Public Sub Cvt2Txt()
While ActiveCell.Value <> ""
   If Left(ActiveCell.Value, 1) <> "'" Then ActiveCell.Value = "'" & ActiveCell.Value
   ActiveCell.Offset(1, 0).Select
Wend
End Sub

it scans the column and forces them to be string. My imports work better.

Another way, link the excel as an external table and run append query to a target table. This target table has the field types defined.
the query can also convert any fields if needed like:
CDate(field)
cLng(field)
etc.

 

[reo4ua]

Thanks Ranman. I'll give this a shot.

#NUM is where Access is confused on what XL has in a field.
If you want to import a field that is a string, but sometimes its a number (with no chars), i solved this by
running a macro in XL to convert every record to text (via the single quote)

Public Sub Cvt2Txt()
While ActiveCell.Value <> ""
   If Left(ActiveCell.Value, 1) <> "'" Then ActiveCell.Value = "'" & ActiveCell.Value
   ActiveCell.Offset(1, 0).Select
Wend
End Sub

it scans the column and forces them to be string. My imports work better.

Another way, link the excel as an external table and run append query to a target table. This target table has the field types defined.
the query can also convert any fields if needed like:
CDate(field)
cLng(field)
etc.

 

[reo4ua]

The macro did the trick. Thanks again!