First, Last function Question

[Joe]

I have an Asset database with main form 'frm_Assets' that contains
'frm_Audit' as a tab. frm_Audit is based on tbl_Audit which contains the
fields AuditID (AN, PK), AssetID (num), EmployeeID (num) and AuditDate
(date/time). The datasheet looks like this -
Audit ID Asset ID Audit Date Audited By
1 1 31/01/2002 3
2 1 28/02/2002 2
3 1 31/03/2002 1
4 2 28/02/2002 5
5 3 28/02/2002 8

I would like to have a text box on frm_Asset that indicates the Last Audit
date for the asset item being viewed but I'm not sure how to use the Last
function as I suspect it will only give me the date for AuditID 5 ie asset
number 3. Is there any other way I can achieve this?

 

[Douglas J. Steele]

"First" and "Last" are concepts that have limited value in relational
databases, since you should never make assumptions about the order of data
in tables. (you can use then in conjunction with queries when you know that
an appropriate ORDER BY clause has been applied, but even then their value
is dubious)

What you really want is the maximum value of AuditDate for the given
AssetID. You can use the DMax aggregate function for this. Assuming the
AssetID is a numeric field (as opposed to a Text field), and you've got the
value of the current AssetID in a variable named lngAssetID, you can use
something like:

DMax("[AuditDate]", "[tbl_Audit", "[AssetID] = " & lngAssetID)

 

[Joe]

Doug,

Many thanks for your help. I hadn't thought of DMax as I haven't used it
before. The AssetID is a numeric field. I've tried pasting the code you
provided into the text box control source but all I'm getting is a #Name?
error. I'm not sure I follow what you mean by "you've got the value of the
current AssetID in a variable named lngAssetID". Please could you enlighten
me?

Regards,

Joe

"First" and "Last" are concepts that have limited value in relational
databases, since you should never make assumptions about the order of data
in tables. (you can use then in conjunction with queries when you know that
an appropriate ORDER BY clause has been applied, but even then their value
is dubious)

What you really want is the maximum value of AuditDate for the given
AssetID. You can use the DMax aggregate function for this. Assuming the
AssetID is a numeric field (as opposed to a Text field), and you've got the
value of the current AssetID in a variable named lngAssetID, you can use
something like:

DMax("[AuditDate]", "[tbl_Audit", "[AssetID] = " & lngAssetID)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have an Asset database with main form 'frm_Assets' that contains
'frm_Audit' as a tab. frm_Audit is based on tbl_Audit which contains the
fields AuditID (AN, PK), AssetID (num), EmployeeID (num) and AuditDate
(date/time). The datasheet looks like this -
Audit ID Asset ID Audit Date Audited By
1 1 31/01/2002 3
2 1 28/02/2002 2
3 1 31/03/2002 1
4 2 28/02/2002 5
5 3 28/02/2002 8

I would like to have a text box on frm_Asset that indicates the Last Audit
date for the asset item being viewed but I'm not sure how to use the Last
function as I suspect it will only give me the date for AuditID 5 ie asset
number 3. Is there any other way I can achieve this?

 

[Klatuu]

First, you need to preceed the statement with an = when you use it in either
the Control Source or Default Value properties:

= DMax("[AuditDate]", "[tbl_Audit", "[AssetID] = " & lngAssetID)

The breakdown of the statement is"
You want the maximum value of a field named AuditDate in a table named
tblAudit Where the field AssestID in the table tbl_Audit is equal to the
value of the memory variable lngAssestID.

As coded, it is expecting the field AssetID to be a numeric field. Were it
a text field, the syntax would be:

= DMax("[AuditDate]", "[tbl_Audit", "[AssetID] = '" & lngAssetID) & "'"

And if it were a date/time field:

= DMax("[AuditDate]", "[tbl_Audit", "[AssetID] = #" & lngAssetID) & "#"

If value is in control on your form instead of a memory varilable. It would
look like this:

= DMax("[AuditDate]", "[tbl_Audit", "[AssetID] = " & [txtAssetID])



--
Dave Hargis, Microsoft Access MVP

Doug,

Many thanks for your help. I hadn't thought of DMax as I haven't used it
before. The AssetID is a numeric field. I've tried pasting the code you
provided into the text box control source but all I'm getting is a #Name?
error. I'm not sure I follow what you mean by "you've got the value of the
current AssetID in a variable named lngAssetID". Please could you enlighten
me?

Regards,

Joe

"First" and "Last" are concepts that have limited value in relational
databases, since you should never make assumptions about the order of data
in tables. (you can use then in conjunction with queries when you know that
an appropriate ORDER BY clause has been applied, but even then their value
is dubious)

What you really want is the maximum value of AuditDate for the given
AssetID. You can use the DMax aggregate function for this. Assuming the
AssetID is a numeric field (as opposed to a Text field), and you've got the
value of the current AssetID in a variable named lngAssetID, you can use
something like:

DMax("[AuditDate]", "[tbl_Audit", "[AssetID] = " & lngAssetID)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have an Asset database with main form 'frm_Assets' that contains
'frm_Audit' as a tab. frm_Audit is based on tbl_Audit which contains the
fields AuditID (AN, PK), AssetID (num), EmployeeID (num) and AuditDate
(date/time). The datasheet looks like this -
Audit ID Asset ID Audit Date Audited By
1 1 31/01/2002 3
2 1 28/02/2002 2
3 1 31/03/2002 1
4 2 28/02/2002 5
5 3 28/02/2002 8

I would like to have a text box on frm_Asset that indicates the Last Audit
date for the asset item being viewed but I'm not sure how to use the Last
function as I suspect it will only give me the date for AuditID 5 ie asset
number 3. Is there any other way I can achieve this?

 

[Joe]

Thanks for your assistance. You guys are awesome for spending time to help us!

I've tried all day to get this function to work and I just cannot get it to
give me anything other than #name? error. I've checked syntax, spelling,
quotes, brackets etc etc without any resolution.

Anyway, I've trolled through the DD again and found a response under "Last
Sale Date" for 7/23/2007 from Allen Browne that solved my problem. I am
leaving a copy of it here for anyone else looking for an alternative DMax
expression.

"Put something like this in the Control Source of the text box on your form.
(It's one line.)
=DMax("Sale Date", "Sales Detail",
"[Product Number] = " & Nz([Product Number],0))

If Product Number is actually a text field, you need more quotes. It's the
same as for DLookup(), explained here:
http://allenbrowne.com/casu-07.html"

Thanks again for helping me to get on the right track.

Joe

First, you need to preceed the statement with an = when you use it in either
the Control Source or Default Value properties:

= DMax("[AuditDate]", "[tbl_Audit", "[AssetID] = " & lngAssetID)

The breakdown of the statement is"
You want the maximum value of a field named AuditDate in a table named
tblAudit Where the field AssestID in the table tbl_Audit is equal to the
value of the memory variable lngAssestID.

As coded, it is expecting the field AssetID to be a numeric field. Were it
a text field, the syntax would be:

= DMax("[AuditDate]", "[tbl_Audit", "[AssetID] = '" & lngAssetID) & "'"

And if it were a date/time field:

= DMax("[AuditDate]", "[tbl_Audit", "[AssetID] = #" & lngAssetID) & "#"

If value is in control on your form instead of a memory varilable. It would
look like this:

= DMax("[AuditDate]", "[tbl_Audit", "[AssetID] = " & [txtAssetID])



--
Dave Hargis, Microsoft Access MVP

Doug,

Many thanks for your help. I hadn't thought of DMax as I haven't used it
before. The AssetID is a numeric field. I've tried pasting the code you
provided into the text box control source but all I'm getting is a #Name?
error. I'm not sure I follow what you mean by "you've got the value of the
current AssetID in a variable named lngAssetID". Please could you enlighten
me?

Regards,

Joe

"First" and "Last" are concepts that have limited value in relational
databases, since you should never make assumptions about the order of data
in tables. (you can use then in conjunction with queries when you know that
an appropriate ORDER BY clause has been applied, but even then their value
is dubious)

What you really want is the maximum value of AuditDate for the given
AssetID. You can use the DMax aggregate function for this. Assuming the
AssetID is a numeric field (as opposed to a Text field), and you've got the
value of the current AssetID in a variable named lngAssetID, you can use
something like:

DMax("[AuditDate]", "[tbl_Audit", "[AssetID] = " & lngAssetID)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have an Asset database with main form 'frm_Assets' that contains
'frm_Audit' as a tab. frm_Audit is based on tbl_Audit which contains the
fields AuditID (AN, PK), AssetID (num), EmployeeID (num) and AuditDate
(date/time). The datasheet looks like this -
Audit ID Asset ID Audit Date Audited By
1 1 31/01/2002 3
2 1 28/02/2002 2
3 1 31/03/2002 1
4 2 28/02/2002 5
5 3 28/02/2002 8

I would like to have a text box on frm_Asset that indicates the Last Audit
date for the asset item being viewed but I'm not sure how to use the Last
function as I suspect it will only give me the date for AuditID 5 ie asset
number 3. Is there any other way I can achieve this?

 

[Klatuu]

I just saw the original error that has been driving you mad. It made me look
because what you posted was what I had proposed, so I had to see what the
difference was. Notice the missing bracket in the Domain Name argument:
= DMax("[AuditDate]", "[tbl_Audit", "[AssetID] = " & lngAssetID)
Missing Bracket ^
Should be:
= DMax("[AuditDate]", "[tbl_Audit]", "[AssetID] = " & lngAssetID)

--
Dave Hargis, Microsoft Access MVP

Thanks for your assistance. You guys are awesome for spending time to help us!

I've tried all day to get this function to work and I just cannot get it to
give me anything other than #name? error. I've checked syntax, spelling,
quotes, brackets etc etc without any resolution.

Anyway, I've trolled through the DD again and found a response under "Last
Sale Date" for 7/23/2007 from Allen Browne that solved my problem. I am
leaving a copy of it here for anyone else looking for an alternative DMax
expression.

"Put something like this in the Control Source of the text box on your form.
(It's one line.)
=DMax("Sale Date", "Sales Detail",
"[Product Number] = " & Nz([Product Number],0))

If Product Number is actually a text field, you need more quotes. It's the
same as for DLookup(), explained here:
http://allenbrowne.com/casu-07.html"

Thanks again for helping me to get on the right track.

Joe

First, you need to preceed the statement with an = when you use it in either
the Control Source or Default Value properties:

= DMax("[AuditDate]", "[tbl_Audit", "[AssetID] = " & lngAssetID)

The breakdown of the statement is"
You want the maximum value of a field named AuditDate in a table named
tblAudit Where the field AssestID in the table tbl_Audit is equal to the
value of the memory variable lngAssestID.

As coded, it is expecting the field AssetID to be a numeric field. Were it
a text field, the syntax would be:

= DMax("[AuditDate]", "[tbl_Audit", "[AssetID] = '" & lngAssetID) & "'"

And if it were a date/time field:

= DMax("[AuditDate]", "[tbl_Audit", "[AssetID] = #" & lngAssetID) & "#"

If value is in control on your form instead of a memory varilable. It would
look like this:

= DMax("[AuditDate]", "[tbl_Audit", "[AssetID] = " & [txtAssetID])



--
Dave Hargis, Microsoft Access MVP

Doug,

Many thanks for your help. I hadn't thought of DMax as I haven't used it
before. The AssetID is a numeric field. I've tried pasting the code you
provided into the text box control source but all I'm getting is a #Name?
error. I'm not sure I follow what you mean by "you've got the value of the
current AssetID in a variable named lngAssetID". Please could you enlighten
me?

Regards,

Joe

:

"First" and "Last" are concepts that have limited value in relational
databases, since you should never make assumptions about the order of data
in tables. (you can use then in conjunction with queries when you know that
an appropriate ORDER BY clause has been applied, but even then their value
is dubious)

What you really want is the maximum value of AuditDate for the given
AssetID. You can use the DMax aggregate function for this. Assuming the
AssetID is a numeric field (as opposed to a Text field), and you've got the
value of the current AssetID in a variable named lngAssetID, you can use
something like:

DMax("[AuditDate]", "[tbl_Audit", "[AssetID] = " & lngAssetID)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have an Asset database with main form 'frm_Assets' that contains
'frm_Audit' as a tab. frm_Audit is based on tbl_Audit which contains the
fields AuditID (AN, PK), AssetID (num), EmployeeID (num) and AuditDate
(date/time). The datasheet looks like this -
Audit ID Asset ID Audit Date Audited By
1 1 31/01/2002 3
2 1 28/02/2002 2
3 1 31/03/2002 1
4 2 28/02/2002 5
5 3 28/02/2002 8

I would like to have a text box on frm_Asset that indicates the Last Audit
date for the asset item being viewed but I'm not sure how to use the Last
function as I suspect it will only give me the date for AuditID 5 ie asset
number 3. Is there any other way I can achieve this?