DMax date where other field is not null

  • Thread starter Thread starter Jim L.
  • Start date Start date
J

Jim L.

I am almost finished with a database I have been working on for some time. I
have learned so much from these posts, including the fact that the way I have
set up this db was not the best way to go about it. I still have much to
learn about normality, but am trying to apply what I have learned to my next
project.
That being said, I have one last problem (hopefully) to get working before
this thing is up and running. Hopefully we can work it out without a total
redesign, as I am already past my due date.
I have a report based on a query, and I need a text box to display the most
current date (DMax?), but only if a particular field ([12CNV]) is not null.
I have tried writing the function a few different ways, but whenever I change
back to Print Preview, the text box says "Error".
It sounds like a simple problem, so I am hoping there is a simple solution.
(My boss and) I thank you for any help on this problem.
 
Try:
=DMax("YourField", "YourTable", "[12CNV] Is Not Null")

If it turns out that you need another criterion on (say) a numeric ID field,
use:
=DMax("YourField", "YourTable", "([ID] = " & [ID] & ") AND ([12CNV] Is
Not Null)")

If the ID might be null, that will still error, so:
=DMax("YourField", "YourTable", "([ID] = " & Nz([ID],0) & ") AND
([12CNV] Is Not Null)")

There might be a better (faster) way to do this in a query, as the domain
aggregate functions can be slow.
 
Allen,
This works perfect, except for one little detail I forgot to mention. This
date that I am looking for is a "last delivery date". Also on this report is
the "current" inventory date. In other words, if I look at the report for
today, the current date shows 7 August 2009, the current inventory, and the
"last delivery date" shows dates from July 2009 when we had our last
delivery, which is great. However, if I scroll back to an inventory report
from February 2009 for example, it still shows the last delivery as July 2009
(in the future).
Is it possible to find the "last delivery date" that was on or before the
"current" date?
I guess my questions never do end. I have one more small annoyance that may
or may not be fixable. This report also has a bar chart that shows the
current level in each tank. Is it possible to conditionally format the bars
so they will change color depending on the product type in the tank? If not,
no big deal, I can do it manually.
I know my db is kinda screwy and difficult for you to come up with workable
solutions, but I do appreciate you taking the time. If you have a
recommended site I could check out for tips on how to normalize a new
database, my next project should be much easier to work with.
Thanks again Mr. Browne

Allen Browne said:
Try:
=DMax("YourField", "YourTable", "[12CNV] Is Not Null")

If it turns out that you need another criterion on (say) a numeric ID field,
use:
=DMax("YourField", "YourTable", "([ID] = " & [ID] & ") AND ([12CNV] Is
Not Null)")

If the ID might be null, that will still error, so:
=DMax("YourField", "YourTable", "([ID] = " & Nz([ID],0) & ") AND
([12CNV] Is Not Null)")

There might be a better (faster) way to do this in a query, as the domain
aggregate functions can be slow.
--
Allen Browne - Microsoft MVP. Perth, Western Australia

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

Jim L. said:
I am almost finished with a database I have been working on for some time.
I
have learned so much from these posts, including the fact that the way I
have
set up this db was not the best way to go about it. I still have much to
learn about normality, but am trying to apply what I have learned to my
next
project.
That being said, I have one last problem (hopefully) to get working before
this thing is up and running. Hopefully we can work it out without a
total
redesign, as I am already past my due date.
I have a report based on a query, and I need a text box to display the
most
current date (DMax?), but only if a particular field ([12CNV]) is not
null.
I have tried writing the function a few different ways, but whenever I
change
back to Print Preview, the text box says "Error".
It sounds like a simple problem, so I am hoping there is a simple
solution.
(My boss and) I thank you for any help on this problem.
Click to expand...
Click to expand...
 
You can specify a date earlier than the one on the record the report is
handling. You will need to add that condition to the 3rd argument too.

If it's more complex, you may need to use a subquery in the 3rd argument. If
subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

The question about the bar chart: it might be good to start a new thread to
ask about that.

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

Reply to group, rather than allenbrowne at mvps dot org.
Jim L. said:
Allen,
This works perfect, except for one little detail I forgot to mention.
This
date that I am looking for is a "last delivery date". Also on this report
is
the "current" inventory date. In other words, if I look at the report for
today, the current date shows 7 August 2009, the current inventory, and
the
"last delivery date" shows dates from July 2009 when we had our last
delivery, which is great. However, if I scroll back to an inventory
report
from February 2009 for example, it still shows the last delivery as July
2009
(in the future).
Is it possible to find the "last delivery date" that was on or before the
"current" date?
I guess my questions never do end. I have one more small annoyance that
may
or may not be fixable. This report also has a bar chart that shows the
current level in each tank. Is it possible to conditionally format the
bars
so they will change color depending on the product type in the tank? If
not,
no big deal, I can do it manually.
I know my db is kinda screwy and difficult for you to come up with
workable
solutions, but I do appreciate you taking the time. If you have a
recommended site I could check out for tips on how to normalize a new
database, my next project should be much easier to work with.
Thanks again Mr. Browne

Allen Browne said:
Try:
=DMax("YourField", "YourTable", "[12CNV] Is Not Null")

If it turns out that you need another criterion on (say) a numeric ID
field,
use:
=DMax("YourField", "YourTable", "([ID] = " & [ID] & ") AND ([12CNV]
Is
Not Null)")

If the ID might be null, that will still error, so:
=DMax("YourField", "YourTable", "([ID] = " & Nz([ID],0) & ") AND
([12CNV] Is Not Null)")

There might be a better (faster) way to do this in a query, as the domain
aggregate functions can be slow.
--
Allen Browne - Microsoft MVP. Perth, Western Australia

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

Jim L. said:
I am almost finished with a database I have been working on for some
time.
I
have learned so much from these posts, including the fact that the way
I
have
set up this db was not the best way to go about it. I still have much
to
learn about normality, but am trying to apply what I have learned to my
next
project.
That being said, I have one last problem (hopefully) to get working
before
this thing is up and running. Hopefully we can work it out without a
total
redesign, as I am already past my due date.
I have a report based on a query, and I need a text box to display the
most
current date (DMax?), but only if a particular field ([12CNV]) is not
null.
I have tried writing the function a few different ways, but whenever I
change
back to Print Preview, the text box says "Error".
It sounds like a simple problem, so I am hoping there is a simple
solution.
(My boss and) I thank you for any help on this problem.
Click to expand...
Click to expand...
Click to expand...
 
Allen,
Perfect. Things are coming together better than I had expected. I looked
at the subquery info you sent, and most of it is over my head. I ended up
using the expressions instead.
I should probably quit for the day while I'm ahead, but I posted a new
thread with the chart question anyway.
Thanks so much for your help!!

Allen Browne said:
You can specify a date earlier than the one on the record the report is
handling. You will need to add that condition to the 3rd argument too.

If it's more complex, you may need to use a subquery in the 3rd argument. If
subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

The question about the bar chart: it might be good to start a new thread to
ask about that.

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

Reply to group, rather than allenbrowne at mvps dot org.
Jim L. said:
Allen,
This works perfect, except for one little detail I forgot to mention.
This
date that I am looking for is a "last delivery date". Also on this report
is
the "current" inventory date. In other words, if I look at the report for
today, the current date shows 7 August 2009, the current inventory, and
the
"last delivery date" shows dates from July 2009 when we had our last
delivery, which is great. However, if I scroll back to an inventory
report
from February 2009 for example, it still shows the last delivery as July
2009
(in the future).
Is it possible to find the "last delivery date" that was on or before the
"current" date?
I guess my questions never do end. I have one more small annoyance that
may
or may not be fixable. This report also has a bar chart that shows the
current level in each tank. Is it possible to conditionally format the
bars
so they will change color depending on the product type in the tank? If
not,
no big deal, I can do it manually.
I know my db is kinda screwy and difficult for you to come up with
workable
solutions, but I do appreciate you taking the time. If you have a
recommended site I could check out for tips on how to normalize a new
database, my next project should be much easier to work with.
Thanks again Mr. Browne

Allen Browne said:
Try:
=DMax("YourField", "YourTable", "[12CNV] Is Not Null")

If it turns out that you need another criterion on (say) a numeric ID
field,
use:
=DMax("YourField", "YourTable", "([ID] = " & [ID] & ") AND ([12CNV]
Is
Not Null)")

If the ID might be null, that will still error, so:
=DMax("YourField", "YourTable", "([ID] = " & Nz([ID],0) & ") AND
([12CNV] Is Not Null)")

There might be a better (faster) way to do this in a query, as the domain
aggregate functions can be slow.
--
Allen Browne - Microsoft MVP. Perth, Western Australia

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

I am almost finished with a database I have been working on for some
time.
I
have learned so much from these posts, including the fact that the way
I
have
set up this db was not the best way to go about it. I still have much
to
learn about normality, but am trying to apply what I have learned to my
next
project.
That being said, I have one last problem (hopefully) to get working
before
this thing is up and running. Hopefully we can work it out without a
total
redesign, as I am already past my due date.
I have a report based on a query, and I need a text box to display the
most
current date (DMax?), but only if a particular field ([12CNV]) is not
null.
I have tried writing the function a few different ways, but whenever I
change
back to Print Preview, the text box says "Error".
It sounds like a simple problem, so I am hoping there is a simple
solution.
(My boss and) I thank you for any help on this problem.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top