markhardee Posted January 31, 2001 Share Posted January 31, 2001 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 More sharing options...
PinnWal Posted January 31, 2001 Share Posted January 31, 2001 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 :-) Link to comment Share on other sites More sharing options...
markhardee Posted January 31, 2001 Author Share Posted January 31, 2001 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? Link to comment Share on other sites More sharing options...
BobWeaver Posted January 31, 2001 Share Posted January 31, 2001 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. Link to comment Share on other sites More sharing options...
markhardee Posted February 1, 2001 Author Share Posted February 1, 2001 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.. Link to comment Share on other sites More sharing options...
Yeti Posted February 5, 2001 Share Posted February 5, 2001 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 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now