Simple calculation field - date format problem

[Erin learningasigo]

In Database A I have a calculated field (text) called FLName_Birthdate. This literally puts the first name + space + last name + birthdate. In Database A, it looks like this: sam jones09/01/1980.

In Database B, I have the same calculated field, but I can't get the day and month to have leading zeros in the calculated text field. Instead, it looks like this: sam jones9/1/1980.

Everything looks exactly the same in both databases. Any ideas?:)

[mr_vodka]

It depends on how the data was entered.

To get all your existing data with leading zeros,

you can try something like:

Right ( "0" & Day ( YourDate ); 2 ) & "/" & Right ( "0" & Month ( YourDate ); 2 ) & "/" &

Right ( Year ( YourDate ); 2 )

[Erin learningasigo]

Thank you for your reply.

The data is manually entered in Database A, and it is imported from a text file for Database B (the database that isn't working).

In Database B, I formatted the birthdate field to show leading zeros, and it works. But the calculated (text) field that combines the names and birthdate somehow ignores the leading zeros. I don't understand how this would be possible?

[comment]

Make sure both files are set to use the system formats (in File Options). This is a patch, and it may or may not work - the real cure is to clone both files under the current system, import the records from A into A_clone, and re-import the records into B_clone from A–clone.

Formatting a field on a layout is irrelevant to this issue.

[Erin learningasigo]

Okay - I'm ready to clone. Is that the same as "Save file as...."?

Can you type the steps in order, one more time? Sorry, I just don't want to mess it up. Thanks!!

[comment]

1. Close and backup your files.

2. Make sure your computer's short date format is set to use leading zeros.

3. Open A and save it as clone. Close A.

4. Open A_clone and import the records from A. Close A_clone.

5. Open B and save it as clone. Close B.

6. Open B_clone and import records directly from A_clone. Close B_clone.

7. Rename your clones and re-link them as necessary.

BTW, all this is not necessary if you only want to get leading zeros in your calculation. For this, it's better to construct the calculation as John has shown - this will continue working even if the file is opened/cloned under yet another system format, or if someone manually enters a date with no leading zeros. As long as the dates are valid, it doesn't matter if they have leading zeros or not.

Another point: you didn't say what's the purpose of this, but a combination of name and birthday is by no means guaranteed to be unique. If you're using this for relationships or another way of unique identification, you should rethink this.