Getting Date only from a TimeStamp in Access Qry
Hello,
Want to show just the date in a qry but the below does not show just today's date, using Now shows just the current date and time...but only wan the date for today only...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Which comment are you referring to, my comment above this one seemed to do the job in a test here...
»bp
»bp
ASKER
I got the Fix to work to sort the date but couldn't get the format to work to sort by date, but the format works to strip the Timestamp. Giving both points.
Thanks to both...
Thanks to both...
ASKER
Sorry you were quicker than my typing and viewing.
but got it from both of you.
awesome work..
but got it from both of you.
awesome work..
I see that you have a "solution" but I don't like either of them.
1. Don't use Fix() with dates. Use the correct Date functions. There are several of them that will do whatever needs doing. Here is a link to all the Access VBA functions. BOOKMARK it. https://support.office.com/en-us/article/Access-Functions-by-category-b8b136c3-2716-4d39-94a2-658ce330ed83?ui=en-US&rs=en-US&ad=US&fromAR=1 I don't know if there are any adverse consequences of using Fix() which converts a double to an integer, but why risk it when there is a function specific to the task. And besides that, the DateValue() takes any string that looks like a date, not just actual datetime columns. So, "May 18, 2017" is returned as 5/18/17 which in reality is 42873 internally since dates are stored as the number of days since Dec 30, 1899.
2. The Format() function converts a date to a string so unless you have formatted it in year, month, day order, it will NEVER sort correctly. Always use dates in date format if you want to sort or compare. To pull just the date from a datetime field, use DateValue().
1. Don't use Fix() with dates. Use the correct Date functions. There are several of them that will do whatever needs doing. Here is a link to all the Access VBA functions. BOOKMARK it. https://support.office.com/en-us/article/Access-Functions-by-category-b8b136c3-2716-4d39-94a2-658ce330ed83?ui=en-US&rs=en-US&ad=US&fromAR=1 I don't know if there are any adverse consequences of using Fix() which converts a double to an integer, but why risk it when there is a function specific to the task. And besides that, the DateValue() takes any string that looks like a date, not just actual datetime columns. So, "May 18, 2017" is returned as 5/18/17 which in reality is 42873 internally since dates are stored as the number of days since Dec 30, 1899.
2. The Format() function converts a date to a string so unless you have formatted it in year, month, day order, it will NEVER sort correctly. Always use dates in date format if you want to sort or compare. To pull just the date from a datetime field, use DateValue().
ASKER