Transfer Spreadsheet Null Values

[bdmoran]

Hello - I am using a TransferSpreadsheet macro where I append an excel spreadsheet to a table in access called "Timesheet".

I keep getting the "Type Conversion Failure" import error. It is because some of the fields in my excel file are null. Is there a way to not recieve this error? I tried setting the "Required" and "allow zero length" properties to no and yes but I am still getting this error.

Does anyone know how I can accept these nulls?

 

[SkipVought]

hi,

Which specific

1) Excel data element, mapped to what

2) FIELD/Date Type???

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[bdmoran]

Excel File : In Time column has a military time format: 13:30:51.

It is mapped to my IN Time column in my Access table called
Timesheet. The data Type for IN Time in my table is Date/Time.

Everything works out perfectly but when I have the Null values I get the import errors.

I don't want to change anything on my excel file because eventually I will be sending this database off to Finance. My goal is they don't need to do anything besides run a macro.

 

[MazeWorX]

how about importing to a temp table as text before updating your table with a query where you could handle the nulls with the Nz function?

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[bdmoran]

Hey MazeWorX - I actually was looking at my data again and realized it wasn't the null fields that were causing errors. I apologize for this.

My problem now is that I am getting the "Type Conversion Error" for 26 random date/time records. The records in each column are in the same format (10:26:39) so I don't see why some records are causing an error while the rest are fine.

I have searched all over the internet for any solutions but I didn't come across anything.

 

[MazeWorX]

have you tried importing them as text first? then format them in access? They may be causing errors because of a leading space? or even a trailing space. You should be able to test for this and correct the offending data

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[bdmoran]

I still recieve the import error table even when I import to a table table with TEXT datatypes.

Does the error table refer to the failed rows in access table or excel file?

 

[SkipVought]

The records in each column are in the same format (10:26:39) so I don't see why some records are causing an error while the rest are fine.

The error means that the value from Excel caused an error in Access. So I'm guessing that several values in Excel are not REAL Date/Time values.

Here's how to find out.
[tt]
1. Select the COLUMN containing Date/Time values.

2. Change the Number Format to GENERAL.

3. Turn on the AutoFilter

4. Find the rows that do not have NUMBERS.
[/tt]
If you do have NON NUMBERS, then do this...
[tt]
1. Enter a [highlight]1[/highlight] in an unused cell

2. COPY that cell

3. Select the DATA in the Date/Time column

4. Right-click select Paste Special -- MULTIPLY > OK

5. SAVE
[/tt]
Now you're ready to import this data.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Oh, yes -- AFTER the MULTIPLY step, select the column and FORMAT as Date/Time.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[bdmoran]

The only problem is it is doing this for fields that have a "general" data type in excel. (Example: Total Hrs column). I do not want my finance department to have to alter their spreadsheet. There is absolutely no way to just accept NULL values without producing errors?

 

[bdmoran]

This is the strangest thing. I went back in my excel spreadsheet and put in random values for the null values that were causing errors. EXAMPLE: IN Time field was NULL so i put in 11:14. When I imported again, this didn't come up as an error(as expected). I did this for all values that were causing me to get the Import Error Table, re-imported and everything worked perfect.

Then I went back to my excel spreadsheet and took out random values (including some that were causing errors before), imported again and still everything worked perfect.

Why is it when I take out the values manually I don't recieve any errors???