I am linking to an Excel Spreadsheet from Access and when I open a Recordset and loop through the fields going across I run into an "numeric field overflow. /Error 3021" when I run into a blank date field. If I look at where the cursor is stoping, it is where the blank field is in the spreadsheet. The spreadsheet has a blank/null field, but if I open the linked spreadsheet/table and view it in access there is a #NUM! in all of the blank date fields. Is there some way around this similar to the [" StrVar = IIf(IsNull(Rst1(IndexCnt)), "", Rst1(IndexCnt))"] piece of code I have highlighted below??? I tried the IIF(Nz function also to try to force the cursor to turn the value into an empty string.
Rst2 is just a list of Rst1's linked fieldnames, because I want the fields to be mapped to a different column in Rst2. Please ignore the reasons behind why I'm doing this because it is to lengthy to discuss. I just wish to loop through a certain range of fields sucessfully to append to a destination table. I could have setup a simple append query, but the linked table will be created dynamically at run time and the field names from the linked spreadsheet may or may not be the same each time it is linked and this will error out the append query. Let's assume if it is a format issue in Excel, I won't be able to control. How could I force the cursor to just look at the value of the field. Any help would be very much appreciated...
Here is a piece of the code...
strRcdSet = "SourceSpreadsheet" '*This will be the linked spreadsheet/table
strRcdSet2 = "FieldList" '*please ignore this for now...
strRcdSet3 = "DestinationTable" 'Data read from strRcdSet one will be written here'
Set Rst1 = Thisdbase.OpenRecordset(StrRcdSet, dbOpenDynaset)
Set Rst2 = Thisdbase.OpenRecordset(StrRcdSet2, dbOpenTable)
Set Rst3 = Thisdbase.OpenRecordset(StrRcdSet3, dbOpenTable)
Rst1.MoveNext
Do Until Rst1.EOF = True
IndexCnt = 0
Rst2.MoveFirst
Rst3.AddNew
Do Until IndexCnt = Rst1.Fields.Count
'***RECEIVING NUMBERIC OVERFLOW ON THE NEXT LINE***
StrVar = IIf(IsNull(Rst1(IndexCnt)), "", Rst1(IndexCnt))
If Not StrVar = "" Then
Rst3(Rst2(1)) = Rst1(IndexCnt)
End If
IndexCnt = IndexCnt + 1
Rst2.MoveNext
If IndexCnt > Rst1.Fields.Count Then
Exit Do
End If
Loop
Rst1.MoveNext
Rst3.Update
Loop
Rst1.Close
Rst2.Close
Rst3.Close
Set Thisdbase = Nothing
Rst2 is just a list of Rst1's linked fieldnames, because I want the fields to be mapped to a different column in Rst2. Please ignore the reasons behind why I'm doing this because it is to lengthy to discuss. I just wish to loop through a certain range of fields sucessfully to append to a destination table. I could have setup a simple append query, but the linked table will be created dynamically at run time and the field names from the linked spreadsheet may or may not be the same each time it is linked and this will error out the append query. Let's assume if it is a format issue in Excel, I won't be able to control. How could I force the cursor to just look at the value of the field. Any help would be very much appreciated...
Here is a piece of the code...
strRcdSet = "SourceSpreadsheet" '*This will be the linked spreadsheet/table
strRcdSet2 = "FieldList" '*please ignore this for now...
strRcdSet3 = "DestinationTable" 'Data read from strRcdSet one will be written here'
Set Rst1 = Thisdbase.OpenRecordset(StrRcdSet, dbOpenDynaset)
Set Rst2 = Thisdbase.OpenRecordset(StrRcdSet2, dbOpenTable)
Set Rst3 = Thisdbase.OpenRecordset(StrRcdSet3, dbOpenTable)
Rst1.MoveNext
Do Until Rst1.EOF = True
IndexCnt = 0
Rst2.MoveFirst
Rst3.AddNew
Do Until IndexCnt = Rst1.Fields.Count
'***RECEIVING NUMBERIC OVERFLOW ON THE NEXT LINE***
StrVar = IIf(IsNull(Rst1(IndexCnt)), "", Rst1(IndexCnt))
If Not StrVar = "" Then
Rst3(Rst2(1)) = Rst1(IndexCnt)
End If
IndexCnt = IndexCnt + 1
Rst2.MoveNext
If IndexCnt > Rst1.Fields.Count Then
Exit Do
End If
Loop
Rst1.MoveNext
Rst3.Update
Loop
Rst1.Close
Rst2.Close
Rst3.Close
Set Thisdbase = Nothing
