Date query

[okl]

hi
I have a table with a date field ddmmyyyy hhmmss from year 2014 and year 2015

If I want to query on certain date e.g year to date for 2015 : 01/01/2015 00:00 to 28/08/2015 2359

How can I do the same for year 2014 too from the same table .

Purpose is to compare the mtd or ytd value of two different years.


Thanks

 

[123rickfear]

if 28/08/2015 23:59 is in A1, the below will take away 1 year

=EDATE(A1,-12)+"23:59"

 

[Joe4]

=EDATE(A1,-12)+"23:59"

Excel solutions usually will not work for Access questions!

I have a table with a date field ddmmyyyy hhmmss from year 2014 and year 2015

So are you fields actually entered as Text data type or Date data type?

 

[okl]

Data data type ?

 

[Micron]

If your table date/time field does in fact contain the time component one often needs to deal with it when supplying an end date to your query.
For a range from a date to end of year, it is simply >=#01/01/2015 - no end date required.

For a range between two dates (where the date comes from a form control):
Between [Forms]![Switchboard]![StartDate] And DateAdd("n",1440,[Forms]![Switchboard]![EndDate])
The DateAdd function adds a date/time value to the EndDate. In this case, it is 1440 (60min*24hrs) minutes . Otherwise, the cutoff point would be at the zero hour of the end date. You could get this down to the last second by adding the appropriate number of seconds to the date if need be. For my purpose, no one would be doing anything that creates records that late in the day, so I probably went overboard anyway.

For a date range between dates where the field does not contain the time component, it would be:
Between #01/01/2015# And #08/01/2015#

 

[Joe4]

Data data type ?

No, I said "Date Data Type".

If you open up your table in Design View, does it show your field's Data Type as "Text" or "Date/Time"?
Many times, if dates in a "weird" format, the field is stored as Text (and the user may not be fully aware of it).
The Data Type of the field affects how you would approach your question.