String instead of count number

  • Thread starter Thread starter Ralf
  • Start date Start date
R

Ralf

Hi,

I use a count function in my SELECT query. Now, this query will always
return 1 or 0. But instead of a number I would like to receive a string, like
complete or incomplete (1 or 0). Is that possible?

The result of the query needs to be shown in a ListBox.

Thank you,
Ralf
 
Hi Ralf

Try this:

SELECT field1, IIf(Count([IngredientID])=1,"Complete","Incomplete") AS
Status
FROM table
GROUP BY field 1;

If this doesn't make sense, post your existing sql and I will try to convert
it for you.

Regards
Sandy

AA Absolute Access, ACT - Australia
 
Hi Sandy,

Is your query SQL standard conform? At a later stage, I have to migrate my
access database to SQL Server or Oracle. So I was wondering if "iif" would
not cause an issue?!

Thank you,
Ralf

Sandy Hayman said:
Hi Ralf

Try this:

SELECT field1, IIf(Count([IngredientID])=1,"Complete","Incomplete") AS
Status
FROM table
GROUP BY field 1;

If this doesn't make sense, post your existing sql and I will try to convert
it for you.

Regards
Sandy

AA Absolute Access, ACT - Australia


Ralf said:
Hi,

I use a count function in my SELECT query. Now, this query will always
return 1 or 0. But instead of a number I would like to receive a string,
like
complete or incomplete (1 or 0). Is that possible?

The result of the query needs to be shown in a ListBox.

Thank you,
Ralf
Click to expand...
Click to expand...
 
NO, that is not standard SQL - it is JET (the native db engine for
Access) SQL. In MS SQL Server, you would use a case statement.

On the other hand if you migrate your data to MS SQL and link to the
tables, the SQL statement with the IIF will be interpreted by the ODBC
driver and give you the same results.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hi Sandy,

Is your query SQL standard conform? At a later stage, I have to migrate my
access database to SQL Server or Oracle. So I was wondering if "iif" would
not cause an issue?!

Thank you,
Ralf

Sandy Hayman said:
Hi Ralf

Try this:

SELECT field1, IIf(Count([IngredientID])=1,"Complete","Incomplete") AS
Status
FROM table
GROUP BY field 1;

If this doesn't make sense, post your existing sql and I will try to convert
it for you.

Regards
Sandy

AA Absolute Access, ACT - Australia


Ralf said:
Hi,

I use a count function in my SELECT query. Now, this query will always
return 1 or 0. But instead of a number I would like to receive a string,
like
complete or incomplete (1 or 0). Is that possible?

The result of the query needs to be shown in a ListBox.

Thank you,
Ralf
Click to expand...
Click to expand...
Click to expand...
 
Back
Top