openargs

B

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
 
J

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/
 
B

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

Jeff Boyce said:
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 said:
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
Click to expand...
Click to expand...
 
J

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/


Bob Wickham said:
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

Jeff Boyce said:
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 said:
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
Click to expand...
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)
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
B

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.

Jeff Boyce said:
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/


Bob Wickham said:
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Crosstab Query Building Report 2
open report based on several mulit select list boxes causes error7769 5
Create Report On the Fly 1
Add code to handle All jobs FROm dropdown list if None selected- 5
select child records from listbox and generate a report 0
CrossPosat - Multiple Criteria in a List Box 2
Works, But Doesn't Filter 1
Error 13 - Type Mismatch 5

Top