CDate ...Between.....Data Type Mismatch

[dridgeway]

My data field "pdate" is a NON-DATE field in a linked table that I cannot change. This query works PERFECTLY unless there is a blank "pdate" field. Can anyone help?


PARAMETERS [Start] DateTime, [End] DateTime;
SELECT CDate(Nz([pDate],'01/01/1900')) AS PICKDATE
FROM dpl
WHERE (((CDate(Nz([pDate],'01/01/1900'))) Between CDate([Start]) And CDate([End])));

 

[cmmrfrds]

The default for the NZ looks okay, so the field must be blank. The IIF might be a little more flexible.

(((CDate(iif([pDate] is NULL or [pDate] = "" or [pDate] = " ",'01/01/1900',pDate)))

 

[PHV]

PARAMETERS [Start] DateTime, [End] DateTime;
SELECT IIf(Trim(pDate & "")='',#01/01/1900#,CDate(pDate)) AS PICKDATE
FROM dpl
WHERE IIf(Trim(pDate & "")='',#01/01/1900#,CDate(pDate)) Between [Start] And [End];


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