VBA Help

[jalenben1]

I am trying to automate a report I do and I have the desired end report that I need. I am just trying to make sure that the data comes in cleanly. In the form I would like for the end users to put in a beginning and ending date and only the data between those dates appears in the report. The dates are derived from the field name DateCreated so when an end user puts in a beginning and ending date based off the field name DateCreated data from those dates will show in the report. Below is the VBS code I created. If I dont put in dates then I get all of the data which is what I want but if I put in a beginning date 11/1/2010 and the ending date 11/30/2010 nothing shows in the report and I would like to see the data between the two dates. Any reason why?? Any suggestions??


Private Sub Generate_ECM_Report_Click()
On Error GoTo Err_Generate_ECM_Report_Click

Dim stDocName As String
Dim stWhere As String
Dim stDates As String
Dim blnTrim As Boolean

If IsNull(Me.txtBeginning) And Me.txtBeginning = " " Then
If Not IsNull(Me.txtEnding) And Me.txtEnding <> " " Then
stWhere = stWhere & "[DateCreated] <=" & Me.txtEnding & "#"
blnTrim = False
End If
Else
If IsNull(Me.txtEnding) And Me.txtEnding = " " Then
If Not IsNull(Me.txtBeginning) And Me.txtBeginning <> " " Then
stWhere = stWhere & "[DateCreated] >=" & Me.txtBeginning
blnTrim = False
End If
Else
If (Not IsNull(Me.txtBeginning) And Me.txtBeginning <> " ") And (Not IsNull(Me.txtEnding) Or Me.txtEnding <> " ") Then
stWhere = stWhere & "[DateCreated] Between #" & Me.txtBeginning & "# And #" & Me.txtEnding & "#"
blnTrim = False
End If
End If
End If

If blnTrim Then
stWhere = Left(stWhere, Len(stWhere) - 5)
End If
stDocName = "Enterprise Change Management Report"
DoCmd.OpenReport stDocName, acPreview, , stWhere
Exit_Generate_ECM_Report_Click:
Exit Sub

Err_Generate_ECM_Report_Click:
MsgBox Err.Description
Resume Exit_Generate_ECM_Report_Click

End Sub

 

[MajP]

untested but something like

Private Sub Generate_ECM_Report_Click()
On Error GoTo Err_Generate_ECM_Report_Click

    Dim stDocName As String
    Dim stWhere As String
     
        If Not IsDate(Me.txtBeginning) And IsDate(Me.txtEnding) Then
          stWhere = "[DateCreated]  <= " & SQLDate(Me.txtEnding)
        ElseIf IsDate(Me.txtBeginning) And Not IsDate(Me.txtEnding) Then
          stWhere = "[DateCreated] >= " & SQLDate(Me.txtBeginning)
        ElseIf IsDate(Me.txtBeginning) And IsDate(Me.txtEnding) Then
           stWhere = "[DateCreated] Between " & SQLDate(Me.txtBeginning) & "And " & SQLDate(Me.txtEnding)
        End If
    'Msgbox strWhere
    stDocName = "Enterprise Change Management Report"
    DoCmd.OpenReport stDocName, acPreview, , stWhere
Exit_Generate_ECM_Report_Click:
    Exit Sub

Err_Generate_ECM_Report_Click:
    MsgBox Err.Description
    Resume Exit_Generate_ECM_Report_Click
    
End Sub
Function SQLDate(varDate As Variant) As Variant
    'Purpose:    Return a delimited string in the date format used natively by JET SQL.
    'Argument:   A date/time value.
    'Note:       Returns just the date format if the argument has no time component,
    '                or a date/time format if it does.
    'Author:     Allen Browne. allen@allenbrowne.com, June 2006.
    If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function

 

[jalenben1]

Do I use the entire code?

 

[MajP]

Yes, but as I said it is untested. The second function just takes whatever is in your text box
and formats it into a proper sql date with the #s.

 

[jalenben1]

I used the code below:

Private Sub Generate_ECM_Report_Click()
On Error GoTo Err_Generate_ECM_Report_Click

Dim stDocName As String
Dim stWhere As String

If Not IsDate(Me.txtBeginning) And IsDate(Me.txtEnding) Then
stWhere = "[DateCreated] <= " & SQLDate(Me.txtEnding)
ElseIf IsDate(Me.txtBeginning) And Not IsDate(Me.txtEnding) Then
stWhere = "[DateCreated] >= " & SQLDate(Me.txtBeginning)
ElseIf IsDate(Me.txtBeginning) And IsDate(Me.txtEnding) Then
stWhere = "[DateCreated] Between " & SQLDate(Me.txtBeginning) & "And " & SQLDate(Me.txtEnding)
End If
'Msgbox strWhere
stDocName = "Enterprise Change Management Report"
DoCmd.OpenReport stDocName, acViewPreview, , stWhere
Exit_Generate_ECM_Report_Click:
Exit Sub

Err_Generate_ECM_Report_Click:
MsgBox Err.Description
Resume Exit_Generate_ECM_Report_Click

End Sub

If I put in 11/1/2010 as the beggining date and 11/30/2010 as the ending date this is the error message I get:
Syntax error (missing operator) in query expression '([DateCreated] Between AND).

 

[MajP]

put this line of code in prior to the docmd.open report:
debug.print "Beginning: " & me.txtBeginning & " Ending: " & me.txtEnding & " StrWhere: " & strWhere

Then
1) comment out the docmd.openreport
2) test all four cases.
begin date only
end date only
begin and end date
no dates
3) go to the immediate window and look at the debug string.
4) Post back.

 

[PHV]

strWhere or stWhere ?

 

[MajP]

Nice catch, PHV.
Also ensure you have "Option Explicit" at the top of the module to avoid my mistakes.

 

[dhookom]

I like the SQLDate() function but prefer to simplify the logic in generating the where condition.

Private Sub Generate_ECM_Report_Click()
On Error GoTo Err_Generate_ECM_Report_Click

    Dim stDocName As String
    Dim stWhere As String
    stWhere = "1=1 " [green]'now just "AND" all other criteria[/green]
    If IsDate(Me.txtBeginning) Then
        stWhere = stWhere & " AND [DateCreated] >= " & SQLDate(Me.txtBeginning)
    End If
    If IsDate(Me.txtEnding) Then
        stWhere = stWhere & " AND [DateCreated] <= " & SQLDate(Me.txtEnding)
    End If
    Debug.Print stWhere
    stDocName = "Enterprise Change Management Report"
    DoCmd.OpenReport stDocName, acViewPreview, , stWhere
Exit_Generate_ECM_Report_Click:
    Exit Sub
    
Err_Generate_ECM_Report_Click:
    MsgBox Err.Description
    Resume Exit_Generate_ECM_Report_Click
 
End Sub

Duane
Hook'D on Access
MS Access MVP