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.
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.
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):
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.
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:
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.