IIF(Len)

[rschultz]

I'm trying to add a 0 to the front of a date field (in text format) if the length is 7. In otehr words if the date is 8172001 I need to add a 0 to I can add two /s after that. What's wrong with this formula?
iff(Len([Date In]=7),0+[Date In],[Date In])

 

[Jack Cowley]

What are you going to do if the field is 1272001? Is the date 12/07/2001 or 01/27/2001? 122001 which is only 6 characters long? Is it 01/02/2001, 10/02/2001, 01/20/2001 or 10/20/2001?

 

[rschultz]

wow! about a half hour after I left this message I figured out what the problem was and I was bracing for a lot of flack from here. If you look closer the MAIN problem was that I was using IFF not IIF! dah! but no one else caught it either<grin>. After that I ran into the other problem of using the & rather than a +. Actually, I figured that out faster than the IFF error. And yes I'm trying to get the data into an acceptable format to copy into a date/time field. The data comes to us in an Excel spreadsheet copied form a text file from computer truck-scales. Thanks for the info and for not spotting the FF error<grin>