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 4164
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***@marleycotenospam.fsnet.co.uk> wrote in message
news:c1**********@newsg1.svr.pol.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****@ThisPartNotVailid.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***@marleycotenospam.fsnet.co.uk> wrote in message news:c1**********@newsg1.svr.pol.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***@marleycotenospam.fsnet.co.uk> wrote in message
news:c1**********@newsg4.svr.pol.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****@ThisPartNotVailid.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***@marleycotenospam.fsnet.co.uk> wrote in message news:c1**********@newsg1.svr.pol.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***@marleycotenospam.fsnet.co.uk> wrote in message
news:c1**********@newsg1.svr.pol.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**********@AlphaBetCityDataworks.com> wrote in message
news:YP********************@speakeasy.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***@marleycotenospam.fsnet.co.uk> wrote in message news:c1**********@newsg1.svr.pol.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**********@AlphaBetCityDataworks.com> wrote in
news:YP********************@speakeasy.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.nl: 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.nl:
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
Bas Cost Budde <ba*@heuveltop.org> wrote in news:c1qnib$li7$2
@news2.solcon.nl: Lyle Fairfield wrote:
Bas Cost Budde <ba*@heuveltop.org> wrote in news:c1qcn4$li7$1 @news2.solcon.nl:
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.
Better yet: yesterday never happened!
--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
Lyle Fairfield wrote: Hmm, yesterday never arrives... sounds a little like Transport Tycoon.
Better yet: yesterday never happened!
<G>
--
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...
|
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...
|
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...
|
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...
|
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...
|
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,...
|
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...
|
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...
|
by: lllomh |
last post by:
Define the method first
this.state = {
buttonBackgroundColor: 'green',
isBlinking: false, // A new status is added to identify whether the button is blinking or not
}
autoStart=()=>{
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM)
The start time is equivalent to 19:00 (7PM) in Central...
|
by: NeoPa |
last post by:
Hello everyone.
I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report).
I know it can be done by selecting :...
|
by: Teri B |
last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course.
0ne-to-many. One course many roles.
Then I created a report based on the Course form and...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM)
Please note that the UK and Europe revert to winter time on...
|
by: NeoPa |
last post by:
Introduction
For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
|
by: isladogs |
last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, Mike...
|
by: GKJR |
last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...
|
by: SueHopson |
last post by:
Hi All,
I'm trying to create a single code (run off a button that calls the Private Sub) for our parts list report that will allow the user to filter by either/both PartVendor and PartType. On...
| |