What is it with these dates? What are they so much trouble? All I want to
do is subtract an hour, but not if that makes dtmTest less than dtmClt.
Dim dtmStart As Date
Dim dtmEnd As Date
Dim dtmCtl As Date
Dim dtmTest As Date
Me!cbxEndTime.R equery
Me!cbxStartTime .Requery
dtmStart = #1/1/1000# & " " & Me!cbxStartTime
dtmEnd = #1/1/1000# & " " & Me!cbxEndTime
dtmCtl = #1/1/1000# & " " & "12:00 AM"
dtmTest = DateAdd("h", -1, dtmEnd)
Debug.Print Format((dtmStar t), "m/d/yyyy h:nn am/pm") & " = start"
Debug.Print Format((dtmEnd) , "m/d/yyyy h:nn am/pm") & " = end"
Debug.Print Format((dtmCtl) , "m/d/yyyy h:nn am/pm") & " = control"
Debug.Print Format((dtmTest ), "m/d/yyyy h:nn am/pm") & " = test"
Debug.Print
If t = "start" Then
If dtmTest < dtmCtl Then
Debug.Print Format((dtmTest ), "m/d/yyyy h:nn am/pm")
Debug.Print "is less than"
Debug.Print Format((dtmCtl) , "m/d/yyyy h:nn am/pm")
FixTime = Format(TimeValu e(dtmEnd), "h:nn am/pm")
Else
FixTime = Format(TimeValu e(DateAdd("h", -1, dtmEnd)), "h:nn am/pm")
End If
End If
[output]
1/1/1000 4:30 am = start
1/1/1000 4:00 am = end
1/1/1000 12:00 am = control
1/1/1000 3:00 am = test
1/1/1000 3:00 am
is less than
1/1/1000 12:00 am
But dtmTest is NOT less than dtmCtl.... why all the drama? Seems too high
maintenance for me.
9  2045 
On Tue, 28 Dec 2004 02:17:52 GMT, "deko"
<www.clearpoint systems.com@use _contact_form.c om> wrote:
To compare dates, use the DateDiff function.
-Tom. What is it with these dates? What are they so much trouble? All I want to
do is subtract an hour, but not if that makes dtmTest less than dtmClt.
Dim dtmStart As Date
Dim dtmEnd As Date
Dim dtmCtl As Date
Dim dtmTest As Date
Me!cbxEndTime. Requery
Me!cbxStartTim e.Requery
dtmStart = #1/1/1000# & " " & Me!cbxStartTime
dtmEnd = #1/1/1000# & " " & Me!cbxEndTime
dtmCtl = #1/1/1000# & " " & "12:00 AM"
dtmTest = DateAdd("h", -1, dtmEnd)
Debug.Print Format((dtmStar t), "m/d/yyyy h:nn am/pm") & " = start"
Debug.Print Format((dtmEnd) , "m/d/yyyy h:nn am/pm") & " = end"
Debug.Print Format((dtmCtl) , "m/d/yyyy h:nn am/pm") & " = control"
Debug.Print Format((dtmTest ), "m/d/yyyy h:nn am/pm") & " = test"
Debug.Print
If t = "start" Then
If dtmTest < dtmCtl Then
Debug.Print Format((dtmTest ), "m/d/yyyy h:nn am/pm")
Debug.Print "is less than"
Debug.Print Format((dtmCtl) , "m/d/yyyy h:nn am/pm")
FixTime = Format(TimeValu e(dtmEnd), "h:nn am/pm")
Else
FixTime = Format(TimeValu e(DateAdd("h", -1, dtmEnd)), "h:nn am/pm")
End If
End If
[output]
1/1/1000 4:30 am = start
1/1/1000 4:00 am = end
1/1/1000 12:00 am = control
1/1/1000 3:00 am = test
1/1/1000 3:00 am
is less than
1/1/1000 12:00 am
But dtmTest is NOT less than dtmCtl.... why all the drama? Seems too high
maintenance for me.
Here's output that shows how the Function is supposed to work.
1/1/1000 12:30 am = start
1/1/1000 12:00 am = end
1/1/1000 12:00 am = control
12/31/0999 11:00 pm = test
12/31/0999 11:00 pm
is less than
1/1/1000 12:00 am
The Bible says we should not be unequally yoked, so I tried converting my
Date like this:
dtmStart = CDate(#1/1/1000# & " " & Me!cbxStartTime )
dtmEnd = CDate(#1/1/1000# & " " & Me!cbxEndTime)
dtmCtl = CDate(#1/1/1000# & " " & "12:00 AM")
All to no avail. So I'm moving on to a new, younger Date:
dtmStart = CDate(DateValue (Date) & " " & Me!cbxStartTime )
dtmEnd = CDate(DateValue (Date) & " " & Me!cbxEndTime)
dtmCtl = CDate(DateValue (Date) & " " & "12:00 AM")
Now that's a thing of beauty!
Well, you may not get what you think you should when you compare the
character strings that result from a Format statement, as you were doing
earlier. It's no wonder that date comparisons work better using date/time
variables than they do using strings.
Larry Linson
Microsoft Access MVP
"deko" <www.clearpoint systems.com@use _contact_form.c om> wrote in message
news:aZ******** *********@newss vr14.news.prodi gy.com... Here's output that shows how the Function is supposed to work.
1/1/1000 12:30 am = start
1/1/1000 12:00 am = end
1/1/1000 12:00 am = control
12/31/0999 11:00 pm = test
12/31/0999 11:00 pm
is less than
1/1/1000 12:00 am
The Bible says we should not be unequally yoked, so I tried converting my
Date like this:
dtmStart = CDate(#1/1/1000# & " " & Me!cbxStartTime )
dtmEnd = CDate(#1/1/1000# & " " & Me!cbxEndTime)
dtmCtl = CDate(#1/1/1000# & " " & "12:00 AM")
All to no avail. So I'm moving on to a new, younger Date:
dtmStart = CDate(DateValue (Date) & " " & Me!cbxStartTime )
dtmEnd = CDate(DateValue (Date) & " " & Me!cbxEndTime)
dtmCtl = CDate(DateValue (Date) & " " & "12:00 AM")
Now that's a thing of beauty!
> Well, you may not get what you think you should when you compare the character strings that result from a Format statement, as you were doing
earlier. It's no wonder that date comparisons work better using date/time
variables than they do using strings.
The problem now is the date fails to roll over to the next day. My guess is
that's because the variable is built on the Date() function. Is this a
situation where I should pass in the date variables by Ref?
Dim dtmStart As Date
Dim dtmEnd As Date
Dim dtmCtl As Date
Dim dtmTest As Date
Me!cbxEndTime.R equery
Me!cbxStartTime .Requery
dtmStart = DateValue(Date) & " " & Me!cbxStartTime
dtmEnd = DateValue(Date) & " " & Me!cbxEndTime
If ...
[code omitted]
ElseIf t = "end" Then
dtmTest = DateAdd("h", 1, dtmEnd)
dtmCtl = DateValue(Date) & " " & "11:30 PM"
Debug.Print Format((dtmStar t), "m/d/yyyy h:nn am/pm") & " = start"
Debug.Print Format((dtmEnd) , "m/d/yyyy h:nn am/pm") & " = end"
Debug.Print Format((dtmCtl) , "m/d/yyyy h:nn am/pm") & " = control"
Debug.Print Format((dtmTest ), "m/d/yyyy h:nn am/pm") & " = test"
If dtmTest > dtmCtl Then
FixTime = Format(TimeValu e(dtmStart), "h:nn am/pm")
Else
FixTime = Format(TimeValu e(DateAdd("h", 1, dtmStart)), "h:nn am/pm")
End If
[output]
12/27/2004 11:30 pm = start
12/27/2004 12:30 am = end
12/27/2004 11:30 pm = control
12/27/2004 1:30 am = test
What happened is that an hour was added to the start date to get "12/27/2004
1:30 am" as a value for FixTime. The problem is that this should have met
the "dtmTest > dtmCtl" condition (i.e. 12/28/2004 1:30 am > 12/27/2004 11:30
pm) and returned the start date. Should I make another function and pass
the start and end variables in by Ref? I think VBA passes ByVal by
default - is this correct? Perhaps there is a better way around this?
Thanks for the help!
typo: What happened is that an hour was added to the start date to get
"12/27/2004 1:30 am" as a value for FixTime. The problem is that this should have met
correction:
.... to the start date to get *12/27/2004 12:30 am* as a value for FixTime.
You are relying on Access' implicit coercion of data types, and I don't know
how many are required in the expressions you show...
try using the CDat function on the start and end times, and adding the Date
and Time.
I am not sure what benefit you would get by using DateValue on the
Variant(Date) returned by the Date function. The "date" that is an argument
to DateValue is normally a String.
And, frankly, if it doesn't matter whether the date part is 1/1/1000 or the
current date, I am puzzled as to what it is you are trying to do...
Reading Help on these functions and date manipulations would be a good start
here.
Here are a couple of things I tried in the Immediate Window:
? Date() + Time()
12/28/2004 6:49:58 PM
? Date() + CDate("06:49:58 PM")
12/28/2004 6:49:58 PM
I hope someone will pick up this thread because I am not going to be
"newsgroupi ng" for several days, starting tomorrow.
Larry Linson
Microsoft Access MVP
"deko" <www.clearpoint systems.com@use _contact_form.c om> wrote in message
news:6_******** ********@newssv r13.news.prodig y.com... Well, you may not get what you think you should when you compare the
character strings that result from a Format statement, as you were doing
earlier. It's no wonder that date comparisons work better using
date/time variables than they do using strings.
The problem now is the date fails to roll over to the next day. My guess
is that's because the variable is built on the Date() function. Is this a
situation where I should pass in the date variables by Ref?
Dim dtmStart As Date
Dim dtmEnd As Date
Dim dtmCtl As Date
Dim dtmTest As Date
Me!cbxEndTime.R equery
Me!cbxStartTime .Requery
dtmStart = DateValue(Date) & " " & Me!cbxStartTime
dtmEnd = DateValue(Date) & " " & Me!cbxEndTime
If ...
[code omitted]
ElseIf t = "end" Then
dtmTest = DateAdd("h", 1, dtmEnd)
dtmCtl = DateValue(Date) & " " & "11:30 PM"
Debug.Print Format((dtmStar t), "m/d/yyyy h:nn am/pm") & " = start"
Debug.Print Format((dtmEnd) , "m/d/yyyy h:nn am/pm") & " = end"
Debug.Print Format((dtmCtl) , "m/d/yyyy h:nn am/pm") & " = control"
Debug.Print Format((dtmTest ), "m/d/yyyy h:nn am/pm") & " = test"
If dtmTest > dtmCtl Then
FixTime = Format(TimeValu e(dtmStart), "h:nn am/pm")
Else
FixTime = Format(TimeValu e(DateAdd("h", 1, dtmStart)), "h:nn
am/pm") End If
[output]
12/27/2004 11:30 pm = start
12/27/2004 12:30 am = end
12/27/2004 11:30 pm = control
12/27/2004 1:30 am = test
What happened is that an hour was added to the start date to get
"12/27/2004 1:30 am" as a value for FixTime. The problem is that this should have met
the "dtmTest > dtmCtl" condition (i.e. 12/28/2004 1:30 am > 12/27/2004
11:30 pm) and returned the start date. Should I make another function and pass
the start and end variables in by Ref? I think VBA passes ByVal by
default - is this correct? Perhaps there is a better way around this?
Thanks for the help!
> Here are a couple of things I tried in the Immediate Window:
? Date() + Time()
12/28/2004 6:49:58 PM
? Date() + CDate("06:49:58 PM")
12/28/2004 6:49:58 PM
Thanks for the reply. I'm getting along better with my date now :)
What I'm trying to do is automatically adjust time values in two combo
boxes - a start time and an end time.
If the user selects a start time that occurs after the end time, (or and end
time that occurs before the start time) the end time (or start time) is
automatically fixed by adding (or subtracting) an hour to the selected time.
But if the adjustment would cause the "fixed" time to exceed the current
day, then just "fix" it to the same value as the selected time. The times
must stay within the current day or I get a negative Duration (which is
calculated from the values in the combo boxes) that causes problems
elsewhere.
My problem was that I thought I needed a full date value for a control value
that would circumscribe a 24-hr day. I've discovered that time values work
just fine. So, I have two time values - 12:00 AM and 11:59 PM - and compare
the proposed "fixed" times to the control value and adjust from there.
Private Function FixTime(t As String) As String
Dim dtmStart As Date
Dim dtmEnd As Date
Dim dtmCtl As Date
Dim dtmTest As Date
Me!cbxEndTime.R equery
Me!cbxStartTime .Requery
dtmStart = TimeValue(Me!cb xStartTime)
dtmEnd = TimeValue(Me!cb xEndTime)
If t = "start" Then
dtmTest = DateAdd("h", -1, dtmEnd)
dtmCtl = TimeValue("12:0 0 AM")
If dtmTest < dtmCtl Then
FixTime = Format(TimeValu e(dtmEnd), "h:nn am/pm")
Else
FixTime = Format(TimeValu e(DateAdd("h", -1, dtmEnd)), "h:nn
am/pm")
End If
ElseIf t = "end" Then
dtmTest = DateAdd("h", 1, dtmStart)
dtmCtl = TimeValue("11:5 9 PM")
If dtmTest > dtmCtl Then
FixTime = Format(TimeValu e(dtmStart), "h:nn am/pm")
Else
FixTime = Format(TimeValu e(DateAdd("h", 1, dtmStart)), "h:nn
am/pm")
End If
End If
End Function
I'm not sure if I'll stick with this forever, but it's okay for now :)
One thing that might help is knowing that the date is the integer
portion, while the time is the decimal portion. So to add a day, you
can simply add 1 to your datetime value. Adding 1/24 (0.0416666666
yadah,yadah) would add one hour, adding 1/(24*60)
(0.000694444444 444) would add one minute.
ALWAYS do date/time calculations in date/time variables - it just
doesn't pay to try to deal with them otherwise. Working in date/time
and convert it at the end if you need to display it, but all
calculations and comparisions should be in date/time.
> One thing that might help is knowing that the date is the integer portion, while the time is the decimal portion. So to add a day, you
can simply add 1 to your datetime value. Adding 1/24 (0.0416666666
yadah,yadah) would add one hour, adding 1/(24*60)
(0.000694444444 444) would add one minute.
helpful tip.
ALWAYS do date/time calculations in date/time variables - it just
doesn't pay to try to deal with them otherwise. Working in date/time
and convert it at the end if you need to display it, but all
calculations and comparisions should be in date/time.
10-4 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: mike420 |
last post by:
In the context of LATEX, some Pythonista asked what the big
successes of Lisp were. I think there were at least three *big*
successes.
a. orbitz.com web site uses Lisp for algorithms, etc.
b. Yahoo store was originally written in Lisp.
c. Emacs
The issues with these will probably come up, so I might as well
mention them myself (which will also make this a more balanced
|
by: Simon Brooke |
last post by:
I'm investigating a bug a customer has reported in our database
abstraction layer, and it's making me very unhappy.
Brief summary:
I have a database abstraction layer which is intended to mediate
between webapps and arbitrary database backends using JDBC. I am very
unwilling indeed to write special-case code for particular
databases. Our code has worked satisfactorily with many databases,
including many instances MS SQLServer 2000...
|
by: Max |
last post by:
I'm new with Javascript and can't seem to figure out what I'm doing wrong
here as I'm not able to pass a simple variable to a function.
In the head of doc I have:
<script type="text/javascript">
function radioenable(value) {
document.forms.search.elements.value.disabled=false;
|
by: Eric A. Johnson |
last post by:
I am using scanf (from stdio.h) and having trouble inputting a date. I want
the user to be able to input a date in the format mm/dd/yyyy. I use the
following:
printf ("Please enter your first date in the form of mm/dd/yyyy: ");
scanf ("%i/%i/%i", &date1.month, &date1.day, &date1.year);
where date1 is a struct of type date, defined as:
struct date
{
int month;
int day;
|
by: Giordano |
last post by:
I am trying to construct a report menu that will allow the user to
select any combination of 3 variables from 3 combo boxes (Select
Subject/SelectCategory/Date Range). There are 2 possible options for
each combo ie <Allor a specific value . That means there are 8
possible outcomes from the user selection process.
The SQL for the report is constructed 'on the fly' when the report is
opened, using a series of "if" calculations in the...
| | |
by: Stan |
last post by:
I am running in ACCESS 2003 a database with a single table. It
records service rendered to clients of a food pantry. As each client
is served the date is entered into 1-6 fields SvcDate1, SvcDate2 etc.
I would like to query certain data for monthly periods. The date of
the month may appear in any one of the six fields. I now am running
the query for each of the fields separately. Is there any way I can
write a query that will respond...
|
by: numberwhun |
last post by:
Hello everyone!
I have a data file that contains miscellaneous information on each line. (Unfortunately, I cannot go into detail of the file layout as it is sensitive information), but I can say that on each line are dates in multiple positions. Some areas of a line contain multiple dates, strung together because there is no separation between the fields (ie: no space(s)):
ie: 05/26/200706/03/200707/24/2007
As you can see,...
|
by: lostdawg |
last post by:
Hi,
I am having trouble with the following query.
I need to sort from a list of contacts the last date each was contacted.
This is to be represented in days so for instance:
0-42 days since last contact I have where
|
by: Innocent2104 |
last post by:
Hi there,
The script below displays the attached output but as shown, it skips certain days and i need to include these to calculate my avg balance for a certain month, i.e.Nov. How do i update the script to include for example between 01 Nov and 05 Nov, there was no entries, therefore my balance should remain the same & display my missing dates 02,03,04 Nov??
DECLARE
@STARTDATE DATETIME,
@ENDDATE DATETIME
SET @STARTDATE = '2009-11-01' ...
|
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: 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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
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: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
