Results 1 to 3 of 3
  1. #1
    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,
    [1] - ID (primary key and autonumber) which is formatted to appear as a three digit number, 001, 002 etc

    [2] - Year (default value Year(Date())) formatted to appear as a four digit number

    [3] - Day (default value Day(Date())) formatted to appear as a two digit number

    [4] - 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?

    Many thanks

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

  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts