Format(Date,"m/dd/yyyy") vs Short Date "m/d/yyy" (1 Viewer)

Im having a problem retrieving records using between dates in my query where clause.
I have a form with start date and end date that is passed the query. Form dates are formatted as short date "m/d/yyyy" using the date picker. query date is "m/dd/yyyy". Records between 1st and 9th is not picked up.

I've tried different ways.

• 
  Form date "m/dd/yyyy" and Query Date "m/dd/yyyy"

• 
  Form date "m/d/yyyy" and Query Date "m/d/yyyy"

The only thing works is if i take out the date picker and input the date as text using format "m/dd/yyyy" in both form and query. This wont work for users , i need to be able to use the date picker.

Any ideas...

If you are using a form and the query designer you shouldn't need to do any date formatting if your date field in your table is actually a date data type.

Access will take care of everything for your.

So don't format the date in the where clause, that will format your date as a string, which is why your criteria isn't working - format it in the display output of the query.

Minty said:

If you are using a form and the query designer you shouldn't need to do any date formatting if your date field in your table is actually a date data type.

Access will take care of everything for your.

So don't format the date in the where clause, that will format your date as a string, which is why your criteria isn't working - format it in the display output of the query.

Click to expand...

My data type is date/time but i imputing a TimeStamp "m/d/yyyy h:mm:ss" e.g "3/8/2018 1:04:25 PM". So i need to format the time stamp so i can query the dates.

As long as it was properly formatted when it was output, it should input in the correct format.

If the form you showed us used bound date fields, then they will be input correctly. So what is the medium of this input that holds time and date? Typed? Excel? Text file of some indeterminate source? How is that time getting to you?

By the way, I'm with Minty. If you want to do date and/or time comparisons, there is no better format than DATE format for the fields and variables involved. Access, given items in DATE format, can do that comparison all day long standing on its head.

So the question is, what operation forces your hand to use a text-formatted date? If you can explain this part a bit more, we might be able to advise better because it seems to be the crux of your issue.

Let me start over. There 3 parts to this equation

1. Date field in my table
2. Formatted date field in my query
3. Start and End Date text fields on my form

See attached images.

Date field in table is inputted via VBA as NOW() because i need a time stamp
Formatted date field is my query = Format(Datefield,"m/dd/yyyy")
Text field in my form = Short date "m/d/yyyy"

Problem
Single number dates i.e. 1-9 are not being retrieved.

I've tried formatting the date in my query to match my date on the form to Format(Datefield, "m/d/yyyy") to no avail.

Dates are stored internally as double precision numbers. The integer part is the number of days since Dec 30, 1899 ( for Jet and ACE. Excel, SQL Server and others may use a different origin date. ODBC takes care of any necessary conversion so don't worry about it.) As has already been mentioned, when you Format() a date, you convert it to a string. That will make it act like a string. Strings compare (and sort) left to right. That means that 01/01/2018 will sort BEFORE or compare as LESS than 02/01/1910.

If you MUST work with formatted dates (you don't have to since there are other methods), Always format them as yyyy/mm/dd so they will compare and sort correctly.

In the cases where you have a time component to your dates and you want to work only with the dates, use a function that returns a Date data type. DateValue() gives you just the date. TimeValue() gives you just the time.

VBA has a wealth of functions. Get a list organized by category so you can see all the date functions together and all the string functions together, etc. And make them your friend. Before you write any code or create your own conversion, look to see if one already exists.

Keep in mind that formatting is for display only. Always work with unformatted dates even if you need to bring a date into a query twice so you can have a formatted version and an actual date data type version.

And finally, NEVER, EVER format dates or other fields at the table level. Formatting is for display and does NOT change the actual stored value. If you have a date field in a table and you format it as short date but mistakenly populate it with Now() which includes time rather than Date() which only includes a Date, the time will be stored. You won't see it when you open the table in DS view or run a query against the table because the query will pick up the default format from the table but the time will still be there and it will most definitely mess up any date comparisons you do.

Pat, thank you for that wealth of info. i tried datevalue() instead of format() and it worked like a charm. There is so much i don't know but i'm learning as developer. Thank you again

Access is a lot like bridge. You can learn how to build an app or bid and play a bridge hand in an afternoon but both take a lifetime to master Enjoy the journey.

Pat, I've got bridge perfectly figured out. When I play in the 1/10th-cent game, I bring lots of small change. See? Perfectly figured out.

Wow. Someone who plays money bridge. One of my friends took me to the Cavendish in NYC one day and I played in the cheap game. It was a terrifying experience for a duplicate player who never plays rubber bridge. I managed to win $80 but I got an ulcer because none of my partners ever respected a forcing bid. Duplicate is a completely different world and once I got that bug, I dropped out of the lunch time pick up games and the social evenings with people who didn't play duplicate. For social evenings with duplicate players, we play Chicago which mimics duplicate scoring and so doesn't alter the bidding as radically as rubber scoring does.

I also played for money in Australia. That was even more ulcer-producing because not only was I back to rubber bridge scoring, I was playing a bidding systom (Acol) which is not commonly played in the US so I was even more out of my depth. I won money there also. I think what happened in both cases was that every one who played a rubber with me was on their best behavior and since their objective was to get out without a loss when playing with the "newbie", I also benefited.

Pat, your bidding might be iffy in a non-duplicate situation, but your play from duplicate allows you to know the odds better and play for the average+ situation. Which at rubber bridge doesn't win you the big pots - but it keeps out of losing the big pots.

In college I was a director for the university-sponsored duplicate tournament and even was allowed to represent the university at the state regionals. We didn't win but my newbie partner and I were in the upper third of the field. These days my directorial certification is long expired, and to be honest, I'm not interested.

I've decided my style is more or less "stranded American" - lost and no way to get home. Only bridge players would get the joke inherent in the style's name.

But enough about bridge in a tech thread.