problem with 2 text boxes and 2 list boxes

[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.

[Code=vb]

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 "rptDateReceive d"


If IsDate([txtFirstDate]) And IsDate([txtEndDate]) Then
pstrCriteria = "[DateReceived] >= # " & [txtFirstDate] & "# and [DateReceived]<=#" & [txtEndDate] & "# AND DocumentType = '" & Me.lstTest2 & "'"


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

Else
MsgBox "You must enter two dates"

End If

End Select
End Sub

[/Code]

[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.

[Code=vb]

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 "rptDateReceive d"


If IsDate([txtFirstDate]) And IsDate([txtEndDate]) Then
pstrCriteria = "[DateReceived] >= # " & [txtFirstDate] & "# and [DateReceived]<=#" & [txtEndDate] & "# AND DocumentType = '" & Me.lstTest2 & "'"


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

Else
MsgBox "You must enter two dates"

End If

End Select
End Sub

[/Code]

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:
[Code=vb]FieldName In ('Value1', 'Value2', 'Value3')[/Code]

[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:
[Code=vb]FieldName In ('Value1', 'Value2', 'Value3')[/Code]

Thank you Sir, howeve I am not sure I understand what you mean with the code below. Thanks again.
[Code=vb]FieldName In ('Value1', 'Value2', 'Value3')[/Code][/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.