Link to home
Start Free TrialLog in
Avatar of deNZity
deNZity

asked on

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
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

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?

Avatar of deNZity
deNZity

ASKER

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,'')
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of deNZity

ASKER

Great stuff, thanks Angellll