Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Results are early 1800s

Discussion in 'Information Technology' started by Gina, Jul 28, 2006.

Tags:
  1. Gina

    Gina
    Expand Collapse
    Guest

    I am using the following script to populate a table with dates. However, the
    dates that are populating are early 1800s instead of today's dates.

    strDate = Format(Date, "mm/dd/yyyy")

    Select Case fraDept.Value 'Determine which dept has been selected from
    the list

    Case 1

    sIdList = ""

    With Me.lstItems

    For Each vItem In .ItemsSelected

    sIdList = sIdList & .ItemData(vItem) & ","

    Next vItem

    End With



    sIdList = Left(sIdList, Len(sIdList) - 1)

    sSql = "Update Billing SET Closed = '-1', CloseDate = " &
    strDate _

    & " Where ID in ( " & sIdList & ");"

    CurrentDb.Execute sSql, dbFailOnError


    Any thoughts?

    Thank you in advance.
    --
    Gina
     
  2. Loading...


  3. Gina

    Gina
    Expand Collapse
    Guest

    Figured it out. ACCESS was reading it as time instead of a date. Needed to
    put in # signs for ACCESS to recognize it as a date.
    --
    Gina


    "Gina" wrote:

    > I am using the following script to populate a table with dates. However, the
    > dates that are populating are early 1800s instead of today's dates.
    >
    > strDate = Format(Date, "mm/dd/yyyy")
    >
    > Select Case fraDept.Value 'Determine which dept has been selected from
    > the list
    >
    > Case 1
    >
    > sIdList = ""
    >
    > With Me.lstItems
    >
    > For Each vItem In .ItemsSelected
    >
    > sIdList = sIdList & .ItemData(vItem) & ","
    >
    > Next vItem
    >
    > End With
    >
    >
    >
    > sIdList = Left(sIdList, Len(sIdList) - 1)
    >
    > sSql = "Update Billing SET Closed = '-1', CloseDate = " &
    > strDate _
    >
    > & " Where ID in ( " & sIdList & ");"
    >
    > CurrentDb.Execute sSql, dbFailOnError
    >
    >
    > Any thoughts?
    >
    > Thank you in advance.
    > --
    > Gina
     
  4. Graham Mandeno

    Graham Mandeno
    Expand Collapse
    Guest

    Hi Gina

    Your update query (shortened) looks like this:

    SET CloseDate = 06/28/2006

    SQL sees this as an arithmetic expression "6 divided by 28 divided by 2006",
    which is a Very Small Number!

    Since "Date Zero" is 30 Dec 1899, all your CloseDates will be on that day.

    To tell SQL to interpret this as a date, you must enclose it in # signs.
    The easiest way to do this is to include them in the format:

    strDate = Format(Date, "\#mm/dd/yyyy\#")

    SQL also understands the Date() function, so if you are always setting the
    CloseDate to today you can just say:

    SET CloseDate = Date()

    Also, if Closed is a boolean (yes/no) field, lose the quotes around the -1:

    SET Closed = -1

    or better:

    SET Closed = True
    --
    Good Luck!

    Graham Mandeno [Access MVP]
    Auckland, New Zealand

    "Gina" <Gina@discussions.microsoft.com> wrote in message
    news:E100F853-C996-47A2-AB71-A8F40EF5FE01@microsoft.com...
    >I am using the following script to populate a table with dates. However,
    >the
    > dates that are populating are early 1800s instead of today's dates.
    >
    > strDate = Format(Date, "mm/dd/yyyy")
    >
    > Select Case fraDept.Value 'Determine which dept has been selected from
    > the list
    >
    > Case 1
    >
    > sIdList = ""
    >
    > With Me.lstItems
    >
    > For Each vItem In .ItemsSelected
    >
    > sIdList = sIdList & .ItemData(vItem) & ","
    >
    > Next vItem
    >
    > End With
    >
    >
    >
    > sIdList = Left(sIdList, Len(sIdList) - 1)
    >
    > sSql = "Update Billing SET Closed = '-1', CloseDate = " &
    > strDate _
    >
    > & " Where ID in ( " & sIdList & ");"
    >
    > CurrentDb.Execute sSql, dbFailOnError
    >
    >
    > Any thoughts?
    >
    > Thank you in advance.
    > --
    > Gina
     

Share This Page