DMAX function

[Maria]

I am using Access 2000

I want to create a field in a query that will give me the
latest date within a date field for all records that have
the same serial#. This is the formula that I used.
Where "calibrationdata" is the Table, [caldate] is the
field I want the max date picked from for "serial" number
that are alike.

DMax([Calibrationdata]![CalDate],"calibrationdata",
[CalibrationData]![Serial])

It came of with "error" in some records and in others it
gave me a number such as "2.99877780".
Can anyone tell me what I have done wrong?? This is the
first time I have tried to use DMAX.

Maria

 

[John Vinson]

I am using Access 2000

I want to create a field in a query that will give me the
latest date within a date field for all records that have
the same serial#. This is the formula that I used.
Where "calibrationdata" is the Table, [caldate] is the
field I want the max date picked from for "serial" number
that are alike.

DMax([Calibrationdata]![CalDate],"calibrationdata",
[CalibrationData]![Serial])

It came of with "error" in some records and in others it
gave me a number such as "2.99877780".
Can anyone tell me what I have done wrong?? This is the
first time I have tried to use DMAX.

Several things. For one, all three arguments to DMax() should be text
strings; you're passing a date as the first argument. For another,
you're using ! rather than .; the ! is used for Form references, not
for table fields. Finally, the third argument of DMax should be a
valid SQL WHERE clause without the word WHERE. If this is a field in a
query, you want to pass it the serial number in the current record.
Try:

DMax("[CalDate]", "[calibrationdata]", "[Serial] = " & [Serial])

 

[Maria]

John:

Thank you for answering in question. Following is what I
typed in but I am still getting errors.

Expr1: DMax("[CalDate]","[calibrationdata]","[Serial]= " &
[Serial])

I received the following error message.

"Syntax Error (missing operator) in query "[Serial]=99-
006A"

99-006A = serial number of one of the machines.
The error messages repeats with other serial numbers.
Can you see what I am missing??

Maria

-----Original Message-----

I am using Access 2000

I want to create a field in a query that will give me the
latest date within a date field for all records that have
the same serial#. This is the formula that I used.
Where "calibrationdata" is the Table, [caldate] is the
field I want the max date picked from for "serial" number
that are alike.

DMax([Calibrationdata]![CalDate],"calibrationdata",
[CalibrationData]![Serial])

It came of with "error" in some records and in others it
gave me a number such as "2.99877780".
Can anyone tell me what I have done wrong?? This is the
first time I have tried to use DMAX.

Several things. For one, all three arguments to DMax() should be text
strings; you're passing a date as the first argument. For another,
you're using ! rather than .; the ! is used for Form references, not
for table fields. Finally, the third argument of DMax should be a
valid SQL WHERE clause without the word WHERE. If this is a field in a
query, you want to pass it the serial number in the current record.
Try:

DMax("[CalDate]", "[calibrationdata]", "[Serial] = " & [Serial])




.

 

[John Vinson]

John:

Thank you for answering in question. Following is what I
typed in but I am still getting errors.

Expr1: DMax("[CalDate]","[calibrationdata]","[Serial]= " &
[Serial])

I received the following error message.

"Syntax Error (missing operator) in query "[Serial]=99-
006A"

Sorry! Should have covered both possibilities. The syntax I suggested
works if Serial is a Number datatype. For Text you need to surround
the argument with quotemarks:

Expr1: DMax("[CalDate]","[calibrationdata]","[Serial]= '" &
[Serial] & "'")

 

[Maria]

John - THANK YOU -- THANK YOU.
That worked beautifully. But now I have to develop a
report that will tell which calibrations are due next
month. I am having a problem with this but I will put it
on the newsletter under REPORTS. If possible, maybe you
can help me again.

Again - thank you very much

Maria

-----Original Message-----

John:

Thank you for answering in question. Following is what I
typed in but I am still getting errors.

Expr1: DMax("[CalDate]","[calibrationdata]","[Serial]= " &
[Serial])

I received the following error message.

"Syntax Error (missing operator) in query "[Serial]=99-
006A"

Sorry! Should have covered both possibilities. The syntax I suggested
works if Serial is a Number datatype. For Text you need to surround
the argument with quotemarks:

Expr1: DMax("[CalDate]","[calibrationdata]","[Serial]= '" &
[Serial] & "'")




.

 

[John Vinson]

John - THANK YOU -- THANK YOU.
That worked beautifully. But now I have to develop a
report that will tell which calibrations are due next
month. I am having a problem with this but I will put it
on the newsletter under REPORTS. If possible, maybe you
can help me again.

Again - thank you very much

Use a criterion on the date-due field of

BETWEEN DateSerial(Year(Date()), Month(Date()) + 1, 1) AND
DateSerial(Year(Date()), Month(Date()) + 2, 0)