472,993 Members | 2,532 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,993 software developers and data experts.

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

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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
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...
4
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...
1
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...
1
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
8
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...
6
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...
5
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,...
13
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...
2
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...
0
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=()=>{
2
isladogs
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...
4
NeoPa
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 :...
1
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...
0
isladogs
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...
0
NeoPa
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...
0
isladogs
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...
4
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...
3
SueHopson
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.