Today's date in previous years

[Michael Iannantuoni]

Hi,

I am fairly new to access but want to set up a field in a table that,
using the date(in UK format of dd/mm/yyyy) from another field in the
sane table, allows me to create something that can be used to make an
index such that records are listed to show those that were created on
the same day in previous years ie today's would show all March 1st
entries for 2008, 2007, 2006 etc

Can anyone help with how this can be done.

TIA

Michael

 

[Jeff Boyce]

Michael

Don't bother creating a field to do that.

Use a query against the data you already have.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Michael Iannantuoni]

Jeff,

What would I put in the Query?

To expound on my initial question I really want the option of listing
all of my records as follows:

those from today 2008,
those from today 2007,
those from today 2006,
.. . .. etc,
those from yesterday 2008,
those from yesterday 2007,
those from yesterday 2006,
. . . . etc,
those from the day before yesterday 2008,
those from the day before yesterday 2007,
those from the day before yesterday 2006,
. . . . etc,

Sorry if I didn't make that clear initially.

Michael

Michael

Don't bother creating a field to do that.

Use a query against the data you already have.

-- Regards Jeff Boyce
www.InformationFutures.net Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/ "Michael Iannantuoni"

 

[John W. Vinson]

Hi,

I am fairly new to access but want to set up a field in a table that,
using the date(in UK format of dd/mm/yyyy) from another field in the
sane table, allows me to create something that can be used to make an
index such that records are listed to show those that were created on
the same day in previous years ie today's would show all March 1st
entries for 2008, 2007, 2006 etc

Can anyone help with how this can be done.

TIA

Michael

Create a Query based on your table. Add three new calculated fields by typing

HistYear: Year([datefield])
HistMonth: Month([datefield])
HistDay: Day([datefield])

Put a criterion on HistMonth of

Month(Date())

and under HistDay of

Day([datefield])

Select whicever other fields you want to see.

You won't have seen very many records yesterday (2/29) of course!

 

[Michael Iannantuoni]

Thanks for that John,

Maybe I haven't installed something or am looking in the wrong place,
but I don't seen to be able to find a Glossary of Functions &
Expressions anywhere. Can you point me in the right direction at all please?

Michael

Hi,

I am fairly new to access but want to set up a field in a table that,
using the date(in UK format of dd/mm/yyyy) from another field in the
sane table, allows me to create something that can be used to make an
index such that records are listed to show those that were created on
the same day in previous years ie today's would show all March 1st
entries for 2008, 2007, 2006 etc

Can anyone help with how this can be done.

TIA

Michael

Create a Query based on your table. Add three new calculated fields by typing

HistYear: Year([datefield])
HistMonth: Month([datefield])
HistDay: Day([datefield])

Put a criterion on HistMonth of

Month(Date())

and under HistDay of

Day([datefield])

Select whicever other fields you want to see.

You won't have seen very many records yesterday (2/29) of course!

 

[Jeff Boyce]

Try Access HELP and search on one of those John mentioned...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Thanks for that John,

Maybe I haven't installed something or am looking in the wrong place,
but I don't seen to be able to find a Glossary of Functions &
Expressions anywhere. Can you point me in the right direction at all please?

Michael

Hi,

I am fairly new to access but want to set up a field in a table that,
using the date(in UK format of dd/mm/yyyy) from another field in the
sane table, allows me to create something that can be used to make an
index such that records are listed to show those that were created on
the same day in previous years ie today's would show all March 1st
entries for 2008, 2007, 2006 etc

Can anyone help with how this can be done.

TIA

Michael

Create a Query based on your table. Add three new calculated fields by typing

HistYear: Year([datefield])
HistMonth: Month([datefield])
HistDay: Day([datefield])

Put a criterion on HistMonth of

Month(Date())

and under HistDay of

Day([datefield])

Select whicever other fields you want to see.

You won't have seen very many records yesterday (2/29) of course!

 

[John W. Vinson]

Maybe I haven't installed something or am looking in the wrong place,
but I don't seen to be able to find a Glossary of Functions &
Expressions anywhere. Can you point me in the right direction at all please?

You'll need to open the VBA editor before searching for help - that gives you
a different (and appropriate for that search) Help file.

 

[Michael Iannantuoni]

So that's where they were!!

Having found them and experimented with Str$ for a while, Format did
what I wanted.

Creating the expression Format([DateField],"mm/dd/yyyy") in a query and
sorting on it achieved what I wanted.

A variation on that, in another query, was to add a Criteria of
Format(Date(), "mm/dd/") + Format([DateField]) this just displayed
records on **this day** (March 3rd) in previous years.

Do you know of good book that deals with these VBA functions?

Thanks again,

Michael