#Error problem in a query

[simon4amiee]

DischargeDate: CDate(Format([disdate],"dd/mm/yyyy"))

I use the above formula, but when the [disdate] is blank I get the #Error message, ideally I would like this to be blank if its possible???

Any thoughts

Kind Regards

 

[pbaldy]

Try

IIf(IsNull(disdate), Null, CDate(Format([disdate],"dd/mm/yyyy")))

 

[pr2-eugin]

Just wondering, what is the use of the Format function there? If you are using a CDate function, ther Format is redundant anyway.

Anyway, instead of using IIF to check for Null, and provided the Format is useless there, you can simply use - CVDate([disdate])

 

[pbaldy]

Learn something new every day. I had not heard of CVDate() before.

 

[pr2-eugin]

Learn something new every day.

That is true Paul.

I had not heard of CVDate() before.

Well CVDate is the same syntax as that of CDate, except for the fact it can handle Null values. Also the fact that the return type is yet again a variant type not the Date as opposed to the CDate function.

 

[pbaldy]

Yes, I looked it up. Supposedly exists for backwards compatibility.

 

[simon4amiee]

Ok those worked a treat and many many thanks for the support on here.

LinkCheck: IIf([DateOfDevice]>=[AdmissionDate] And [DateOfDevice]<=[DischargeDate],"POSSIBLELINK","IGNORE")

Based on the same query I now have 3 Date Fields all formatted and identical, however when I put in some criteria to just show me the "POSSIBLELINK" I get what looks like a parameter pop up and the query doesnt work. Im stupid I know but its really bugging me.

Reading around its something to do with calculating date fields???

 

[pr2-eugin]

Please show the entire query.

 

[pbaldy]

It probably has to do with using aliased fields (your calculated fields) in the WHERE clause of the query. Can't do that because of the order in which the clauses are evaluated. You likely need to repeat the calculation there, or base a second query on this one.