By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,905 Members | 2,083 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,905 IT Pros & Developers. It's quick & easy.

IsNull anomaly

P: n/a
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

Jul 20 '06 #1
Share this Question
Share on Google+
16 Replies


P: n/a
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

P: n/a
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(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
Jul 20 '06 #3

P: n/a
>
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.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

Jul 20 '06 #4

P: n/a

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...fingers crossed:

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

Jul 20 '06 #5

P: n/a
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...fingers crossed:

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
Jul 20 '06 #6

P: n/a

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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a

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.
I've just run it and the value is stated to be Null, so the real
question is why does

Do While Not IsNull(CDate(FormatDateTime(rst!F1.Value, vbShortDate)))

Not take any notice whether the value is null or not, it is always
false to the while loop!!!!

Jul 20 '06 #11

P: n/a
You can check the length of the value and react on that, but that's a
bit "cludgey".

You really need to figure out why it's returning a Null. What's the
value of rst!F1? What's the value of FormatDateTime(rst!F1.Value,
vbShortDate)? And finally, what's the value of
(CDate(FormatDateTime(rst!F1.Value, vbShortDate))? Try picking apart
the expression and see if you can find an error in it. Maybe it's not
formatted to be a date originally?
madeleine wrote:
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.

I've just run it and the value is stated to be Null, so the real
question is why does

Do While Not IsNull(CDate(FormatDateTime(rst!F1.Value, vbShortDate)))

Not take any notice whether the value is null or not, it is always
false to the while loop!!!!
Jul 20 '06 #12

P: n/a
Because its looping through rows in a spreadsheet (well that have been
brought into the database) and needs to get all of the data out where
the date cell is not null I am expecting to be able to use the fact
that its null to get out of the loop.

What is confusing me is how can:

Do While Not IsNull(CDate(FormatDateTime(rst!F1.Value, vbShortDate)))

IsNull above is False
directly afterwards:
If IsNull(CDate(FormatDateTime(rst!F1.Value, vbShortDate))) Then
IsNull is True

Do you think that somehow it can evaluate it to be different values
even though nothing happens in between?
ManningFan wrote:
You can check the length of the value and react on that, but that's a
bit "cludgey".

You really need to figure out why it's returning a Null. What's the
value of rst!F1? What's the value of FormatDateTime(rst!F1.Value,
vbShortDate)? And finally, what's the value of
(CDate(FormatDateTime(rst!F1.Value, vbShortDate))? Try picking apart
the expression and see if you can find an error in it. Maybe it's not
formatted to be a date originally?


I've just run it and the value is stated to be Null, so the real
question is why does

Do While Not IsNull(CDate(FormatDateTime(rst!F1.Value, vbShortDate)))

Not take any notice whether the value is null or not, it is always
false to the while loop!!!!
Jul 20 '06 #13

P: n/a
Try switching the code around so it tests for Null instead of Not Null.
See if that gets you anywhere.

The fact that it's seeing the identical formula as both Null and Not
Null makes me think something strange is going on with the "Do While
Not IsNull" statement. Sometimes VBA reacts funny and you need to
change it to "Is Not Null", you can try that also. You can also try
using a Nz() function to force Null to be 0, which might make it easier
to trap.
madeleine wrote:
Because its looping through rows in a spreadsheet (well that have been
brought into the database) and needs to get all of the data out where
the date cell is not null I am expecting to be able to use the fact
that its null to get out of the loop.

What is confusing me is how can:

Do While Not IsNull(CDate(FormatDateTime(rst!F1.Value, vbShortDate)))

IsNull above is False
directly afterwards:
If IsNull(CDate(FormatDateTime(rst!F1.Value, vbShortDate))) Then
IsNull is True

Do you think that somehow it can evaluate it to be different values
even though nothing happens in between?
ManningFan wrote:
You can check the length of the value and react on that, but that's a
bit "cludgey".

You really need to figure out why it's returning a Null. What's the
value of rst!F1? What's the value of FormatDateTime(rst!F1.Value,
vbShortDate)? And finally, what's the value of
(CDate(FormatDateTime(rst!F1.Value, vbShortDate))? Try picking apart
the expression and see if you can find an error in it. Maybe it's not
formatted to be a date originally?
>
I've just run it and the value is stated to be Null, so the real
question is why does
>
Do While Not IsNull(CDate(FormatDateTime(rst!F1.Value, vbShortDate)))
>
Not take any notice whether the value is null or not, it is always
false to the while loop!!!!
Jul 20 '06 #14

P: n/a
If there are a 100 iterations before you hit the line you want to test, I
think you need to introduce yourself to the Debug.Assert statement.

I find your reported results suspect.

Working your test in Access VBA if we replace the rst!F1.Value with Null it
throws a type mismatch error
So
?FormatDateTime(Null, vbShortDate)

Is ok as this returns an empty string, but
?cdate(FormatDateTime(Null, vbShortDate) )

Throws an error as we are feeding an empty string to cdate

If we replace the Null with Empty we get
?FormatDateTime(Empty, vbShortDate)
30/12/1899

and
?cdate(FormatDateTime(Empty, vbShortDate) )
00:00:00

but
?IsNull(CDate(FormatDateTime(Empty, vbShortDate)))
False

which is fairly obvious.

I don't really understand why you are using all these functions anyway, it
looks as if all you need to do is

Do While Len(rst!F1.Value & "") 1
--

Terry Kreft
"madeleine" <ma****************@lmco.comwrote in message
news:11**********************@h48g2000cwc.googlegr oups.com...
>
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 #15

P: n/a
First thing to do is comment out the G@#$ D@!!N
ON ERROR RESUME NEXT statements.

From the code you posted ==================
'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
'======================== end of code

Then fix the code that's raising the error.
Bob

"madeleine" <ma****************@lmco.comwrote in
news:11**********************@m79g2000cwm.googlegr oups.com:
Because its looping through rows in a spreadsheet (well that
have been brought into the database) and needs to get all of
the data out where the date cell is not null I am expecting to
be able to use the fact that its null to get out of the loop.

What is confusing me is how can:

Do While Not IsNull(CDate(FormatDateTime(rst!F1.Value,
vbShortDate)))

IsNull above is False
directly afterwards:
If IsNull(CDate(FormatDateTime(rst!F1.Value, vbShortDate)))
Then IsNull is True

Do you think that somehow it can evaluate it to be different
values even though nothing happens in between?
ManningFan wrote:
>You can check the length of the value and react on that, but
that's a bit "cludgey".

You really need to figure out why it's returning a Null.
What's the value of rst!F1? What's the value of
FormatDateTime(rst!F1.Value, vbShortDate)? And finally,
what's the value of (CDate(FormatDateTime(rst!F1.Value,
vbShortDate))? Try picking apart the expression and see if
you can find an error in it. Maybe it's not formatted to be
a date originally?

>
I've just run it and the value is stated to be Null, so the
real question is why does

Do While Not IsNull(CDate(FormatDateTime(rst!F1.Value,
vbShortDate)))

Not take any notice whether the value is null or not, it is
always false to the while loop!!!!


--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Jul 20 '06 #16

P: n/a
I moved the While from after the Do part of the statement to after the
loop part of the statement and then it was all tickety boo, guess I've
learned the lesson of not trusting the Do part of the loop to actually
be run everytime, thanks very much for your help everyone.

Terry Kreft wrote:
If there are a 100 iterations before you hit the line you want to test, I
think you need to introduce yourself to the Debug.Assert statement.

I find your reported results suspect.

Working your test in Access VBA if we replace the rst!F1.Value with Null it
throws a type mismatch error
So
?FormatDateTime(Null, vbShortDate)

Is ok as this returns an empty string, but
?cdate(FormatDateTime(Null, vbShortDate) )

Throws an error as we are feeding an empty string to cdate

If we replace the Null with Empty we get
?FormatDateTime(Empty, vbShortDate)
30/12/1899

and
?cdate(FormatDateTime(Empty, vbShortDate) )
00:00:00

but
?IsNull(CDate(FormatDateTime(Empty, vbShortDate)))
False

which is fairly obvious.

I don't really understand why you are using all these functions anyway, it
looks as if all you need to do is

Do While Len(rst!F1.Value & "") 1
--

Terry Kreft
"madeleine" <ma****************@lmco.comwrote in message
news:11**********************@h48g2000cwc.googlegr oups.com...

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 21 '06 #17

This discussion thread is closed

Replies have been disabled for this discussion.