DateAdd problem

[WhytheQ]

I'm using the following criteria in my query:

=DateAdd("d",-60,Date())

....basically returns a date 60 days before today. Really I want it to
return the date 60 days before the highest(max) date in the date field,
which may not be the same as today if the database hasn't been updated.
HELP!!

Any help greatly appreciated
Jason.

 

[Allen Browne]

Try:

=DateAdd("d", -60,Nz((SELECT Max([MyDate]) FROM [MyTable] As Dupe),

Date())

Replace MyDate with the name of the date field, and MyTable with the name of
the table.

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

 

[WhytheQ]

Nice one Allen: I'll give that a go.
I'm new to Access/SQL so I'll have to look into this Nz function you've
used - looks like it might be something similar to Indirect used in
Excel.

Thanks again
Jason

Try:

=DateAdd("d", -60,Nz((SELECT Max([MyDate]) FROM [MyTable] As Dupe),

Date())

Replace MyDate with the name of the date field, and MyTable with the name of
the table.

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I'm using the following criteria in my query:


...basically returns a date 60 days before today. Really I want it to
return the date 60 days before the highest(max) date in the date field,
which may not be the same as today if the database hasn't been updated.
HELP!!

Any help greatly appreciated
Jason.

 

[Allen Browne]

Nz() test if the value is Null.
If so, it replaces the Null with the 2nd argument.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Nice one Allen: I'll give that a go.
I'm new to Access/SQL so I'll have to look into this Nz function you've
used - looks like it might be something similar to Indirect used in
Excel.

Thanks again
Jason

Try:

=DateAdd("d", -60,Nz((SELECT Max([MyDate]) FROM [MyTable] As Dupe),

Date())

Replace MyDate with the name of the date field, and MyTable with the name
of
the table.

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

I'm using the following criteria in my query:

=DateAdd("d",-60,Date())

...basically returns a date 60 days before today. Really I want it to
return the date 60 days before the highest(max) date in the date field,
which may not be the same as today if the database hasn't been updated.
HELP!!

 

[WhytheQ]

ok I've added in the criteria and it looks like the below:

=DateAdd("d", -60,Nz((SELECT Max([Date]) FROM [Financial Data] As Dupe),Date())

Where the table is 'Financial data' and the Field is 'Date': I get a
warning about invalid syntax when I try to enter this criteria.

Any help greatly apprecaited.
Jason.

Nz() test if the value is Null.
If so, it replaces the Null with the 2nd argument.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Nice one Allen: I'll give that a go.
I'm new to Access/SQL so I'll have to look into this Nz function you've
used - looks like it might be something similar to Indirect used in
Excel.

Thanks again
Jason

Try:
=DateAdd("d", -60,Nz((SELECT Max([MyDate]) FROM [MyTable] As Dupe),
Date())

Replace MyDate with the name of the date field, and MyTable with the name
of
the table.

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

I'm using the following criteria in my query:

=DateAdd("d",-60,Date())

...basically returns a date 60 days before today. Really I want it to
return the date 60 days before the highest(max) date in the date field,
which may not be the same as today if the database hasn't been updated.
HELP!!

 

[Allen Browne]

Okay, lets break it down.

Remove the criteria for now.
Instead type this into a fresh column in the Field row:
(SELECT Max(Dupe.[Date]) FROM [Financial Data] As Dupe)

Does that work? Showing the most recent date? An alternative expression:
(SELECT TOP 1 Dupe.[Date]
FROM [Financial Data] As Dupe
ORDER BY Dupe.[Date] DESC, Dupe.[ID] DESC)
where you need to replace [ID] with the name of your primary key field.

Once you have that expression working, get the DateAdd() working as well
(still in the Field row):
DateAdd("d", -60, Nz(xxx, Date()))
where the xxx represents the entire expression you got working.

Once that's working, you can move the expression back into the Criteria row
again.

(You should also be aware thate Date is a reserved word in JET and in VBA,
so not a good name for a field.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

ok I've added in the criteria and it looks like the below:

=DateAdd("d", -60,Nz((SELECT Max([Date]) FROM [Financial Data] As
Dupe),Date())

Where the table is 'Financial data' and the Field is 'Date': I get a
warning about invalid syntax when I try to enter this criteria.

Nz() test if the value is Null.
If so, it replaces the Null with the 2nd argument.

Nice one Allen: I'll give that a go.
I'm new to Access/SQL so I'll have to look into this Nz function you've
used - looks like it might be something similar to Indirect used in
Excel.

Allen Browne wrote:
Try:
=DateAdd("d", -60,Nz((SELECT Max([MyDate]) FROM [MyTable] As
Dupe),
Date())

Replace MyDate with the name of the date field, and MyTable with the
name
of
the table.

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

I'm using the following criteria in my query:

=DateAdd("d",-60,Date())

...basically returns a date 60 days before today. Really I want it
to
return the date 60 days before the highest(max) date in the date
field,
which may not be the same as today if the database hasn't been
updated.
HELP!!

 

[WhytheQ]

Allen: I've just renamed the field to DataDates and the criteria:

=DateAdd("d",-60,Nz((SELECT Max([DataDates]) FROM [Financial Data] As

Dupe),Date()))

......now works!! a good lesson has been learned in field naming.

Thanks for stopping my head hitting the proverbial brickwall.
Jason.

Okay, lets break it down.

Remove the criteria for now.
Instead type this into a fresh column in the Field row:
(SELECT Max(Dupe.[Date]) FROM [Financial Data] As Dupe)

Does that work? Showing the most recent date? An alternative expression:
(SELECT TOP 1 Dupe.[Date]
FROM [Financial Data] As Dupe
ORDER BY Dupe.[Date] DESC, Dupe.[ID] DESC)
where you need to replace [ID] with the name of your primary key field.

Once you have that expression working, get the DateAdd() working as well
(still in the Field row):
DateAdd("d", -60, Nz(xxx, Date()))
where the xxx represents the entire expression you got working.

Once that's working, you can move the expression back into the Criteria row
again.

(You should also be aware thate Date is a reserved word in JET and in VBA,
so not a good name for a field.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

ok I've added in the criteria and it looks like the below:

=DateAdd("d", -60,Nz((SELECT Max([Date]) FROM [Financial Data] As
Dupe),Date())

Where the table is 'Financial data' and the Field is 'Date': I get a
warning about invalid syntax when I try to enter this criteria.

Nz() test if the value is Null.
If so, it replaces the Null with the 2nd argument.

Nice one Allen: I'll give that a go.
I'm new to Access/SQL so I'll have to look into this Nz function you've
used - looks like it might be something similar to Indirect used in
Excel.

Allen Browne wrote:
Try:
=DateAdd("d", -60,Nz((SELECT Max([MyDate]) FROM [MyTable] As
Dupe),
Date())

Replace MyDate with the name of the date field, and MyTable with the
name
of
the table.

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

I'm using the following criteria in my query:

=DateAdd("d",-60,Date())

...basically returns a date 60 days before today. Really I want it
to
return the date 60 days before the highest(max) date in the date
field,
which may not be the same as today if the database hasn't been
updated.
HELP!!

 

[Allen Browne]

Excellent!

Date and Name are probably the most common field names that give trouble,
but there's more than 1000 that can give you grief, including, Width, Form,
Year, Height, Month, and so on. The ones I know of are listed here:
Problem names and reserved words
at:
http://allenbrowne.com/AppIssueBadWord.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen: I've just renamed the field to DataDates and the criteria:

=DateAdd("d",-60,Nz((SELECT Max([DataDates]) FROM [Financial Data] As

Dupe),Date()))

.....now works!! a good lesson has been learned in field naming.

Thanks for stopping my head hitting the proverbial brickwall.
Jason.

 

[WhytheQ]

Hi Allen,
Thanks for all the help.
Can you recommend any online Access courses for setting up intermediate
stuff i.e I know the basics of Access but need a little kick-start to
move me up a gear. I'm especially keen on setting up queries where I
can extract the results into Excel.
(I've been mucking around with the Excel/Outlook/Word object models for
nearly 5 years now)

Regards
Jason

Excellent!

Date and Name are probably the most common field names that give trouble,
but there's more than 1000 that can give you grief, including, Width, Form,
Year, Height, Month, and so on. The ones I know of are listed here:
Problem names and reserved words
at:
http://allenbrowne.com/AppIssueBadWord.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen: I've just renamed the field to DataDates and the criteria:

=DateAdd("d",-60,Nz((SELECT Max([DataDates]) FROM [Financial Data] As

Dupe),Date()))

.....now works!! a good lesson has been learned in field naming.

Thanks for stopping my head hitting the proverbial brickwall.
Jason.

 

[Allen Browne]

The resources I know of are listed at the website below.
Not sure if anything is specific to what you need.

Perhaps someone else can add suggestions too.