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
[Code=sql]
SELECT *
FROM Table1
WHERE EventDate BETWEEN Forms!Form1!txt StartDate AND Forms!Form1!txt EndDate;
[/Code]

[margot]

Form1
txtStartDate
txtEndDate

Query1
[Code=sql]
SELECT *
FROM Table1
WHERE EventDate BETWEEN Forms!Form1!txt StartDate AND Forms!Form1!txt EndDate;
[/Code]

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.OpenRepor t 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.OpenRepor t from an event of your choosing.

Thank you sir. However can I do somehting like the code below using cases for a combo box?
[Code=vb]
Private Sub Command38_Click ()
Dim stDocName
Dim stLinkCriteria
Dim pstCriteria As String


Select Case frmOptionX

Case "DateReceiv ed"
stDocName = "rptDateReceive d"

If IsDate([txtFirstDate]) And IsDate([txtEndDate]) Then
pstrCriteria = "[DateReceived] >= # " & [txtFirstDate] & "# and [DateReceived]<=#" & [txtEndDate] & "#"

DoCmd.OpenRepor t "rptDateReceive d", acViewPreview, , pstrCriteria

Else
MsgBox "You must enter two dates"

End If


Case "DateReplie dOn"

stDocName = "rptDateReplied On"


If IsDate([txtFirstDate]) And IsDate([txtEndDate]) Then
pstrCriteria = "[rptDateRepliedO n] >= # " & [txtFirstDate] & "# and [rptDateRepliedO n]<=#" & [txtEndDate] & "#"

DoCmd.OpenRepor t "rptDateReplied On", acViewPreview, , pstrCriteria

Else
MsgBox "You must enter two dates"

End If

Case "ReplyDeadl ine"

If IsDate([txtFirstDate]) And IsDate([txtEndDate]) Then
pstrCriteria = "[ReplyDateline] >= # " & [txtFirstDate] & "# and [ReplyDateline]<=#" & [txtEndDate] & "#"

DoCmd.OpenRepor t "rptReplyDateli ne", acViewPreview, , pstrCriteria

Else
MsgBox "You must enter two dates"

End Select


End Sub
[/Code]
Thanks in advance

[Rabbit]

That could work but it would be easier just to do:
[Code=vb]
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.OpenRepor t stDocName, acViewPreview, , pstrCriteria

Else
MsgBox "You must enter two dates"

End If
End Sub
[/Code]
Thanks in advance[/QUOTE]

[margot]

That could work but it would be easier just to do:
[Code=vb]
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.OpenRepor t stDocName, acViewPreview, , pstrCriteria

Else
MsgBox "You must enter two dates"

End If
End Sub
[/Code]
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 "RepliedDeadlin e". 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?

[Code=vb]
Private Sub Command48_Click ()

Dim stDocName
Dim pstCriteria As String


Select Case frmOptionX

Case "DateReceiv ed"
stDocName = "rptDateReceive d"

If IsDate([txtFirstDate]) And IsDate([txtEndDate]) Then
pstrCriteria = "[DateReceived] >= # " & [txtFirstDate] & "# and [DateReceived]<=#" & [txtEndDate] & "#"

DoCmd.OpenRepor t "rptDateReceive d", acViewPreview, , pstrCriteria

Else
MsgBox "You must enter two dates"

End If


Case "DateReplie dOn"

stDocName = "rptDateReplied On"


If IsDate([txtFirstDate]) And IsDate([txtEndDate]) Then
pstrCriteria = "[DateRepliedOn] >= # " & [txtFirstDate] & "# and [DateRepliedOn]<=#" & [txtEndDate] & "#"

DoCmd.OpenRepor t "rptDateReplied On", acViewPreview, , pstrCriteria

Else
MsgBox "You must enter two dates"

End If

Case "ReplyDeadl ine"

If IsDate([txtFirstDate]) And IsDate([txtEndDate]) Then
pstrCriteria = "[ReplyDateline] >= # " & [txtFirstDate] & "# and [ReplyDateline]<=#" & [txtEndDate] & "#"

DoCmd.OpenRepor t "rptReplyDateli ne", acViewPreview, , pstrCriteria

Else
MsgBox "You must enter two dates"

End Select


End Sub
[/Code]
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.

[Code=vb]

Private Sub Command48_Click ()


Dim pstCriteria As String
Dim strSelect As String



Select Case frmOptionX


Case "rptDateReceive d"


If IsDate([txtFirstDate]) And IsDate([txtEndDate]) Then
pstrCriteria = "[DateReceived] >= # " & [txtFirstDate] & "# and [DateReceived]<=#" & [txtEndDate] & "#" & strSelect = "DocumentTy pe = '" & Me.frmTest & "'"


DoCmd.OpenRepor t "rptDateReceive d", acViewPreview, , pstrCriteria, , strSelect

Else
MsgBox "You must enter two dates"

End If
[/Code]