openargs

[Bob Wickham]

Hi,
I am trying to get the OpenArgs on my report to display Yes or No instead
of -1 or 0.
I have an unbound listbox on a form with its Row Source property set to
SELECT DISTINCT tblCommission.Debtored FROM tblCommission;
The debtored field is a Yes/No field in the table tblCommission and when it
appears on the form it reads Yes or No.
I'm using a multi-select listbox instead of check boxes as there are 3
possible choices, Yes, No or Both
The code that runs from the OnClick event of a button is (in part) which is
borrowed mostly from Allen Browne.

With Me.lstDebtored
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column
strWhere5 = strWhere5 & strDelim5 & .ItemData(varItem) &
strDelim5 & ","
'Build up the description from the text in the visible
column.
strDescrip5 = strDescrip5 & """" & .Column(0, varItem) &
""", "

End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere5) - 1
If lngLen > 0 Then

strWhere5 = "[Debtored] IN (" & Left$(strWhere5, lngLen) & ")"
lngLen = Len(strDescrip5) - 2

If lngLen > 0 Then
strDescrip5 = "DEBTORED: " & Left$(strDescrip5, lngLen)
End If
End If

'and then at the end

strWhere = "(" & strWhere1 & ") AND (" & strWhere2 & ") AND (" & strWhere3 &
") AND (" & strWhere4 & ") AND (" & strWhere5 & ")"

strDescrip = "(" & strDescrip1 & ") AND (" & strDescrip2 & ") AND (" &
strDescrip3 & ") AND (" & strDescrip4 & ") AND (" & strDescrip5 & ")"

DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip


In the header of my report I have =Report.OpenArgs.

If "No" is selected on the form, then strDescrip5 is displayed in OpenArgs
on the report as "0"
If "Yes" is selected on the form, then strDescrip5 is displayed in OpenArgs
on the report as "-1"

I need to be able to display it as "No" or "Yes".

I have tried all sorts of ways such as
If strDescrip5 = "-1" Then
strDescrip5 = "Yes"
End If
If strDescrip5 = "0" Then
strDescrip5 = "No"
End If

but it doesn't make any difference.

Any ideas greatly appreciated.

Bob

 

[Jeff Boyce]

Bob

One possibility might be to change what you pass. Where are you getting the
value for OpenArgs? Could you do the conversion there, and pass a
"Yes"/"No" via OpenArgs?

Could you do the conversion in a query, and use the value in the query
instead of passing a value in OpenArgs?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Bob Wickham]

Hi Jeff,

I'm getting the value for OpenArgs from strDescrip5 which is the description
of text in the visible column of the 5th multi-select list box I have on the
form.
The other 4 descriptions made up from visible text in the other 4 list boxes
work perfectly. That is, the actual text visible in the list box is
transferred to OpenArgs on the report, even though, the Row Source for those
list boxes includes an ID number as Primary Key.
I guess what I'm getting is exactly what I should be getting given that
Access thinks of a Yes/No as -1/0.
But -1 doesn't make any sense to my client.
I'm not sure what you mean by "Could you do the conversion in a query, and
use the value in the query
instead of passing a value in OpenArgs?"
Running a query using the same syntax that I have as the Row Source,
SELECT DISTINCT tblCommission.Debtored FROM tblCommission;
actually produces Check boxes with ticks.
I'm using DISTINCT because I would otherwise get hundreds of Yes and
hundreds of No.

Could you suggest how I would go about designing a query to change the
display of OpenArgs.

Thanks,
Bob

Bob

One possibility might be to change what you pass. Where are you getting
the
value for OpenArgs? Could you do the conversion there, and pass a
"Yes"/"No" via OpenArgs?

Could you do the conversion in a query, and use the value in the query
instead of passing a value in OpenArgs?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Hi,
I am trying to get the OpenArgs on my report to display Yes or No instead
of -1 or 0.
I have an unbound listbox on a form with its Row Source property set to
SELECT DISTINCT tblCommission.Debtored FROM tblCommission;
The debtored field is a Yes/No field in the table tblCommission and when it
appears on the form it reads Yes or No.
I'm using a multi-select listbox instead of check boxes as there are 3
possible choices, Yes, No or Both
The code that runs from the OnClick event of a button is (in part) which is
borrowed mostly from Allen Browne.

With Me.lstDebtored
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column
strWhere5 = strWhere5 & strDelim5 & .ItemData(varItem) &
strDelim5 & ","
'Build up the description from the text in the visible
column.
strDescrip5 = strDescrip5 & """" & .Column(0, varItem) &
""", "

End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere5) - 1
If lngLen > 0 Then

strWhere5 = "[Debtored] IN (" & Left$(strWhere5, lngLen) & ")"
lngLen = Len(strDescrip5) - 2

If lngLen > 0 Then
strDescrip5 = "DEBTORED: " & Left$(strDescrip5, lngLen)
End If
End If

'and then at the end

strWhere = "(" & strWhere1 & ") AND (" & strWhere2 & ") AND (" &
strWhere3 &
") AND (" & strWhere4 & ") AND (" & strWhere5 & ")"

strDescrip = "(" & strDescrip1 & ") AND (" & strDescrip2 & ") AND (" &
strDescrip3 & ") AND (" & strDescrip4 & ") AND (" & strDescrip5 & ")"

DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip


In the header of my report I have =Report.OpenArgs.

If "No" is selected on the form, then strDescrip5 is displayed in
OpenArgs
on the report as "0"
If "Yes" is selected on the form, then strDescrip5 is displayed in OpenArgs
on the report as "-1"

I need to be able to display it as "No" or "Yes".

I have tried all sorts of ways such as
If strDescrip5 = "-1" Then
strDescrip5 = "Yes"
End If
If strDescrip5 = "0" Then
strDescrip5 = "No"
End If

but it doesn't make any difference.

Any ideas greatly appreciated.

Bob

 

[Jeff Boyce]

Bob

I wasn't suggesting that you use a query to change the OpenArgs. I was
suggesting that you forget about the OpenArgs and use a query as the source
for your report. In the query, you could add a field that "evaluates"
your -1/0 field and generates the text "Yes/No". For example:

Expr1: IIF([YourField]=0,"No", "Yes")

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Hi Jeff,

I'm getting the value for OpenArgs from strDescrip5 which is the description
of text in the visible column of the 5th multi-select list box I have on the
form.
The other 4 descriptions made up from visible text in the other 4 list boxes
work perfectly. That is, the actual text visible in the list box is
transferred to OpenArgs on the report, even though, the Row Source for those
list boxes includes an ID number as Primary Key.
I guess what I'm getting is exactly what I should be getting given that
Access thinks of a Yes/No as -1/0.
But -1 doesn't make any sense to my client.
I'm not sure what you mean by "Could you do the conversion in a query, and
use the value in the query
instead of passing a value in OpenArgs?"
Running a query using the same syntax that I have as the Row Source,
SELECT DISTINCT tblCommission.Debtored FROM tblCommission;
actually produces Check boxes with ticks.
I'm using DISTINCT because I would otherwise get hundreds of Yes and
hundreds of No.

Could you suggest how I would go about designing a query to change the
display of OpenArgs.

Thanks,
Bob

Bob

One possibility might be to change what you pass. Where are you getting
the
value for OpenArgs? Could you do the conversion there, and pass a
"Yes"/"No" via OpenArgs?

Could you do the conversion in a query, and use the value in the query
instead of passing a value in OpenArgs?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Hi,
I am trying to get the OpenArgs on my report to display Yes or No instead
of -1 or 0.
I have an unbound listbox on a form with its Row Source property set to
SELECT DISTINCT tblCommission.Debtored FROM tblCommission;
The debtored field is a Yes/No field in the table tblCommission and

when
it

appears on the form it reads Yes or No.
I'm using a multi-select listbox instead of check boxes as there are 3
possible choices, Yes, No or Both
The code that runs from the OnClick event of a button is (in part)

which
is

borrowed mostly from Allen Browne.

With Me.lstDebtored
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column
strWhere5 = strWhere5 & strDelim5 & .ItemData(varItem) &
strDelim5 & ","
'Build up the description from the text in the visible
column.
strDescrip5 = strDescrip5 & """" & .Column(0, varItem) &
""", "

End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere5) - 1
If lngLen > 0 Then

strWhere5 = "[Debtored] IN (" & Left$(strWhere5, lngLen) & ")"
lngLen = Len(strDescrip5) - 2

If lngLen > 0 Then
strDescrip5 = "DEBTORED: " & Left$(strDescrip5, lngLen)
End If
End If

'and then at the end

strWhere = "(" & strWhere1 & ") AND (" & strWhere2 & ") AND (" &
strWhere3 &
") AND (" & strWhere4 & ") AND (" & strWhere5 & ")"

strDescrip = "(" & strDescrip1 & ") AND (" & strDescrip2 & ") AND (" &
strDescrip3 & ") AND (" & strDescrip4 & ") AND (" & strDescrip5 & ")"

DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip


In the header of my report I have =Report.OpenArgs.

If "No" is selected on the form, then strDescrip5 is displayed in
OpenArgs
on the report as "0"
If "Yes" is selected on the form, then strDescrip5 is displayed in OpenArgs
on the report as "-1"

I need to be able to display it as "No" or "Yes".

I have tried all sorts of ways such as
If strDescrip5 = "-1" Then
strDescrip5 = "Yes"
End If
If strDescrip5 = "0" Then
strDescrip5 = "No"
End If

but it doesn't make any difference.

Any ideas greatly appreciated.

Bob

 

[Bob]

Thanks Jeff,
I did misunderstand you.
What you say makes sense but its its seems a bit drastic as I'm managing to
create a report by simply building a string from items selected in the form.
I'm not having to bother with making tables or querying tables. I'll have a
go with your suggestion, though.
On the topic of OpenArgs, do you know of a way to format the output on the
report. For instance, I would like to change the font and also force a new
line at each change in the source of the OpenArg string.
strDescrip1................new line
strDescrip2................new line
etc
etc
strDescrip = "(" & strDescrip1 & ") AND (" & strDescrip2 & ") AND (" &

Bob.

Bob

I wasn't suggesting that you use a query to change the OpenArgs. I was
suggesting that you forget about the OpenArgs and use a query as the
source
for your report. In the query, you could add a field that "evaluates"
your -1/0 field and generates the text "Yes/No". For example:

Expr1: IIF([YourField]=0,"No", "Yes")

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Hi Jeff,

I'm getting the value for OpenArgs from strDescrip5 which is the description
of text in the visible column of the 5th multi-select list box I have on the
form.
The other 4 descriptions made up from visible text in the other 4 list boxes
work perfectly. That is, the actual text visible in the list box is
transferred to OpenArgs on the report, even though, the Row Source for those
list boxes includes an ID number as Primary Key.
I guess what I'm getting is exactly what I should be getting given that
Access thinks of a Yes/No as -1/0.
But -1 doesn't make any sense to my client.
I'm not sure what you mean by "Could you do the conversion in a query,
and
use the value in the query
instead of passing a value in OpenArgs?"
Running a query using the same syntax that I have as the Row Source,
SELECT DISTINCT tblCommission.Debtored FROM tblCommission;
actually produces Check boxes with ticks.
I'm using DISTINCT because I would otherwise get hundreds of Yes and
hundreds of No.

Could you suggest how I would go about designing a query to change the
display of OpenArgs.

Thanks,
Bob

message

Bob

One possibility might be to change what you pass. Where are you
getting
the
value for OpenArgs? Could you do the conversion there, and pass a
"Yes"/"No" via OpenArgs?

Could you do the conversion in a query, and use the value in the query
instead of passing a value in OpenArgs?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


Hi,
I am trying to get the OpenArgs on my report to display Yes or No instead
of -1 or 0.
I have an unbound listbox on a form with its Row Source property set
to
SELECT DISTINCT tblCommission.Debtored FROM tblCommission;
The debtored field is a Yes/No field in the table tblCommission and when
it
appears on the form it reads Yes or No.
I'm using a multi-select listbox instead of check boxes as there are 3
possible choices, Yes, No or Both
The code that runs from the OnClick event of a button is (in part) which
is
borrowed mostly from Allen Browne.

With Me.lstDebtored
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column
strWhere5 = strWhere5 & strDelim5 & .ItemData(varItem) &
strDelim5 & ","
'Build up the description from the text in the visible
column.
strDescrip5 = strDescrip5 & """" & .Column(0, varItem) &
""", "

End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere5) - 1
If lngLen > 0 Then

strWhere5 = "[Debtored] IN (" & Left$(strWhere5, lngLen) & ")"
lngLen = Len(strDescrip5) - 2

If lngLen > 0 Then
strDescrip5 = "DEBTORED: " & Left$(strDescrip5, lngLen)
End If
End If

'and then at the end

strWhere = "(" & strWhere1 & ") AND (" & strWhere2 & ") AND (" &
strWhere3
&
") AND (" & strWhere4 & ") AND (" & strWhere5 & ")"

strDescrip = "(" & strDescrip1 & ") AND (" & strDescrip2 & ") AND ("
&
strDescrip3 & ") AND (" & strDescrip4 & ") AND (" & strDescrip5 & ")"

DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip


In the header of my report I have =Report.OpenArgs.

If "No" is selected on the form, then strDescrip5 is displayed in
OpenArgs
on the report as "0"
If "Yes" is selected on the form, then strDescrip5 is displayed in
OpenArgs
on the report as "-1"

I need to be able to display it as "No" or "Yes".

I have tried all sorts of ways such as
If strDescrip5 = "-1" Then
strDescrip5 = "Yes"
End If
If strDescrip5 = "0" Then
strDescrip5 = "No"
End If

but it doesn't make any difference.

Any ideas greatly appreciated.

Bob

 

[Jeff Boyce]

The controls on your report should be able to have their Font property set.

If you are trying to force "new lines", maybe you have more than one detail
record. Are you looking at using the Detail section?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Thanks Jeff,
I did misunderstand you.
What you say makes sense but its its seems a bit drastic as I'm managing to
create a report by simply building a string from items selected in the form.
I'm not having to bother with making tables or querying tables. I'll have a
go with your suggestion, though.
On the topic of OpenArgs, do you know of a way to format the output on the
report. For instance, I would like to change the font and also force a new
line at each change in the source of the OpenArg string.
strDescrip1................new line
strDescrip2................new line
etc
etc
strDescrip = "(" & strDescrip1 & ") AND (" & strDescrip2 & ") AND (" &

Bob.

Bob

I wasn't suggesting that you use a query to change the OpenArgs. I was
suggesting that you forget about the OpenArgs and use a query as the
source
for your report. In the query, you could add a field that "evaluates"
your -1/0 field and generates the text "Yes/No". For example:

Expr1: IIF([YourField]=0,"No", "Yes")

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Hi Jeff,

I'm getting the value for OpenArgs from strDescrip5 which is the description
of text in the visible column of the 5th multi-select list box I have

on
the

form.
The other 4 descriptions made up from visible text in the other 4 list boxes
work perfectly. That is, the actual text visible in the list box is
transferred to OpenArgs on the report, even though, the Row Source for those
list boxes includes an ID number as Primary Key.
I guess what I'm getting is exactly what I should be getting given that
Access thinks of a Yes/No as -1/0.
But -1 doesn't make any sense to my client.
I'm not sure what you mean by "Could you do the conversion in a query,
and
use the value in the query
instead of passing a value in OpenArgs?"
Running a query using the same syntax that I have as the Row Source,
SELECT DISTINCT tblCommission.Debtored FROM tblCommission;
actually produces Check boxes with ticks.
I'm using DISTINCT because I would otherwise get hundreds of Yes and
hundreds of No.

Could you suggest how I would go about designing a query to change the
display of OpenArgs.

Thanks,
Bob

message
Bob

One possibility might be to change what you pass. Where are you
getting
the
value for OpenArgs? Could you do the conversion there, and pass a
"Yes"/"No" via OpenArgs?

Could you do the conversion in a query, and use the value in the query
instead of passing a value in OpenArgs?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


Hi,
I am trying to get the OpenArgs on my report to display Yes or No instead
of -1 or 0.
I have an unbound listbox on a form with its Row Source property set
to
SELECT DISTINCT tblCommission.Debtored FROM tblCommission;
The debtored field is a Yes/No field in the table tblCommission and when
it
appears on the form it reads Yes or No.
I'm using a multi-select listbox instead of check boxes as there are 3
possible choices, Yes, No or Both
The code that runs from the OnClick event of a button is (in part) which
is
borrowed mostly from Allen Browne.

With Me.lstDebtored
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column
strWhere5 = strWhere5 & strDelim5 &

..ItemData(varItem)
&

strDelim5 & ","
'Build up the description from the text in the visible
column.
strDescrip5 = strDescrip5 & """" & .Column(0,

varItem)
&

""", "

End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere5) - 1
If lngLen > 0 Then

strWhere5 = "[Debtored] IN (" & Left$(strWhere5, lngLen) & ")"
lngLen = Len(strDescrip5) - 2

If lngLen > 0 Then
strDescrip5 = "DEBTORED: " & Left$(strDescrip5, lngLen)
End If
End If

'and then at the end

strWhere = "(" & strWhere1 & ") AND (" & strWhere2 & ") AND (" &
strWhere3
&
") AND (" & strWhere4 & ") AND (" & strWhere5 & ")"

strDescrip = "(" & strDescrip1 & ") AND (" & strDescrip2 & ") AND ("
&
strDescrip3 & ") AND (" & strDescrip4 & ") AND (" & strDescrip5 & ")"

DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip


In the header of my report I have =Report.OpenArgs.

If "No" is selected on the form, then strDescrip5 is displayed in
OpenArgs
on the report as "0"
If "Yes" is selected on the form, then strDescrip5 is displayed in
OpenArgs
on the report as "-1"

I need to be able to display it as "No" or "Yes".

I have tried all sorts of ways such as
If strDescrip5 = "-1" Then
strDescrip5 = "Yes"
End If
If strDescrip5 = "0" Then
strDescrip5 = "No"
End If

but it doesn't make any difference.

Any ideas greatly appreciated.

Bob

 

[Bob]

Hi Jeff,
I think we have a misunderstanding here.
I'm talking about the OpenArgs feature where its possible to print the
arguments making up the data the report is displaying in the header section
of the report.
I get that by entering =Report.OpenArgs in a text box in the header of the
report.
I managed to change the font to Verdana and I just found a way to force new
lines. & vbCrLf & in the vba code.
Now if only I can lose the brackets I'd be happy, except that a Yes still
shows up as -1.

regards,
Bob Wickham

The controls on your report should be able to have their Font property
set.

If you are trying to force "new lines", maybe you have more than one
detail
record. Are you looking at using the Detail section?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Thanks Jeff,
I did misunderstand you.
What you say makes sense but its its seems a bit drastic as I'm managing to
create a report by simply building a string from items selected in the form.
I'm not having to bother with making tables or querying tables. I'll have a
go with your suggestion, though.
On the topic of OpenArgs, do you know of a way to format the output on
the
report. For instance, I would like to change the font and also force a
new
line at each change in the source of the OpenArg string.
strDescrip1................new line
strDescrip2................new line
etc
etc
strDescrip = "(" & strDescrip1 & ") AND (" & strDescrip2 & ") AND (" &

strDescrip3 & ") AND (" & strDescrip4 & ") AND (" & strDescrip5 & ")"

DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Bob.

message

Bob

I wasn't suggesting that you use a query to change the OpenArgs. I was
suggesting that you forget about the OpenArgs and use a query as the
source
for your report. In the query, you could add a field that "evaluates"
your -1/0 field and generates the text "Yes/No". For example:

Expr1: IIF([YourField]=0,"No", "Yes")

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


Hi Jeff,

I'm getting the value for OpenArgs from strDescrip5 which is the
description
of text in the visible column of the 5th multi-select list box I have on
the
form.
The other 4 descriptions made up from visible text in the other 4 list
boxes
work perfectly. That is, the actual text visible in the list box is
transferred to OpenArgs on the report, even though, the Row Source for
those
list boxes includes an ID number as Primary Key.
I guess what I'm getting is exactly what I should be getting given
that
Access thinks of a Yes/No as -1/0.
But -1 doesn't make any sense to my client.
I'm not sure what you mean by "Could you do the conversion in a query,
and
use the value in the query
instead of passing a value in OpenArgs?"
Running a query using the same syntax that I have as the Row Source,
SELECT DISTINCT tblCommission.Debtored FROM tblCommission;
actually produces Check boxes with ticks.
I'm using DISTINCT because I would otherwise get hundreds of Yes and
hundreds of No.

Could you suggest how I would go about designing a query to change the
display of OpenArgs.

Thanks,
Bob

message
Bob

One possibility might be to change what you pass. Where are you
getting
the
value for OpenArgs? Could you do the conversion there, and pass a
"Yes"/"No" via OpenArgs?

Could you do the conversion in a query, and use the value in the query
instead of passing a value in OpenArgs?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


Hi,
I am trying to get the OpenArgs on my report to display Yes or No
instead
of -1 or 0.
I have an unbound listbox on a form with its Row Source property
set
to
SELECT DISTINCT tblCommission.Debtored FROM tblCommission;
The debtored field is a Yes/No field in the table tblCommission and
when
it
appears on the form it reads Yes or No.
I'm using a multi-select listbox instead of check boxes as there
are 3
possible choices, Yes, No or Both
The code that runs from the OnClick event of a button is (in part)
which
is
borrowed mostly from Allen Browne.

With Me.lstDebtored
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column
strWhere5 = strWhere5 & strDelim5 & .ItemData(varItem)
&
strDelim5 & ","
'Build up the description from the text in the visible
column.
strDescrip5 = strDescrip5 & """" & .Column(0, varItem)
&
""", "

End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere5) - 1
If lngLen > 0 Then

strWhere5 = "[Debtored] IN (" & Left$(strWhere5, lngLen) & ")"
lngLen = Len(strDescrip5) - 2

If lngLen > 0 Then
strDescrip5 = "DEBTORED: " & Left$(strDescrip5, lngLen)
End If
End If

'and then at the end

strWhere = "(" & strWhere1 & ") AND (" & strWhere2 & ") AND (" &
strWhere3
&
") AND (" & strWhere4 & ") AND (" & strWhere5 & ")"

strDescrip = "(" & strDescrip1 & ") AND (" & strDescrip2 & ") AND ("
&
strDescrip3 & ") AND (" & strDescrip4 & ") AND (" & strDescrip5 & ")"

DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip


In the header of my report I have =Report.OpenArgs.

If "No" is selected on the form, then strDescrip5 is displayed in
OpenArgs
on the report as "0"
If "Yes" is selected on the form, then strDescrip5 is displayed in
OpenArgs
on the report as "-1"

I need to be able to display it as "No" or "Yes".

I have tried all sorts of ways such as
If strDescrip5 = "-1" Then
strDescrip5 = "Yes"
End If
If strDescrip5 = "0" Then
strDescrip5 = "No"
End If

but it doesn't make any difference.

Any ideas greatly appreciated.

Bob

 

[Jeff Boyce]

Bob

I didn't realize you were still trying to use OpenArgs to pass in a complex
string. My recommendation would still be to do the complex stuff in an
underlying query and use that as the report's source.

But hey! If you have OpenArgs doing the job for you...


--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Hi Jeff,
I think we have a misunderstanding here.
I'm talking about the OpenArgs feature where its possible to print the
arguments making up the data the report is displaying in the header section
of the report.
I get that by entering =Report.OpenArgs in a text box in the header of the
report.
I managed to change the font to Verdana and I just found a way to force new
lines. & vbCrLf & in the vba code.
Now if only I can lose the brackets I'd be happy, except that a Yes still
shows up as -1.

regards,
Bob Wickham

The controls on your report should be able to have their Font property
set.

If you are trying to force "new lines", maybe you have more than one
detail
record. Are you looking at using the Detail section?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Thanks Jeff,
I did misunderstand you.
What you say makes sense but its its seems a bit drastic as I'm

managing
to

create a report by simply building a string from items selected in the form.
I'm not having to bother with making tables or querying tables. I'll

have
a

go with your suggestion, though.
On the topic of OpenArgs, do you know of a way to format the output on
the
report. For instance, I would like to change the font and also force a
new
line at each change in the source of the OpenArg string.
strDescrip1................new line
strDescrip2................new line
etc
etc
strDescrip = "(" & strDescrip1 & ") AND (" & strDescrip2 & ") AND (" &
strDescrip3 & ") AND (" & strDescrip4 & ") AND (" & strDescrip5 & ")"

DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip


Bob.

message
Bob

I wasn't suggesting that you use a query to change the OpenArgs. I was
suggesting that you forget about the OpenArgs and use a query as the
source
for your report. In the query, you could add a field that "evaluates"
your -1/0 field and generates the text "Yes/No". For example:

Expr1: IIF([YourField]=0,"No", "Yes")

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


Hi Jeff,

I'm getting the value for OpenArgs from strDescrip5 which is the
description
of text in the visible column of the 5th multi-select list box I

have
on

the
form.
The other 4 descriptions made up from visible text in the other 4 list
boxes
work perfectly. That is, the actual text visible in the list box is
transferred to OpenArgs on the report, even though, the Row Source for
those
list boxes includes an ID number as Primary Key.
I guess what I'm getting is exactly what I should be getting given
that
Access thinks of a Yes/No as -1/0.
But -1 doesn't make any sense to my client.
I'm not sure what you mean by "Could you do the conversion in a query,
and
use the value in the query
instead of passing a value in OpenArgs?"
Running a query using the same syntax that I have as the Row Source,
SELECT DISTINCT tblCommission.Debtored FROM tblCommission;
actually produces Check boxes with ticks.
I'm using DISTINCT because I would otherwise get hundreds of Yes and
hundreds of No.

Could you suggest how I would go about designing a query to change the
display of OpenArgs.

Thanks,
Bob

message
Bob

One possibility might be to change what you pass. Where are you
getting
the
value for OpenArgs? Could you do the conversion there, and pass a
"Yes"/"No" via OpenArgs?

Could you do the conversion in a query, and use the value in the query
instead of passing a value in OpenArgs?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


Hi,
I am trying to get the OpenArgs on my report to display Yes or No
instead
of -1 or 0.
I have an unbound listbox on a form with its Row Source property
set
to
SELECT DISTINCT tblCommission.Debtored FROM tblCommission;
The debtored field is a Yes/No field in the table tblCommission and
when
it
appears on the form it reads Yes or No.
I'm using a multi-select listbox instead of check boxes as there
are 3
possible choices, Yes, No or Both
The code that runs from the OnClick event of a button is (in part)
which
is
borrowed mostly from Allen Browne.

With Me.lstDebtored
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column
strWhere5 = strWhere5 & strDelim5 & .ItemData(varItem)
&
strDelim5 & ","
'Build up the description from the text in the visible
column.
strDescrip5 = strDescrip5 & """" & .Column(0, varItem)
&
""", "

End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere5) - 1
If lngLen > 0 Then

strWhere5 = "[Debtored] IN (" & Left$(strWhere5, lngLen)

&
")"

lngLen = Len(strDescrip5) - 2

If lngLen > 0 Then
strDescrip5 = "DEBTORED: " & Left$(strDescrip5, lngLen)
End If
End If

'and then at the end

strWhere = "(" & strWhere1 & ") AND (" & strWhere2 & ") AND (" &
strWhere3
&
") AND (" & strWhere4 & ") AND (" & strWhere5 & ")"

strDescrip = "(" & strDescrip1 & ") AND (" & strDescrip2 & ")

AND
("

&
strDescrip3 & ") AND (" & strDescrip4 & ") AND (" & strDescrip5 & ")"

DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip


In the header of my report I have =Report.OpenArgs.

If "No" is selected on the form, then strDescrip5 is displayed in
OpenArgs
on the report as "0"
If "Yes" is selected on the form, then strDescrip5 is displayed in
OpenArgs
on the report as "-1"

I need to be able to display it as "No" or "Yes".

I have tried all sorts of ways such as
If strDescrip5 = "-1" Then
strDescrip5 = "Yes"
End If
If strDescrip5 = "0" Then
strDescrip5 = "No"
End If

but it doesn't make any difference.

Any ideas greatly appreciated.

Bob