473,842 Members | 1,924 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

IsNull anomaly

Please can someone help me, I think I may go mad with this one:

Do While Not IsNull(CDate(Fo rmatDateTime(rs t!F1.Value, vbShortDate)))
If IsNull(CDate(Fo rmatDateTime(rs t!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

Jul 20 '06 #1
16 2946
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.

Jul 20 '06 #2
Are you using a counter or something that forces a rst.MoveNext? If
not, it will process the first record endlessly.

madeleine wrote:
Please can someone help me, I think I may go mad with this one:

Do While Not IsNull(CDate(Fo rmatDateTime(rs t!F1.Value, vbShortDate)))
If IsNull(CDate(Fo rmatDateTime(rs t!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
Jul 20 '06 #3
>
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.
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.Applicati on
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.Worksp aces(0).Databas es(0)
Set rst = Current_DB.Open Recordset("Temp _Historical_Hou rs")
'MsgBox "oh my God"
Set rst_update = Current_DB.Open Recordset("AOD_ Historical_Hour s")
'MsgBox "and even"
Set rst_csci = Current_DB.Open Recordset("CSCI ")
'MsgBox "and then"

'startcol = "B"
'endcol = "R"
XLTARGET = "Temp_Historica l_Hours"

DoCmd.SetWarnin gs False
DoCmd.RunSQL "delete * from Temp_Historical _Hours" 'Clear table
DoCmd.RunSQL "delete * from AOD_Historical_ Hours" 'Clear table
MsgBox "running"

Set xlApp = CreateObject("E xcel.Applicatio n")
'Path of the file being imported
Workbook_Array = Array("D:\N15_C harts\MSRS_&_RA P 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.Workshee ts
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.TransferS preadsheet acImport, _
acSpreadsheetTy peExcel5, _
XLTARGET, _
strPath, _
False, _
xlSheet.Name & "!A25:J80"
Set rst = Current_DB.Open Recordset("Temp _Historical_Hou rs")

'Get the overarching data into local variables
CSCI = xlSheet.Name
csci_flag = False
rst_csci.MoveFi rst

Do While Not rst_csci.EOF And Not csci_flag

If CSCI Like "*" & rst_csci!CSCI.V alue & "*" Then
csci_flag = True
CSCI = rst_csci!CSCI.V alue
End If
rst_csci.MoveNe xt
Loop
AOD = rst!F1.Value
Phase = rst!F2.Value

'Get today's date
Today = CDate(FormatDat eTime(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(FormatDat eTime(rst!F1.Va lue, vbShortDate))

Do While Not IsEmpty(CDate(F ormatDateTime(r st!F1.Value,
vbShortDate)))
If IsEmpty(CDate(F ormatDateTime(r st!F1.Value, vbShortDate)))
Then
MsgBox "this is not actually possible"
End If

'On Error Resume Next
'MsgBox "I'm in"
rst_update.AddN ew
rst_update![AOD] = AOD
rst_update![Week Ending] =
CDate(FormatDat eTime(rst!F1.Va lue, vbShortDate))
If IsNull(rst_upda te![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.Upda te
'MsgBox "and I should have done summat"
rst.MoveNext
'week_end = CDate(FormatDat eTime(rst!F1.Va lue,
vbShortDate))
Loop

'end of status addition

'Commit changes to the recordset
'rst_update.Upd ate
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.Applicat ion.Quit

'Delete data that does not apply
DoCmd.OpenQuery "Delete_bla nks"

'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_AOD 1"
DoCmd.OpenQuery "Append_CSCI_AO D"
'DoCmd.OpenQuer y "Append_AOD_Pha se"
'DoCmd.OpenQuer y "Append_HLD "
'DoCmd.OpenQuer y "Append_DD"
'DoCmd.OpenQuer y "Append_Cod e"
'DoCmd.OpenQuer y "Append_UCT "
'DoCmd.OpenQuer y "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

Jul 20 '06 #4

ManningFan wrote:
Are you using a counter or something that forces a rst.MoveNext? If
not, it will process the first record endlessly.
I've inserted the function and hopefully you can tell me the blindingly
obvious solutions...fin gers crossed:

Function Get_Historical_ AOD_Hours()

On Error GoTo Err_Handler
Dim xlApp As Object ' Excel.Applicati on
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.Worksp aces(0).Databas es(0)
Set rst = Current_DB.Open Recordset("Temp _Historical_Hou rs")
'MsgBox "oh my God"
Set rst_update = Current_DB.Open Recordset("AOD_ Historical_Hour s")
'MsgBox "and even"
Set rst_csci = Current_DB.Open Recordset("CSCI ")
'MsgBox "and then"

'startcol = "B"
'endcol = "R"
XLTARGET = "Temp_Historica l_Hours"

DoCmd.SetWarnin gs False
DoCmd.RunSQL "delete * from Temp_Historical _Hours" 'Clear table
DoCmd.RunSQL "delete * from AOD_Historical_ Hours" 'Clear table
MsgBox "running"

Set xlApp = CreateObject("E xcel.Applicatio n")
'Path of the file being imported
Workbook_Array = Array("D:\N15_C harts\MSRS_&_RA P 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.Workshee ts
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.TransferS preadsheet acImport, _
acSpreadsheetTy peExcel5, _
XLTARGET, _
strPath, _
False, _
xlSheet.Name & "!A25:J80"
Set rst = Current_DB.Open Recordset("Temp _Historical_Hou rs")

'Get the overarching data into local variables
CSCI = xlSheet.Name
csci_flag = False
rst_csci.MoveFi rst

Do While Not rst_csci.EOF And Not csci_flag

If CSCI Like "*" & rst_csci!CSCI.V alue & "*" Then
csci_flag = True
CSCI = rst_csci!CSCI.V alue
End If
rst_csci.MoveNe xt
Loop
AOD = rst!F1.Value
Phase = rst!F2.Value

'Get today's date
Today = CDate(FormatDat eTime(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(FormatDat eTime(rst!F1.Va lue, vbShortDate))

Do While Not IsEmpty(CDate(F ormatDateTime(r st!F1.Value,
vbShortDate)))
If IsEmpty(CDate(F ormatDateTime(r st!F1.Value, vbShortDate)))
Then
MsgBox "this is not actually possible"
End If

'On Error Resume Next
'MsgBox "I'm in"
rst_update.AddN ew
rst_update![AOD] = AOD
rst_update![Week Ending] =
CDate(FormatDat eTime(rst!F1.Va lue, vbShortDate))
If IsNull(rst_upda te![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.Upda te
'MsgBox "and I should have done summat"
rst.MoveNext
'week_end = CDate(FormatDat eTime(rst!F1.Va lue,
vbShortDate))
Loop

'end of status addition

'Commit changes to the recordset
'rst_update.Upd ate
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.Applicat ion.Quit

'Delete data that does not apply
DoCmd.OpenQuery "Delete_bla nks"

'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_AOD 1"
DoCmd.OpenQuery "Append_CSCI_AO D"
'DoCmd.OpenQuer y "Append_AOD_Pha se"
'DoCmd.OpenQuer y "Append_HLD "
'DoCmd.OpenQuer y "Append_DD"
'DoCmd.OpenQuer y "Append_Cod e"
'DoCmd.OpenQuer y "Append_UCT "
'DoCmd.OpenQuer y "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

Jul 20 '06 #5
There's a loop in there, so you should be fine. Have you tried adding
a breakpoint on the "Do While" statement and stepping through the code?
Sometimes it becomes instantly obvious what the problem is that way.

madeleine wrote:
ManningFan wrote:
Are you using a counter or something that forces a rst.MoveNext? If
not, it will process the first record endlessly.
I've inserted the function and hopefully you can tell me the blindingly
obvious solutions...fin gers crossed:

Function Get_Historical_ AOD_Hours()

On Error GoTo Err_Handler
Dim xlApp As Object ' Excel.Applicati on
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.Worksp aces(0).Databas es(0)
Set rst = Current_DB.Open Recordset("Temp _Historical_Hou rs")
'MsgBox "oh my God"
Set rst_update = Current_DB.Open Recordset("AOD_ Historical_Hour s")
'MsgBox "and even"
Set rst_csci = Current_DB.Open Recordset("CSCI ")
'MsgBox "and then"

'startcol = "B"
'endcol = "R"
XLTARGET = "Temp_Historica l_Hours"

DoCmd.SetWarnin gs False
DoCmd.RunSQL "delete * from Temp_Historical _Hours" 'Clear table
DoCmd.RunSQL "delete * from AOD_Historical_ Hours" 'Clear table
MsgBox "running"

Set xlApp = CreateObject("E xcel.Applicatio n")
'Path of the file being imported
Workbook_Array = Array("D:\N15_C harts\MSRS_&_RA P 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.Workshee ts
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.TransferS preadsheet acImport, _
acSpreadsheetTy peExcel5, _
XLTARGET, _
strPath, _
False, _
xlSheet.Name & "!A25:J80"
Set rst = Current_DB.Open Recordset("Temp _Historical_Hou rs")

'Get the overarching data into local variables
CSCI = xlSheet.Name
csci_flag = False
rst_csci.MoveFi rst

Do While Not rst_csci.EOF And Not csci_flag

If CSCI Like "*" & rst_csci!CSCI.V alue & "*" Then
csci_flag = True
CSCI = rst_csci!CSCI.V alue
End If
rst_csci.MoveNe xt
Loop
AOD = rst!F1.Value
Phase = rst!F2.Value

'Get today's date
Today = CDate(FormatDat eTime(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(FormatDat eTime(rst!F1.Va lue, vbShortDate))

Do While Not IsEmpty(CDate(F ormatDateTime(r st!F1.Value,
vbShortDate)))
If IsEmpty(CDate(F ormatDateTime(r st!F1.Value, vbShortDate)))
Then
MsgBox "this is not actually possible"
End If

'On Error Resume Next
'MsgBox "I'm in"
rst_update.AddN ew
rst_update![AOD] = AOD
rst_update![Week Ending] =
CDate(FormatDat eTime(rst!F1.Va lue, vbShortDate))
If IsNull(rst_upda te![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.Upda te
'MsgBox "and I should have done summat"
rst.MoveNext
'week_end = CDate(FormatDat eTime(rst!F1.Va lue,
vbShortDate))
Loop

'end of status addition

'Commit changes to the recordset
'rst_update.Upd ate
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.Applicat ion.Quit

'Delete data that does not apply
DoCmd.OpenQuery "Delete_bla nks"

'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_AOD 1"
DoCmd.OpenQuery "Append_CSCI_AO D"
'DoCmd.OpenQuer y "Append_AOD_Pha se"
'DoCmd.OpenQuer y "Append_HLD "
'DoCmd.OpenQuer y "Append_DD"
'DoCmd.OpenQuer y "Append_Cod e"
'DoCmd.OpenQuer y "Append_UCT "
'DoCmd.OpenQuer y "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
Jul 20 '06 #6

ManningFan wrote:
There's a loop in there, so you should be fine. Have you tried adding
a breakpoint on the "Do While" statement and stepping through the code?
Sometimes it becomes instantly obvious what the problem is that way.
That's the problem, it should be fine, it shouldn't be possible to have
a loop condition that if a value is null it goes into the loop and then
immediately afterwards a check on whether the value is null returns
with the fact its true.

Unfortunately stepping through the code isn't an option as it takes
about 1000 or so loops to hit the first null value.

My only thought is that IsNull somehow changes the value of the field
that is being checked. Is there some other way I can check for
something being Null? IsEmpty does exactly the same thing, have I gone
quite mad?

Jul 20 '06 #7
Sorry I should have said a "loop condition that if a value is not null"
madeleine wrote:
ManningFan wrote:
There's a loop in there, so you should be fine. Have you tried adding
a breakpoint on the "Do While" statement and stepping through the code?
Sometimes it becomes instantly obvious what the problem is that way.
That's the problem, it should be fine, it shouldn't be possible to have
a loop condition that if a value is null it goes into the loop and then
immediately afterwards a check on whether the value is null returns
with the fact its true.

Unfortunately stepping through the code isn't an option as it takes
about 1000 or so loops to hit the first null value.

My only thought is that IsNull somehow changes the value of the field
that is being checked. Is there some other way I can check for
something being Null? IsEmpty does exactly the same thing, have I gone
quite mad?
Jul 20 '06 #8
Then put a break on the msgbox and check the values of all your
variables by using the debug screen (just in case you don't know, type
?VarName in the debug screen where VarName is the name of your variable
and it will give you the current value, or you can hover your cursor
over the variable and it will display it onscreen also). You have to
figure out what's causing the issue, and without the dataset in front
of me I can't be of much help in that respect.

madeleine wrote:
Sorry I should have said a "loop condition that if a value is not null"
madeleine wrote:
ManningFan wrote:
There's a loop in there, so you should be fine. Have you tried adding
a breakpoint on the "Do While" statement and stepping through the code?
Sometimes it becomes instantly obvious what the problem is that way.
>
That's the problem, it should be fine, it shouldn't be possible to have
a loop condition that if a value is null it goes into the loop and then
immediately afterwards a check on whether the value is null returns
with the fact its true.

Unfortunately stepping through the code isn't an option as it takes
about 1000 or so loops to hit the first null value.

My only thought is that IsNull somehow changes the value of the field
that is being checked. Is there some other way I can check for
something being Null? IsEmpty does exactly the same thing, have I gone
quite mad?
Jul 20 '06 #9
Aha very good idea, thanks for that.

ManningFan wrote:
Then put a break on the msgbox and check the values of all your
variables by using the debug screen (just in case you don't know, type
?VarName in the debug screen where VarName is the name of your variable
and it will give you the current value, or you can hover your cursor
over the variable and it will display it onscreen also). You have to
figure out what's causing the issue, and without the dataset in front
of me I can't be of much help in that respect.

madeleine wrote:
Sorry I should have said a "loop condition that if a value is not null"
madeleine wrote:
ManningFan wrote:
>
There's a loop in there, so you should be fine. Have you tried adding
a breakpoint on the "Do While" statement and stepping through the code?
Sometimes it becomes instantly obvious what the problem is that way.

That's the problem, it should be fine, it shouldn't be possible to have
a loop condition that if a value is null it goes into the loop and then
immediately afterwards a check on whether the value is null returns
with the fact its true.
>
Unfortunately stepping through the code isn't an option as it takes
about 1000 or so loops to hit the first null value.
>
My only thought is that IsNull somehow changes the value of the field
that is being checked. Is there some other way I can check for
something being Null? IsEmpty does exactly the same thing, have I gone
quite mad?
Jul 20 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
14289
by: Bob Cottis | last post by:
I am getting wierd behaviour with IsNull in ASP. I am passing a string (which may be null) to a function. When the string is null, IsNull seems to return false the first time it is called, then True the second time. The code follows, am I doing something wrong, or is this a bug? Bob function unquote(st) 'make database string suitable for
2
13965
by: Trev | last post by:
I have two tables, tblMTO and tblIMPORT_MTO. If I import an entire MTO into the import table I want to create a delta from it (i.e. leave only the changed items). I have a view (simplified) SELECT dbo.tblIMPORT_MTO.ImportID, dbo.tblIMPORT_MTO.MTONo, dbo.tblIMPORT_MTO.Rev AS New_Rev, dbo.tblMTO.Rev AS Old_Rev FROM dbo.tblIMPORT_MTO LEFT OUTER JOIN dbo.tblMTO ON dbo.tblIMPORT_MTO.MTONo = dbo.tblMTO.MTONo Now to get all rows...
6
10803
by: Eric J Owens | last post by:
TIA! I recently moved some forms from an a2k mdb file to an a2k adp. There is now an error when opening one of the forms 'the isnull function requires 2 arguments', but I only find references to IsNull() having 1 argument. Here is an example of the offending code: ----------------------------- Private Sub AEnd_DblClick(Cancel As Integer)
16
2190
by: clintonG | last post by:
At design-time the application just decides to go boom claiming it can't find a dll. This occurs sporadically. Doing a simple edit in the HTML for example and then viewing the application has caused the application to go boom. Sometimes the page will compile and run using F5 and others not. So I do the web search tango looking around the blogs and the tuts and determine I should go into Temporary ASP.NET Files and delete the directory...
4
10049
by: Paul Spratley | last post by:
Hi all Firstly this my first time posting to technical groups - so any mistakes I apologise for in advance. I am trying to count records in several secondary tables for the same run in a primary table. However, there might be no records in these secondary tables for the specific run. Hence the sql below returns nulls.
2
9194
by: Raoul Watson | last post by:
I have used isNull statement for as long as I have used VB.. Recently I am devugging a program and it is very clear that the "IsNull" function sometimes would return a true even when the value is not Null. Any wild shot or theory as to why this is happening is appreciated. Also, any other alternative is appreciated since I am stuck and cannot proceed without fixing this issue (tried = Null and that doesn't work either). Basically, I...
4
5101
by: jimm.sander | last post by:
Hello, Problem: Im using isnull() in vbscript to determine if a field returned from a ado object call is in fact null. The problem is when I use isnull in anything other than a response.write() I get the following error: Error Type: ADODB.Recordset (0x800A0CC1) Item cannot be found in the collection corresponding to the requested name or ordinal.
1
2813
by: mai | last post by:
Hi everyone, i'm trying to exhibit FIFO anomaly(page replacement algorithm),, I searched over 2000 random strings but i couldnt find any anomaly,, am i I doing it right?,, Please help,,,The following is the code,, #include <iostream> #include <string> #include <stdio.h> #include <stdlib.h> #include <ctime // For time()
16
1981
kcdoell
by: kcdoell | last post by:
Hello: When I write new record I have the following code: Private Sub Form_BeforeUpdate(Cancel As Integer) Me!RowIsActive = False 'When a user is creating a new record the following code inserts the MonthID, YearID and
0
9709
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10936
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10669
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9448
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7853
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5695
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5882
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4498
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4085
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.