CDate Help

[Mike555]

This query expression produces the error "Data Type Mismatch in Query Expression".
FirstDayofWeek: CDate(IIf(IsNull([ShipDate]),"NoShipDate",[ShipDate]-Format([ShipDate],"w",2)))

It appears that it doesn't like the CDate function, which is trying to convert this text to date format.

How can I fix this? Thanks.

--
Mike

Why make it simple and efficient when it can be complex and wonderful?

 

[PHV]

FirstDayofWeek: CDate(IIf(IsNull([ShipDate]),"NoShipDate",[ShipDate]-Format([ShipDate],"w",2)))
The CDate function doesn't like "NoShipDate" as argument ...
You may try this (typed, untested):
FirstDayofWeek: IIf(IsNull([ShipDate]),"NoShipDate",Format([ShipDate]-Format([ShipDate],"w",2),"mm/dd/yyyy"))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[rac2]

CDate(IIf(IsNull([ShipDate]),"NoShipDate",[ShipDate]-Format([ShipDate],"w",2)))

Suppose ShipDate is null, then the IIf returns "NoShipDate", which might not be a valid date string.

I dont know exactly what Format([ShipDate],"w",2) does but it might be returning a string which must then be converted to a number in order to do the substraction.

And again I dont know what Access might do with an expression involving the difference of dates; myself I would use the DateDiff(datepart, date1, date2) for that.

Which leads to the thought that the subtraction must yield a number, which is going to be a bit tricky to convert to a date.

You have completely lost me with that FirstDayofWeek: notation.

What result do you want?

 

[Mike555]

You are both correct about the "NoShipDate" causing the invalid string. I've simply changed "NoShipDate" to null "".

--
Mike

Not until I executed my first DLookup function did I truly appreciate the power of the relational database.