form filter

  • Thread starter dhoover via AccessMonster.com
  • Start date
D

dhoover via AccessMonster.com

I have taken over a database and the code it a but confusing to me. I have a
form in which the user selects a report from a list on the right and then
inputs a from and to date in the corresponding combo boxes on the same form.
Below is the code tied to the From box:

On Error Resume Next
Me.cboToDate = ""

Select Case Me.lstReports
Case "Exam History by Date", "Exam History with Concerns"
stSQL = "SELECT DISTINCT ExamDate FROM tblExamHistory GROUP BY
ExamDate HAVING " _
& "ExamDate Is Not Null And ExamDate >= #" & Me.cboFromDate &
"# ORDER BY ExamDate;"
Case "Exam History by Grade"
stSQL = "SELECT DISTINCT Grade FROM tblExamHistory GROUP BY Grade
HAVING " _
& "Grade Is Not Null And Grade >= '" & Me.cboFromDate & "'
ORDER BY Grade;"
Case "Exam History by Region"
stSQL = "SELECT tblRegion.Region FROM tblExamHistory LEFT JOIN
tblRegion ON tblExamHistory.Region = tblRegion.RegionID" _
& " GROUP BY tblRegion.Region HAVING tblRegion.Region Is Not
Null And tblRegion.Region >= '" & Me.cboFromDate _
& "' ORDER BY tblRegion.Region;"

Case Else
End Select
Me.cboToDate.RowSource = stSQL
Me.cboToDate.Requery



And this is the code for the open report button:

On Error Resume Next

Dim myCondition As String
myCondition = ""


Select Case stRpt


Case "Exam History by Date", "Exam History with Concerns"

If Len(Trim(Me.cboCustomer) & "") <> 0 Then
myCondition = myCondition & stCusId & " = " & Me.cboCustomer.
Column(1)
End If


If Len(Trim(Me.cboFromDate) & "") <> 0 Then
myCondition = myCondition & " And ExamDate >= #" & Me.
cboFromDate & "#"
End If
If Len(Trim(Me.cboToDate) & "") <> 0 Then
myCondition = myCondition & " And ExamDate <= #" & Me.
cboToDate & "#"
End If
varItems = PutItem(varItems, "FromDate", Nz(Me.cboFromDate,
""))
varItems = PutItem(varItems, "ToDate", Nz(Me.cboToDate, ""))

DoCmd.OpenReport stDsc, acViewPreview, , myCondition

Case "Exam History by Grade"
If Len(Trim(Me.cboFromDate) & "") <> 0 Then
myCondition = myCondition & " Grade >= '" & Me.cboFromDate &
"'"
End If
If Len(Trim(Me.cboToDate) & "") <> 0 Then
myCondition = myCondition & " And Grade <= '" & Me.cboToDate
& "'"
End If
varItems = PutItem(varItems, "FromDate", Nz(Me.cboFromDate,
""))
varItems = PutItem(varItems, "ToDate", Nz(Me.cboToDate, ""))

DoCmd.OpenReport stDsc, acViewPreview, , myCondition


Case "Exam History by Region"
If Len(Trim(Me.cboFromDate) & "") <> 0 Then
myCondition = myCondition & " Region >= '" & Me.cboFromDate &
"'"
End If
If Len(Trim(Me.cboToDate) & "") <> 0 Then
myCondition = myCondition & " And Region <= '" & Me.cboToDate
& "'"
End If
varItems = PutItem(varItems, "FromDate", Nz(Me.cboFromDate,
""))
varItems = PutItem(varItems, "ToDate", Nz(Me.cboToDate, ""))

DoCmd.OpenReport stDsc, acViewPreview, , myCondition

Case "Exam History by Customer"



End Select
xExit:
DoCmd.Close acForm, Me.Name


When the open report button is selected, nothing happens. Sorry for the long
post
 
P

Piet Linden

I have taken over a database and the code it a but confusing to me.  I have a
form in which the user selects a report from a list on the right and then
inputs a from and to date in the corresponding combo boxes on the same form.
Below is the code tied to the From box:

On Error Resume Next
    Me.cboToDate = ""

    Select Case Me.lstReports
        Case "Exam History by Date", "Exam History with Concerns"
            stSQL = "SELECT DISTINCT ExamDate FROM tblExamHistory GROUP BY
ExamDate HAVING " _
                & "ExamDate Is Not Null And ExamDate >=#" & Me.cboFromDate &
"# ORDER BY ExamDate;"
        Case "Exam History by Grade"
            stSQL = "SELECT DISTINCT Grade FROM tblExamHistory GROUP BY Grade
HAVING " _
                & "Grade Is Not Null And Grade >= '" & Me.cboFromDate & "'
ORDER BY Grade;"
        Case "Exam History by Region"
            stSQL = "SELECT tblRegion.Region FROM tblExamHistory LEFT JOIN
tblRegion ON tblExamHistory.Region = tblRegion.RegionID" _
                & " GROUP BY tblRegion.Region HAVING tblRegion.Region Is Not
Null And tblRegion.Region >= '" & Me.cboFromDate _
                & "' ORDER BY tblRegion.Region;"

        Case Else
    End Select
    Me.cboToDate.RowSource = stSQL
    Me.cboToDate.Requery

 And this is the code for the open report button:

On Error Resume Next

    Dim myCondition As String
    myCondition = ""

    Select Case stRpt

        Case "Exam History by Date", "Exam History with Concerns"

            If Len(Trim(Me.cboCustomer) & "") <> 0 Then
                myCondition = myCondition & stCusId & "= " & Me.cboCustomer.
Column(1)
            End If

            If Len(Trim(Me.cboFromDate) & "") <> 0 Then
                myCondition = myCondition & " And ExamDate >= #" & Me.
cboFromDate & "#"
            End If
            If Len(Trim(Me.cboToDate) & "") <> 0 Then
                myCondition = myCondition & " And ExamDate <= #" & Me.
cboToDate & "#"
            End If
                varItems = PutItem(varItems, "FromDate", Nz(Me.cboFromDate,
""))
                varItems = PutItem(varItems, "ToDate", Nz(Me.cboToDate, ""))

            DoCmd.OpenReport stDsc, acViewPreview, , myCondition

        Case "Exam History by Grade"
            If Len(Trim(Me.cboFromDate) & "") <> 0 Then
                myCondition = myCondition & " Grade >= '" & Me.cboFromDate &
"'"
            End If
            If Len(Trim(Me.cboToDate) & "") <> 0 Then
                myCondition = myCondition & " And Grade<= '" & Me.cboToDate
& "'"
            End If
                varItems = PutItem(varItems, "FromDate", Nz(Me.cboFromDate,
""))
                varItems = PutItem(varItems, "ToDate", Nz(Me.cboToDate, ""))

            DoCmd.OpenReport stDsc, acViewPreview, , myCondition

        Case "Exam History by Region"
            If Len(Trim(Me.cboFromDate) & "") <> 0 Then
                myCondition = myCondition & " Region >= '" & Me.cboFromDate &
"'"
            End If
            If Len(Trim(Me.cboToDate) & "") <> 0 Then
                myCondition = myCondition & " And Region <= '" & Me.cboToDate
& "'"
            End If
                varItems = PutItem(varItems, "FromDate", Nz(Me.cboFromDate,
""))
                varItems = PutItem(varItems, "ToDate", Nz(Me.cboToDate, ""))

            DoCmd.OpenReport stDsc, acViewPreview, , myCondition

        Case "Exam History by Customer"

    End Select
xExit:
    DoCmd.Close acForm, Me.Name

When the open report button is selected, nothing happens.  Sorry for the long
post
Click to expand...

First thing I would do is comment out the ON ERROR RESUME NEXT
statements (just put a single quote in front of each). At least that
way, you'll know where the code doesn't work. If your intention is to
build a SQL statement with dates, you should delimit the dates with #,
and not '.
 
D

dhoover via AccessMonster.com

That does't seem to help. The code doesn't error out anywhere, it steps
right through it but does nothing in the end.

Piet said:
I have taken over a database and the code it a but confusing to me.  I have a
form in which the user selects a report from a list on the right and then
Click to expand...
[quoted text clipped - 98 lines]
First thing I would do is comment out the ON ERROR RESUME NEXT
statements (just put a single quote in front of each). At least that
way, you'll know where the code doesn't work. If your intention is to
build a SQL statement with dates, you should delimit the dates with #,
and not '.
Click to expand...
 
D

dhoover via AccessMonster.com

This is the part I'm struggling with, it doesn't filter the report like it
should.

If Len(Trim(Me.cboFromDate) & "") <> 0 Then
myCondition = myCondition & " And ExamDate >= #" & Me.
cboFromDate & "#"
End If



That does't seem to help. The code doesn't error out anywhere, it steps
right through it but does nothing in the end.
[quoted text clipped - 7 lines]
build a SQL statement with dates, you should delimit the dates with #,
and not '.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Export to PDF 0
type mismatch error in macro in excel 0
Use of Me in function 6
Unpredicatable BeforeUpdate event 2
Switchboard 7
Filter by form with beginning date and enddate, where calender we 2
Help with this code please. 2
Numaric to Inword in excel by VB 1

Top