Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

MS Access - Date()-1 and Time Between 8:00am and 9:30PM

Apr 13, 2010 8:36PM PDT

Hi everyone, I hope someone can help me with this problem. I have a table where I upload an SMDR file for the previous day at 12:05AM. In this table I have a field named Call Start. I would want to do a query in access which gets the Calls for yesterday's date between 8:00am and 9:30pm only. I am having a very difficult time with this.

Thanks!
Sheila

Discussion is locked

- Collapse -
Re: selection
Apr 14, 2010 5:30PM PDT

It's not really important where the data comes from. It's important how it's stored in the database. Is it one field with Date/Time format? Or 2 with text format? Or ...?

And can you tell what your difficulty is? What did you try and didn't work yet?

Kees

- Collapse -
re: selection
Apr 17, 2010 8:03PM PDT

Hi Kees, thanks for the reply.

1. Date/Time format - General Date
2. I have tried putting:

>Date()-1 and Between #8:00:00 AM# and #9:30:00 PM#

But it won't work. I hope you can help me with this.

- Collapse -
Re: selection on date time
Apr 17, 2010 8:34PM PDT

To select all fields from Table1 with the selection you specified based on field dt in that table I used the following SQL-statement (that's another word for a query).
SELECT *
FROM Tabel1
WHERE (((Format([dt],"yyyymmdd"))=Format(Now()-1,"yyyymmdd")) AND ((Format([dt],"hh:mm"))>="08:00" And (Format([dt],"hh:mm"))<="21:30"));

The trick, as you see, is to convert the relevant parts of the date to strings.

Kees

- Collapse -
re: se
Apr 17, 2010 9:17PM PDT

Thank you so much Kees! It works! =)

- Collapse -
(NT) You're welcome. Thanks for letting us know.
Apr 17, 2010 10:44PM PDT