Working with Dates in a report 1

[xlbo]

Hello all,
currently working on a report where within any given group, a certain date may or may not exist.

Have taken care of that in the SQL using:

ISNULL(dbo.ScheduleOfDilapidations.SurveyDate, '') AS SurveyDate

However, in the report itself, as the field is formatted as dd/MM/yyyy, these are being displayed as 01/01/1900. Now, this is not entirely unexpected so I tried putting some IIF statements together to coerce a blank field instead but nothing seems to work....

I have tried amending the format of the field:
=IIF(LEN(Fields.SurveyDate.Value.tostring) < 1 ,"General","dd/MM/yyyy")

and the colour of the field
=IIF(Fields.SurveyDate.Value) < 1 ,"White","Black")

Neither of these approaches seems to work and I cannot figure out if it is because I need to convert the date differently 1st or whether anything in a dd/MM/yyyy format will show as 01/01/1900 if there is no data or even if I should handle it differently in the SQL

Any suggestions would be welcomed

Rgds, Geoff

A file that big? It might be very useful. But now it is gone

Please read FAQ222-2244 before you ask a question

 

[Catadmin]

Have you tried using the isnothing() function yet?

IIF(isnothing(Fields.SurveyDate.Value) ,"White","Black"))

and

=IIF(isnothing(Fields.SurveyDate.Value),"General","dd/MM/yyyy")

THe first one, for colors, I've used plenty of times, so I know it works. The second one, not so sure about.




Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[xlbo]

cheers - will try that. I'm a bit of a noob at .NET. I'm pretty experienced at VBA and it all looks kinda similar but there are enough differences to throw me off !!

Thanks again

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[Catadmin]

Anytime. You've certainly helped me enough in other forums. @=)



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[xlbo]

=> * for that Catadmin - did the trick


Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question