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?

 

[Pat Hartman]

The plus sign is an arithmetic operator which in certain situations can be used as the concatenation operator (but not this one). The ampersand (&) will append the 0 to the front of the field. When you are dealing with two numeric the + operator will add them and the & operator will put them side by side. 1+1 = 2 but 1&1 = 11. Read the help entries for these two operators to learn how to use them correctly. There's a lot of information there.

You should always work with date/time fields to hold dates in Access NEVER text fields. I hope what you're trying to do is to convert the text string to an acceptable format so that you can convert it to a date/time field.

 

[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>