Thread: Formatting misbehaviour....
07-20-11, 09:26 #1Registered User
- Join Date
- Jul 2011
Unanswered: Formatting misbehaviour....
Hi all - again
I posted a query up earlier - have kinda got around it, although ideas gratefully considered.
However, Access just loves to set me a challenge or two.
In my table I have four fields,
 - ID (primary key and autonumber) which is formatted to appear as a three digit number, 001, 002 etc
 - Year (default value Year(Date())) formatted to appear as a four digit number
 - Day (default value Day(Date())) formatted to appear as a two digit number
 - Month (default value Month(Date())) formatted to appear as a two digit number
All so far so good.
Next step, chuck it all in a query and string them together to form an unique 11 digit number - simples - NO!!!!! All the formatting goes, all preceeding 0s disappear.
Can anyone help me on this please? How to get them to string together as originally formatted?
07-20-11, 10:15 #2Registered User
- Join Date
- Jun 2011
- Inside your mind
See your other post, I suggested a way to complete this.
Personally, I would just use a 'Date' field, instead of seperating them before hand. If the field is setup as Date/Time in the table, with the format 'Short Date', it should retain its original DD/MM/YYYY format.
I would also avoid using the "000" as a numerical, because it will usually lose it's preceding 0's as it doesn't think they're important.
It really depends how much you know about VBA, things like this are tricky to achieve with using queries alone.
Your best bet to make this work is to use the code I gave you, but modified slightly to check the previous record (using moveLast) instead of the date and counter number, and simply 'INSERT INTO' the table with the previous record + 1.
The exception being to check that it is not next the day (again see my code), if it is, create a new format using todays date, and continue the process.
07-20-11, 11:20 #3Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
bear in mind the format only applies to the dispkay of the value not the storage
so you store date values in a date datatypoe, but you can display them in any format you wish such as ISO yyyy/mm/dd, real dates dd/mm/yyyy, heck if you want to you cna even format them US style mm/dd/yyyy (although why anyone would want to beats me). but yu can also tweak the formatting to display the month name and so on.. all from the same stored value,
I repeat formatting doesn't affect the actual storage of the number just the way its displayed outside the db.I'd rather be riding on the Tiger 800 or the Norton