problem with 2 text boxes and 2 list boxes

[margot]

I am using Access 2007 and I have two list boxes where the user is suppose to type a range of dates.
Then I have a list box so the user can select wheter he/she wants to pull letters or proposals.
Finally I have a list box to see what report the user wants to open (date document was received, deadline, date when the doc. was replied).

I want the user to be able to type the dates on the textboxes select a report and type of document and a report will be generated with those choosen fields.

Do someone has an example code for this situation, I need it badly.

Thanks in advance.

[Rabbit]

Form1
txtStartDate
txtEndDate

Query1

SELECT *
FROM Table1
WHERE EventDate BETWEEN Forms!Form1!txtStartDate AND Forms!Form1!txtEndDate;
 

[margot]

Form1
txtStartDate
txtEndDate

Query1

SELECT *
FROM Table1
WHERE EventDate BETWEEN Forms!Form1!txtStartDate AND Forms!Form1!txtEndDate;
 

Thank you Sir;
However I am not too familiarize with sql code, is that all the code that I need? and how is that related with the first part. Do I only need to declare the text boxes for the first part?
Thanks again

[Rabbit]

Thank you Sir;
However I am not too familiarize with sql code, is that all the code that I need? and how is that related with the first part. Do I only need to declare the text boxes for the first part?
Thanks again

This is only an inkling of what you need. But it's enough to get you started on creating a report that uses a query that pulls its criteria from a form. Opening the report is a simple matter of running DoCmd.OpenReport from an event of your choosing.

[margot]

This is only an inkling of what you need. But it's enough to get you started on creating a report that uses a query that pulls its criteria from a form. Opening the report is a simple matter of running DoCmd.OpenReport from an event of your choosing.

Thank you sir. However can I do somehting like the code below using cases for a combo box?

Private Sub Command38_Click()
Dim stDocName
Dim stLinkCriteria
Dim pstCriteria As String
 
 
Select Case frmOptionX
 
    Case "DateReceived"
        stDocName = "rptDateReceived"
 
        If IsDate([txtFirstDate]) And IsDate([txtEndDate]) Then
            pstrCriteria = "[DateReceived] >= # " & [txtFirstDate] & "# and [DateReceived]<=#" & [txtEndDate] & "#"
 
        DoCmd.OpenReport "rptDateReceived", acViewPreview, , pstrCriteria
 
            Else
                MsgBox "You must enter two dates"
 
  End If
 
 
    Case "DateRepliedOn"
 
        stDocName = "rptDateRepliedOn"
 
 
          If IsDate([txtFirstDate]) And IsDate([txtEndDate]) Then
              pstrCriteria = "[rptDateRepliedOn] >= # " & [txtFirstDate] & "# and [rptDateRepliedOn]<=#" & [txtEndDate] & "#"
 
            DoCmd.OpenReport "rptDateRepliedOn", acViewPreview, , pstrCriteria
 
        Else
             MsgBox "You must enter two dates"
 
  End If
 
    Case "ReplyDeadline"
 
         If IsDate([txtFirstDate]) And IsDate([txtEndDate]) Then
              pstrCriteria = "[ReplyDateline] >= # " & [txtFirstDate] & "# and [ReplyDateline]<=#" & [txtEndDate] & "#"
 
            DoCmd.OpenReport "rptReplyDateline", acViewPreview, , pstrCriteria
 
        Else
             MsgBox "You must enter two dates"
 
    End Select
 
 
End Sub
 

Thanks in advance

[Rabbit]

That could work but it would be easier just to do:

Private Sub Command38_Click()
Dim stDocName
Dim pstrCriteria As String
 
        stDocName = frmOptionX
 
        If IsDate([txtFirstDate]) And IsDate([txtEndDate]) Then
            pstrCriteria = "[DateReceived] >= # " & [txtFirstDate] & "# and [DateReceived]<=#" & [txtEndDate] & "#"
 
        DoCmd.OpenReport stDocName, acViewPreview, , pstrCriteria
 
            Else
                MsgBox "You must enter two dates"
 
  End If
End Sub
 

Thanks in advance[/quote]

[margot]

That could work but it would be easier just to do:

Private Sub Command38_Click()
Dim stDocName
Dim pstrCriteria As String
 
        stDocName = frmOptionX
 
        If IsDate([txtFirstDate]) And IsDate([txtEndDate]) Then
            pstrCriteria = "[DateReceived] >= # " & [txtFirstDate] & "# and [DateReceived]<=#" & [txtEndDate] & "#"
 
        DoCmd.OpenReport stDocName, acViewPreview, , pstrCriteria
 
            Else
                MsgBox "You must enter two dates"
 
  End If
End Sub
 

Thanks in advance

[/quote]

Thank you. This is great. However this will only work for one report since pstrCriteria = "[DateReceived]" will change for each report. For instance "[DateReceived] " will change for "RepliedOn" and "RepliedDeadline". How can I fix this small problem.

Thank you so much.

[Rabbit]

Oh, I didn't realize the fields were named differently. In that case, then yes, you would have to use the Case statement that you used earlier.

[margot]

Oh, I didn't realize the fields were named differently. In that case, then yes, you would have to use the Case statement that you used earlier.

Thank you. However, when I run the code below with the cases I get an error "End Select without Select Case." My question is, do i need to declare the case differently? or what am I doing wrong?

Private Sub Command48_Click()
 
Dim stDocName
Dim pstCriteria As String
 
 
Select Case frmOptionX
 
    Case "DateReceived"
        stDocName = "rptDateReceived"
 
        If IsDate([txtFirstDate]) And IsDate([txtEndDate]) Then
            pstrCriteria = "[DateReceived] >= # " & [txtFirstDate] & "# and [DateReceived]<=#" & [txtEndDate] & "#"
 
        DoCmd.OpenReport "rptDateReceived", acViewPreview, , pstrCriteria
 
            Else
                MsgBox "You must enter two dates"
 
  End If
 
 
    Case "DateRepliedOn"
 
        stDocName = "rptDateRepliedOn"
 
 
          If IsDate([txtFirstDate]) And IsDate([txtEndDate]) Then
              pstrCriteria = "[DateRepliedOn] >= # " & [txtFirstDate] & "# and [DateRepliedOn]<=#" & [txtEndDate] & "#"
 
            DoCmd.OpenReport "rptDateRepliedOn", acViewPreview, , pstrCriteria
 
        Else
             MsgBox "You must enter two dates"
 
  End If
 
    Case "ReplyDeadline"
 
         If IsDate([txtFirstDate]) And IsDate([txtEndDate]) Then
              pstrCriteria = "[ReplyDateline] >= # " & [txtFirstDate] & "# and [ReplyDateline]<=#" & [txtEndDate] & "#"
 
            DoCmd.OpenReport "rptReplyDateline", acViewPreview, , pstrCriteria
 
        Else
             MsgBox "You must enter two dates"
 
    End Select
 
 
End Sub
 

Thank you!!

[Rabbit]

It looks like you fogot an End If on your last case.

[margot]

It looks like you fogot an End If on your last case.

Thank you very much, it is working now! One more question. If I want to add 2 checkboxes so the user can select a date, the drop down, and a check box and the report will be generated. Is there a way that I can add the check box to this code?
Thank you again.

[Rabbit]

Thank you very much, it is working now! One more question. If I want to add 2 checkboxes so the user can select a date, the drop down, and a check box and the report will be generated. Is there a way that I can add the check box to this code?
Thank you again.

Yes, how you do it depends on what those checkboxes represent. But there's no reason why you can't incorporate it into the code.

[margot]

Yes, how you do it depends on what those checkboxes represent. But there's no reason why you can't incorporate it into the code.

Thank you sir, I will start working on it.

[Rabbit]

Thank you sir, I will start working on it.

No problem, let us know if you run into any problems.

[margot]

No problem, let us know if you run into any problems.

I added a new ListBox so the user needs to select letter, proposal, or all. So I declared strSelect and gave the selection to that variable. However when I open it, it will open the report but it will not show me any information. Thank you for your time.

 
Private Sub Command48_Click()
 
 
Dim pstCriteria As String
Dim strSelect As String
 
 
 
Select Case frmOptionX
 
 
    Case "rptDateReceived"
 
 
        If IsDate([txtFirstDate]) And IsDate([txtEndDate]) Then
            pstrCriteria = "[DateReceived] >= # " & [txtFirstDate] & "# and [DateReceived]<=#" & [txtEndDate] & "#" & strSelect = "DocumentType = '" & Me.frmTest & "'"
 
 
        DoCmd.OpenReport "rptDateReceived", acViewPreview, , pstrCriteria, , strSelect
 
            Else
                MsgBox "You must enter two dates"
 
  End If
 

[Rabbit]

You use Code tags by surrounding it with [ Code=vb ] and [ /Code ] without the spaces between the [ and ].

As for this:

pstrCriteria = "[DateReceived] >= # " & [txtFirstDate] & "# and [DateReceived]<=#" & [txtEndDate] & "#" & strSelect = "DocumentType = '" & Me.frmTest & "'"
 

You can't have two assignments on the same line. But that is moot as you don't need two.

pstrCriteria = "[DateReceived] >= # " & [txtFirstDate] & "# and [DateReceived]<=#" & [txtEndDate] & "# AND DocumentType = '" & Me.frmTest & "'"
 

Also, a note about naming conventions. I usually reserve the frm keyword when naming forms. If it's a listbox I usually use lst. Using frm is confusing as people might take it to mean that you're trying to return the value of a form.

[margot]

You use Code tags by surrounding it with [ Code=vb ] and [ /Code ] without the spaces between the [ and ].

As for this:

pstrCriteria = "[DateReceived] >= # " & [txtFirstDate] & "# and [DateReceived]<=#" & [txtEndDate] & "#" & strSelect = "DocumentType = '" & Me.frmTest & "'"
 

You can't have two assignments on the same line. But that is moot as you don't need two.

pstrCriteria = "[DateReceived] >= # " & [txtFirstDate] & "# and [DateReceived]<=#" & [txtEndDate] & "# AND DocumentType = '" & Me.frmTest & "'"
 

Also, a note about naming conventions. I usually reserve the frm keyword when naming forms. If it's a listbox I usually use lst. Using frm is confusing as people might take it to mean that you're trying to return the value of a form.

Thank you!! However is there a way that I can select more than one option? Because if I want to select ALL how would I do that??

THanks again.

[Rabbit]

If I wanted to select all I would use:

pstrCriteria = "[DateReceived] >= # " & [txtFirstDate] & "# and [DateReceived]<=#" & [txtEndDate] & "# AND DocumentType Like '" & Me.frmTest & "'"
 

And if I wanted all document types, I would have frmTest return *.

[margot]

If I wanted to select all I would use:

pstrCriteria = "[DateReceived] >= # " & [txtFirstDate] & "# and [DateReceived]<=#" & [txtEndDate] & "# AND DocumentType Like '" & Me.frmTest & "'"
 

And if I wanted all document types, I would have frmTest return *.

Thank you sir. However, I cannot select more than one option even if I do Shit and try to select more than one option. It will only return the value of one option.
Thabk you.

[Rabbit]

Selecting all and selecting some are different problems. With a select some, you need a multi-select list box. You'll need to create a comma delimited string by looping through the selections in the list box. And in your criteria you'll use the In keyword.

[margot]

Selecting all and selecting some are different problems. With a select some, you need a multi-select list box. You'll need to create a comma delimited string by looping through the selections in the list box. And in your criteria you'll use the In keyword.

Thank you Sir. This is what I did following your advise. I do not get any errors but it does not work properly. It will open the report but blank. Could you please help. Thank you so much.

 
Dim Criteria As String
Dim pstCriteria As String
Dim strSelect As String
Dim i As Variant
 
   Criteria = ""
   For Each i In Me![lstTest2].ItemsSelected
      If Criteria <> "" Then
         Criteria = Criteria & " OR "
      End If
      Criteria = Criteria & "[DocumentType]='" _
       & Me![lstTest2].ItemData(i) & "'"
   Next i
 
   Me.Filter = Criteria
   Me.FilterOn = True
 
 
 
Select Case frmOptionX
 
 
    Case "rptDateReceived"
 
 
             If IsDate([txtFirstDate]) And IsDate([txtEndDate]) Then
              pstrCriteria = "[DateReceived] >= # " & [txtFirstDate] & "# and [DateReceived]<=#" & [txtEndDate] & "# AND DocumentType = '" & Me.lstTest2 & "'"
 
 
        DoCmd.OpenReport "rptDateReceived", acViewPreview, , pstrCriteria
 
            Else
                MsgBox "You must enter two dates"
 
   End If
 
  End Select
End Sub
 
 

[Rabbit]

Thank you Sir. This is what I did following your advise. I do not get any errors but it does not work properly. It will open the report but blank. Could you please help. Thank you so much.

 
Dim Criteria As String
Dim pstCriteria As String
Dim strSelect As String
Dim i As Variant
 
   Criteria = ""
   For Each i In Me![lstTest2].ItemsSelected
      If Criteria <> "" Then
         Criteria = Criteria & " OR "
      End If
      Criteria = Criteria & "[DocumentType]='" _
       & Me![lstTest2].ItemData(i) & "'"
   Next i
 
   Me.Filter = Criteria
   Me.FilterOn = True
 
 
 
Select Case frmOptionX
 
 
    Case "rptDateReceived"
 
 
             If IsDate([txtFirstDate]) And IsDate([txtEndDate]) Then
              pstrCriteria = "[DateReceived] >= # " & [txtFirstDate] & "# and [DateReceived]<=#" & [txtEndDate] & "# AND DocumentType = '" & Me.lstTest2 & "'"
 
 
        DoCmd.OpenReport "rptDateReceived", acViewPreview, , pstrCriteria
 
            Else
                MsgBox "You must enter two dates"
 
   End If
 
  End Select
End Sub
 
 

When using the Code tags, you don't put spaces between the brackets [ ].

There are a few things wrong with this.

1) You're using the Filter property of the form, this has no effect on a report.
2) You have multiple criteria strings but you only use one of them in your OpenReport.
3) Your string is constructed impropery. It should be of the format:

FieldName In ('Value1', 'Value2', 'Value3')

[margot]

When using the Code tags, you don't put spaces between the brackets [ ].

There are a few things wrong with this.

1) You're using the Filter property of the form, this has no effect on a report.
2) You have multiple criteria strings but you only use one of them in your OpenReport.
3) Your string is constructed impropery. It should be of the format:

FieldName In ('Value1', 'Value2', 'Value3')

Thank you Sir, howeve I am not sure I understand what you mean with the code below. Thanks again.

FieldName In ('Value1', 'Value2', 'Value3')

[/quote]

[Rabbit]

That's what your criteria string should look like after you've looped through your listbox to build it. What you currently have is incorrect. It's not that the syntax is wrong, it'll work, but if there's too many selections, your string will become too long.

[margot]

That's what your criteria string should look like after you've looped through your listbox to build it. What you currently have is incorrect. It's not that the syntax is wrong, it'll work, but if there's too many selections, your string will become too long.

Ok thank you Sir. I will try to fix it.