Access 1/0 conversion to yes/no

[MIGhunter]

I use Access as my Database for a Coldfusion page. I want to use the yes/no tickbox function for somplicity. However, I want the output display to be yes/no for my viewers. How can I do this. I tried doing a search and the only solution I can find is from a pay site. Can someone help me out please? Thanks.

 

[coco86]

Try putting a textbox control on the form and setting the control source to:

=IIf([FieldName],"Yes","No")

-Coco

 

[coco86]

Sorry, didn't notice you were using coldfusion...thought this was an access report. Still, the coding will probably be different (I don't use Coldfusion) but the approach would probably work. I've used similar techniques in ASP.

-Coco

 

[MIGhunter]

hmm, can you dumb it down a few notches. I really don't know that much about Access other than normal data input. Exactly where do I put that expression and do I need to alter it so that actually works?

 

[sxschech]

You can place the statement in a query, form or report.

in a query
(Design View):
DisplayName:IIf([FieldName],"Yes","No")

(SQL View):
IIf([FieldName],"Yes","No") AS DisplayName

On form or report:
Add a text box and enter =IIf([FieldName],"Yes","No") into the Control Source of the properties window.

You would, of course change the FieldName to the actual name of the field containing the 1's and 0's and you would change DisplayName to the name you want users to see when viewing the query.

 

[coco86]

sxschech brought up a good point. If you use a query as the datasource for your page, you can have it return yes/no instead of -1/0 (using the technique he described) and you won't have to do any special formatting on your page. "DisplayName" (or whatever you change it to) would be the new fieldname for your page instead of the fieldname from the table.

Do you know how to create an Access query?

-Coco

 

[MIGhunter]

Usually I just make a .mdb file. I am not sure if Coldfusion will use a query.

 

[coco86]

You can have a query inside an MDB file but I don't know how coldfusion works so I don't know how to specify to use a query instead of a table.

Do you include a table name inside your connection string? If so, you can probably use the query name instead.


-Coco

 

[MIGhunter]

Well, a typical CF page accesses the database with:

<cfset CurrentPage=GetFileFromPath(GetTemplatePath())>
<cfparam name="PageNum_DynamisMembers" default="1">
<cfquery name="DynamisMembers" datasource="DynamisPoints">
SELECT *
FROM DynamisPoints
ORDER BY Job ASC</cfquery>

 

[coco86]

Well, I don't know if this will work or not but try

SELECT *, IIf([FieldName],"Yes","No") as DisplayName
FROM DynamisPoints
ORDER BY Job ASC

-Coco