Results 1 to 7 of 7

Thread: Date formatting

  1. #1
    Join Date
    Mar 2015
    Posts
    31

    Question Answered: Date formatting

    Hello all,

    I have to make an expression which will allow me to convert the form of one of my date columns so that it will eb coherent for my next query.

    This is what i tried
    ' Mois: Format([001- Format OF 7-15-29]![' Mois];""mm/yyyy")

    But it doesn't work !

    What i'm looking for is 01/2015 and so on so forth

    Any ideas ?

  2. Best Answer
    Posted by healdem

    "
    Code:
    ' Mois: Format([001- Format OF 7-15-29]![' Mois];""mm/yyyy")
    ...looks wrong
    ..where are the apostrophe's coming from? as said before ' is a reserved symbol and should not be used except for its intended purpose
    ..you have an extra " in the format mask

    it looks like you are assigning the value of a control or variable back to the same control....
    im not quite too sure where you are using this....
    but what happens if (in the debugger's immediate window you try:-


    ?Format([001- Format OF 7-15-29]![Mois];"mm/yyyy")"


  3. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Code:
    ' Mois: Format([001- Format OF 7-15-29]![' Mois];""mm/yyyy")
    ...looks wrong
    ..where are the apostrophe's coming from? as said before ' is a reserved symbol and should not be used except for its intended purpose
    ..you have an extra " in the format mask

    it looks like you are assigning the value of a control or variable back to the same control....
    im not quite too sure where you are using this....
    but what happens if (in the debugger's immediate window you try:-


    ?Format([001- Format OF 7-15-29]![Mois];"mm/yyyy")
    I'd rather be riding on the Tiger 800 or the Norton

  4. #3
    Join Date
    Mar 2015
    Posts
    31
    Yes i have to keep the ' symbol because otherwise when i import all of my tables (which i don't control) then it would just make a mess each time. So i have to keep the same field name, however this isn't causing any problems.

    I tried your new formula and it gave me the same result. When you mention debugger's immediate window i suppose you're talking about the expression builder ?

  5. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    where are you using this?
    I'd rather be riding on the Tiger 800 or the Norton

  6. #5
    Join Date
    Mar 2015
    Posts
    31
    In a query, expression builder

  7. #6
    Join Date
    Mar 2015
    Posts
    31
    I'm using a french version of Access; so instead of "mm/yyyy" it's "mm/aaaa". Thanks for guiding me towards the answer !

  8. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    OK, I tend not to use the query builder. Can you post the actual SQL you are using.. in the query designer, switch to the SQL view, then paste the actual SQL here.

    I suspect that part of the problem is that you are formatting a date time value then trying to assign that value to the same thing. I'm not familiar enough with the expression builder and or query designer to be sure. hence why I'd like to see the actual SQL. Like a lot of 'front end' tools the query designer is fine for some purposes, however it imposes another layer between you the designer and the SQL engine.
    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
  •