Using NZ function in a long string

[giddyup43]

Hi.
I'm not sure where to put in the NZ function here. I'm trying to convert dates written as 20160917 to mm/dd/yyyy and use this function in a query: Formatted Date: CDate(Mid([Date Last Sold],5,2) & "/" & Right([Date Last Sold],2) & "/" & Left([Date Last Sold],4)).
The problem is that if there are no dates in the [Date Last Sold] column, it will return a #error.
I believe that if i put in an nz somewhere it will return a 0 instead.
If you can help me do this, I would really appreciate it.
Also, I might want to put in 1/1/1900 instead of 0. Can you please show me both options?
Thanks.

 

[Joe4]

I would do something like this, rather than using NZ three times (one for each reference of "Date Last Sold":

IIf(IsNull([Date Last Sold]),DateSerial(1900,1,1),CDate(Mid([Date Last Sold],5,2) & "/" & Right([Date Last Sold],2) & "/" & Left([Date Last Sold],4)))

 

[giddyup43]

thanks a lot for your prompt response.
It looks ok in the results, however when I try to sort the column, I get the error message: syntax error (missing operator) in query expression 'formatted date'

 

[Joe4]

Don't use spaces in calculated field names. Try Formatted_Date.
Also, make sure that you don't have any "bad" data in the "Date Last Sold" field (i.e. text or values that cannot be converted to dates).

 

[giddyup43]

oh boy. I see now that there's bad data there. This table gets updated weekly from an outside source that has bad data in it. (there's no way I'll get them to change it) How can i convert it in a query to equal a null value if it's not in the 20160719 format?

 

[Joe4]

Can you post a few samples of what this bad data looks like?
Is there any common pattern to the bad data?

 

[giddyup43]

Date Last Sold
||
||
||
||
|CAP|
|CT|
|F|
|F|
|F|
|GEL|
|GR|
|OZ.|
|OZ.|
|OZ.|
|OZ.|
|OZ.|
|OZ.|
|OZ.|
|OZ.|
|OZ.|
|OZ.|
|VITAMIN|
|VITAMIN|
|VITAMIN|
|VITAMIN|
|VITAMIN|
|VITAMIN|
|VITAMINS|
|VITAMINS|
|VITAMINS|
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
-1.00
20151206
20151206
20151206
20151206
20151206
20151206
20151206
20151206
20151206
20151206
20151206
20151206
20151206
20151206
20151206
20151206
20151206
20151206
20151206
20151206
20151206

<tbody>
</tbody>

the dates on the bottom are right, anything above it is bad data.

 

[Joe4]

It isn't 100% foolproof, but see if this works. It checked to at least make sure the entry is all numbers and 8 characters long.

IIf((Len([Date Last Sold])=8) And (IsNumeric([Date Last Sold])), CDate(Mid([Date Last Sold],5,2) & "/" & Right([Date Last Sold],2) & "/" & Left([Date Last Sold],4)),DateSerial(1900,1,1))

Where you might run into trouble is if you have an entry where it is all numbers that does not translate to a date, i.e. 20169999

Of course, the best thing to do would be to get them to clean up their data!

 

[giddyup43]

thanks a million for your help! i will try it!

 

[Joe4]

You are welcome.

Note: When I come across problems like this that start to get really complex or need to check a number of things, I will often create my often function in VBA (User Defined Function).
It certainly gives you a lot more control over how things work.