470,815 Members | 1,267 Online

# Between Date criteria problem

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

Nov 12 '05 #1
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

Nov 12 '05 #2
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

Nov 12 '05 #3
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

Nov 12 '05 #4
> 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

Nov 12 '05 #5
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

Nov 12 '05 #6
"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)
Nov 12 '05 #7
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

Nov 12 '05 #8
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)
Nov 12 '05 #9
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

Nov 12 '05 #10
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)
Nov 12 '05 #11
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

Nov 12 '05 #12

### This discussion thread is closed

Replies have been disabled for this discussion.