Change data within a query

  • Thread starter Thread starter Al Mackay
  • Start date Start date
A

Al Mackay

I have a set of data within an Access table that holds the following
data:
0 - 0 represents Open
1 - 1 represents Closed

Whenever I run a query obviously the results just come out as either
1's or 0's.

Would it be possible @ all within the query to amend how the data is
displayed (e.g. within field data, display 0 as Open and 1 as
Closed?).

Really appreciate any advise you can offer on this. Unfortunately I
have not got access to amend the table properties either as this is an
Access database linking to a SQL based table.

Many Thanks, Al ([email protected])
 
You could use this as your field, just cut and paste it
in...

Open: iif
((yourfieldnamewiththedataopen/closed=1),"Closed","Open")
 
Hi Al,

You can use the IIf function in a query.

Say the table name is MyTable and the field you are selecting is MyField.
Then you use this
SELECT ........... , IIf(MyTable.MyField = 0, "Open", "Closed") AS Status,
.......
This will work as long as the field contains either 0 or 1. If it can
contain Null, you need to include a check for Null in the clause.

If you are unfamiliar with writing queries in SQL you can create the query
in design view (QBE) then change to SQL view and modify it there. I ususally
do theat anyway to reduce the chance of typos.

Ragnar
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use the IIf() function in the query: E.g.:

SELECT IIf(Status=0,"Open","Close") As CurrentStatus
FROM TableName
WHERE < criteria >

Use your column/table names.

The above assumes that Status can only be 0 or 1, not NULL. If it can
be NULL use the following function:

IIf(IsNull(Status),"Unknown",IIf(Status=0,"Open","Close"))

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQI680oechKqOuFEgEQLKAwCg73b/w73ZE/qRxfxAVU6HY8AsPzQAnipu
6FSeEJ27zPZJhvhA0ZBQtBlS
=DV7M
-----END PGP SIGNATURE-----
 
I have a set of data within an Access table that holds the following
data:
0 - 0 represents Open
1 - 1 represents Closed

Whenever I run a query obviously the results just come out as either
1's or 0's.

Would it be possible @ all within the query to amend how the data is
displayed (e.g. within field data, display 0 as Open and 1 as
Closed?).
Click to expand...

There's a sneaky way to do this using the Format property: number
formats allow you to specify FOUR different formats for positive,
negative, zero and NULL values. Set the Format property of the textbox
on the Form or Report (or, if you're unwisely displaying data in a
query datasheet, the format property of the field) to

"Open";"Ooops!";"Closed";"(Unknown)"
 
Back
Top