Invalid Use of Null

[ktmiller11]

I inherited a database about a year ago. The programmer set it up so a button would import 4 csv speadsheets and update the database. I have had no issues importing these spreadsheets for the last year, the database updated as it should. Three weeks ago only one csv file would update, then I get the 'Invalid Use of Null' message. Preventing the other spreadsheets from updating to the database. I have compared old spreadsheets that imported with no problem with the ones that will not import now. There is no difference between the two and I am really confused. I imported old files from last yar and they updated as they should. I made no changes to the DB. I updated on Sept 25 with no issues, then Sept 29 the 'Invalid Use of Null" showed up. I am not totally sure what I should be looking for, any help would be appreciated. I should also add that I am a novice with Access and VBA. My only experience has been with this DB.

 

[James Deckert]

open the csv file in wordpad/notepad (don't open in Excel). scroll down to the bottom and see if there are any empty rows. They'll look something like, "","","","" (the empty rows actually could be anywhere in the file, but probably at the end)
If so remove the whole row and save the file, then try the import again.
If this is not the case, them maybe you have an empty field that is required on the import.

 

[ktmiller11]

Thanks James, I did that and there are no empty fields or missing items. While following your advice, I thought of another idea to try. I deleted the folders from the source file and ran the import command again. I expected a 'data not found' type error, instead the 'Invalid use of Null' was displayed. That leads me to believe that it is not a spreadsheet problem, I think Access cannot see the other spreadsheets after it opens the first. I notice that when I open the excel program (not any particular spreadsheet, just the program), the first imported file opens automatically with the program. Can a csv file get stuck in Excel and prevent Access from opening other spreadsheets?

 

[James Deckert]

I deleted the folders from the source file

I don't understand this. Did you mean that you deleted the source files (csv's) for the folder?
I don't know how Excel could lock a csv file. If that is what you're thinking, then reboot the computer. csv files aren't really a spreadsheet, they are text files which Excel can open.
How are the csv files chosen to open? All csv files in a folder? Or are the files named something which is hard coded in Access? You said the older ones open but not the newer ones. Is it consistently the same newer ones which have a problem? What happens if you copy a problem new one and name it like one of the older ones? If you consistently have problems with the same csv files, can you post one which works and one which doesn't?

 

[gemma-the-husky]

I expect that there is blank value in one of the spreadsheet cells, that your database will not permit to be blank

for text values, you just cannot identify a difference by inspection of a spreadsheet, or a access table. you might be able to see a difference in a csv.

the csv will look like this, though

zero length strings - ,"","","",""
nulls ,,,,,

it will only become an issue when you try to process a null, or insert it into a field that is set as required, no nulls.

It is tricky to find stuff like this, to be honest.

you could test specific columns of the import file for the existence of nulls. You need a systematic approach.