List Box MultiSelect

G

Guest

Hi:

I have a list box named org, and I trying to make it work; the multiselect
property is set Extended.

Where the below code should be placed; also what is [somecontrol]?

Dim varItem as Variant
Dim strResult as String
For Each varItem In org.ItemsSelected
strResult = strResult & Me!org.ItemData(varItem) & ", "
Next varItem
strResult = Left(strResult, Len(strResult) - 2)
[SomeControl] = strResult
*****

The query that the report is based on is:


SELECT DISTINCT Journals.org, Journals.account, Journals.sub_account,
Journals.product, Journals.cust_group, Journals.eff_date, Journals.curr_code,
Journals.journal_id, Journals.amt_class_type, Journals.amount_book,
Journals.amount_tran, Journals.post_period, Journals.post_date,
Journals.journal_seq, Journals.description
FROM Journals
WHERE (((Journals.org)=[forms]![paramform]![org]) AND
((Journals.account)=[forms]![paramform]![account]) AND
((Journals.product)=[forms]![paramform]![product]) AND ((Journals.eff_date)
Between [Please enter the Start Date] And [Please enter the End Date]));

Thanks a lot,

Dan
 
P

Pieter Wijnen

Assuming you want to use the data to filter the report
Create a Command Button (cmdPrint)
& use the code snippet like so in The OnClick Event ([Event Procedure])

Private Sub cmdPrint_Click()
Dim varItem as Variant
Dim strResult as String

For Each varItem In org.ItemsSelected
strResult = strResult & Me.org.ItemData(varItem) & ", " ' Numeric field
'strResult = strResult & "'" & Me.org.ItemData(varItem) & "', " ' Text
Field
'strResult = strResult & "#" &
VBA.Format(Me.org.ItemData(varItem),"yyyy-mm-dd") & "#', " ' Date Field
Next 'varItem

strResult = VBA.Left(strResult, VBA.Len(strResult) - 2)
If VBA.Len(strResult) = 0 Then
Access.Application.DoCmd.OpenReport "MyReport",
Access.AcView.acViewPreview
Else
Access.Application.DoCmd.OpenReport "MyReport",
Access.AcView.acViewPreview, WhereCondition:="org In (" & strResult & ")"
End if

End Sub

HtH

Pieter
 
G

Guest

Thank you Pieter!

I am not getting any records; but I know there are!

Any other idea?

Thanks again,

Dan

Pieter Wijnen said:
Assuming you want to use the data to filter the report
Create a Command Button (cmdPrint)
& use the code snippet like so in The OnClick Event ([Event Procedure])

Private Sub cmdPrint_Click()
Dim varItem as Variant
Dim strResult as String

For Each varItem In org.ItemsSelected
strResult = strResult & Me.org.ItemData(varItem) & ", " ' Numeric field
'strResult = strResult & "'" & Me.org.ItemData(varItem) & "', " ' Text
Field
'strResult = strResult & "#" &
VBA.Format(Me.org.ItemData(varItem),"yyyy-mm-dd") & "#', " ' Date Field
Next 'varItem

strResult = VBA.Left(strResult, VBA.Len(strResult) - 2)
If VBA.Len(strResult) = 0 Then
Access.Application.DoCmd.OpenReport "MyReport",
Access.AcView.acViewPreview
Else
Access.Application.DoCmd.OpenReport "MyReport",
Access.AcView.acViewPreview, WhereCondition:="org In (" & strResult & ")"
End if

End Sub

HtH

Pieter

D said:
Hi:

I have a list box named org, and I trying to make it work; the multiselect
property is set Extended.

Where the below code should be placed; also what is [somecontrol]?

Dim varItem as Variant
Dim strResult as String
For Each varItem In org.ItemsSelected
strResult = strResult & Me!org.ItemData(varItem) & ", "
Next varItem
strResult = Left(strResult, Len(strResult) - 2)
[SomeControl] = strResult
*****

The query that the report is based on is:


SELECT DISTINCT Journals.org, Journals.account, Journals.sub_account,
Journals.product, Journals.cust_group, Journals.eff_date,
Journals.curr_code,
Journals.journal_id, Journals.amt_class_type, Journals.amount_book,
Journals.amount_tran, Journals.post_period, Journals.post_date,
Journals.journal_seq, Journals.description
FROM Journals
WHERE (((Journals.org)=[forms]![paramform]![org]) AND
((Journals.account)=[forms]![paramform]![account]) AND
((Journals.product)=[forms]![paramform]![product]) AND
((Journals.eff_date)
Between [Please enter the Start Date] And [Please enter the End Date]));

Thanks a lot,

Dan
Click to expand...
Click to expand...
 
D

Douglas J. Steele

If you're looking at date values, do the fields also have time (because you
used the Now() function to populate them)?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


D said:
Thank you Pieter!

I am not getting any records; but I know there are!

Any other idea?

Thanks again,

Dan

Pieter Wijnen said:
Assuming you want to use the data to filter the report
Create a Command Button (cmdPrint)
& use the code snippet like so in The OnClick Event ([Event Procedure])

Private Sub cmdPrint_Click()
Dim varItem as Variant
Dim strResult as String

For Each varItem In org.ItemsSelected
strResult = strResult & Me.org.ItemData(varItem) & ", " ' Numeric field
'strResult = strResult & "'" & Me.org.ItemData(varItem) & "', " ' Text
Field
'strResult = strResult & "#" &
VBA.Format(Me.org.ItemData(varItem),"yyyy-mm-dd") & "#', " ' Date Field
Next 'varItem

strResult = VBA.Left(strResult, VBA.Len(strResult) - 2)
If VBA.Len(strResult) = 0 Then
Access.Application.DoCmd.OpenReport "MyReport",
Access.AcView.acViewPreview
Else
Access.Application.DoCmd.OpenReport "MyReport",
Access.AcView.acViewPreview, WhereCondition:="org In (" & strResult & ")"
End if

End Sub

HtH

Pieter

D said:
Hi:

I have a list box named org, and I trying to make it work; the
multiselect
property is set Extended.

Where the below code should be placed; also what is [somecontrol]?

Dim varItem as Variant
Dim strResult as String
For Each varItem In org.ItemsSelected
strResult = strResult & Me!org.ItemData(varItem) & ", "
Next varItem
strResult = Left(strResult, Len(strResult) - 2)
[SomeControl] = strResult
*****

The query that the report is based on is:


SELECT DISTINCT Journals.org, Journals.account, Journals.sub_account,
Journals.product, Journals.cust_group, Journals.eff_date,
Journals.curr_code,
Journals.journal_id, Journals.amt_class_type, Journals.amount_book,
Journals.amount_tran, Journals.post_period, Journals.post_date,
Journals.journal_seq, Journals.description
FROM Journals
WHERE (((Journals.org)=[forms]![paramform]![org]) AND
((Journals.account)=[forms]![paramform]![account]) AND
((Journals.product)=[forms]![paramform]![product]) AND
((Journals.eff_date)
Between [Please enter the Start Date] And [Please enter the End
Date]));

Thanks a lot,

Dan
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hi Douglas:

If I run the below query, that the report is based on, I get the right
result; when I use the form - on report open to select org - from a list box,
account and product from cmbo box, no result; the dates are not part of the
form; are in the qry criteria.

As per your question when I run the qry directly and I get to enter the
dates I am not entering the time; just 11/1/2007.

I am going nuts!

Thanks,

Dan

Douglas J. Steele said:
If you're looking at date values, do the fields also have time (because you
used the Now() function to populate them)?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


D said:
Thank you Pieter!

I am not getting any records; but I know there are!

Any other idea?

Thanks again,

Dan

Pieter Wijnen said:
Assuming you want to use the data to filter the report
Create a Command Button (cmdPrint)
& use the code snippet like so in The OnClick Event ([Event Procedure])

Private Sub cmdPrint_Click()
Dim varItem as Variant
Dim strResult as String

For Each varItem In org.ItemsSelected
strResult = strResult & Me.org.ItemData(varItem) & ", " ' Numeric field
'strResult = strResult & "'" & Me.org.ItemData(varItem) & "', " ' Text
Field
'strResult = strResult & "#" &
VBA.Format(Me.org.ItemData(varItem),"yyyy-mm-dd") & "#', " ' Date Field
Next 'varItem

strResult = VBA.Left(strResult, VBA.Len(strResult) - 2)
If VBA.Len(strResult) = 0 Then
Access.Application.DoCmd.OpenReport "MyReport",
Access.AcView.acViewPreview
Else
Access.Application.DoCmd.OpenReport "MyReport",
Access.AcView.acViewPreview, WhereCondition:="org In (" & strResult & ")"
End if

End Sub

HtH

Pieter

Hi:

I have a list box named org, and I trying to make it work; the
multiselect
property is set Extended.

Where the below code should be placed; also what is [somecontrol]?

Dim varItem as Variant
Dim strResult as String
For Each varItem In org.ItemsSelected
strResult = strResult & Me!org.ItemData(varItem) & ", "
Next varItem
strResult = Left(strResult, Len(strResult) - 2)
[SomeControl] = strResult
*****

The query that the report is based on is:


SELECT DISTINCT Journals.org, Journals.account, Journals.sub_account,
Journals.product, Journals.cust_group, Journals.eff_date,
Journals.curr_code,
Journals.journal_id, Journals.amt_class_type, Journals.amount_book,
Journals.amount_tran, Journals.post_period, Journals.post_date,
Journals.journal_seq, Journals.description
FROM Journals
WHERE (((Journals.org)=[forms]![paramform]![org]) AND
((Journals.account)=[forms]![paramform]![account]) AND
((Journals.product)=[forms]![paramform]![product]) AND
((Journals.eff_date)
Between [Please enter the Start Date] And [Please enter the End
Date]));

Thanks a lot,

Dan
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

I probably shouldn't have jumped in, because I didn't read everything that
preceded, so let me go over some basics.

You say that you can run the query below and it works, but that when you use
the form, it doesn't. What does that mean? The query you posted refers to
controls on form paramform.

What exactly did you use from Pieter's code? (In other words, show me the
code you're using.)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


D said:
Hi Douglas:

If I run the below query, that the report is based on, I get the right
result; when I use the form - on report open to select org - from a list
box,
account and product from cmbo box, no result; the dates are not part of
the
form; are in the qry criteria.

As per your question when I run the qry directly and I get to enter the
dates I am not entering the time; just 11/1/2007.

I am going nuts!

Thanks,

Dan

Douglas J. Steele said:
If you're looking at date values, do the fields also have time (because
you
used the Now() function to populate them)?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


D said:
Thank you Pieter!

I am not getting any records; but I know there are!

Any other idea?

Thanks again,

Dan

:

Assuming you want to use the data to filter the report
Create a Command Button (cmdPrint)
& use the code snippet like so in The OnClick Event ([Event
Procedure])

Private Sub cmdPrint_Click()
Dim varItem as Variant
Dim strResult as String

For Each varItem In org.ItemsSelected
strResult = strResult & Me.org.ItemData(varItem) & ", " ' Numeric
field
'strResult = strResult & "'" & Me.org.ItemData(varItem) & "', " '
Text
Field
'strResult = strResult & "#" &
VBA.Format(Me.org.ItemData(varItem),"yyyy-mm-dd") & "#', " ' Date
Field
Next 'varItem

strResult = VBA.Left(strResult, VBA.Len(strResult) - 2)
If VBA.Len(strResult) = 0 Then
Access.Application.DoCmd.OpenReport "MyReport",
Access.AcView.acViewPreview
Else
Access.Application.DoCmd.OpenReport "MyReport",
Access.AcView.acViewPreview, WhereCondition:="org In (" & strResult &
")"
End if

End Sub

HtH

Pieter

Hi:

I have a list box named org, and I trying to make it work; the
multiselect
property is set Extended.

Where the below code should be placed; also what is [somecontrol]?

Dim varItem as Variant
Dim strResult as String
For Each varItem In org.ItemsSelected
strResult = strResult & Me!org.ItemData(varItem) & ", "
Next varItem
strResult = Left(strResult, Len(strResult) - 2)
[SomeControl] = strResult
*****

The query that the report is based on is:


SELECT DISTINCT Journals.org, Journals.account,
Journals.sub_account,
Journals.product, Journals.cust_group, Journals.eff_date,
Journals.curr_code,
Journals.journal_id, Journals.amt_class_type, Journals.amount_book,
Journals.amount_tran, Journals.post_period, Journals.post_date,
Journals.journal_seq, Journals.description
FROM Journals
WHERE (((Journals.org)=[forms]![paramform]![org]) AND
((Journals.account)=[forms]![paramform]![account]) AND
((Journals.product)=[forms]![paramform]![product]) AND
((Journals.eff_date)
Between [Please enter the Start Date] And [Please enter the End
Date]));

Thanks a lot,

Dan
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

HI Doug:

ParamForm is used on open report - mnl-report - to filter the report;
mnl-report is based on the below qry. if I set the multiselect to none, it
works.

here is the code that is use.


Private Sub cmdPrint_Click()

Dim varItem As Variant
Dim strResult As String

For Each varItem In org.ItemsSelected
'strResult = strResult & Me.org.ItemData(varItem) & ", " ' Numeric field
strResult = strResult & "'" & Me.org.ItemData(varItem) & "', " ' Text
'Field
'strResult = strResult & "#" &
'VBA.Format(Me.org.ItemData(varItem),"yyyy-mm-dd") & "#', " ' Date Field
Next varItem

strResult = VBA.Left(strResult, VBA.Len(strResult) - 1)
If VBA.Len(strResult) = 0 Then
Access.Application.DoCmd.OpenReport "Mnl-report", _
Access.AcView.acViewPreview
Else
Access.Application.DoCmd.OpenReport "Mnl-report", _
Access.AcView.acViewPreview, WhereCondition:="org In (" & strResult & ")"

End If
Me.Visible = False
End Sub


Douglas J. Steele said:
I probably shouldn't have jumped in, because I didn't read everything that
preceded, so let me go over some basics.

You say that you can run the query below and it works, but that when you use
the form, it doesn't. What does that mean? The query you posted refers to
controls on form paramform.

What exactly did you use from Pieter's code? (In other words, show me the
code you're using.)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


D said:
Hi Douglas:

If I run the below query, that the report is based on, I get the right
result; when I use the form - on report open to select org - from a list
box,
account and product from cmbo box, no result; the dates are not part of
the
form; are in the qry criteria.

As per your question when I run the qry directly and I get to enter the
dates I am not entering the time; just 11/1/2007.

I am going nuts!

Thanks,

Dan

Douglas J. Steele said:
If you're looking at date values, do the fields also have time (because
you
used the Now() function to populate them)?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thank you Pieter!

I am not getting any records; but I know there are!

Any other idea?

Thanks again,

Dan

:

Assuming you want to use the data to filter the report
Create a Command Button (cmdPrint)
& use the code snippet like so in The OnClick Event ([Event
Procedure])

Private Sub cmdPrint_Click()
Dim varItem as Variant
Dim strResult as String

For Each varItem In org.ItemsSelected
strResult = strResult & Me.org.ItemData(varItem) & ", " ' Numeric
field
'strResult = strResult & "'" & Me.org.ItemData(varItem) & "', " '
Text
Field
'strResult = strResult & "#" &
VBA.Format(Me.org.ItemData(varItem),"yyyy-mm-dd") & "#', " ' Date
Field
Next 'varItem

strResult = VBA.Left(strResult, VBA.Len(strResult) - 2)
If VBA.Len(strResult) = 0 Then
Access.Application.DoCmd.OpenReport "MyReport",
Access.AcView.acViewPreview
Else
Access.Application.DoCmd.OpenReport "MyReport",
Access.AcView.acViewPreview, WhereCondition:="org In (" & strResult &
")"
End if

End Sub

HtH

Pieter

Hi:

I have a list box named org, and I trying to make it work; the
multiselect
property is set Extended.

Where the below code should be placed; also what is [somecontrol]?

Dim varItem as Variant
Dim strResult as String
For Each varItem In org.ItemsSelected
strResult = strResult & Me!org.ItemData(varItem) & ", "
Next varItem
strResult = Left(strResult, Len(strResult) - 2)
[SomeControl] = strResult
*****

The query that the report is based on is:


SELECT DISTINCT Journals.org, Journals.account,
Journals.sub_account,
Journals.product, Journals.cust_group, Journals.eff_date,
Journals.curr_code,
Journals.journal_id, Journals.amt_class_type, Journals.amount_book,
Journals.amount_tran, Journals.post_period, Journals.post_date,
Journals.journal_seq, Journals.description
FROM Journals
WHERE (((Journals.org)=[forms]![paramform]![org]) AND
((Journals.account)=[forms]![paramform]![account]) AND
((Journals.product)=[forms]![paramform]![product]) AND
((Journals.eff_date)
Between [Please enter the Start Date] And [Please enter the End
Date]));

Thanks a lot,

Dan
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Does your query still contain

(((Journals.org)=[forms]![paramform]![org])

If so, remove it. Referring to a multiselect listbox returns Null,
regardless of whether none, one or multiple entries are selected. (That's
why I couldn't understand how the query could work if you ran it by itself)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


D said:
HI Doug:

ParamForm is used on open report - mnl-report - to filter the report;
mnl-report is based on the below qry. if I set the multiselect to none, it
works.

here is the code that is use.


Private Sub cmdPrint_Click()

Dim varItem As Variant
Dim strResult As String

For Each varItem In org.ItemsSelected
'strResult = strResult & Me.org.ItemData(varItem) & ", " ' Numeric field
strResult = strResult & "'" & Me.org.ItemData(varItem) & "', " ' Text
'Field
'strResult = strResult & "#" &
'VBA.Format(Me.org.ItemData(varItem),"yyyy-mm-dd") & "#', " ' Date Field
Next varItem

strResult = VBA.Left(strResult, VBA.Len(strResult) - 1)
If VBA.Len(strResult) = 0 Then
Access.Application.DoCmd.OpenReport "Mnl-report", _
Access.AcView.acViewPreview
Else
Access.Application.DoCmd.OpenReport "Mnl-report", _
Access.AcView.acViewPreview, WhereCondition:="org In (" & strResult & ")"

End If
Me.Visible = False
End Sub


Douglas J. Steele said:
I probably shouldn't have jumped in, because I didn't read everything
that
preceded, so let me go over some basics.

You say that you can run the query below and it works, but that when you
use
the form, it doesn't. What does that mean? The query you posted refers to
controls on form paramform.

What exactly did you use from Pieter's code? (In other words, show me the
code you're using.)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


D said:
Hi Douglas:

If I run the below query, that the report is based on, I get the right
result; when I use the form - on report open to select org - from a
list
box,
account and product from cmbo box, no result; the dates are not part of
the
form; are in the qry criteria.

As per your question when I run the qry directly and I get to enter the
dates I am not entering the time; just 11/1/2007.

I am going nuts!

Thanks,

Dan

:

If you're looking at date values, do the fields also have time
(because
you
used the Now() function to populate them)?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thank you Pieter!

I am not getting any records; but I know there are!

Any other idea?

Thanks again,

Dan

:

Assuming you want to use the data to filter the report
Create a Command Button (cmdPrint)
& use the code snippet like so in The OnClick Event ([Event
Procedure])

Private Sub cmdPrint_Click()
Dim varItem as Variant
Dim strResult as String

For Each varItem In org.ItemsSelected
strResult = strResult & Me.org.ItemData(varItem) & ", " ' Numeric
field
'strResult = strResult & "'" & Me.org.ItemData(varItem) & "', " '
Text
Field
'strResult = strResult & "#" &
VBA.Format(Me.org.ItemData(varItem),"yyyy-mm-dd") & "#', " ' Date
Field
Next 'varItem

strResult = VBA.Left(strResult, VBA.Len(strResult) - 2)
If VBA.Len(strResult) = 0 Then
Access.Application.DoCmd.OpenReport "MyReport",
Access.AcView.acViewPreview
Else
Access.Application.DoCmd.OpenReport "MyReport",
Access.AcView.acViewPreview, WhereCondition:="org In (" & strResult
&
")"
End if

End Sub

HtH

Pieter

Hi:

I have a list box named org, and I trying to make it work; the
multiselect
property is set Extended.

Where the below code should be placed; also what is
[somecontrol]?

Dim varItem as Variant
Dim strResult as String
For Each varItem In org.ItemsSelected
strResult = strResult & Me!org.ItemData(varItem) & ", "
Next varItem
strResult = Left(strResult, Len(strResult) - 2)
[SomeControl] = strResult
*****

The query that the report is based on is:


SELECT DISTINCT Journals.org, Journals.account,
Journals.sub_account,
Journals.product, Journals.cust_group, Journals.eff_date,
Journals.curr_code,
Journals.journal_id, Journals.amt_class_type,
Journals.amount_book,
Journals.amount_tran, Journals.post_period, Journals.post_date,
Journals.journal_seq, Journals.description
FROM Journals
WHERE (((Journals.org)=[forms]![paramform]![org]) AND
((Journals.account)=[forms]![paramform]![account]) AND
((Journals.product)=[forms]![paramform]![product]) AND
((Journals.eff_date)
Between [Please enter the Start Date] And [Please enter the End
Date]));

Thanks a lot,

Dan
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Perfect! it worked!!

THANKS Doug/Pieter!!

I did not konw that; it works fine with CmboBox.

Douglas J. Steele said:
Does your query still contain

(((Journals.org)=[forms]![paramform]![org])

If so, remove it. Referring to a multiselect listbox returns Null,
regardless of whether none, one or multiple entries are selected. (That's
why I couldn't understand how the query could work if you ran it by itself)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


D said:
HI Doug:

ParamForm is used on open report - mnl-report - to filter the report;
mnl-report is based on the below qry. if I set the multiselect to none, it
works.

here is the code that is use.


Private Sub cmdPrint_Click()

Dim varItem As Variant
Dim strResult As String

For Each varItem In org.ItemsSelected
'strResult = strResult & Me.org.ItemData(varItem) & ", " ' Numeric field
strResult = strResult & "'" & Me.org.ItemData(varItem) & "', " ' Text
'Field
'strResult = strResult & "#" &
'VBA.Format(Me.org.ItemData(varItem),"yyyy-mm-dd") & "#', " ' Date Field
Next varItem

strResult = VBA.Left(strResult, VBA.Len(strResult) - 1)
If VBA.Len(strResult) = 0 Then
Access.Application.DoCmd.OpenReport "Mnl-report", _
Access.AcView.acViewPreview
Else
Access.Application.DoCmd.OpenReport "Mnl-report", _
Access.AcView.acViewPreview, WhereCondition:="org In (" & strResult & ")"

End If
Me.Visible = False
End Sub


Douglas J. Steele said:
I probably shouldn't have jumped in, because I didn't read everything
that
preceded, so let me go over some basics.

You say that you can run the query below and it works, but that when you
use
the form, it doesn't. What does that mean? The query you posted refers to
controls on form paramform.

What exactly did you use from Pieter's code? (In other words, show me the
code you're using.)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Douglas:

If I run the below query, that the report is based on, I get the right
result; when I use the form - on report open to select org - from a
list
box,
account and product from cmbo box, no result; the dates are not part of
the
form; are in the qry criteria.

As per your question when I run the qry directly and I get to enter the
dates I am not entering the time; just 11/1/2007.

I am going nuts!

Thanks,

Dan

:

If you're looking at date values, do the fields also have time
(because
you
used the Now() function to populate them)?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thank you Pieter!

I am not getting any records; but I know there are!

Any other idea?

Thanks again,

Dan

:

Assuming you want to use the data to filter the report
Create a Command Button (cmdPrint)
& use the code snippet like so in The OnClick Event ([Event
Procedure])

Private Sub cmdPrint_Click()
Dim varItem as Variant
Dim strResult as String

For Each varItem In org.ItemsSelected
strResult = strResult & Me.org.ItemData(varItem) & ", " ' Numeric
field
'strResult = strResult & "'" & Me.org.ItemData(varItem) & "', " '
Text
Field
'strResult = strResult & "#" &
VBA.Format(Me.org.ItemData(varItem),"yyyy-mm-dd") & "#', " ' Date
Field
Next 'varItem

strResult = VBA.Left(strResult, VBA.Len(strResult) - 2)
If VBA.Len(strResult) = 0 Then
Access.Application.DoCmd.OpenReport "MyReport",
Access.AcView.acViewPreview
Else
Access.Application.DoCmd.OpenReport "MyReport",
Access.AcView.acViewPreview, WhereCondition:="org In (" & strResult
&
")"
End if

End Sub

HtH

Pieter

Hi:

I have a list box named org, and I trying to make it work; the
multiselect
property is set Extended.

Where the below code should be placed; also what is
[somecontrol]?

Dim varItem as Variant
Dim strResult as String
For Each varItem In org.ItemsSelected
strResult = strResult & Me!org.ItemData(varItem) & ", "
Next varItem
strResult = Left(strResult, Len(strResult) - 2)
[SomeControl] = strResult
*****

The query that the report is based on is:


SELECT DISTINCT Journals.org, Journals.account,
Journals.sub_account,
Journals.product, Journals.cust_group, Journals.eff_date,
Journals.curr_code,
Journals.journal_id, Journals.amt_class_type,
Journals.amount_book,
Journals.amount_tran, Journals.post_period, Journals.post_date,
Journals.journal_seq, Journals.description
FROM Journals
WHERE (((Journals.org)=[forms]![paramform]![org]) AND
((Journals.account)=[forms]![paramform]![account]) AND
((Journals.product)=[forms]![paramform]![product]) AND
((Journals.eff_date)
Between [Please enter the Start Date] And [Please enter the End
Date]));

Thanks a lot,

Dan
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

You're welcome.

Referring to a list box that doesn't allow multiselect works exactly like a
combo box. It's the multiselect that makes the difference.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


D said:
Perfect! it worked!!

THANKS Doug/Pieter!!

I did not konw that; it works fine with CmboBox.

Douglas J. Steele said:
Does your query still contain

(((Journals.org)=[forms]![paramform]![org])

If so, remove it. Referring to a multiselect listbox returns Null,
regardless of whether none, one or multiple entries are selected. (That's
why I couldn't understand how the query could work if you ran it by
itself)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


D said:
HI Doug:

ParamForm is used on open report - mnl-report - to filter the report;
mnl-report is based on the below qry. if I set the multiselect to none,
it
works.

here is the code that is use.


Private Sub cmdPrint_Click()

Dim varItem As Variant
Dim strResult As String

For Each varItem In org.ItemsSelected
'strResult = strResult & Me.org.ItemData(varItem) & ", " ' Numeric
field
strResult = strResult & "'" & Me.org.ItemData(varItem) & "', " ' Text
'Field
'strResult = strResult & "#" &
'VBA.Format(Me.org.ItemData(varItem),"yyyy-mm-dd") & "#', " ' Date
Field
Next varItem

strResult = VBA.Left(strResult, VBA.Len(strResult) - 1)
If VBA.Len(strResult) = 0 Then
Access.Application.DoCmd.OpenReport "Mnl-report", _
Access.AcView.acViewPreview
Else
Access.Application.DoCmd.OpenReport "Mnl-report", _
Access.AcView.acViewPreview, WhereCondition:="org In (" & strResult &
")"

End If
Me.Visible = False
End Sub


:

I probably shouldn't have jumped in, because I didn't read everything
that
preceded, so let me go over some basics.

You say that you can run the query below and it works, but that when
you
use
the form, it doesn't. What does that mean? The query you posted refers
to
controls on form paramform.

What exactly did you use from Pieter's code? (In other words, show me
the
code you're using.)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Douglas:

If I run the below query, that the report is based on, I get the
right
result; when I use the form - on report open to select org - from a
list
box,
account and product from cmbo box, no result; the dates are not part
of
the
form; are in the qry criteria.

As per your question when I run the qry directly and I get to enter
the
dates I am not entering the time; just 11/1/2007.

I am going nuts!

Thanks,

Dan

:

If you're looking at date values, do the fields also have time
(because
you
used the Now() function to populate them)?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thank you Pieter!

I am not getting any records; but I know there are!

Any other idea?

Thanks again,

Dan

:

Assuming you want to use the data to filter the report
Create a Command Button (cmdPrint)
& use the code snippet like so in The OnClick Event ([Event
Procedure])

Private Sub cmdPrint_Click()
Dim varItem as Variant
Dim strResult as String

For Each varItem In org.ItemsSelected
strResult = strResult & Me.org.ItemData(varItem) & ", " '
Numeric
field
'strResult = strResult & "'" & Me.org.ItemData(varItem) & "',
" '
Text
Field
'strResult = strResult & "#" &
VBA.Format(Me.org.ItemData(varItem),"yyyy-mm-dd") & "#', " '
Date
Field
Next 'varItem

strResult = VBA.Left(strResult, VBA.Len(strResult) - 2)
If VBA.Len(strResult) = 0 Then
Access.Application.DoCmd.OpenReport "MyReport",
Access.AcView.acViewPreview
Else
Access.Application.DoCmd.OpenReport "MyReport",
Access.AcView.acViewPreview, WhereCondition:="org In (" &
strResult
&
")"
End if

End Sub

HtH

Pieter

Hi:

I have a list box named org, and I trying to make it work; the
multiselect
property is set Extended.

Where the below code should be placed; also what is
[somecontrol]?

Dim varItem as Variant
Dim strResult as String
For Each varItem In org.ItemsSelected
strResult = strResult & Me!org.ItemData(varItem) & ", "
Next varItem
strResult = Left(strResult, Len(strResult) - 2)
[SomeControl] = strResult
*****

The query that the report is based on is:


SELECT DISTINCT Journals.org, Journals.account,
Journals.sub_account,
Journals.product, Journals.cust_group, Journals.eff_date,
Journals.curr_code,
Journals.journal_id, Journals.amt_class_type,
Journals.amount_book,
Journals.amount_tran, Journals.post_period,
Journals.post_date,
Journals.journal_seq, Journals.description
FROM Journals
WHERE (((Journals.org)=[forms]![paramform]![org]) AND
((Journals.account)=[forms]![paramform]![account]) AND
((Journals.product)=[forms]![paramform]![product]) AND
((Journals.eff_date)
Between [Please enter the Start Date] And [Please enter the
End
Date]));

Thanks a lot,

Dan
Click to expand...
Click to expand...
Click to expand...
 
Top