Blank fields in imported excel spreadsheets

[WAVP375]

I have imported a spreadsheet from excel that I have no control over. Some of the critical fields have no data. In a form to utilize this data I have code, If Len(Me.[CC Exp] & vbNullString) = 0 Then
"Do something'
End If
Mon = Left([CC Exp], 2)
On some of the fields with no data I am getting an 'Illegal use of Null' error, telling me I am not catching all the blank fields. I've tried the NZ, If Null and all the other possible ways I can conceive of. There must be a way to capture all the possible ways a field can be blank.

Any Help?

[ranman256]

does that mean you dont want blank fields?
or you do, but want to change them from blank to 'do something'?

cant you import them as blank then run an update query to set them to whatever you want?

[WAVP375]

The objective is, if there is no data I want to just skip the record. No further processing on it. The code I have just tried is

While Me.CurrentRecord < Me.Recordset.RecordCount

If Len(Me.[CC Exp] & vbNullString) = 0 Or IsNull(Forms!FrmChangeCCDate![CC Exp]) Then
DoCmd.GoToRecord Record:=acNext
End If
Mon = Left([CC Exp], 2) (This is where I get the error message.)

Since the field has no data, why does this 'If' not find the field as empty? Is there some other way to check for empty field? It catches some of the empty fields but not all.

Thanks

[Minty]

If [CC Exp] is a date field just check if it's a date

If Not IsDate(Me.[CC Exp]) Then ....

[ArviLaanemets]

Empty fields in Excel are though case.

1. Field in Excel is empty, when there is nothing entered;
2. Field in Excel is empty for Excel sometimes, when the formula there returns an empty string. But really the field is not empty - it contains a formula!

When reading data from Excel using ODBC query, you can leave out rows with cells in certain column being really empty, like

SELECT ... FROM YourExcelTable WHERE SomeColumn Is Null

When you want to leave out rows with formala returning an empty string, the query must be like

SELECT ... FROM YourExcelTable WHERE SomeColumn <> ""

Additionally you have to take into account, that whenever ODBC Query encounters an empty cell which is not filtered out, it follows it's internal logic to decide, is it text or equivalent of number. And it also uses it's internal logic to decide, is the column text or equivalent of number. In case there will be conflict for some row between those decisions, the row is left out from result table.

[WAVP375]

I am dealing with this in a form and do not want to exclude a row, I merely want to skip the record if the field has no data.

The field is defined as date/time but it is empty but my logic is not catching it as empty. The field is defined as date but it is a bastard date as in YY/Mon. The further processing I am doing is parcing that date to create a valid MM/DD/YYYY format. That processing works fine until I encounter an empty field that is apparently not empty. Looking for a way to catch the empty field in all records so I don't attempt to process it.