Results 1 to 7 of 7
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formatting a Date field in an Append Query (2000)

    If on the startup form of the attached mdb you select any record with a December 2004 date and
    click on the "Missing Monthly Values" button and then on the "Collimations" button on the ensuing form, you can see that the value Dec/05 is added to the MeseAnno field in the AMnote table. I can't make out why it is to since the Annota function that is launched but clicking on the "Collimations" button sets the strSQL variable to
    INSERT INTO AMNote ( MeseAnno ) VALUES ( #12/04# )
    if you select any record with a November 2004 date on the startup form, the correct Nov/04 value is inserted into the MeseAnno field.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formatting a Date field in an Append Query (2000)

    In the Annota function, you have

    strGiorno = "#" & Format(DateSerial(Year(NewData), Month(NewData), 1), _
    "mm/yy") & "#"

    Replace "mm/yy" with "mm/dd/yy"

    Without dd, the string has value 12/04; this is interpreted by VBA and SQL as December 4 in the current year, i.e. 12/04/2005 (US date format). With dd, the string has value 12/01/04; this is interpreted as December 1, 2004.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formatting a Date field in an Append Query (2000)

    Note: your database is corrupt. If you look at the Project Explorer in the Visual Basic Editor, there are modules for non-existing reports. You should create a new blank database and import all database objects from dighe.mdb into it. You will have to set the startup options and VBA references.

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting a Date field in an Append Query (2000)

    Thank you Hans,
    that's in spite of the fact that the "mm/yy" format does specify month and year.

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting a Date field in an Append Query (2000)

    I noticed that too. I had to strip down the original file so as to be able to post it. Are you saying that once you delete reports from an mdb then any associated modules should automatically be removed too?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formatting a Date field in an Append Query (2000)

    SQL does not know that you specified mm/yy, it only sees the resulting string. A string such as "03/04" is ambiguous: it could mean the 3rd of April in UK date format, or the 4th of March in US date format, or April, 2003 in Swedish date format, or - as you intended - March 2004. SQL has to make a decision; it interprets it as 4th of March in the current year, not as March 2004. You may not like that, but you'll have to live with it.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formatting a Date field in an Append Query (2000)

    Normally, removing a form or report also removes the associated module. Apparently, something has gone wrong here.

Posting Permissions

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