Criteria for date

  • Thread starter Thread starter Robin Chapple
  • Start date Start date
R

Robin Chapple

I have a report which covers a financial year and I need to prepare a
report covering the previous six weeks activity. This was
straightforward in the first year when I used:

<Now()-45

but now I need to exclude data before 1st July each year.

I have tried this which is a failure.
#1/07/2005# And <Now()-45
Click to expand...

How do I overcome my problem?

Robin Chapple
 
I have a report which covers a financial year and I need to prepare a
report covering the previous six weeks activity. This was
straightforward in the first year when I used:

<Now()-45

but now I need to exclude data before 1st July each year.

I have tried this which is a failure.


How do I overcome my problem?

Robin Chapple
Click to expand...

I'd suggest a criterion of

Datefield >= IIf(Date() > DateSerial(Year(Date()), 7, 45),
DateAdd("d", -45, Date()), DateSerial(Year(Date()), 7, 1))
AND Datefield <= Date()


John W. Vinson[MVP]
 
I'd suggest a criterion of

Datefield >= IIf(Date() > DateSerial(Year(Date()), 7, 45),
DateAdd("d", -45, Date()), DateSerial(Year(Date()), 7, 1))
AND Datefield <= Date()

John W. Vinson[MVP]
Click to expand...

John,
Is this correct?
 
I have a report which covers a financial year and I need to prepare a
report covering the previous six weeks activity. This was
straightforward in the first year when I used:

<Now()-45

but now I need to exclude data before 1st July each year.

I have tried this which is a failure.


How do I overcome my problem?

Robin Chapple
Click to expand...

In addition to what John Vinson posted, I'd recommend that you always
use Date() instead of Now(), in your table field as well as in your
query, unless the time of day is needed.
Using Now()-45 will return different records depending upon the time
of day you run the query. I don't think that is what you really want.
Notice that John used Date().
 
I'd suggest a criterion of

Datefield >= IIf(Date() > DateSerial(Year(Date()), 7, 45),
DateAdd("d", -45, Date()), DateSerial(Year(Date()), 7, 1))
AND Datefield <= Date()


John W. Vinson[MVP]
Click to expand...
I failed to confirm that the code is being prepared for the criteria
of a query since this is a query newsgroup.

There are 31 records that are dated after 30th June 2005.

I made a single line of the code and the query returned no records.

I experimented and many options returned no records.

This version returns the first record only:

IIf(Date()>DateSerial(Year(Date()),7,45),DateAdd("d",-45,Date()),DateSerial(Year(Date()),7,1))
And <=Date()

I must be close.(I hope)

Thanks,

Robin
 
IIf(Date()>DateSerial(Year(Date()),7,45),DateAdd("d",-45,Date()),DateSerial(Year(Date()),7,1))
And <=Date()

I must be close.(I hope)
Click to expand...

Try putting the various expressions into vacant Field cells just to
see what date is being returned:

DateSerial(Year(Date()),7,45)
DateAdd("d",-45,Date())
IIf(Date()>DateSerial(Year(Date()),7,45),DateAdd("d",-45,Date()),DateSerial(Year(Date()),7,1))

If you manually enter these dates into the >= and <= criteria what do
you get?

John W. Vinson[MVP]
 
Yep. That's 45 days after July 1 during the current year.
Click to expand...

John,

I did not express myself clearly enough. My Apology. I need records
that are in this fiscal year, (after 30th June 2005 and every year if
it can be fixed), but are from the recent 45 days. So that in the
middle of October I get records from 1st September.

Thanks,
Robin
 
Try putting the various expressions into vacant Field cells just to
see what date is being returned:

DateSerial(Year(Date()),7,45)
DateAdd("d",-45,Date())
IIf(Date()>DateSerial(Year(Date()),7,45),DateAdd("d",-45,Date()),DateSerial(Year(Date()),7,1))

If you manually enter these dates into the >= and <= criteria what do
you get?
Click to expand...
i) 13/06/2005

ii) 14/08/2005

iii) 1/07/2005

The manual advice I don't understand.

Robin Chapple

My guess is >=13/06/2005 and <=14/08/2005

which I did not try because it is not what is intended.

Thanks,

Robin
 
Back
Top