Hi,
I have a problem with an MS Access SQL query that does not pull the records I need. Bascially what I need is all records between 4 or 5 days which also fall between 7:00 AM and 10:15 AM. All times before and after the set period must be excluded.
A few of the variations I've tried are as follows:
1.
WHERE logtime >= #01-Feb-2009 07:00:00 AM#
AND logtime <= #04-Feb-2009 10:15:00 AM#
This version does not exclude the records after 10:15 on the 1st,2nd,3rd and 4th of February (). I also cant use BETWEEN Date1 AND Date2 as it also does not return what I need.
2.
WHERE day(logtime ) in (1,2,3,4)
AND MONTH(logtime) = 2
AND YEAR(logtime) = 2009
AND DATEPART('h', logtime) BETWEEN 7 AND 9
Anyone have any tips/suggestions? Thanks
4  1793  NeoPa 32,578
Recognized Expert Moderator MVP
Separate out the Date and Time components and check them separately (Use Between for both).
NeoPa 32,578
Recognized Expert Moderator MVP
As the date part of a DateTime value is integral, and the time part fractional, that can be done something like : - ...
-
WHERE (CDate(Fix([LogTime])) Between #2/1/2009# And #2/4/2009#)
-
AND (CDate([LogTime]-Fix([LogTime])) Between #7:00# And #10:15#)
-
...
NB. Please see Literal DateTimes and Their Delimiters (#) for further explanation about using dates in SQL.
Thanks NeoPA, this issue has been resolved. The MS Access version is : - WHERE (DateValue(LogTime) between #01-Feb-2009# and #04-Feb-2009#
-
And TimeValue(LogTime) between #07:00:00 AM# and #10:15:00 AM#)
NeoPa 32,578
Recognized Expert Moderator MVP
Nice :)
I was hunting around for those functions. To be clear, that is the solution I'd have provided if I'd remembered it ;) This is a better solution than mine.
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Mark Reed |
last post by:
Hi all,
I have a query (query1) which shows scan date, scan time & operator. One
scan = 1 record. What I want to do is create a report based on query 2 from
query1 which shows all the scans AND the difference between each scan.
Something like below. I have spent days on this and can't figure it out.
Date Time Login Diff
04-Dec-03 23:33:12 27478
04-Dec-03 23:33:38 27478...
|
by: rivka.howley |
last post by:
I wrote some code that creates a table with a date/time field at
15-minute intervals. Here's how I create and populate the table
With tblDataTemp
..Fields.Append .CreateField("CT_ID", dbLong)
..Fields.Append .CreateField(strTmpIDFld, dbLong)
..Fields.Append .CreateField(strTmpDateFld, dbDate)
..Fields.Append .CreateField(strTmpDataFld, dbDouble)
|
by: Stewart Graefner |
last post by:
Here is a chunk of code that works for an individual record. It
evaluates dates and checks or unchecks boxes as it goes along. It may
not be pretty but it works. What my problem is that I need it to
evaluate all the records(200+) in my db and change those which need
changing. Having to do it individually would defeat the purpose of
developing this code. What I would like to be able to do is either 1.
Open the db push a button and all the...
|
by: Drum2001 |
last post by:
My question is in reference to the following closed thread.
http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/b0ff255f68735f08/0a9daf29479ec733?q=drum2001&rnum=2#0a9daf29479ec733This
is my current query:
This is my current query:
SELECT .Employee, .TimeTypeIn,
IIf(Sum(DateDiff("s",,))-40*60*60>0,Sum(DateDiff("s",,))-40*60*60,0)
|
by: rn5a |
last post by:
A MS-Access DB table has 2 columns - StartTime & EndTime. Though the
data type of both the columns are Date/Time, the records under these 2
columns stores ONLY the TIME part & NOT the DATE part (for e.g.
7:00:00 AM, 2:00:00 PM, 8:35:00 PM etc.).
A Form in a ASP page has 3 dropdown lists. The 1st one is to select an
hour option, the 2nd one to select a minute option (this dropdown list
has only 4 options - 00, 15, 30, 45). The 3rd...
| | |
by: shriil |
last post by:
Hi
I have this database that calculates and stores the incentive amount
earned by employees of a particular department. Each record is
entered
by entering the Date, Shift (morn, eve, or night) and the 'employee
name'. There is another table which assigns an ID to the Shifts, i.e.
1,2 and 3 for morn, eve & night shifts respectively. From the mother
table, the incentive is calculated datewise for each employee as per
his shift duty. In...
|
by: srusskinyon |
last post by:
I need some help getting unique records from our database! I work for
a small non-profit homeless shelter. We keep track of guest
information as well as what services we have offered for statistical
purposes.
I've been using
Here's the situation:
I have two main tables:
|
by: Dr Al |
last post by:
I have a table with four date fields, some of which may not be filled
in based on our data entry needs. I have a criteria set as <date()-180
which is supposed to pull dates older than 180 days ago. The problem
is that when I use that criteria for all four fields I am not getting
the expected results.
I am trying to find out from this query is the date in date field one
is older than 180, same thing for the other three date fields. For...
|
by: sara |
last post by:
Hi -
I have had this problem MANY times and I just don't think I have the
best solution.
I am running a parameter query to retrieve records where work was
completed between 2 dates. The "completed date" field contains both
date and time (e.g., 11/4/07 15:44:00) and does need the time for the
data to be properly recorded.
That said, is it possible to format the parameter "Between and " to enable the user to enter just the
|
by: Claus Mygind |
last post by:
I have two tables employee and time. Both contain the common field EMPNO.
I want to link the tables on the EMPNO and find all records within a specific date range. But I also want to include any employee who has no time records in the time file.
note - my tables are setup to have "not null" values in the table columns. The default value is ''
here are my attempts to construct the select statement to no avail
select e.*, t.*
from...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
