for some reason when i pull my query with the following parameters...
Date field (formatted to short date)
Time field (formatted to short time)
i still get everything ever inputted for the day. Is there some trick to making access see the time and to only display the times i tell it to?
What i really need it to do is pull all data that is greater than yesterday at 5:00 am...
and in-case you were wondering, I also tried > #05:00:00 am# and that doesn't work either.
sooli,
Try to keep in mind that Dates are integers and times are fractions of integers. So, 5:00 am is actually 0.208333.
To calculate times, the best method is to use the DateAdd() Function. So, to determine a date at 5:00 am, you would take the date (be sure to use a DATE and not a DateTime) and add 5 hours: - WHERE [DateTimeField] > #" & _
-
DataAdd("h", 5, [Enter yesterdays date]) & "#"
This works, because "yesterday's date" is, technically speaking, 12/23/2014.0000. When you add 5 hours, that new, numerical value becomes 12/23/2014.208333.
Hope this hepps!
19  1362 
sooli,
Try to keep in mind that Dates are integers and times are fractions of integers. So, 5:00 am is actually 0.208333.
To calculate times, the best method is to use the DateAdd() Function. So, to determine a date at 5:00 am, you would take the date (be sure to use a DATE and not a DateTime) and add 5 hours: - WHERE [DateTimeField] > #" & _
-
DataAdd("h", 5, [Enter yesterdays date]) & "#"
This works, because "yesterday's date" is, technically speaking, 12/23/2014.0000. When you add 5 hours, that new, numerical value becomes 12/23/2014.208333.
Hope this hepps!
Also, if the parameter is always yesterday's date, there's no need to prompt for it. You can subtract one day from the current date.
By the way, just because you're not displaying the date portion doesn't mean it's not there in the data. #5:00 AM# has a hidden date portion of 12/30/1899.
And Hour(5) is getting the hour of the value #01/04/1900 00:00:00 AM# which is equal to 0.
THANK YOU! This worked perfectly! You rock!
Thank you for the excellent explanation! Now this makes sense!
well.. something has gone wrong.. its not working... it is still pulling the whole day's data... not just from yesterday @ 5:00 am to today @ 5:00 am...
here's my code: -
SELECT MonitoringCheck.Solution, MonitoringCheck.CDate, MonitoringCheck.CTime, MonitoringCheck.Technician, MonitoringCheck.MonitoringComplete, MonitoringCheck.Incident, MonitoringCheck.Server, MonitoringCheck.Comments, MonitoringCheck.LogFile, MonitoringCheck.ProductionVerification, MonitoringCheck.TestVerification, MonitoringCheck.MIPPatchingVerification, MonitoringCheck.MIPPatchingVerification, MonitoringCheck.CAECoreCopied, MonitoringCheck.MachineAddCreation, MonitoringCheck.MachineAddResolution, MonitoringCheck.MachineDesubCreation, MonitoringCheck.MachineDesubResolution, MonitoringCheck.[SEPHomeUse(NoNew)], MonitoringCheck.[SEPHomeUse(Posted)]
-
FROM MonitoringCheck
-
WHERE (((MonitoringCheck.CDate)>=DateAdd("h",0.5,[enter yesterdays date])))
-
ORDER BY MonitoringCheck.CDate, MonitoringCheck.CTime;
-
I think i got it! using the .20833 -
SELECT MonitoringCheck.Solution, MonitoringCheck.CDate, MonitoringCheck.CTime, MonitoringCheck.Technician, MonitoringCheck.MonitoringComplete, MonitoringCheck.Incident, MonitoringCheck.Server, MonitoringCheck.Comments, MonitoringCheck.LogFile, MonitoringCheck.ProductionVerification, MonitoringCheck.TestVerification, MonitoringCheck.MIPPatchingVerification, MonitoringCheck.MIPPatchingVerification, MonitoringCheck.CAECoreCopied, MonitoringCheck.MachineAddCreation, MonitoringCheck.MachineAddResolution, MonitoringCheck.MachineDesubCreation, MonitoringCheck.MachineDesubResolution, MonitoringCheck.[SEPHomeUse(NoNew)], MonitoringCheck.[SEPHomeUse(Posted)]
-
FROM MonitoringCheck
-
WHERE (((MonitoringCheck.CDate)>Date()-2) AND ((MonitoringCheck.CTime)>0.20833))
-
ORDER BY MonitoringCheck.CDate, MonitoringCheck.CTime;
-
seems to be working
nope... i'm at a loss... the last bit of code was eliminating the some of the data before 5am on the current day.. i don't get it...
I would still recommend going with your code in Post #6, but change line 3: - WHERE (((MonitoringCheck.CDate)>=DateAdd("h",5,[enter yesterdays date])))
Your code was looking at all times after 12:30 am.
You didn't mention this before:
... to today @ 5:00 am...
You need an additional filter to account for this.
so something like: - Where monitoringcheck.CDate BETWEEN(((monitoringcheck.CDate)>=DateAdd("h",5,[enter yesterdays date] AND((monitoringcheck.CDate)<=DateAdd("h",5,date()))
-
im not at work to try... but thought I'd run this by you all while it was on my mind...
well that didn't work either... still pulling from midnight rather than 5:00 am....
on another note... if I can...
how in the world do i hide the database window? The thing takes up the whole screen, all my users need are the forms I created... used to be a check box that would let you not display the application... that feature seems to be gone in 2013 =/
First, are you SURE that monitoringcheck.CDate is a Date/Time?
If it is, then try this: - WHERE monitoringcheck.CDate BETWEEN DateAdd("h",5,DateAdd("d", -1, Date()) AND DateAdd("h", 5, Date())
Try that.
As far as your second question in the last post, please start a new thread, and I'd be glad to provide the solution I use (we like to avoid multiple questions in one thread).
Yes, it is definatly a date/time in the table, set to short date format, and the CTime is also a date/time format in the table, set to short time format.
Dates and times are auto populated on a click event
When you assign the Date/Time in the OnClick event are you using Date() or Now()?
You didn't mention this either
...and the CTime...
You said date time field. Not date field and a separate time field. You need to combine them.
@ Twinnyfo - using Now() database is tracking in real time.
@ Rabbit - both fields actually have full date and time in them... they just display either or.. so I should be able to pull off of one or the other right?
Adding to Rabbit's note about the two Date/Times, there should be no need for two separate fields (one for the date and the other for the time). Even though you are using Now(), when you save a date in a field that is set to short date, you are truncating the time portion. Additionally, when you save Now() in a field that is set to short time, you are truncating the Date portion. (Rabbit, or others, you may correct me if I am incorrect).
You should only need one Date/Time field, and set it to General Date, which is stored as "12/30/2014 8:23:33 AM". Then you should be able to run your query based on that Date/Time, using the DateAdd() Function.
Hope this hepps.
I will try to split one field into two then and populate the time field from the date field. My req's were to have both fields as individual fields, I didn't think to have the time field populate from the date... that might fix the whole issue now that I think of it. I'll let you know what happens! =)
Sooli,
No need to split any fields. Just have ONE field, in General Date format. Then, when you need the date, you have it. When you need the time, you have it. When you need a specific date on a specific time, you have it. This alleviates any confusion over which date/time you are using as it is always the same field.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Dan Baker |
last post by:
I'm trying to write a stand-alone application which will
query the contact database used in Outlook (or maybe
Exchange Server). I have no idea where to start
looking. Any advice?
Dan Baker
|
by: Winterminute |
last post by:
I am trying to read a list of install programs using WMI with ASP.NET/C#.
However, it fails with "Invalid Class".
I have confirmed that if I query LOCALHOST then it works fine, but if I
query a...
|
by: MadMan2004 |
last post by:
Hello all!
I'm having a problem with a project I'm working on and I'd like to ask
for anyone's input that might be helpful. I'm building a rather large
front-end application connecting to an...
|
by: jt |
last post by:
This is my sql statement in my vb6 code:
My date variable name is, Fax_Today_Date devired by cdate(string)
Set rs = db.OpenRecordset("SELECT * from psusa WHERE PSUSA.fax_today and...
|
by: Learner |
last post by:
Hi there,
I have installed Sql server 2005 developer on my machine which
already has a Sql server 2000 installed on. Now i am trying to query
the Sqlserver 2005 data(Ex: from Person.Address...
|
by: Rich |
last post by:
Hello,
I need to pull data from 2 tables that reside on the same sql server but 2
different databases. In Query Analyzer I can say this
select t1.*, t2.* from tbl1 t1 join database2.dbo.tbl1...
|
by: Adam G |
last post by:
Greetings. Sorry if this sounds pretty basic, but I'm having some issues with the following...
We have an e-mail list of ~2M, contained in a table. Via some marketing efforts, we've determined...
|
by: dsbsnag |
last post by:
I am using Access 2003 on Windows XP
I'm a school teacher and need help achieving the following:
I have three spreadsheets with various information in them. But, they have a common cell named...
|
by: jennwilson |
last post by:
I am a somewhat Novice Access user and have limited rather zilch programming skills.
Problem:
I am receiving the following error: "The expression you entered contains invalid syntax'
In the...
|
by: Vp |
last post by:
Dear all
I have two tables
Table T1
ID Name
1 A
2 B
3 C
4 D
5 E
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
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,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
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...
| |
