Next most recent date

[BJC]

I recently posted a question to find out how
I would get the most recent date in a query.
Ken Snell was kind enough to refer me to the
DMAX function. Thank You Very Much Ken.
This was his example & it served me well...
DMax("Date", "TableName", "[City]='" & [Enter City Name:] & "'")

Now I would like to get the next most recent date.
In other words the date previous to the most Recent date.
can anyone offer a suggestion.
I can't seem to get the DMAX function to work this out.

Thanks in advance.
Brad

 

[Ken Snell]

Try this:

DMax("Date", "TableName", "[City]='" & [Enter City Name:] & "' And [Date]
<>" & DMax("Date", "TableName", "[City]='" & [Enter City Name:] & "'"))

I haven't tested this, so it's possible that you'll need to delimit the
second DMax function with # characters:

DMax("Date", "TableName", "[City]='" & [Enter City Name:] & "' And [Date]
<>#" & DMax("Date", "TableName", "[City]='" & [Enter City Name:] & "'") &
"#")

Post back if neither one works...in fact, post back if one or both work
(that'll help me learn!).

 

[BJC]

Thanks for the quick response KEN
Unfortunately neither work. I get a Data type mismatch on both.
FYI: Here is what I'm trying to do;
Get the record for the last date which will include a field of Tickets Sold
for a given city.
Then get the record for the Previous Date which will also include Tickets
Sold.
Then determine the difference of Tickets Sold between the two Dates.
This will be an undelying query for a report.
The DMAX function works great to get the Last date.
But I'm at a loss as to how I get the previous to last date.
Maybe the Dmax function isn't the way to go??.... i have no idea.
Thanks for any advice.
Brad

Try this:

DMax("Date", "TableName", "[City]='" & [Enter City Name:] & "' And [Date]
<>" & DMax("Date", "TableName", "[City]='" & [Enter City Name:] & "'"))

I haven't tested this, so it's possible that you'll need to delimit the
second DMax function with # characters:

DMax("Date", "TableName", "[City]='" & [Enter City Name:] & "' And [Date]
<>#" & DMax("Date", "TableName", "[City]='" & [Enter City Name:] & "'") &
"#")

Post back if neither one works...in fact, post back if one or both work
(that'll help me learn!).

--
Ken Snell
<MS ACCESS MVP>

I recently posted a question to find out how
I would get the most recent date in a query.
Ken Snell was kind enough to refer me to the
DMAX function. Thank You Very Much Ken.
This was his example & it served me well...
DMax("Date", "TableName", "[City]='" & [Enter City Name:] & "'")

Now I would like to get the next most recent date.
In other words the date previous to the most Recent date.
can anyone offer a suggestion.
I can't seem to get the DMAX function to work this out.

Thanks in advance.
Brad

 

[BJC]

Just a thought Ken,
Is there a some way to do a DMAX -1
I've tried various syntax and can't get it to work.

Try this:

DMax("Date", "TableName", "[City]='" & [Enter City Name:] & "' And [Date]
<>" & DMax("Date", "TableName", "[City]='" & [Enter City Name:] & "'"))

I haven't tested this, so it's possible that you'll need to delimit the
second DMax function with # characters:

DMax("Date", "TableName", "[City]='" & [Enter City Name:] & "' And [Date]
<>#" & DMax("Date", "TableName", "[City]='" & [Enter City Name:] & "'") &
"#")

Post back if neither one works...in fact, post back if one or both work
(that'll help me learn!).

--
Ken Snell
<MS ACCESS MVP>

I recently posted a question to find out how
I would get the most recent date in a query.
Ken Snell was kind enough to refer me to the
DMAX function. Thank You Very Much Ken.
This was his example & it served me well...
DMax("Date", "TableName", "[City]='" & [Enter City Name:] & "'")

Now I would like to get the next most recent date.
In other words the date previous to the most Recent date.
can anyone offer a suggestion.
I can't seem to get the DMAX function to work this out.

Thanks in advance.
Brad

 

[Ken Snell]

OK - I have been successful in a test with the following type of expression:

DMax("Date", "TableName", "[City]='" & [Enter City Name:] & "' And [Date]
<> DMax(""Date"", ""TableName"", ""[City]='"" & [Enter City Name:] &
""'"")")

Note the doubling up of " characters in the second DMax expression.

--
Ken Snell
<MS ACCESS MVP>

Thanks for the quick response KEN
Unfortunately neither work. I get a Data type mismatch on both.
FYI: Here is what I'm trying to do;
Get the record for the last date which will include a field of Tickets Sold
for a given city.
Then get the record for the Previous Date which will also include Tickets
Sold.
Then determine the difference of Tickets Sold between the two Dates.
This will be an undelying query for a report.
The DMAX function works great to get the Last date.
But I'm at a loss as to how I get the previous to last date.
Maybe the Dmax function isn't the way to go??.... i have no idea.
Thanks for any advice.
Brad

Try this:

DMax("Date", "TableName", "[City]='" & [Enter City Name:] & "' And [Date]
<>" & DMax("Date", "TableName", "[City]='" & [Enter City Name:] & "'"))

I haven't tested this, so it's possible that you'll need to delimit the
second DMax function with # characters:

DMax("Date", "TableName", "[City]='" & [Enter City Name:] & "' And [Date]
<>#" & DMax("Date", "TableName", "[City]='" & [Enter City Name:] & "'") &
"#")

Post back if neither one works...in fact, post back if one or both work
(that'll help me learn!).

--
Ken Snell
<MS ACCESS MVP>

I recently posted a question to find out how
I would get the most recent date in a query.
Ken Snell was kind enough to refer me to the
DMAX function. Thank You Very Much Ken.
This was his example & it served me well...
DMax("Date", "TableName", "[City]='" & [Enter City Name:] & "'")

Now I would like to get the next most recent date.
In other words the date previous to the most Recent date.
can anyone offer a suggestion.
I can't seem to get the DMAX function to work this out.

Thanks in advance.
Brad

 

[Ken Snell]

This is another way to get at the desired result, and there are other ways
to use queries to do this as well (including the use of a subquery to get
the max date and then exclude that from the records selected).

But it is possible to do it via nested DMax functions as well, which may be
the better choice in some situations. All depends upon the setup.

--
Ken Snell
<MS ACCESS MVP>

The answer needs DAO programming skills in vba. Examples
are available in the help files for RecordSet and
QueryDef objects.

The solution is to write a query that orders your result
set by the date field - descending - and set the "Top 2"
clause (see help for details on setting the "top"
property in a query).

That will give you both values in a single recordset, the
most recent date in the first record and the next most
recent date in the second record. You will have to write
vba routines to extract the appropriate value and then
feed those into your final calculation query to get the
ticket sales quantities.

-----Original Message-----
Thanks for the quick response KEN
Unfortunately neither work. I get a Data type mismatch on both.
FYI: Here is what I'm trying to do;
Get the record for the last date which will include a field of Tickets Sold
for a given city.
Then get the record for the Previous Date which will also include Tickets
Sold.
Then determine the difference of Tickets Sold between the two Dates.
This will be an undelying query for a report.
The DMAX function works great to get the Last date.
But I'm at a loss as to how I get the previous to last date.
Maybe the Dmax function isn't the way to go??.... i have no idea.
Thanks for any advice.
Brad

Try this:

DMax("Date", "TableName", "[City]='" & [Enter City Name:] & "' And [Date]
<>" & DMax("Date", "TableName", "[City]='" & [Enter City Name:] & "'"))

I haven't tested this, so it's possible that you'll need to delimit the
second DMax function with # characters:

DMax("Date", "TableName", "[City]='" & [Enter City Name:] & "' And [Date]
<>#" & DMax("Date", "TableName", "[City]='" & [Enter City Name:] & "'") &
"#")

Post back if neither one works...in fact, post back if one or both work
(that'll help me learn!).

--
Ken Snell
<MS ACCESS MVP>


I recently posted a question to find out how
I would get the most recent date in a query.
Ken Snell was kind enough to refer me to the
DMAX function. Thank You Very Much Ken.
This was his example & it served me well...
DMax("Date", "TableName", "[City]='" & [Enter City Name:] & "'")

Now I would like to get the next most recent date.
In other words the date previous to the most Recent date.
can anyone offer a suggestion.
I can't seem to get the DMAX function to work this out.

Thanks in advance.
Brad

.

 

[Ken Snell]

I've put some queries in the db and have emailed it to you at your email
address. These queries are quick and dirty (I didn't have time to put
together anything fancy), but will give you an idea of how to approach this.

I'm sure these queries can be improved.

--
Ken Snell
<MS ACCESS MVP>

Thanks for the continued support.
But Still not getting what I need.
Correct me if I'm wrong Ken; but doesn't the second DMAX
with the 'Not Equal To' operator give me
EVERY DATE EXCEPT the last date.

Let me give you some more details about what I'm trying to do.
I have a table called "Ticket Counts".
In this table we enter the number of tickets sold for each city
on each date along with costs etc...
We want to know the difference of tickets sold
between the LASTdate that tickets were sold
and the PREVIOUS date for each city.

I've included a link to to a small zipped mdb file
Feel free to look at it if your so inclined.
It includes sample info that I am currently testing.
http://www.smartbizops.com/KenSnell.zip
Included in this small databse is a query
with your Original Dmax Function that works
just fine getting me the LAST date for each City(Venue ID)

I really appreciate the assistanceand advice
I've been getting from all of you
Thanks Again
Brad

OK - I have been successful in a test with the following type of expression:

DMax("Date", "TableName", "[City]='" & [Enter City Name:] & "' And [Date]
<> DMax(""Date"", ""TableName"", ""[City]='"" & [Enter City Name:] &
""'"")")

Note the doubling up of " characters in the second DMax expression.

--
Ken Snell
<MS ACCESS MVP>

Thanks for the quick response KEN
Unfortunately neither work. I get a Data type mismatch on both.
FYI: Here is what I'm trying to do;
Get the record for the last date which will include a field of Tickets Sold
for a given city.
Then get the record for the Previous Date which will also include Tickets
Sold.
Then determine the difference of Tickets Sold between the two Dates.
This will be an undelying query for a report.
The DMAX function works great to get the Last date.
But I'm at a loss as to how I get the previous to last date.
Maybe the Dmax function isn't the way to go??.... i have no idea.
Thanks for any advice.
Brad

 

[BJC]

Thanks so very much Ken for all your help!

After playing with your Second DAMX example
I seem to have used similar logic to what you emailed me.

I created a query that finds the Last Date using your first DMAX example.

Then I created a second query to find ALL EXCEPT the LAST date.
using just the second part of your DMAX with the "Not Equal To".

My Third query used the second Query as the data source.
and I used the First DMAX example to find the LAST date
in the records from the second query.

It may be a little convoluted, but it seems to work.

Question:
Are there any limits to the number of sub queries that
can be used to feed a report.

Along with the 3 queries mentioned above, I
have 4 other queries that feed a Final
query wich becomes the Record source for my report.

Am I over extending myself ?
Or is this considered to be an acceptable aproach
to retrieve the required data for a complicated report?

Thanks Again to Ken and ALL who have helped me in the past.
These Groups have proven to be invaluable!

Much Appreciated
Brad

I've put some queries in the db and have emailed it to you at your email
address. These queries are quick and dirty (I didn't have time to put
together anything fancy), but will give you an idea of how to approach this.

I'm sure these queries can be improved.

--
Ken Snell
<MS ACCESS MVP>

Thanks for the continued support.
But Still not getting what I need.
Correct me if I'm wrong Ken; but doesn't the second DMAX
with the 'Not Equal To' operator give me
EVERY DATE EXCEPT the last date.

Let me give you some more details about what I'm trying to do.
I have a table called "Ticket Counts".
In this table we enter the number of tickets sold for each city
on each date along with costs etc...
We want to know the difference of tickets sold
between the LASTdate that tickets were sold
and the PREVIOUS date for each city.

I've included a link to to a small zipped mdb file
Feel free to look at it if your so inclined.
It includes sample info that I am currently testing.
http://www.smartbizops.com/KenSnell.zip
Included in this small databse is a query
with your Original Dmax Function that works
just fine getting me the LAST date for each City(Venue ID)

I really appreciate the assistanceand advice
I've been getting from all of you
Thanks Again
Brad

OK - I have been successful in a test with the following type of expression:

DMax("Date", "TableName", "[City]='" & [Enter City Name:] & "' And [Date]
<> DMax(""Date"", ""TableName"", ""[City]='"" & [Enter City Name:] &
""'"")")

Note the doubling up of " characters in the second DMax expression.

 

[Ken Snell]

Eventually, performance will suffer if you have many, many subqueries. But
what you propose is not a significant concern.

As I noted, what I put together for you is "quick and dirty". Given time,
the number of queries probably could be reduced to just a few in the example
that I did for you, just by revising the queries to do multiple things in
each one.

--
Ken Snell
<MS ACCESS MVP>

Thanks so very much Ken for all your help!

After playing with your Second DAMX example
I seem to have used similar logic to what you emailed me.

I created a query that finds the Last Date using your first DMAX example.

Then I created a second query to find ALL EXCEPT the LAST date.
using just the second part of your DMAX with the "Not Equal To".

My Third query used the second Query as the data source.
and I used the First DMAX example to find the LAST date
in the records from the second query.

It may be a little convoluted, but it seems to work.

Question:
Are there any limits to the number of sub queries that
can be used to feed a report.

Along with the 3 queries mentioned above, I
have 4 other queries that feed a Final
query wich becomes the Record source for my report.

Am I over extending myself ?
Or is this considered to be an acceptable aproach
to retrieve the required data for a complicated report?

Thanks Again to Ken and ALL who have helped me in the past.
These Groups have proven to be invaluable!

Much Appreciated
Brad

I've put some queries in the db and have emailed it to you at your email
address. These queries are quick and dirty (I didn't have time to put
together anything fancy), but will give you an idea of how to approach this.

I'm sure these queries can be improved.

--
Ken Snell
<MS ACCESS MVP>

Thanks for the continued support.
But Still not getting what I need.
Correct me if I'm wrong Ken; but doesn't the second DMAX
with the 'Not Equal To' operator give me
EVERY DATE EXCEPT the last date.

Let me give you some more details about what I'm trying to do.
I have a table called "Ticket Counts".
In this table we enter the number of tickets sold for each city
on each date along with costs etc...
We want to know the difference of tickets sold
between the LASTdate that tickets were sold
and the PREVIOUS date for each city.

I've included a link to to a small zipped mdb file
Feel free to look at it if your so inclined.
It includes sample info that I am currently testing.
http://www.smartbizops.com/KenSnell.zip
Included in this small databse is a query
with your Original Dmax Function that works
just fine getting me the LAST date for each City(Venue ID)

I really appreciate the assistanceand advice
I've been getting from all of you
Thanks Again
Brad


OK - I have been successful in a test with the following type of
expression:

DMax("Date", "TableName", "[City]='" & [Enter City Name:] & "' And [Date]
<> DMax(""Date"", ""TableName"", ""[City]='"" & [Enter City Name:] &
""'"")")

Note the doubling up of " characters in the second DMax expression.