Between Date criteria problem

[David B]

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

[Stewart Allen]

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

[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

[Stewart Allen]

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

[Jeremy Wallace]

> 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

[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

[Lyle Fairfield]

"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)

[Bas Cost Budde]

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

[Lyle Fairfield]

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)

[Bas Cost Budde]

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

[Lyle Fairfield]

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)

[Bas Cost Budde]

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