Anyway to have FM "keep" the leading zeros on dates? I use the date and date to text function to make a job number.

Example..

JOB-0131-1A

JOB is a three letter client code

0131 is supposed to be January 31

1A is 2001 and a incrementing letter (for 2 or more jobs from, the same client in a 24 hour period)

I use:

MATCH CUST CODE::Client Code &" - " &Left(Date TEXT , 2 ) & Middle(Date TEXT , 4 , 2 ) & "-" & Right(Date TEXT , 1 ) & Serial Letter

To get the job number *but* even if I manually enter 01/31/01

the value 1/31/2001 is stored in the field and when I use the DatetoText I get:

1312001

It does the year right but not the day?

Ideas?

In Layout mode, go to Format, then Date & when asked for Leading Characters select "Zero" by the month pull-down menu.

Hope that helps.

Later :-)

In Layout mode, go to Format, then Date & when asked for Leading Characters select "Zero" by the month pull-down menu.

Hope that helps.

Later :-)

Been there - it changes how the date is *displayed* but not stored, evidently..

Again?

The standard way to force a leading zero onto something that may or may not have one is:

Right("00000" & NumToText(FieldWithoutLeadingZeroes), HowManyDigits)

You will have to apply this to both the month and day:

MATCH CUST CODE::Client Code & "-" &

Right("00" & NumToText(Month(Date)),2) &

Right("00" & NumToText(Day(Date)),2

Right(NumToText(Year(Date)),1) &

Serial Letter

By the way, your system will fail nine years from now. I suggest you add a few leading digits to the year. The Y2K fiasco was caused by people who were too modest to think their software would survive more than a couple of years.

Also, by having the job number set up as Customer/month/day/year/serial, if you ever sort by job number, the records will not be in chronological order or any other discernible order for that matter.

Just some things to think about.

quote:

---

Originally posted by BobWeaver:

The standard way to force a leading zero onto something that may or may not have one is:

Also, by having the job number set up as Customer/month/day/year/serial, if you ever sort by job number, the records will not be in chronological order or any other discernible order for that matter.

Just some things to think about.

---

Thank you sir! I have been wrestling with this of and on for too long. As for the sorting by job number, as we always plan for everything when designing a database (yea, right!) I can't forsee ever having to do that. The format is on we have used for 11 years now and are fairly "comfy" with it. Although (having not painted myself into a corner) if the need arose, I could the client ID and date are seperate fields and I could use them both to sort by, I suppose.

Man, I am glad to have stumbled onto this forum. I just hope that I can contribute some tidbit of knowledge or discovery one of these days.

This project is an attempt to replace my current pencil and job ticket, quoting and billing system I have now.

Database designers are worth their weight in gold..

Database designers are worth their weight in gold..

Aha, now I know why I allways feel so awfully rich without a penny in my pocket. Hope I can convince my bank to give me a morgage based on my weight