I have 2 querys with the following in the date criteria
Between DateAdd("m",-6,([Forms]![Maff count form]![1st date])) And
([Forms]![Maff count form]![1st date])
Between DateAdd("m",-24,([Forms]![Maff count form]![1st date])) And
DateAdd("m",-6,([Forms]![Maff count form]![1st date]))
Problem is they both look at the same day 6 months before the date entered. eg
date entered = 28/02/2004 then both querys pick up data from 28/08/2003
How do I alter the first line so that it would ( in the example given ) go from
29/08/2003 to 28/02/2004
TIA
David B
11  4226 
If you want to use the DateAdd function try:
DateAdd("d",-183,([Forms]![Maff count form]![1st date])) because there is
183 day difference between the 2 dates which is 6 months anyway. If you just
want to enter the date try Between #29/08/2003# And #28/02/2004#
Stewart
"David B" <Da***@marleyco tenospam.fsnet. co.uk> wrote in message
news:c1******** **@newsg1.svr.p ol.co.uk... I have 2 querys with the following in the date criteria
Between DateAdd("m",-6,([Forms]![Maff count form]![1st date])) And
([Forms]![Maff count form]![1st date])
Between DateAdd("m",-24,([Forms]![Maff count form]![1st date])) And
DateAdd("m",-6,([Forms]![Maff count form]![1st date]))
Problem is they both look at the same day 6 months before the date
entered. eg date entered = 28/02/2004 then both querys pick up data from 28/08/2003
How do I alter the first line so that it would ( in the example given ) go
from 29/08/2003 to 28/02/2004
TIA
David B
Thanks for the reply
Problem with the 183 days is another 6 month minus 1 day period might not be
that number of days
Don`t want to enter the date direct into the query. I enter one date on a form,
click a button and lots of number crunching happens (of which this is part)
DB
Stewart Allen <sa****@ThisPar tNotVailid.wave .co.nz> wrote in message
news:c1******** **@news.wave.co .nz... If you want to use the DateAdd function try:
DateAdd("d",-183,([Forms]![Maff count form]![1st date])) because there is
183 day difference between the 2 dates which is 6 months anyway. If you just
want to enter the date try Between #29/08/2003# And #28/02/2004#
Stewart
"David B" <Da***@marleyco tenospam.fsnet. co.uk> wrote in message
news:c1******** **@newsg1.svr.p ol.co.uk... I have 2 querys with the following in the date criteria
Between DateAdd("m",-6,([Forms]![Maff count form]![1st date])) And
([Forms]![Maff count form]![1st date])
Between DateAdd("m",-24,([Forms]![Maff count form]![1st date])) And
DateAdd("m",-6,([Forms]![Maff count form]![1st date]))
Problem is they both look at the same day 6 months before the date
entered. eg date entered = 28/02/2004 then both querys pick up data from 28/08/2003
How do I alter the first line so that it would ( in the example given ) go
from 29/08/2003 to 28/02/2004
TIA
David B
Hi David,
The best way to do this is to use 2 date fields on your form and use the
following for your criteria:
Between [Forms]![YourFormName]![DateFrom] And
[Forms]![YourFormName]![DateTo]
This way you can enter any dates you want into the form and use that as the
filter, no hard coding the dates into the query is then needed.
Stewart
"David B" <Da***@marleyco tenospam.fsnet. co.uk> wrote in message
news:c1******** **@newsg4.svr.p ol.co.uk... Thanks for the reply
Problem with the 183 days is another 6 month minus 1 day period might not
be that number of days
Don`t want to enter the date direct into the query. I enter one date on a
form, click a button and lots of number crunching happens (of which this is
part) DB
Stewart Allen <sa****@ThisPar tNotVailid.wave .co.nz> wrote in message
news:c1******** **@news.wave.co .nz... If you want to use the DateAdd function try:
DateAdd("d",-183,([Forms]![Maff count form]![1st date])) because there
is 183 day difference between the 2 dates which is 6 months anyway. If you
just want to enter the date try Between #29/08/2003# And #28/02/2004#
Stewart
"David B" <Da***@marleyco tenospam.fsnet. co.uk> wrote in message
news:c1******** **@newsg1.svr.p ol.co.uk... I have 2 querys with the following in the date criteria
Between DateAdd("m",-6,([Forms]![Maff count form]![1st date])) And
([Forms]![Maff count form]![1st date])
Between DateAdd("m",-24,([Forms]![Maff count form]![1st date])) And
DateAdd("m",-6,([Forms]![Maff count form]![1st date]))
Problem is they both look at the same day 6 months before the date
entered. eg date entered = 28/02/2004 then both querys pick up data from
28/08/2003
How do I alter the first line so that it would ( in the example
given ) go from 29/08/2003 to 28/02/2004
TIA
David B
> Between (DateAdd("m",-6,([Forms]![Maff count form]![1st date])) -1) And ([Forms]![Maff count form]![1st date])
if you subtract one from a date you'll just push it back one day.
--
Jeremy Wallace
AlphaBet City Dataworks
http://www.ABCDataworks.com
"David B" <Da***@marleyco tenospam.fsnet. co.uk> wrote in message
news:c1******** **@newsg1.svr.p ol.co.uk... I have 2 querys with the following in the date criteria
Between DateAdd("m",-6,([Forms]![Maff count form]![1st date])) And
([Forms]![Maff count form]![1st date])
Between DateAdd("m",-24,([Forms]![Maff count form]![1st date])) And
DateAdd("m",-6,([Forms]![Maff count form]![1st date]))
Problem is they both look at the same day 6 months before the date
entered. eg date entered = 28/02/2004 then both querys pick up data from 28/08/2003
How do I alter the first line so that it would ( in the example given ) go
from 29/08/2003 to 28/02/2004
TIA
David B
Thats `s the one, though I think I need +1
Thanks
DB
Jeremy Wallace <ab**********@A lphaBetCityData works.com> wrote in message
news:YP******** ************@sp eakeasy.net... Between (DateAdd("m",-6,([Forms]![Maff count form]![1st date])) -1) And
([Forms]![Maff count form]![1st date])
if you subtract one from a date you'll just push it back one day.
--
Jeremy Wallace
AlphaBet City Dataworks
http://www.ABCDataworks.com
"David B" <Da***@marleyco tenospam.fsnet. co.uk> wrote in message
news:c1******** **@newsg1.svr.p ol.co.uk... I have 2 querys with the following in the date criteria
Between DateAdd("m",-6,([Forms]![Maff count form]![1st date])) And
([Forms]![Maff count form]![1st date])
Between DateAdd("m",-24,([Forms]![Maff count form]![1st date])) And
DateAdd("m",-6,([Forms]![Maff count form]![1st date]))
Problem is they both look at the same day 6 months before the date
entered. eg date entered = 28/02/2004 then both querys pick up data from 28/08/2003
How do I alter the first line so that it would ( in the example given ) go
from 29/08/2003 to 28/02/2004
TIA
David B
"Jeremy Wallace" <ab**********@A lphaBetCityData works.com> wrote in
news:YP******** ************@sp eakeasy.net: Between (DateAdd("m",-6,([Forms]![Maff count form]![1st date])) -1) And
([Forms]![Maff count form]![1st date])
if you subtract one from a date you'll just push it back one day.
Not always.
--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
Lyle Fairfield wrote: if you subtract one from a date you'll just push it back one day.
Not always.
When not?
--
Bas Cost Budde http://www.heuveltop.org/BasCB
but the domain is nl
Bas Cost Budde <ba*@heuveltop. org> wrote in news:c1qcn4$li7 $1
@news2.solcon.n l: Lyle Fairfield wrote:if you subtract one from a date you'll just push it back one day.
Not always.
When not?
Sub temp()
Dim d As Date
Dim m As String
d = #12:00:00 PM#
' (1899-12-30 12:00)
m = "Before: " & Format(d, "yyyy-mm-dd hh:nn")
d = d - 1
m = m & vbNewLine & "After: " & Format(d, "yyyy-mm-dd hh:nn")
Debug.Print m
MsgBox m
'Before: 1899-12-30 12:00
'After: 1899-12-30 12:00
End Sub
--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
Lyle Fairfield wrote: Bas Cost Budde <ba*@heuveltop. org> wrote in news:c1qcn4$li7 $1
@news2.solcon.n l:
Lyle Fairfield wrote:
if you subtract one from a date you'll just push it back one day.
Not always.
When not?
Sub temp()
Dim d As Date
Dim m As String
d = #12:00:00 PM#
' (1899-12-30 12:00)
m = "Before: " & Format(d, "yyyy-mm-dd hh:nn")
d = d - 1
m = m & vbNewLine & "After: " & Format(d, "yyyy-mm-dd hh:nn")
Debug.Print m
MsgBox m
'Before: 1899-12-30 12:00
'After: 1899-12-30 12:00
End Sub
Hmm, yesterday never arrives... sounds a little like Transport Tycoon.
--
Bas Cost Budde http://www.heuveltop.org/BasCB
but the domain is nl This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: David Kuhn |
last post by:
I have a query with a date field criteria of:
Between And
When the query is run, I am asked for the Start date and then the End
Date. So far, so good.
The records returned are all those in between those dates and not
records that are on the start or the end date.
For example, I enter "09/01/03" for the start date and "09/30/03" for
|
by: Greg Iocco |
last post by:
Simple problem, but I've checked the newsgroups and couldn't find an
answer.
On the main swithboard is a command button to open a report. The
report I built is based off a query, and the query is now restricted
by the simple "Between And ". What I want to do is pass the values of the two parameters
into some textboxes in the heading of the report (so if 12/1/03 and
12/31/03 are the parameters, I can display the entered dates in the...
|
by: Jaycee66 |
last post by:
Access 2000 "Between" query question:
I am relatively new to MS Access and everytime I think I have a basic
concept of the program it pokes me in the eye with something I think
would be simple to create.
I want to create 2 separate queries for a label. I would like to have
the query prompt me for the date I choose to start from until the
present date and the other to prompt me for the date I choose to start
from and 2 additional numbers...
|
by: Alain Filiatrault |
last post by:
HI,
I'm having a problem with the following query.
Here it is in my own words :
Table :
T_Test
id (Autonumber)
dtmStartDate (Datetime) format dd-mm
|
by: Paul T. Rong |
last post by:
I used "between and " in a query as a criteria to
a field called ShipDate.
Very strange, in the case between 1.10.2003 and 31.10.2003, then the records
from 31.10.2003 don't show up. I have to modify it as between 1.10.2003 and
1.11.2003 for the records from 31.10.2003 show up.
anybody had same problem? I check the Access Help but didn't get any clue.
Thanks in advance
| | |
by: Tony Williams |
last post by:
I have a query with this Between statement as the criteria:
"Between And "
If I key in June 2002 as the first quarter date and June 2004 as the second
quarter date I get data for June 2002 upto March 2004 not June 2004. How can
I change the Between statement to include June 2004 if I enter June 2004?
TIA
Tony Williams
|
by: DW |
last post by:
I have a query in Access 2003 that has the following criteria
SELECT tblOrder.SessionDate, tblMenus.Item_Name, tblOrder.Type,
Format$(tblorder!SessionDate,"Short Time") AS SessionTime,
Sum(tblMenus.Item_Quantity) AS MenuCount INTO tblSessionQuery
FROM tblOrder, tblMenus
WHERE (((tblOrder.Order_ID)=.) AND
((Format$(!,"mm/dd/yyyy")) Between
Format$(!!,"mm/dd/yyyy") And
Format$(!!,"mm/dd/yyyy")))
|
by: Jim Armstrong |
last post by:
Hi all -
This problem has been driving me crazy, and I'm hoping the answer is
something stupid I am neglecting to see....
The procedure posted below is part of an Access/SQL database I have
developed. Basically, the user would input a beginning and ending date,
and the query goes and pulls records that meet the following criteria:
1. TradeDate is between beginning date and ending date
|
by: falroc |
last post by:
I have a data base that is used for billing. I have a form called a report selector form. Below is the code used to create the report from the user inputs on the form. What I want to do is add a way for the users to input dates and when they push the intake button they get all the date between the two dates the user/s input.
Private Sub cmdIntake_Click()
Dim strCountyName As String
Dim strAgencyStatus As String
Dim...
|
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: 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: 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: 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: 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...
| |
