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

jsdba · Apr 18, 2018

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

Minty · Apr 18, 2018

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.

jsdba · Apr 18, 2018

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.

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.

The_Doc_Man · Apr 18, 2018

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.

jsdba · Apr 18, 2018

Let me start over. There 3 parts to this equation

Date field in my table
Formatted date field in my query
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.

jsdba · Apr 18, 2018

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

The_Doc_Man · Apr 20, 2018

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.

The_Doc_Man · Apr 20, 2018

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.

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

jsdba

Registered User.

Attachments

Minty

AWF VIP

jsdba

Registered User.

The_Doc_Man

Immoderate Moderator

jsdba

Registered User.

Attachments

jsdba

Registered User.

The_Doc_Man

Immoderate Moderator

The_Doc_Man

Immoderate Moderator

Similar threads

Users who are viewing this thread