generate a null value in a sql query

[areejan2000]

u might find this question strange but

how to generate a null value in a sql query (Ms Access)

the Table1 contains only 3 fields
name
age
carno

how to get recordset with field Dummyfield get null value so
that using ado

-set MSHflexgrid.datasource=adoRs

will create additional col with balnk .zero is not acceptable.


name age Carno DummyField
n1 27 232323 (Null)
n2 44 534fq2 (Null)

is there a query


thank you

 

[John Spencer]

Have you tried a query like the following?

SELECT [Name], [Age], [CarNo], NULL as Dummyfield
FROM YourTable

 

[Gary Walter]

PMFBI

just using NULL would produce
a Dummyfield of type Binary!

If you want a Null Dummyfield
of type Long, use

IIF(True,Null,0) AS Dummyfield

If you want a Null Dummyfield
of type Text, use

IIF(True,Null,"") AS Dummyfield

they will always return Null,
but the IIF will cast the Nulls
to type of third parameter.


Apolgies again for butting in

gary

Have you tried a query like the following?

SELECT [Name], [Age], [CarNo], NULL as Dummyfield
FROM YourTable

u might find this question strange but

how to generate a null value in a sql query (Ms Access)

the Table1 contains only 3 fields
name
age
carno

how to get recordset with field Dummyfield get null value so
that using ado

-set MSHflexgrid.datasource=adoRs

will create additional col with balnk .zero is not acceptable.

name age Carno DummyField
n1 27 232323 (Null)
n2 44 534fq2 (Null)

is there a query

thank you

 

[areejan2000]

thank you John and gary

you guys make life lot more easier

 

[areejan2000]

thank you John and gary

you guys make life lot more easier

SELECT [Name], [Age], [CarNo], IIF(True,Null,'' ) as Dummyfield

FROM YourTable.

 

[areejan2000]

here is the next question

when retreving Currency field (Price) into mshflexgrid ,
output is

500.0000


so i used

format(Price ,'#######0.00') AS [Price]

which
gives error Circular refernce caused by alias 'Price' in query
definition`s select list
i cant use any other aliases for Price

thank you.

 

[areejan2000]

here is the next question

when retreving Currency field (Price) into mshflexgrid ,
output is

500.0000


so i used

format(Price ,'#######0.00') AS [Price]

which
gives error Circular refernce caused by alias 'Price' in query
definition`s select list
i cant use any other aliases for Price

thank you.

 

[John Spencer]

Good point, I thought the table was already created and the OP just wanted to
populate the table. In that case, there should be no reason to use your method.
At least as far as I can tell.

PMFBI

just using NULL would produce
a Dummyfield of type Binary!

If you want a Null Dummyfield
of type Long, use

IIF(True,Null,0) AS Dummyfield

If you want a Null Dummyfield
of type Text, use

IIF(True,Null,"") AS Dummyfield

they will always return Null,
but the IIF will cast the Nulls
to type of third parameter.

Apolgies again for butting in

gary

Have you tried a query like the following?

SELECT [Name], [Age], [CarNo], NULL as Dummyfield
FROM YourTable

u might find this question strange but

how to generate a null value in a sql query (Ms Access)

the Table1 contains only 3 fields
name
age
carno

how to get recordset with field Dummyfield get null value so
that using ado

-set MSHflexgrid.datasource=adoRs

will create additional col with balnk .zero is not acceptable.

name age Carno DummyField
n1 27 232323 (Null)
n2 44 534fq2 (Null)

is there a query

thank you

 

[areejan2000]

well i discovered this

format(Price ,'#######0.00') AS [Price]

use tablename.price


ie
format(tablename.Price ,'#######0.00') AS [Price]

and the error was gone.