IsNull异常 [英] IsNull anomaly
问题描述
请有人可以帮助我，我想我可能会生气这个：
Do Not Not IsNull（CDate（FormatDateTime（rst！F1.Value，vbShortDate）））
如果IsNull（CDate（FormatDateTime（rst！F1.Value，vbShortDate）））
那么
MsgBox这实际上是不可能的 ;
结束如果
-
-
-
由于某种原因我结束这个循环并得到消息这不是
实际上可能弹出....没有这个检查我最终在一个
无限循环。
我想要做的是从excel中获取大量数据然后，如果
日期不为空，将该行添加到数据库中的表中，我将无法解决的问题是只是检查日期是否为空，因为你可以看到上面的某些内容出现了错误。
我已经尝试将它添加到局部变量但仍然点击同样的
问题。
谢谢
Please can someone help me, I think I may go mad with this one:
Do While Not IsNull(CDate(FormatDateTime(rst!F1.Value, vbShortDate)))
If IsNull(CDate(FormatDateTime(rst!F1.Value, vbShortDate)))
Then
MsgBox "this is not actually possible"
End If
-
-
-
For some reason I end up in this loop and get the message "this is not
actually possible" popping up....without this check I end up in an
endless loop.
What I''m trying to do is suck in a load of data from excel and then, if
the date is not null, add the row to a table in the database, what I
can''t work out is how to just check whether the date is not null, as
you can see above something is going wonderfully wrong.
I''ve tried feeding it into a local variable but still hit the same
problem.
Thanks
推荐答案
我想要做的是从excel中获取大量数据然后，如果
日期不为null，则将行添加到表中在数据库中，我无法解决的问题是如何检查日期是否为空，因为你可以看到上面的某些内容出现了错误的错误。
What I''m trying to do is suck in a load of data from excel and then, if
the date is not null, add the row to a table in the database, what I
can''t work out is how to just check whether the date is not null, as
you can see above something is going wonderfully wrong.
哦，你这样做的方式比以前更难。如何将
链接到电子表格，然后查询所需的数据。然后你
可以把它变成追加查询，你就完成了。无需代码。
Oh, you''re making this WAY harder than it has to be. How about linking
to the Spreadsheet, and then querying for the data you want. then you
can turn that into an append query and you''re done. No code required.
您使用的是计数器还是强制rst.MoveNext的东西？如果
没有，它将无休止地处理第一条记录。
madeleine写道：
Are you using a counter or something that forces a rst.MoveNext? If
not, it will process the first record endlessly.
madeleine wrote:
请有人可以帮助我，我想我可能会对此感到生气：
Do Not Not IsNull（CDate（FormatDateTime（rst！F1.Value，vbShortDate）））
如果IsNull（CDate（FormatDateTime（rst！F1.Value，vbShortDate）））
那么
MsgBox这实际上是不可能的
结束如果
-
-
-
由于某种原因我最终在这个循环中得到消息这不是
实际上可能弹出....没有这个检查我最终在一个
无限循环。
我想要做的就是吸一个负载来自excel的数据然后，如果
日期不为空，请将行添加到数据库中的表中，我将无法获得
不能解决问题的方法是如何检查日期是否为空，因为你可以看到上面的某些内容非常错误。
我试过了将它送入局部变量但仍然遇到同样的问题。
谢谢
Please can someone help me, I think I may go mad with this one:
Do While Not IsNull(CDate(FormatDateTime(rst!F1.Value, vbShortDate)))
If IsNull(CDate(FormatDateTime(rst!F1.Value, vbShortDate)))
Then
MsgBox "this is not actually possible"
End If
-
-
-
For some reason I end up in this loop and get the message "this is not
actually possible" popping up....without this check I end up in an
endless loop.
What I''m trying to do is suck in a load of data from excel and then, if
the date is not null, add the row to a table in the database, what I
can''t work out is how to just check whether the date is not null, as
you can see above something is going wonderfully wrong.
I''ve tried feeding it into a local variable but still hit the same
problem.
Thanks
>
哦，你正在努力实现这一目标。如何将
链接到电子表格，然后查询所需的数据。然后你
可以把它变成追加查询，你就完成了。无需代码。
>
Oh, you''re making this WAY harder than it has to be. How about linking
to the Spreadsheet, and then querying for the data you want. then you
can turn that into an append query and you''re done. No code required.
如果我可以的话，问题是我有大约10个工作簿带有一个数字
的工作表可以添加或带走从动态，
因此获取所有数据的唯一方法是遍历工作簿中的所有工作表并获取相关的工作表。 />
名字，我在这里插入了代码：
函数Get_Historical_AOD_Hours（）
错误GoTo Err_Handler
Dim xlApp As Object''Excel.Application
Dim xlBook As Object''Excel.Workbook
Dim xlSheet As Object''Excel.Worksheet
Dim strPath As String
Dim Workbook_Array As Variant
Dim intI As Variant
Dim rowcount As Variant
''Dim startcol As Variant
''Dim endcol As Variant
Dim sheetRange As String
''Dim range作为Variant
''Dim stage As String
Dim week_end As Variant
Dim CSCI As Variant
Dim AOD As Variant
Dim Phase As Variant
Dim Current_DB作为数据库
Dim rst作为记录集
Dim rst_update作为记录集
Dim rst_csci作为记录集
''Dim rst_status作为Recordset
Dim XLTARGET作为Variant
今天变暗作为Variant
Dim csci_flag作为布尔值
''MsgBox'我想开始'
设置Current_DB = DBEngine.Workspaces（0）.Databases（0）
设置rst = Current_DB.OpenRecordset（ Temp_Historical_Hours）
''MsgBox'我的天啊
设置rst_update = Current_DB.OpenRecordset（" AOD_Historical_Hours"）
''MsgBox'，甚至是'
设置rst_csci = Current_DB.OpenRecordset（" CSCI"）
''MsgBox"然后"
''startcol =" B"
''endcol =" R&qu ot;
XLTARGET =" Temp_Historical_Hours"
DoCmd.SetWarnings False
DoCmd.RunSQL" delete * from Temp_Historical_Hours" ''清除表
DoCmd.RunSQL" delete * from AOD_Historical_Hours" ''清除表
MsgBox"运行"
设置xlApp = CreateObject（" Excel.Application"）
' '正在导入文件的路径
Workbook_Array =数组（&D：\ N15_Charts\MSRS_& _RAP CTR iFACTS.xls"，
" D：\\ \\ N15_Charts\RDP& CMS CTR iFACTS.xls"，" D：\ N15_Charts\SEC CTR
iFACTS.xls"，" D：\ N15_Charts \ WDM& SCC CTR iFACTS.xls"，
" D：\ N15_Charts\CGW_& _SIS CTR iFACTS.xls"，" D：\ N15_Charts\CxSS CTR
iFACTS.xls"，" D：\N15_Charts\FDP CTR iFACTS.xls"，
" D：\ N15_Charts\ADS_& _TOOLS CTR iFACTS.xls"，" ; D：\ N15_Charts\CMTOOLS CTR
iFACTS.xls"）
每个工作簿中的intI_Array
strPath = intI
''建立工作簿字符串
设置xlBook = xl App.workbooks.Open（strPath，False，True）
''遍历工作簿中的所有表格
For xlSheet in xlBook.Worksheets
On Error Resume Next
sheetRange = xlSheet.Name
''如果工作表的名称包含EV然后导入数据
如果sheetRange喜欢* EV *然后
''从工作表中传输数据
DoCmd.TransferSpreadsheet acImport，_
acSpreadsheetTypeExcel5，_
XLTARGET，_
strPath，_
错误，_
xlSheet.Name& "！A25：J80"
设置rst = Current_DB.OpenRecordset（" Temp_Historical_Hours"）
''将全局数据输入局部变量
CSCI = xlSheet.Name
csci_flag = False
rst_csci.MoveFirst
Do While Not rst_csci。 EOF而不是csci_flag
如果CSCI喜欢* &安培; rst_csci！CSCI.Value& " * QUOT;然后
csci_flag =真
CSCI = rst_csci！CSCI.Value
结束如果
rst_csci.MoveNext
循环
AOD = rst！F1.Value
阶段= rst！F2.Value
' '今天'的日期
今天= CDate（FormatDateTime（现在，vbShortDate））
''移动到第一个记录中的日期，知道这一点
电子表格
rst.MoveNext
rst.MoveNext
''转换为日期格式
week_end = CDate（FormatDateTime（rst！F1.Value，vbShortDate））
Do Is Not IsEmpty（CDate（FormatDateTime（rst！F1.Value，
）
vbShortDate）））
If IsEmpty（CDate（FormatDateTime（rst！F1.Value，vbShortDate）））
然后
MsgBox这实际上是不可能的
结束如果
''On Error Resume Next
''MsgBox"我在'
rst_update.AddNew
rst_update！[AOD] = AOD
rst_更新！[周结束] =
CDate（FormatDateTime（rst！F1.Value，vbShortDate））
If IsNull（rst_update！[Week Ending]）然后
MsgBox"这让我绕过弯道
结束如果
''MsgBox rst_update！[周结束]
rst_update！[CSCI] = CSCI
''MsgBox rst_update！[CSCI]
rst_update！[阶段] =阶段
''MsgBox rst_update！[阶段]
rst_update！[时间类型] ="实施"
rst_update ！[小时总计] = rst！F2.Value
''MsgBox rst_update！[小时总计]
rst_update！[Phase％Complete] = rst！F10.Value
''MsgBox rst_update！[完成阶段％]
rst_update.Update
''MsgBox"我应该做summat"
rst.MoveNext
''week_end = CDate（FormatDateTime（rst！F1.Value，
vbShortDate））
循环
''结束o f状态添加
''提交对记录集的更改
''rst_update.Update
DoCmd.RunSQL" delete *来自Temp_Historical_Hours" ''清楚
表
结束如果
''循环到工作簿中的下一个工作表
下一页
''MsgBox"完整" &安培; strPath
''循环到数组中的下一个工作簿
下一页
xlBook.Application.Quit
''删除不适用的数据
DoCmd.OpenQuery" Delete_blanks"
''从表AOD中删除上周数据， CSCI_AOD，CSCI_AOD_Phase，
CSCI_AOD_HLD（DD，代码，CBT，UCT）
DoCmd.RunSQL" delete * from CSCI_AOD_HLD"
DoCmd。 RunSQL" delete * from CSCI_AOD_DD"
DoCmd.RunSQL" delete * from CSCI_AOD_Code"
DoCmd.RunSQL" delete * from CSCI_AOD_UCT"
DoCmd.RunSQL" delete * from CSCI_AOD_CBT"
DoCmd.RunSQL" delete * from CSCI_AOD_Phase"
DoCmd.RunSQL" delete * from CSCI_AOD"
DoCmd.RunSQL从AOD中删除*
''运行追加查询以将本周的数据添加到$ b $中b DoCmd.OpenQuery" Append_AOD1"
DoCmd.Open查询Append_CSCI_AOD
''DoCmd.OpenQuery" Append_AOD_Phase"
''DoCmd.OpenQuery" Append_HLD"
''DoCmd .OpenQueryAppend_DD
''DoCmd.OpenQuery" Append_Code"
''DoCmd.OpenQuery" Append_UCT"
'' DoCmd.OpenQueryAppend_CBT
''错误处理程序用于在字段为空时捕获错误
退出函数
Exit_Handler：
On Error Resume Next
Err_Handler：
On Error Resume Next
结束功能
If only I could, the problem is I have about 10 workbooks with a number
of worksheets that can be added to or taken away from dynamically,
therefore the only way to get all the data in is to loop through all of
the worksheets in the workbook and pick up the ones with the relevant
name, I''ve inserted the code here:
Function Get_Historical_AOD_Hours()
On Error GoTo Err_Handler
Dim xlApp As Object '' Excel.Application
Dim xlBook As Object '' Excel.Workbook
Dim xlSheet As Object '' Excel.Worksheet
Dim strPath As String
Dim Workbook_Array As Variant
Dim intI As Variant
Dim rowcount As Variant
''Dim startcol As Variant
''Dim endcol As Variant
Dim sheetRange As String
''Dim range As Variant
''Dim stage As String
Dim week_end As Variant
Dim CSCI As Variant
Dim AOD As Variant
Dim Phase As Variant
Dim Current_DB As Database
Dim rst As Recordset
Dim rst_update As Recordset
Dim rst_csci As Recordset
''Dim rst_status As Recordset
Dim XLTARGET As Variant
Dim Today As Variant
Dim csci_flag As Boolean
''MsgBox "i''d like to start"
Set Current_DB = DBEngine.Workspaces(0).Databases(0)
Set rst = Current_DB.OpenRecordset("Temp_Historical_Hours")
''MsgBox "oh my God"
Set rst_update = Current_DB.OpenRecordset("AOD_Historical_Hours")
''MsgBox "and even"
Set rst_csci = Current_DB.OpenRecordset("CSCI")
''MsgBox "and then"
''startcol = "B"
''endcol = "R"
XLTARGET = "Temp_Historical_Hours"
DoCmd.SetWarnings False
DoCmd.RunSQL "delete * from Temp_Historical_Hours" ''Clear table
DoCmd.RunSQL "delete * from AOD_Historical_Hours" ''Clear table
MsgBox "running"
Set xlApp = CreateObject("Excel.Application")
''Path of the file being imported
Workbook_Array = Array("D:\N15_Charts\MSRS_&_RAP CTR iFACTS.xls",
"D:\N15_Charts\RDP & CMS CTR iFACTS.xls", "D:\N15_Charts\SEC CTR
iFACTS.xls", "D:\N15_Charts\WDM & SCC CTR iFACTS.xls",
"D:\N15_Charts\CGW_&_SIS CTR iFACTS.xls", "D:\N15_Charts\CxSS CTR
iFACTS.xls", "D:\N15_Charts\FDP CTR iFACTS.xls",
"D:\N15_Charts\ADS_&_TOOLS CTR iFACTS.xls", "D:\N15_Charts\CMTOOLS CTR
iFACTS.xls")
For Each intI In Workbook_Array
strPath = intI
''Establish workbook string
Set xlBook = xlApp.workbooks.Open(strPath, False, True)
''Loop through all the Sheets in the workbook
For Each xlSheet In xlBook.Worksheets
On Error Resume Next
sheetRange = xlSheet.Name
''if the worksheet''s name contains EV then import the data
If sheetRange Like "*EV*" Then
''Transfer the data from the worksheet
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel5, _
XLTARGET, _
strPath, _
False, _
xlSheet.Name & "!A25:J80"
Set rst = Current_DB.OpenRecordset("Temp_Historical_Hours")
''Get the overarching data into local variables
CSCI = xlSheet.Name
csci_flag = False
rst_csci.MoveFirst
Do While Not rst_csci.EOF And Not csci_flag
If CSCI Like "*" & rst_csci!CSCI.Value & "*" Then
csci_flag = True
CSCI = rst_csci!CSCI.Value
End If
rst_csci.MoveNext
Loop
AOD = rst!F1.Value
Phase = rst!F2.Value
''Get today''s date
Today = CDate(FormatDateTime(Now, vbShortDate))
''move to the first record with a date in it, know this from
the spreadsheet
rst.MoveNext
rst.MoveNext
''convert to date format
week_end = CDate(FormatDateTime(rst!F1.Value, vbShortDate))
Do While Not IsEmpty(CDate(FormatDateTime(rst!F1.Value,
vbShortDate)))
If IsEmpty(CDate(FormatDateTime(rst!F1.Value, vbShortDate)))
Then
MsgBox "this is not actually possible"
End If
''On Error Resume Next
''MsgBox "I''m in"
rst_update.AddNew
rst_update![AOD] = AOD
rst_update![Week Ending] =
CDate(FormatDateTime(rst!F1.Value, vbShortDate))
If IsNull(rst_update![Week Ending]) Then
MsgBox "this is driving me round the bend"
End If
''MsgBox rst_update![Week Ending]
rst_update![CSCI] = CSCI
''MsgBox rst_update![CSCI]
rst_update![Phase] = Phase
''MsgBox rst_update![Phase]
rst_update![Hours Type] = "Implementation"
''MsgBox rst_update![Hours Type]
rst_update![Hours Total] = rst!F2.Value
''MsgBox rst_update![Hours Total]
rst_update![Phase % Complete] = rst!F10.Value
''MsgBox rst_update![Phase % Complete]
rst_update.Update
''MsgBox "and I should have done summat"
rst.MoveNext
''week_end = CDate(FormatDateTime(rst!F1.Value,
vbShortDate))
Loop
''end of status addition
''Commit changes to the recordset
''rst_update.Update
DoCmd.RunSQL "delete * from Temp_Historical_Hours" ''Clear
table
End If
''loop to next worksheet in workbook
Next
''MsgBox "Complete " & strPath
''loop to next workbook in array
Next
xlBook.Application.Quit
''Delete data that does not apply
DoCmd.OpenQuery "Delete_blanks"
''Delete last weeks data from tables AOD, CSCI_AOD, CSCI_AOD_Phase,
CSCI_AOD_HLD (DD, Code, CBT, UCT)
DoCmd.RunSQL "delete * from CSCI_AOD_HLD"
DoCmd.RunSQL "delete * from CSCI_AOD_DD"
DoCmd.RunSQL "delete * from CSCI_AOD_Code"
DoCmd.RunSQL "delete * from CSCI_AOD_UCT"
DoCmd.RunSQL "delete * from CSCI_AOD_CBT"
DoCmd.RunSQL "delete * from CSCI_AOD_Phase"
DoCmd.RunSQL "delete * from CSCI_AOD"
DoCmd.RunSQL "delete * from AOD"
''Run the append query to add this week''s data in
DoCmd.OpenQuery "Append_AOD1"
DoCmd.OpenQuery "Append_CSCI_AOD"
''DoCmd.OpenQuery "Append_AOD_Phase"
''DoCmd.OpenQuery "Append_HLD"
''DoCmd.OpenQuery "Append_DD"
''DoCmd.OpenQuery "Append_Code"
''DoCmd.OpenQuery "Append_UCT"
''DoCmd.OpenQuery "Append_CBT"
''Error Handler used to catch error when the fields are blank
Exit Function
Exit_Handler:
On Error Resume Next
Err_Handler:
On Error Resume Next
End Function
这篇关于IsNull异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
