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 4007
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 discussion thread is closed Replies have been disabled for this discussion. Similar topics
3 posts
views
Thread by David Kuhn |
last post: by
|
4 posts
views
Thread by Greg Iocco |
last post: by
|
1 post
views
Thread by Jaycee66 |
last post: by
|
1 post
views
Thread by Alain Filiatrault |
last post: by
|
8 posts
views
Thread by Paul T. Rong |
last post: by
|
6 posts
views
Thread by Tony Williams |
last post: by
|
5 posts
views
Thread by DW |
last post: by
|
13 posts
views
Thread by Jim Armstrong |
last post: by
| | | | | | | | | | | |