If/Then Statement

[jeran042]

Let me start by saying this procedure works the way I intend.

My question is more of efficiency/elegance. Based on the code below, is this the most efficient way to accomplish this?

Private Sub cmdDAILY_Click()
'Import the daily file from the "PARSE_" file

'Error handling
    On Error GoTo Error_Handler
    
Dim iResponse As Integer

If MsgBox("This action will IMPORT the Daily file, " & vbNewLine & _
                    "Do you want to Continue? ", vbYesNo + vbQuestion, "WARNING") = vbNo Then Exit Sub

'Test to make sure that the daily records were not already appended    
    If FormatDateTime(DMax("[PAY-DATE]", "qryTEMP_MONTH_TO_DATE"), vbShortDate) <> Date Then
                DoCmd.RunSavedImportExport "Append to ""TBL_MONTH_TO_DATE"" TABLE"
                DoCmd.OpenReport "RPT: ACCOUNTS - DO NOT USE_MONTH_TO_DATE", acViewReport, "", "", acNormal
                DoCmd.OpenReport "RPT: TEMP_MONTH_TO_DATE_DAILY", acViewReport, "", "", acNormal
                MsgBox "Your Data Has Successfully been Appended!", vbOKOnly + vbInformation, "SUCCESS!"
    Else
       iResponse = MsgBox("It appears that there are records in this table with that date, do you want to continue??", vbYesNo + vbCritical, "HOLD UP")
            If iResponse = vbNo Then
                Exit Sub
            End If
    End If

Error_Handler_Exit:
    Exit Sub

Error_Handler:
    Select Case Err.NUMBER
        Case 94
            Err.Clear
            Resume Error_Handler_Exit
        Case Else
            MsgBox "Error No. " & Err.NUMBER & vbCrLf & "Description: " & Err.Description, vbExclamation, "Database Error"
            Err.Clear
            Resume Error_Handler_Exit
    End Select

End Sub

Any suggestions/edit welcome, and thank you

 

[Ranman256]

the ELSE block:

    Else
       iResponse = MsgBox("It appears that there are records in this table with that date,

doesnt seem to do anything. If you say yes or no, it still ends.
so why have it?

 

[The_Doc_Man]

The only thing I would question is why you need to trap "Invalid use of Null" when the only thing I can see there is a DMax that should never return a Null unless the table is empty.

However,

FormatDateTime(DMax("[PAY-DATE]", "qryTEMP_MONTH_TO_DATE"), vbShortDate) <> Date

looks like it might have a chance to give you an error 13, "Type mismatch" since the output of FormatDateTime will be a string and the return from the Date() function will be a date/time value... unless you have a string variable NAMED "Date" that we didn't see in the code. (If you do, change that name immediately - "Date" is a reserved word.)

As to efficiency: Not too bad, but no top - because if you ARE looking to today's date, you should be able to compare the output of DMax directly to the Date() function without first converting it to a date string. So there IS the slight inefficiency of doing the Format-related function when you don't need it. Not to mention the potential for type mismatch errors.

As to Ranman's comment about the Else block: I understand that you are notifying your user (or yourself) either way the process goes, so I don't have an issue.

 

[jeran042]

I do not have a string variable NAMED "Date", my thought is the word "Date" in the line:

If FormatDateTime(DMax("[PAY-DATE]", "qryTEMP_MONTH_TO_DATE"), vbShortDate) <> Date Then

was basically using the Date() function. Am I incorrect in this thought?

I was getting the Runtime error 13 before I formatted the DMAX. As the DMAX was returning a serial number string, and the date was returning the MM/DD/YYYY, and obviously those would not match.

As for the error handling, it was brought over from another piece of code, and never updated. So realistically, I was to get rid of the piece:

Case 94
            Err.Clear
            Resume Error_Handler_Exit

 

[jeran042]

the ELSE block:

    Else
       iResponse = MsgBox("It appears that there are records in this table with that date,

doesnt seem to do anything. If you say yes or no, it still ends.
so why have it?

Very good thought, how would I edit this code, so a vbYes answer would run the procedure?

 

[The_Doc_Man]

If you thought you were using Date to mean the Date function, you did the right thing.

The OTHER way to make that test might be:

IF DCount("[PAY-DATE]", "qryTEMP_MONTH_TO_DATE", "[PAY-DATE]=#" & FormatDateTime( Date(), vbShortDate) & "#") > 0 Then ....

This would work if the [PAY-DATE] field is always date-only (no time involved). Note that if [PAY-DATE] is NOT a date/time field, then your problem is in that field. But from this discussion, I don't think that is your issue.

DCount will not be bothered by nulls and this method is equally as specific as what you originally did. You were NOT wrong - I'm just showing an alternative.

 

[Acropolis]

Else
       iResponse = MsgBox("It appears that there are records in this table with that date, do you want to continue??", vbYesNo + vbCritical, "HOLD UP")
            If iResponse = vbNo Then
                Exit Sub
            End If
    End If
End If

Could just be

ElseIf MsgBox("It appears that there are records in this table with that date, do you want to continue??", vbYesNo + vbCritical, "HOLD UP") = vbYes Then
   #Whatever you want to do here
End If