Access Cdate gives #Error

Great.. Thank you Gustav, that worked for me.

You are welcome!

Cvdate is obsolete.  Should not be used.  Just use cdate(nz([field])).    You should almost always use nz when using fields in functions or a null value will cause the call to fail.   If the field is empty it's null. Not an empty string. They're different sadly.

• CVDate is not obsolete.

• CVDate can perfectly well be used. As demonstrated.

• Your expression will not return Null but #00:00:00#, which is not what was asked for.

• There is no general rule for using Nz to not cause a fail for Null; that depends on the function.

• There is nothing "sad" about Null and a zero length string not being the same. That's by design.

Yes it is. Read about it. And no need to flame on here pal.  Th thing about null vs empty causes no end of problems in vba esp access.

Check among others. https://www.oreilly.com/library/view/vb-vba/1565923588/1565923588_ch07-340-fm2xml.html

But whatever works. For now.

You are mislead.

That link is nearly a copy-paste from the official documentation:
Type Conversion Functions
What it says is, that when dealing with converting to data type Date, you should use CDate which, of course, is correct. However, that is not the case here - the converted output is to fill a textbox, not a Date variable.

Th(e) thing about null vs empty causes no end of problems in vba

Not at all. That only happens if you don't know what you are doing.

cdate(nz([field])) returns 00:00:00 wherever is null

I stand corrected re cvdate. But losing the attitude about my experience with null vs empty reversible would make for a better day for you. Microsoft's answer and your as "working as designed" means it's working as coded. And usually a cop out.  
But the arrogant approach doesn't help

Okay, so how can i correct it. As i mentioned, your code gives me 00:00:00 wherever there is blank. How can convert text date to proper date, blank places to null in the date field

Cheers

There is nothing to correct, but if you do prefer CDate, you can use this expression:

=IIf(IsNull([QryRprtDtl].[BsDate]),Null,CDate(Nz([QryRprtDtl].[BsDate])))

Select allOpen in new window

But the arrogant approach doesn't help

That was not my intention. Sorry about that. What I mean is, that it - in VBA - is very easy to distinguish between Null and a zls, and I've never experienced a problem with it.
That said, I prefer the way C# handle this - including having nullable types - but that's another discussion.

ok - here's an example - see uploaded test database.  Table1 design view will show comments on the fields.
See also simple test subroutine in module one.  
does this explain what you need - be happy to adjust to suit your requirements.
testtexttodate.accdb

to Gustav -
Yes, I know it's easy - just a bit annoying as touching a null field frequently makes the code error out.
At least in JavaScript, you can do the following
var a;
if (a) {  - code for a = "" or a never been set (null) }
in VBA  in Access, especially examining a field which is null, it's really important you use NZ.  
also in a calculated field in a query, not using NZ will cause it to fail without an error - just won't work for example .
select calcfunction([field1]) as f1 from .. will give you nothing where as  select calcfunction(nz([field1])) as f1 from .. will work  - at least in my experience in reports.  

I didn't mean to overreact to a complete stranger,  mutual respect is the way to go on these boards,  but I do know what I'm doing - however, not always obvious to my customers I'm sure.

Peace.

Most important  to the contributor - did this answer your question.  Looks like the cdate might be the fastest.