Jump to content
Claris Engage 2025 - March 25-26 Austin Texas ×

Getting 'them Zeros?


This topic is 8672 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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?

Link to comment
Share on other sites

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. smile.gif

Link to comment
Share on other sites

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.
smile.gif

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

Link to comment
Share on other sites

This topic is 8672 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.