Using Dmax on Table with dates, some dates returned, but also getting #Error (1 Viewer)

[Bettany]

Hi, I'm using this expression in QBE to build a calculated column in a query to query a table on a date column. Some of the transactions have no dates, and I suspect that's why I'm getting some dates, but I'm also getting "#Error." How can I modify this query so I don't get errors in the calculated field, but rather those entries are just blank. Thanks.

MaxDate: DateValue(DMax("InteractionDate","InteractionT","PersonID = " & [PersonT.PersonID] & " AND InteractionDate IS NOT NULL"))

 

[Pat Hartman]

MaxDate: DMax("InteractionDate","InteractionT","PersonID = " & [PersonT.PersonID] & " AND InteractionDate IS NOT NULL")

 

[Bettany]

MaxDate: DMax("InteractionDate","InteractionT","PersonID = " & [PersonT.PersonID] & " AND InteractionDate IS NOT NULL")

Ok making progress and this works, thanks! Ah ok, so Datevalue is running against values that are null which is generating the error(?).

But I do not want to display the time. Should I make another calculated field based on this one? Or is there some modification I can make to this expression to rectify the datevalue/null issue?

 

[ebs17]

build a calculated column in a query

Cleaner than DMax is pure SQL, in this case a subquery.

SELECT
    [FieldList],
    (SELECT MAX(DateValue(InteractionDate)) FROM InteractionT WHERE PersonID = PersonT.PersonID AND InteractionDate > 0) AS MaxDate
FROM
    PersonT

If no record meets the criteria or the table contains no records, the DMax function returns a null value.
Therefore, the DateValue must be moved to direct application to InteractionDate.

 

[Pat Hartman]

You can set the format property of the MaxDate field to show short date. Keep in mind that formatting may hide the time but it is still there and will interfere with sorting and your Where clause unless you account for the time component.

You can use the Format() function but you will have to account for a null date and the result will be a text data type rather than a date data type so depending on what you are doing with the field, that will also cause problems with sorting and comparing.

Before we go down the rabbit hole of other possibilities, do you need the returned value to be a date if one exists or null if not OR is a string OK? Remember strings sort character by character left to right so if you sort a date formatted as a string, you will be very unhappy with the outcome.

 

[Bettany]

Cleaner than DMax is pure SQL, in this case a subquery.

SELECT
    [FieldList],
    (SELECT MAX(DateValue(InteractionDate)) FROM InteractionT WHERE PersonID = PersonT.PersonID AND InteractionDate > 0) AS MaxDate
FROM
    PersonT

If no record meets the criteria or the table contains no records, the DMax function returns a null value.
Therefore, the DateValue must be moved to direct application to InteractionDate.

Does this go in a VBA function that I call from the QBE screen? Sorry now sure how to implement this. I understand the code but not sure where you're advising that it goes.

 

[Bettany]

You can set the format property of the MaxDate field to show short date. Keep in mind that formatting may hide the time but it is still there and will interfere with sorting and your Where clause unless you account for the time component.

You can use the Format() function but you will have to account for a null date and the result will be a text data type rather than a date data type so depending on what you are doing with the field, that will also cause problems with sorting and comparing.

Before we go down the rabbit hole of other possibilities, do you need the returned value to be a date if one exists or null if not OR is a string OK? Remember strings sort character by character left to right so if you sort a date formatted as a string, you will be very unhappy with the outcome.

Formating the date with format() worked fine for me, and I'm all set now. Thank you!

 

[Pat Hartman]

you're welcome