What type of value is in a field that’s been cut?

[aaronb50]

I have an excel spreadsheet that I import into Access. Once in a while I get a Runtime error ‘3349’
I think the problem is that the user makes a mistake when filling out the spreadsheet and does a Cut and Paste. Access does not like the field that was Cut. I’m able to figure out what field it is and just place in any character and then delete it and it works fine.
What happens to that field when you use Cut? What type of value does it now hold that Access doesn’t like?

 

[sneuberg]

If if were anything but Null, I would be surprised. Maybe the Nz function in the right place would help.

 

[aaronb50]

I use

[FONT=Calibri]DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "OTT Table", "\\111\222\333\O1.xlsm", True, "OTT!A1:AF99"[/FONT]

to get it into the Access table and this work just fine 99% of the time.

Once in a while I get the error and have to figure out what field it does not like. It’s never a while column or row, it’s always just one field.
When I found the column the field was in today, I realized only two of the records had an entry in that column. One happened to be right below the bad field. That leads me to believe the user accidently placed the data in the wrong field, and then cut and pasted it into the record below leaving something in the field that was cut that Access does not like.

 

[plog]

What happens to that field when you use Cut? What type of value does it now hold that Access doesn’t like?

I just did a test and I was able to succesfully import it without an error. It came through as NULL.

Run some tests of your own. Get a file that gives that error and keep narrowing it down to not only find the offending column, but the offending row. Determine what specific piece of data is causing it.

Once you do, even if its a character you can't actually see, you will be able to use the CODE function in Excel to find out what character it actually is. So if G7 was the culprit, put this somewhere to see what G7 actually holds:

=CODE(G7)

Most likely a tab character, space, new line or carriage return.

 

[aaronb50]

It gave me a 32.

And that's 32 on the ASCII table correct?

So there was a space in there?

Thank you so much!!!!!

 

[aaronb50]

Yeah I went back to the original copy and there is indeed a space in there.

Cut was not the issue.

 

[plog]

Ok, now you've identified it, how do you fix it? Train users to not put spaces in? Ha ha, good luck.

I would use a staging table. First, copy the structure of OTTTable and name it 'tmp_import'. Then change all the field types to Short Text--everything can be brought in as Short Text. Next, change your import script to bring the data into tmp_import.

From there you can create a query to find bad records in tmp_import by using IsNumeric and IsDate on the fields that should be and showing those that aren't. You could even roll up logic that does a DCount into it. If the result is 0 you could automatically run an APPEND query to put tmp_import into OTTable, or pop up the error query to identify the bad data so you can fix it.

 

[aaronb50]

Yeah............... there is no way I can stop the madness of the data the user put in.

So if I make all the fields in the temp table Short Text, it will except everything?

From there I transfer it to the table I actually want the data on while putting in checks to make sure its putting in what I know it should be?

I have text, dates and number fields. Do I have to convert the dates and numbers first or will they do it on their own as they get into the final table? I'm guessing I'm gong to have to convert each field in each record before I move it over.

But that's still better then tracking down a random space in over 1000 fields every time this comes up.

 

[plog]

Yes, text will accomodate everything. Then on the append Access should automatically a Short Text field to a Date/Number if its valid a valid Date/Number. That's why you build the Error query. Find the errors and fix them before doing the append.

 

[Galaxiom]

Convert spaces to Null with:

Iif(Len(Trim([field]))= 0, Null, [field])

 

[aaronb50]

Alright ganna give that a try now!!

Thanks!!!

 

[aaronb50]

So, I have the data in the Temp table.

Now to get it to the table I actually want without the errors I would put in a loop that would check each field in each record on the temp table.

starting on the first record I would check the first field to make sure it was acceptable and if so I would move it over, if not, I would not move it over. and then do this again for each field in each row.

While I know this would work, I'll be the first to admit I'm making this all up as I go so if there is a better way I'm in.

This Error query, is that a faster way to do it? How would I build it?

 

[plog]

Simplified Error Query:

--Using tmp_Import as the table
--every field is Short Text
--tmp_Date is field in tmp_Import that should have a date in it
--tmp_Number is field in tmp_Import that should have a number in it

SELECT tmp_Date, IsDate(tmp_Date) as valid_tmp_Date, tmp_Number, IsNumeric(tmp_Number) as valid_tmp_Number
FROM tmp_Import
WHERE IsDate(tmp_Date)=False OR IsNumeric(tmp_Number)=False

Any record with bad data will show, any data without bad data will not show. You might have to code a little to account for NULL values

 

[aaronb50]

Is that code giving me a list of the records with bad data in it so I can go in and fix them?

If I change those False to True, and then have a list of ones that are good, could I then run an insert line at the end into the new table?

But then I guess that would not help with identifying the bad ones and they would just be left off completely.

 

[aaronb50]

So I got it all set up.

Instead of doing the Error Query(I don't fully understand it just yet), I just looped down the whole temp table and moved over every filed one at a time.

Assuming if I go with....

On Error Resume Next

......it will move over what is acceptable and leave what is not.

I'm not 100% sure this will work the way I think it will yet.

When I try to import the file to the temp table that is made up of all text fields, I still get the same error. It does not like just a space in a field. I took out the space that was causing the problem to begin with and it imports just fine. If I create a random one anywhere on the Excel Spreadsheet, I get that "Runtime error ‘3349’" again.

Why is it having such an issue taking in the space?

 

[aaronb50]

Is it maybe having something to do with the Excel side? It does not like a space in a number field?

 

[plog]

I'm running a test importing a space in Excel into a text field in Access and its doing it fine--brings in the space without issue.

Does that field in the temp table have its Required property set to Yes? That's the only thing I can think of.

Actually, 1 more thought. Instead of CODE(G7), find out the length of it, LEN(G7). Maybe there's more than 1 invisible character (tab, newline, carriage return)

 

[aaronb50]

Temp table is set to no.

I get 1 when using =LEN(G7)

I double checked to make sure that its not just that field. It does it no matter what field I put the space in.

Maybe I need to start over with a different Excel form? That's not going to go over well. Getting everyone switched over to a new one is not going to be easy.

Should I use a different way to import it? I'm currently using:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "TEMP_OTT Table", "\\111\222\333\O1.xlsm", True, "OTT!A1:AF99"

Is there something else I should be using?

 

[aaronb50]

I think its the Excel side.

The field is a number field.

When I put in a "1", I have no issues.

When I put in an "A", I get the error

 

[plog]

Maybe the best way is to write a macro in Excel to Replace all cells that have a single space with Null. That way you don't have to get people a new spreadsheet, you simply point your macro at whatever sheet you want to run it on.