Return String from Boolean Field

  • Thread starter Thread starter Dragon
  • Start date Start date
D

Dragon

Hi,

I have the following query collecting data from a table where 'Complete'
field is a yes/no field. I am exporting this data for a PDF merge and what I
need is the Strings 'Yes' or 'Off' from the query results for this field
instead of a 0/1 value.

Any ideas?

Thank you.

SELECT Businesses.BusinessName AS Business, Businesses.Address AS Address,
Businesses.City AS City, Businesses.State AS State,
Businesses.Zip+Businesses.ZipPlus AS Zip, Businesses.DocumentNo,
Businesses.Complete
FROM Businesses
WHERE ((([Businesses].[Zip]+[Businesses].[ZipPlus])=99056));
 
Hi,

I have the following query collecting data from a table where 'Complete'
field is a yes/no field. I am exporting this data for a PDF merge and what I
need is the Strings 'Yes' or 'Off' from the query results for this field
instead of a 0/1 value.

Any ideas?

Thank you.

SELECT Businesses.BusinessName AS Business, Businesses.Address AS Address,
Businesses.City AS City, Businesses.State AS State,
Businesses.Zip+Businesses.ZipPlus AS Zip, Businesses.DocumentNo,
Businesses.Complete
FROM Businesses
WHERE ((([Businesses].[Zip]+[Businesses].[ZipPlus])=99056));
Click to expand...

1) A Yes/No Check box field does not return a value of 0 or 1, it
returns a value of 0 or -1.


2) You asked for 0/1 Yes or Off? not 1/0 Yes or No?
Change the SQL to:

SELECT Businesses.BusinessName AS Business, Businesses.Address AS
Address,
Businesses.City AS City, Businesses.State AS State,
Businesses.Zip+Businesses.ZipPlus AS Zip, Businesses.DocumentNo,
IIf(Businesses.Complete = -1,"Off","Yes" as Completed
FROM Businesses
WHERE ((([Businesses].[Zip]+[Businesses].[ZipPlus])=99056));

Then use the [Completed] field in the export.
 
Great. Thank you.

Yes, I did need 'Yes' and 'Off' :-)


fredg said:
Hi,

I have the following query collecting data from a table where 'Complete'
field is a yes/no field. I am exporting this data for a PDF merge and what I
need is the Strings 'Yes' or 'Off' from the query results for this field
instead of a 0/1 value.

Any ideas?

Thank you.

SELECT Businesses.BusinessName AS Business, Businesses.Address AS Address,
Businesses.City AS City, Businesses.State AS State,
Businesses.Zip+Businesses.ZipPlus AS Zip, Businesses.DocumentNo,
Businesses.Complete
FROM Businesses
WHERE ((([Businesses].[Zip]+[Businesses].[ZipPlus])=99056));
Click to expand...

1) A Yes/No Check box field does not return a value of 0 or 1, it
returns a value of 0 or -1.


2) You asked for 0/1 Yes or Off? not 1/0 Yes or No?
Change the SQL to:

SELECT Businesses.BusinessName AS Business, Businesses.Address AS
Address,
Businesses.City AS City, Businesses.State AS State,
Businesses.Zip+Businesses.ZipPlus AS Zip, Businesses.DocumentNo,
IIf(Businesses.Complete = -1,"Off","Yes" as Completed
FROM Businesses
WHERE ((([Businesses].[Zip]+[Businesses].[ZipPlus])=99056));

Then use the [Completed] field in the export.
Click to expand...
 
Back
Top