Report returning 01/01/1900 in field when date value is null

I have a query that provides data for a report one of the fields is a date type and will on occasion be empty, however when that is the case sql server returns the value '01/01/1900' I want the field to display nothing if there is no value
I tried  =iif(me.Value = 01/01/1900, " ","me.Value") without success
in the query I tried   ISNULL(sub.Date_Returned,'')as Date_Returned also without succes
deNZityAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what about this sql server expression:

CASE WHEN sub.Date_returned < convert(datetime,  '1901-01-01' , 120) then NULL ELSE sub.date_returned end date_returned
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
empty and NULL are not the same thing, especially not for date type...
now, with IIF() in the query, I assume you run the query on a linked table?
can you clarify?

0
 
deNZityAuthor Commented:
hi thanks for reply.

The iif() is in the report, We use reporting services so in the textbox that displays the date I created an
expression =iif(me.Value = 01/01/1900, " ","me.Value")

in the actual query that provides the datasource I used ISNULL(sub.Date_Returned,'')
0
 
deNZityAuthor Commented:
Great stuff, thanks Angellll
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.