I am linking to an Excel Spreadsheet from Access and when I open a
Recordset and loop through the fields going across using a cursor, I run into an
"numeric field overflow. /Error 3021" when I run into a blank field (i.e. Text, Dates) . 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 and this did not work either.
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 too 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 =T hisdbase.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
Recordset and loop through the fields going across using a cursor, I run into an
"numeric field overflow. /Error 3021" when I run into a blank field (i.e. Text, Dates) . 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 and this did not work either.
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 too 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 =T hisdbase.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
