Criteria - Date with time

chris186h

Board Regular
Joined
Mar 18, 2015
Messages
111
Hello all, im new to access. Ive been hunting for a way to filter by the criteria of
Between date-1 at 07:00 am and date at 07:00am (Giving a 24 hour window)
any help on how to put this in as a criteria would be greatly appreciated
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,019
Office Version
  1. 365
Platform
  1. Windows
Between date-1 at 07:00 am and date at 07:00am
This is just an example, which could be for any time - or is it just that you want 7:00? You might want to look at the DateAdd function. I hesitate to elaborate given that uncertainty, plus you're not saying if this is for a query criteria field, form control, report control or what. Nor can I tell if "date" is to be the current date or some date you wish to input.
 
Last edited:

chris186h

Board Regular
Joined
Mar 18, 2015
Messages
111
This is just an example, which could be for any time - or is it just that you want 7:00? You might want to look at the DateAdd function. I hesitate to elaborate given that uncertainty, plus you're not saying if this is for a query criteria field, form control, report control or what. Nor can I tell if "date" is to be the current date or some date you wish to input.

Sorry I should have explained myself better. It is for a query criteria field.
It is just the set time of 7:00 which will never change. The date I want to be based on yesterdays date at 7:00am to todays date at 7:00am. I assumed it would be written similar to. date()-1 07:00 to date() 07:00
Thankyou for you reply
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,019
Office Version
  1. 365
Platform
  1. Windows
try Between (Date-1 + #07:00#) AND (Date + #07:00#) I'm thinking the DateAdd function is not really required in this case. This should work if your Regional Settings is the same as mine for date/time (mm/dd/yyyy hh:mm:ss)
 
Last edited:

chris186h

Board Regular
Joined
Mar 18, 2015
Messages
111

ADVERTISEMENT

try Between (Date-1 + #07:00#) AND (Date + #07:00#) I'm thinking the DateAdd function is not really required in this case. This should work if your Regional Settings is the same as mine for date/time (mm/dd/yyyy hh:mm:ss)

Thats great thank you for your help. I will try it when I get to work and let you know.
Many thanks for replying
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,019
Office Version
  1. 365
Platform
  1. Windows
You're welcome. If that doesn't work, post back but make sure you include what you typed, what happened and what was supposed to happen. Good luck!
 

chris186h

Board Regular
Joined
Mar 18, 2015
Messages
111
The only change it needed to work was () after the date and then it worked perfectly.
Between (Date()-1+#07:00:00#) And (Date()+#07:00:00#)

Thankyou for your help
 

Arsen01

New Member
Joined
Dec 16, 2017
Messages
3
hi all, my name is Arsen)) I am a new member here )) thanks for your post , nice to meet you all here, in this web site
 

Watch MrExcel Video

Forum statistics

Threads
1,123,473
Messages
5,601,863
Members
414,479
Latest member
Beau the dog

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top