Hi,
I have some sql in a sub that finds dates in a table but only if the
date is less than today
WHERE [MonthYear] < Now()
I want it to do that but to not include the current month.
For instance, a list of dates in dd/mm/yy format
1/01/05
01/02/05
etc
etc
01/11/05
01/12/05
01/01/06
01/02/06
If I run this today (11/01/06) 01/01/06 is included in the result
because it is less than today.
But I dont want the current month included.
Any ideas.
Bob 24 2441
"Bob Wickham" <wi*********@ya hoo.com.au> wrote in message
news:43******@d news.tpgi.com.a u... Hi,
I have some sql in a sub that finds dates in a table but only if the date is less than today
WHERE [MonthYear] < Now()
I want it to do that but to not include the current month.
For instance, a list of dates in dd/mm/yy format 1/01/05 01/02/05 etc etc 01/11/05 01/12/05 01/01/06 01/02/06
If I run this today (11/01/06) 01/01/06 is included in the result because it is less than today. But I dont want the current month included.
Any ideas.
Bob
So you want to include dates less than the first of the current month?
You can use the DateSerial function:
....WHERE MyDate<DateSeri al(Year(Date),M onth(Date),1)
I'm assuming that your field [MonthYear] is an actual DateTime field
"Bob Wickham" <wi*********@ya hoo.com.au> schreef in bericht news:43******@d news.tpgi.com.a u... Hi, I have some sql in a sub that finds dates in a table but only if the date is less than today WHERE [MonthYear] < Now() I want it to do that but to not include the current month. For instance, a list of dates in dd/mm/yy format 1/01/05 01/02/05 etc etc 01/11/05 01/12/05 01/01/06 01/02/06 If I run this today (11/01/06) 01/01/06 is included in the result because it is less than today. But I dont want the current month included. Any ideas. Bob
So you want to select all dates less than the first day of the current month?
WHERE DateField < DateSerial(Year (Now), Month(Now), 1)
Arno R
"Arno R" <ar***********@ tiscali.nl> wrote in message
news:43******** *************** @text.nova.plan et.nl...
"Bob Wickham" <wi*********@ya hoo.com.au> schreef in bericht
news:43******@d news.tpgi.com.a u... Hi,
I have some sql in a sub that finds dates in a table but only if the date is less than today
WHERE [MonthYear] < Now()
I want it to do that but to not include the current month.
For instance, a list of dates in dd/mm/yy format 1/01/05 01/02/05 etc etc 01/11/05 01/12/05 01/01/06 01/02/06
If I run this today (11/01/06) 01/01/06 is included in the result because it is less than today. But I dont want the current month included.
Any ideas.
Bob
So you want to select all dates less than the first day of the current
month?
WHERE DateField < DateSerial(Year (Now), Month(Now), 1)
Arno R
Hey! Are you copying my answers?
Arno R wrote: "Bob Wickham" <wi*********@ya hoo.com.au> schreef in bericht news:43******@d news.tpgi.com.a u...
Hi,
I have some sql in a sub that finds dates in a table but only if the date is less than today
WHERE [MonthYear] < Now()
I want it to do that but to not include the current month.
For instance, a list of dates in dd/mm/yy format 1/01/05 01/02/05 etc etc 01/11/05 01/12/05 01/01/06 01/02/06
If I run this today (11/01/06) 01/01/06 is included in the result because it is less than today. But I dont want the current month included.
Any ideas.
Bob
So you want to select all dates less than the first day of the current month?
WHERE DateField < DateSerial(Year (Now), Month(Now), 1)
Arno R
Thats it, perfect.
Thankyou to you both.
Bob
"Anthony England" <ae******@oops. co.uk> wrote in message
news:dq******** **@nwrdmz03.dmz .ncs.ea.ibs-infra.bt.com... "Arno R" <ar***********@ tiscali.nl> wrote in message news:43******** *************** @text.nova.plan et.nl... "Bob Wickham" <wi*********@ya hoo.com.au> schreef in bericht news:43******@d news.tpgi.com.a u... Hi,
I have some sql in a sub that finds dates in a table but only if the date is less than today
WHERE [MonthYear] < Now()
I want it to do that but to not include the current month.
For instance, a list of dates in dd/mm/yy format 1/01/05 01/02/05 etc etc 01/11/05 01/12/05 01/01/06 01/02/06
If I run this today (11/01/06) 01/01/06 is included in the result because it is less than today. But I dont want the current month included.
Any ideas.
Bob
So you want to select all dates less than the first day of the current month?
WHERE DateField < DateSerial(Year (Now), Month(Now), 1)
Arno R
Hey! Are you copying my answers?
Based on the time stamps on your posts, it was a dead heat. :-{)
Bob,
Just a quick FYI.
I see that you got your answer and it will take care of the problem I'm
about to mention also. The problem with the original code is that not only
would it have included dates from earlier in the month, it would also have
included dates from the current date. You were comparing to Now() which is
both date and time. If the values you were comparing to this were from the
current day but with an earlier time, they would have been included also. A
date with no time component (i.e. time component is zero) will be treated as
midnight. So any date without a time component that was from the current
date would have been less than Now() unless you happened to catch it just a
midnight.
The reason this happens is because VBA treats dates as a floating point
number. The integer part is the date and the decimal part is the time of
day. The integer portion gives the number of days since 30 Dec 1899 and the
decimal portion give the time as fractions of a day (.0 = midnight, .25 =
6am, .5 = noon, etc). So, a date with no time component would have a decimal
portion of zero (i.e. midnight). If all you want is the date component, I
recommend using the Date() function instead of the Now() function.
--
Wayne Morgan
MS Access MVP
"Bob Wickham" <wi*********@ya hoo.com.au> wrote in message
news:43******@d news.tpgi.com.a u... Hi,
I have some sql in a sub that finds dates in a table but only if the date is less than today
WHERE [MonthYear] < Now()
I want it to do that but to not include the current month.
For instance, a list of dates in dd/mm/yy format 1/01/05 01/02/05 etc etc 01/11/05 01/12/05 01/01/06 01/02/06
If I run this today (11/01/06) 01/01/06 is included in the result because it is less than today. But I dont want the current month included.
Any ideas.
Bob
"Anthony England" <ae******@oops. co.uk> schreef in bericht news:dq******** **@nwrdmz03.dmz .ncs.ea.ibs-infra.bt.com... Hey! Are you copying my answers?
Funny this is:
Your Timestamp: Date: Tue, 10 Jan 2006 23:50:50 +0000 (UTC)
My Timestamp: Date: Wed, 11 Jan 2006 00:50:50 +0100
So there is not even a second difference here ...
both almost identical answers ...
Are you *my* evil twin ??
I am looking for one ;-)
Btw:
According to Google this means for both of us:
Local: Wed, Jan 11 2006 12:50 am ??
Arno R
Wayne Morgan wrote: Bob,
Just a quick FYI.
I see that you got your answer and it will take care of the problem I'm about to mention also. The problem with the original code is that not only would it have included dates from earlier in the month, it would also have included dates from the current date. You were comparing to Now() which is both date and time. If the values you were comparing to this were from the current day but with an earlier time, they would have been included also. A date with no time component (i.e. time component is zero) will be treated as midnight. So any date without a time component that was from the current date would have been less than Now() unless you happened to catch it just a midnight.
The reason this happens is because VBA treats dates as a floating point number. The integer part is the date and the decimal part is the time of day. The integer portion gives the number of days since 30 Dec 1899 and the decimal portion give the time as fractions of a day (.0 = midnight, .25 = 6am, .5 = noon, etc). So, a date with no time component would have a decimal portion of zero (i.e. midnight). If all you want is the date component, I recommend using the Date() function instead of the Now() function.
Thanks Wayne,
I understand your advice but, in practise, I can't get it to work.
Should I be able to simply change Now to Date in the solution offered by
Arno and Anthony
WHERE DateField < DateSerial(Year (Now), Month(Now), 1)
like
WHERE DateField < DateSerial(Year (Date), Month(Date), 1)
because doing that results in a "Too few parameters error"
Bob
Bob:
If you're using a European version, I think you need to use ; instead
of , as the parameter separator. (Not 100% sure, though!)
Try WHERE DateField < DateSerial(Year (Date); Month(Date); 1)
HTH,
Jana This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Jeffrey Melloy |
last post by:
I was recently running into performance problems with a query
containing now()::date or CURRENT_DATE. When I went to debug,
'now'::date made efficient use of the index (on a timestamp field).
The docs say that 'now' is turned into a constant right away. Is this
overhead/poor planning simply because 'now' gets converted to a
constant so much earlier in the process?
I've pasted the query plans below.
|
by: Willie jan |
last post by:
place this behind a button that fills a listbox.
as you will see the time is now and then 0 or filled in????????????
by hitting the button.
is there a way to determine the real elapsed time?
thanks, Willie
Dim T As Double
|
by: Rob R. Ainscough |
last post by:
VS 2005 I have:
ClickOnce deployment
User's that hate and or don't want to use an IE Client (don't blame them)
I don't see how ASPX web pages are going to survive? With .NET 2.0 and
clickonce deployment my app is 427KB (even with modem dialup speed it
doesn't take long to download) -- the user gets a very friendly secure
WindowsForm app (most of them don't even notice they're not under IE
anymore) that performs considerably faster than...
|
by: archana |
last post by:
Hi all,
I am facing some wired problem while using above mention data type.
What i am doing is i am writing
DateTime.Now.Hour.ToString() + ":" + DateTime.Now.Minute.ToString() +
":" + DateTime.Now.Second.ToString() +
":"+DateTime.Now.Millisecond.ToString()
to the file.
|
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: 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: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
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: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
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...
| |