Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2009
    Location
    Rochester NY
    Posts
    11

    Unanswered: Access Select (Date) Textbox On A Timestamp Field

    The sad part is I should be able to do this by now, I'm just plain brain dead today.

    Have two textboxes where dates are entered, representing Begin and End dates. Example of values:
    txtBegDate is 06/01/2009
    txtEndDate is 06/30/2009.

    Field, TRANSACTIONDATE, in the (SQL) database is a Timestamp data type. Example: 06/30/2009 08:42:27.710.

    Want to construct a VBA select statement for all transactions within the date range. Obviously "SELECT ... WHERE transactiondate Between #" & Me.txtBegDate & "# And #" & Me.txtEndDate & "#" does not include the End Date transactions after midnight (00:00:00.000).

    I've been trying DateSerial, DateValue, and other functions but get a variety of errors, i.e. incorrect data type, etc.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What is the server ? if it's MS SQL Server it cannot work because the Timestamp data type does not store a date nor a time.

    To quote from SQL Server Books Online:
    The SQL Server timestamp data type has nothing to do with times or dates. SQL Server timestamps are binary numbers that indicate the relative sequence in which data modifications took place in a database. The timestamp data type was originally implemented to support the SQL Server recovery algorithms.
    And it further states:
    Never use timestamp columns in keys, especially primary keys, because the timestamp value changes every time the row is modified.
    Otherwise the Format function should do the work.

    Have a nice day!

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Assuming it is not SQL Server timestamp then:
    Code:
    "SELECT ... 
    WHERE transactiondate >= #" & Me.txtBegDate & "# And transactiondate < #" & DateAdd("d", 1, Me.txtEndDate) & "#"
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jun 2009
    Location
    Rochester NY
    Posts
    11
    My apologies. Bad choice of words on my part. It is a DateTime field type. (Yes, it is MS SQL server). In my effort to communicate that the value included the time of day, not just a date, I erred.

    I tried using Format. It didn't work - at first. The following returned ALL dates 06/01 thru 06/30, regardless of the year (and our data goes back to 1999):
    Field:
    XactDate: Format([INVENTORY_TRAN]![ITTRANDATE],"mm/dd/yy")
    Criteria:
    Between Format([Forms]![frmMachMRTally]![txtDate1],'mm/dd/yy') And Format([Forms]![frmMachMRTally]![txtDate2],'mm/dd/yy')

    I thought maybe the 2 digit year was the problem, so I changed yy to yyyy.
    Same results.

    Then I changed the format to one that a database language I used in the 80s and 90s (INFO by HENCO), stored its date values - yyyymmdd. (BTW - You could sort on date and it works).

    The field and criteria are now:
    Field:
    XactDate: Format([INVENTORY_TRAN]![ITTRANDATE],"yyyymmdd")
    Criteria:
    Between Format([Forms]![frmMachMRTally]![txtDate1],'yyyymmdd') And Format([Forms]![frmMachMRTally]![txtDate2],'yyyymmdd')

    This added about 35 seconds to a 2-3 second execution.
    Thanks for the help. Apprecate it. I'm past another hurdle.

  5. #5
    Join Date
    Jun 2009
    Location
    Rochester NY
    Posts
    11
    Pootle Flump - This one works great.
    "SELECT ...
    WHERE transactiondate >= #" & Me.txtBegDate & "# And transactiondate < #" & DateAdd("d", 1, Me.txtEndDate) & "#"

    I can't tell any performance degradation. DateAdd - Sheesh! Didn't even think of using that one. :-)
    Thank you very much!

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    MS (Sybase?) chose a very unfortunate word for that particular data type - it is now deprecated and they use the much more sensible term rowversion.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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