If Statement issue End If debug error

[nick243]

In the below code I am having an issue with the End if statement at the end of the code. I am trying to loop through all the files in a folder and copy those meeting the if statement "And" statement then looping back to look at the next file that meets that strfile criteria. I bolded and underlined the text in the code where the error is. There is no error code. It just keeps spinning when I run the code and I break the code and given a debug statement but no code. The if statement if collecting the file information and has successfully copied the file to the correct file location just can't end it.

Function DocPurge()




Dim DDate, strfile As String
Dim LMsg, LMsg2 As String
Dim DDDate, ThisDay As Date
Dim MyDate, MyWeekDay
Dim MyTime, MMyTime




ChDir ("K:\System Reports")
strfile = Dir("K:\System Reports\SDC1201S SDC185S???????????????????????????????????.pdf")


    '** Step 2 -- Begin the loop process.
    Do While Len(strfile) > 0


        DoCmd.SetWarnings False
        DDate = Mid(strfile, 41, 2) & "/" & Mid(strfile, 43, 2) & "/" & Mid(strfile, 39, 2)
        DDDate = DDate  ' This got the date from the file name.
        MyDate = DDDate            ' Assign a date.
        MyWeekDay = Weekday(MyDate)
        ' MyWeekDay contains 4 because
        ' MyDate represents a Wednesday.
        MyTime = Mid(strfile, 46, 2) & "." & Mid(strfile, 48, 2)
        MMyTime = MyTime
        ''MMyTime = Format(MyTime, "mm/dd/yyyy hh:nn:ss am/pm")
        
        


If MyWeekDay = 7 And MMyTime > 12 Then
        FileCopy "K:\System Reports\" & strfile, "K:\Teams and Projects\Processing Reports\SDC185_Report_History\" & strfile
End If


Loop




'' DoCmd.SetWarnings True


End Function

Thanks,
Nick

[ranman256]

It looks OK, but instead of
DO WHILE
LOOP

Try :
WHILE Len(strFile)>0
.....
wend

it might find

[nick243]

It looks OK, but instead of
DO WHILE
LOOP

Try :
WHILE Len(strFile)>0
.....
wend

it might find

OK thank you, I read up on that function tried it. It is still having the same error. The while wend may be the way to go.

Function DocPurge()




Dim DDate, strfile As String
Dim LMsg, LMsg2 As String
Dim DDDate, ThisDay As Date
Dim MyDate, MyWeekDay
Dim MyTime, MMyTime




ChDir ("K:\System Reports")
strfile = Dir("K:\System Reports\SDC1201S SDC185S???????????????????????????????????.pdf")


    '** Step 2 -- Begin the loop process.
    While Len(strfile) > 0


        DoCmd.SetWarnings False
        DDate = Mid(strfile, 41, 2) & "/" & Mid(strfile, 43, 2) & "/" & Mid(strfile, 39, 2)
        DDDate = DDate  ' This got the date from the file name.
        MyDate = DDDate            ' Assign a date.
        MyWeekDay = Weekday(MyDate)
        ' MyWeekDay contains 4 because
        ' MyDate represents a Wednesday.
        MyTime = Mid(strfile, 46, 2) & "." & Mid(strfile, 48, 2)
        MMyTime = MyTime
        ''MMyTime = Format(MyTime, "mm/dd/yyyy hh:nn:ss am/pm")
        
        


If MyWeekDay = 7 And MMyTime > 12 Then
        FileCopy "K:\System Reports\" & strfile, "K:\Teams and Projects\Processing Reports\SDC185_Report_History\" & strfile
End If


Wend




'' DoCmd.SetWarnings True


End Function

Thanks,
Nick

[pbaldy]

Unless I'm blind, you never change the value of strfile within the loop, so it has no way of breaking out. Why the loop if there's only one file?

[nick243]

Unless I'm blind, you never change the value of strfile within the loop, so it has no way of breaking out. Why the loop if there's only one file?

I am running this code because there are several files in this folder that I have to check through. The way I have seen this code work before is that it checks each file in the folder for that strfile file format and runs it through the loop until it runs out of files to check. Is this a wrong way to go about this? The code seems to be extracting the right information it just doesn't seem to go to the next file.

Thanks,
Nick

[nick243]

I changed my code to the strfile = dir to change it. I stepped through 1 at a time and it is looping correctly now. It seems that it is running without a hitch. I don't seem to see files being copied over? I would think each time it runs it will copy something over but nothing seems to be copied over now.

Function DocPurge()




Dim DDate, strfile As String
Dim LMsg, LMsg2 As String
Dim DDDate, ThisDay As Date
Dim MyDate, MyWeekDay
Dim MyTime, MMyTime




ChDir ("K:\System Reports")
strfile = Dir("K:\System Reports\SDC1201S SDC185S???????????????????????????????????.pdf")


    '** Step 2 -- Begin the loop process.
Do While Len(strfile) > 0


        DoCmd.SetWarnings True
        DDate = Mid(strfile, 41, 2) & "/" & Mid(strfile, 43, 2) & "/" & Mid(strfile, 39, 2)
        DDDate = DDate  ' This got the date from the file name.
        MyDate = DDDate            ' Assign a date.
        MyWeekDay = Weekday(MyDate)
        ' MyWeekDay contains 4 because
        ' MyDate represents a Wednesday.
        MyTime = Mid(strfile, 46, 2) & "." & Mid(strfile, 48, 2)
        MMyTime = MyTime
        ''MMyTime = Format(MyTime, "mm/dd/yyyy hh:nn:ss am/pm")
        
        


If MyWeekDay = 7 And MMyTime > 12 Then
        FileCopy "K:\System Reports\" & strfile, "K:\Teams and Projects\Processing Reports\SDC185_Report_History\" & strfile
End If


strfile = Dir


Loop




'' DoCmd.SetWarnings True


End Function

[pbaldy]

Is this test being met?

If MyWeekDay = 7 And MMyTime > 12 Then

Do you know how to set a breakpoint and follow the code?

http://www.baldyweb.com/Debugging.htm

[nick243]

Is this test being met?

If MyWeekDay = 7 And MMyTime > 12 Then

Do you know how to set a breakpoint and follow the code?

http://www.baldyweb.com/Debugging.htm

I ran it with a break point and huvered my mouse over the variables to see if they have values. They have valles MyWeek Day ==7 and MMYTime = 21.15

So both criteria were met so it should have copied.

[CJ_London]

I ran it with a break point and huvered my mouse over the variables to see if they have values. They have valles MyWeek Day ==7 and MMYTime = 21.15

- so did you hit the F8 key to step through - did it go into the if part of the code, or give it a miss?

I'm not saying it won't work, but you haven't specified datatypes

MyTime = Mid(strfile, 46, 2) & "." & Mid(strfile, 48, 2)
MMyTime = MyTime

So MMyTime is a string "21.15"

which you are comparing with a number (12)

By not specifying datatypes, it might be giving you the right evaluation, or it might not - you are the one saying that 21.15>12 - but vba may not be evaluating it that way

You are not using a date datatype but if you do, be aware that dates are stored as decimal numbers with the time element being the decimal part expressed as number of seconds to the time divided by the number of seconds in a day (86400), so 12 noon is 0.5, 21:15 would be 0.885 etc

[nick243]

- so did you hit the F8 key to step through - did it go into the if part of the code, or give it a miss?

I'm not saying it won't work, but you haven't specified datatypes

MyTime = Mid(strfile, 46, 2) & "." & Mid(strfile, 48, 2)
MMyTime = MyTime

So MMyTime is a string "21.15"

which you are comparing with a number (12)

By not specifying datatypes, it might be giving you the right evaluation, or it might not - you are the one saying that 21.15>12 - but vba may not be evaluating it that way

You are not using a date datatype but if you do, be aware that dates are stored as decimal numbers with the time element being the decimal part expressed as number of seconds to the time divided by the number of seconds in a day (86400), so 12 noon is 0.5, 21:15 would be 0.885 etc

Yes I hit f8 to step through. It has successfully copied before until I made some changes to the code today. I will definitely keep in mind my data types. Yes your assumptions are correct above.

Thanks,
Nick

[nick243]

Ok so I changed my code to this to compare like data values (or at least tired to) still no copy

My values were My week day 7=7 and MMyTime = 9:15:00 PM > 12:00:00 PM

Would this seem to be correct?

Option Compare Database
Option Explicit


Function DocPurge()




Dim DDate, strfile As String
Dim LMsg, LMsg2 As String
Dim DDDate, ThisDay As Date
Dim MyDate, MyWeekDay
Dim MyTime, MMyTime, MMMyTime




ChDir ("K:\System Reports")
strfile = Dir("K:\System Reports\SDC1201S SDC185S???????????????????????????????????.pdf")


    '** Step 2 -- Begin the loop process.
Do While Len(strfile) > 0


        DoCmd.SetWarnings True
        DDate = Mid(strfile, 41, 2) & "/" & Mid(strfile, 43, 2) & "/" & Mid(strfile, 39, 2)
        DDDate = DDate  ' This got the date from the file name.
        MyDate = DDDate
        ' Assign a date.
        MyWeekDay = Weekday(MyDate)
        ' MyWeekDay contains 4 because
        ' MyDate represents a Wednesday.
        
        
        MyTime = Mid(strfile, 46, 2) & ":" & Mid(strfile, 48, 2) & ":00"
        
        MMyTime = TimeValue(MyTime)
        ''MMyTime = Format(MyTime, "mm/dd/yyyy hh:nn:ss am/pm")
        MMMyTime = TimeValue("12:00:00")
        
        
        
        


If MyWeekDay = 7 And MMyTime > MMMyTime Then
        FileCopy "K:\System Reports" & strfile, "K:\Teams and Projects\Processing Reports\SDC185_Report_History" & strfile
End If


strfile = Dir




Loop




'' DoCmd.SetWarnings True


End Function

Thanks,
Nick

[pbaldy]

When you're stepping through the code, you're saying the code jumps from the If line to the End If line, skipping the FileCopy line? These are all Variants, you should declare them explicitly:

Dim MyDate As Date, MyWeekDay As Integer
Dim MyTime As Date, MMyTime As Date, MMMyTime As Date

[nick243]

When you're stepping through the code, you're saying the code jumps from the If line to the End If line, skipping the FileCopy line? These are all Variants, you should declare them explicitly:

Dim MyDate As Date, MyWeekDay As Integer
Dim MyTime As Date, MMyTime As Date, MMMyTime As Date

No, the code is correctly going through the process and not skipping the copy. It goes through the loop twice then ends the function coping no files to the new area. When I believe they meet the correct requirements. I made your your changes at the top to declare them explicit.

Option Compare Database
Option Explicit


Function DocPurge()




Dim DDate, strfile As String
Dim DDDate, ThisDay As Date
Dim MyDate, MyWeekDay As Integer
Dim MyTime, MMyTime, MMMyTime As Date






ChDir ("K:\System Reports")
strfile = Dir("K:\System Reports\SDC1201S SDC185S???????????????????????????????????.pdf")


    '** Step 2 -- Begin the loop process.
Do While Len(strfile) > 0


        DoCmd.SetWarnings True
        DDate = Mid(strfile, 41, 2) & "/" & Mid(strfile, 43, 2) & "/" & Mid(strfile, 39, 2)
        DDDate = DDate  ' This got the date from the file name.
        MyDate = DDDate
        ' Assign a date.
        MyWeekDay = Weekday(MyDate)
        ' MyWeekDay contains 4 because
        ' MyDate represents a Wednesday.
        
        
        MyTime = Mid(strfile, 46, 2) & ":" & Mid(strfile, 48, 2) & ":00"
        
        MMyTime = TimeValue(MyTime)
        ''MMyTime = Format(MyTime, "mm/dd/yyyy hh:nn:ss am/pm")
        MMMyTime = TimeValue("12:00:00")
        
        
        
        


If MyWeekDay = 7 And MMyTime > MMMyTime Then
        FileCopy "K:\System Reports\" & strfile, "K:\Teams and Projects\Processing Reports\SDC185_Report_History\" & strfile
End If


strfile = Dir




Loop




'' DoCmd.SetWarnings True


End Function

Thanks,
Nick

[pbaldy]

So if you hit F8 while on this line:

If MyWeekDay = 7 And MMyTime > MMMyTime Then

Is the next line highlighed the FileCopy line or the End If line? If you're saying the FileCopy line is highlighted and executes if you hit F8 again, but no files are copied, that's a whole 'nother kettle of fish. Then we're looking at valid file names, permissions, that type of thing.

[nick243]

So if you hit F8 while on this line:

If MyWeekDay = 7 And MMyTime > MMMyTime Then

Is the next line highlighed the FileCopy line or the End If line? If you're saying the FileCopy line is highlighted and executes if you hit F8 again, but no files are copied, that's a whole 'nother kettle of fish. Then we're looking at valid file names, permissions, that type of thing.

The file copy line. yes it highlights and nothing is executed. I am at a business so I will check my permissions but it has worked yesterday to be able to copy in that folder. I don't think I can get back to where I was yesterday.

Thanks,
Nick