Converting True False values to Yes / No on report 1

[drbtodd1971]

I have several reports where the Has claim been settled field is set to either true or false, I would like to be able to display this as Yes or No on the report. How can this be achieved, if at all?

I also have a problem with dates which are being stored in two variables @startdate and @enddate which are in the format yyyy/mon/dd, I would like to display them in the Report title as dd mon yyyy.

 

[SgtJarrow]

For the Yes/No......Make sure the HasClaim field is on the details section of the report. Set the visible property of this field to False. Add another unbound textbox control on the report. Using the OnFormat event of the details section, put:

Me.TextboxName = IIF(Me.HasClaimFieldName, "Yes", "No")

For the date fields, I would fix these in the query that populates the report. In the query field that has the date you wish to work with, use:

FormattedDate: Format([DateFieldName], "dd mmm yyyy")

You may then need to tweak the report to use this FormattedDate instead of the DateFieldName if the report is already created as this is the "name" of the field that will be passed to the report instead of the true field name.

Any problems/questions, please ask away.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[drbtodd1971]

Hi,
Thanks for that I've got the first part working, although now I've got a problem where there are null values. I've tried writing code using IsNull and Hasdata but haven't been successful. Is there a way of some sort of decode statement to convert null values to false?

 

[dhookom]

To change true/false to yes/no, you should only have to modify the Format property of your text box:
Format: Yes/No

The same should be true for displaying your dates. If they are actually date/time values, you should be able to set the format of your text box:
Format: dd mon yyyy

I don't usually provide any formatting in my queries. I leave this for the Format property of controls on forms and reports.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[dhookom]

oops....
the date format should have been
Format: Format: dd mmm yyyy


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[SgtJarrow]

WOW....Duane, I did not know that trick for true/false to yes/no. Star for you. The formatting thing I think is mostly preference where you do it.

drbtodd1971,

If you keep with what I provided and don't try Duane's excellent suggestions try the following for the IIF:

IIF(Nz(Me.HasClaimFieldName, False), "Yes", "No")

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[drbtodd1971]

Thanks guys for the suggestions, I've now got it working as I want. I went with the Yes/No Format option in the end as there was less coding, but both ways worked.

I've sorted out the date problem using a Format before the report is generated.