Append union query to table with null date value

[DrSmyth]

Hi, i'm appending a union query to another table, one part of the union query reports on a date field so will have a date value, the other part won't does not report on this field sop the value will always be null.

The problem i've got is with the part of the union query with no dates. I've used this line of SQL

 ,"" AS DateAllocated,

to generate a null date value but it's causing the row to be thrown out of the source table as it is not coming through as a date.

Does anybody know how i can create a null date value using SQL?

 

[Remou]

I used:
,Null,
Instead of:
,"" AS DateAllocated,
and it seemed to work.

 

[DrSmyth]

So that would be

null as DateAllocated

I'll give it a go, cheers for the response

 

[SeeThru]

is not the same as null - "" is an empty length string, and will come up as not null.

Weirdly, a custom function set to return a Date value will return 0 rather than null if it is not set. The 0 will translate to 01/01/1899 !

 

[DrSmyth]

Cheers gents, works like a dream...