Function like Nz for zero-length strings?

[option]

Hey everyone,

I'm stuck on getting data into a table. I'm importing data into a recordset and there is a date field which is optional in some cases. When passing that date from the source table to the destination table, I get "type mismatch" for an error when a blank value is passed. Now, is the data being passed were null, the Nz function would take care of it. However, the data being passed is a zero-length character, and Nz doesn't work on those . I've tried wrapping CDate around the source text, but that doesn't help either. Does anyone know if there is something like Nz for zero-length characters, or a way to work around this? Thanks!

 

[boblarson]

If you want to check for nulls AND zero length strings, consider code like this:

If Len([FieldNameHere] & "") = 0 Then...

 

[gemma-the-husky]

if its a zero length character, then it cant be a date - since a date is a number, and a blank date will show as zero

a date can only be null or numeric.

the likely alternative is that your imported file needs massaging, as the field is probably showing as type text rather than date, if some values are not valid dates. I hope that makes sense - you need to use a function to change the text string into a date try cdte

 

[JANR]

I've tried wrapping CDate around the source text, but that doesn't help either.

CDate dosen't handle Null's but CVDate should work with Nulldates.

JR

 

[Brianwarnock]

Nowhere does the OP say what he wants in the Date field for the zero length option.

Brian