DMax() criteria syntax

[Scott]

I need to use DMax() function in event procedures but do not understand the
syntax of criteria part especially with two criteria. Can someone explain
it to me.

Thanks,

Scott

 

[Douglas J Steele]

The criteria for DMax (and for all the other domain aggregate functions, for
that matter) should like like a WHERE clause without the word WHERE in front
of it. The following assumes that CustomerId is a text field, and that the
customer id of interest is stored in variable strCustomerId. If CustomerId
was numeric, you would remove the single quotes in the string:

DMax("InvoiceAmount", "MyTable", "CustomerId = '" & strCustomerId & "' And
Year([InvoiceDate]) = 2006")

 

[Scott]

Douglas,

Thanks for your explanation.

In your example, it appears to have two criteria:

"CustomerId = '" & strCustomerId & "'
Year([InvoiceDate]) = 2006"

They are connected with AND.

The 2nd one is easy to understand while I don't understand the 1st one.

Is CustomerId a field name? What does the condition of strCustomerId mean?
Why does the condition is enclosed with &, double quote and sigle quote?
According to your explanation below, the text data requires enclosed with &,
double quote and sigle quote, the numeric data requires enclosed with & and
double quote and assume date & time requires none of above.

Thanks,

Scott

The criteria for DMax (and for all the other domain aggregate functions,
for
that matter) should like like a WHERE clause without the word WHERE in
front
of it. The following assumes that CustomerId is a text field, and that the
customer id of interest is stored in variable strCustomerId. If CustomerId
was numeric, you would remove the single quotes in the string:

DMax("InvoiceAmount", "MyTable", "CustomerId = '" & strCustomerId & "' And
Year([InvoiceDate]) = 2006")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I need to use DMax() function in event procedures but do not understand the
syntax of criteria part especially with two criteria. Can someone
explain
it to me.

Thanks,

Scott

 

[Douglas J Steele]

As I said in my explanation, strCustomerId is a variable that holds the
Customer Id for the customer of interest.

What that code is doing is concatenating the value of the Customer Id into
the Where clause. Since it's a text value, it must be enclosed in quotes. if
strCustomerId was STEE123, you'd end up with:

CustomerID = 'STEE123' And Year([InvoiceDate]) = 2006

It's not a case of "the text data requires enclosed with &, double quote and
sigle quote, the numeric data requires enclosed with & and double quote and
assume date & time requires none of above."

The & is there strictly for concatenation purposes.

Text data requires quotes. You can use either single or double quotes: I
could have written that as

"CustomerId = """ & strCustomerId & """ And Year([InvoiceDate]) = 2006"

(to represent a single double quote, you need to put two in a row) or

"CustomerId = " & Chr$(34) & strCustomerId & Chr$(34) & " And
Year([InvoiceDate]) = 2006"

(Chr$(34) represents ")

In fact, there are some issues using a single quote as I did in the original
example. If the text value contained a single quote in it (say it was a
name, not an Id, and the name was O'Reilly), you'd run into problems. I talk
about this in my May, 2004 "Access Answers" column in Pinnacle Publication's
"Smart Access". You can download the column (and sample database) for free
at http://www.accessmvp.com/DJSteele/SmartAccess.html

Numeric data doesn't require quotes. If CustomerId was a numeric field,
you'd use:

"CustomerId = " & lngCustomerId & " And Year([InvoiceDate]) = 2006"

The quotes are there because you're creating a string.

Dates require # delimiters (as opposed to quotes). As well, the date needs
to be in mm/dd/yyyy format, regardless of what you may have set your Short
Date format to in Regional Settings. (Okay, this isn't strictly true. You
can use any unambiguous format, such as yyyy-mm-dd or dd mmm yyyy. The point
is, you can't rely on using dd/mm/yyyy format, because Access will assume
it's mm/dd/yyyy and misinterpret it for the first 12 days of each month. In
other words, it will always interpret 12/04/2006 as 04 Dec, 2006, not as 12
April, 2006.)



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas,

Thanks for your explanation.

In your example, it appears to have two criteria:

"CustomerId = '" & strCustomerId & "'
Year([InvoiceDate]) = 2006"

They are connected with AND.

The 2nd one is easy to understand while I don't understand the 1st one.

Is CustomerId a field name? What does the condition of strCustomerId mean?
Why does the condition is enclosed with &, double quote and sigle quote?
According to your explanation below, the text data requires enclosed with &,
double quote and sigle quote, the numeric data requires enclosed with & and
double quote and assume date & time requires none of above.

Thanks,

Scott

The criteria for DMax (and for all the other domain aggregate functions,
for
that matter) should like like a WHERE clause without the word WHERE in
front
of it. The following assumes that CustomerId is a text field, and that the
customer id of interest is stored in variable strCustomerId. If CustomerId
was numeric, you would remove the single quotes in the string:

DMax("InvoiceAmount", "MyTable", "CustomerId = '" & strCustomerId & "' And
Year([InvoiceDate]) = 2006")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I need to use DMax() function in event procedures but do not understand the
syntax of criteria part especially with two criteria. Can someone
explain
it to me.

Thanks,

Scott

 

[Scott]

Douglas,

Many thanks for your advice and useful information. I deeply appreciate
your information sharing.

Scott

As I said in my explanation, strCustomerId is a variable that holds the
Customer Id for the customer of interest.

What that code is doing is concatenating the value of the Customer Id into
the Where clause. Since it's a text value, it must be enclosed in quotes.
if
strCustomerId was STEE123, you'd end up with:

CustomerID = 'STEE123' And Year([InvoiceDate]) = 2006

It's not a case of "the text data requires enclosed with &, double quote
and
sigle quote, the numeric data requires enclosed with & and double quote
and
assume date & time requires none of above."

The & is there strictly for concatenation purposes.

Text data requires quotes. You can use either single or double quotes: I
could have written that as

"CustomerId = """ & strCustomerId & """ And Year([InvoiceDate]) = 2006"

(to represent a single double quote, you need to put two in a row) or

"CustomerId = " & Chr$(34) & strCustomerId & Chr$(34) & " And
Year([InvoiceDate]) = 2006"

(Chr$(34) represents ")

In fact, there are some issues using a single quote as I did in the
original
example. If the text value contained a single quote in it (say it was a
name, not an Id, and the name was O'Reilly), you'd run into problems. I
talk
about this in my May, 2004 "Access Answers" column in Pinnacle
Publication's
"Smart Access". You can download the column (and sample database) for free
at http://www.accessmvp.com/DJSteele/SmartAccess.html

Numeric data doesn't require quotes. If CustomerId was a numeric field,
you'd use:

"CustomerId = " & lngCustomerId & " And Year([InvoiceDate]) = 2006"

The quotes are there because you're creating a string.

Dates require # delimiters (as opposed to quotes). As well, the date needs
to be in mm/dd/yyyy format, regardless of what you may have set your Short
Date format to in Regional Settings. (Okay, this isn't strictly true. You
can use any unambiguous format, such as yyyy-mm-dd or dd mmm yyyy. The
point
is, you can't rely on using dd/mm/yyyy format, because Access will assume
it's mm/dd/yyyy and misinterpret it for the first 12 days of each month.
In
other words, it will always interpret 12/04/2006 as 04 Dec, 2006, not as
12
April, 2006.)



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas,

Thanks for your explanation.

In your example, it appears to have two criteria:

"CustomerId = '" & strCustomerId & "'
Year([InvoiceDate]) = 2006"

They are connected with AND.

The 2nd one is easy to understand while I don't understand the 1st one.

Is CustomerId a field name? What does the condition of strCustomerId mean?
Why does the condition is enclosed with &, double quote and sigle quote?
According to your explanation below, the text data requires enclosed with &,
double quote and sigle quote, the numeric data requires enclosed with & and
double quote and assume date & time requires none of above.

Thanks,

Scott

The criteria for DMax (and for all the other domain aggregate
functions,
for
that matter) should like like a WHERE clause without the word WHERE in
front
of it. The following assumes that CustomerId is a text field, and that the
customer id of interest is stored in variable strCustomerId. If CustomerId
was numeric, you would remove the single quotes in the string:

DMax("InvoiceAmount", "MyTable", "CustomerId = '" & strCustomerId & "' And
Year([InvoiceDate]) = 2006")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I need to use DMax() function in event procedures but do not
understand
the
syntax of criteria part especially with two criteria. Can someone
explain
it to me.

Thanks,

Scott

 

[Scott]

Douglas,

I tried to use what you have told me on my today's issue but seems the
scenario is more complex than I can handle.

I have a form having three fields: IDate, report and version. I would like
to automatically generate the version so I try to construct a DMax function
in After update event procedure of report as below:-

Me.Version = Nz(DMax("Version", "Table1", Year([IDate]) = Year([IDate]) AND
Report = Report),0)+1

I do not know how to reference to the values just entered into the fields as
the criteria. Is it a correct syntax for DMax or it is not inapplicable to
this function?

Thanks,

Scott

As I said in my explanation, strCustomerId is a variable that holds the
Customer Id for the customer of interest.

What that code is doing is concatenating the value of the Customer Id into
the Where clause. Since it's a text value, it must be enclosed in quotes.
if
strCustomerId was STEE123, you'd end up with:

CustomerID = 'STEE123' And Year([InvoiceDate]) = 2006

It's not a case of "the text data requires enclosed with &, double quote
and
sigle quote, the numeric data requires enclosed with & and double quote
and
assume date & time requires none of above."

The & is there strictly for concatenation purposes.

Text data requires quotes. You can use either single or double quotes: I
could have written that as

"CustomerId = """ & strCustomerId & """ And Year([InvoiceDate]) = 2006"

(to represent a single double quote, you need to put two in a row) or

"CustomerId = " & Chr$(34) & strCustomerId & Chr$(34) & " And
Year([InvoiceDate]) = 2006"

(Chr$(34) represents ")

In fact, there are some issues using a single quote as I did in the
original
example. If the text value contained a single quote in it (say it was a
name, not an Id, and the name was O'Reilly), you'd run into problems. I
talk
about this in my May, 2004 "Access Answers" column in Pinnacle
Publication's
"Smart Access". You can download the column (and sample database) for free
at http://www.accessmvp.com/DJSteele/SmartAccess.html

Numeric data doesn't require quotes. If CustomerId was a numeric field,
you'd use:

"CustomerId = " & lngCustomerId & " And Year([InvoiceDate]) = 2006"

The quotes are there because you're creating a string.

Dates require # delimiters (as opposed to quotes). As well, the date needs
to be in mm/dd/yyyy format, regardless of what you may have set your Short
Date format to in Regional Settings. (Okay, this isn't strictly true. You
can use any unambiguous format, such as yyyy-mm-dd or dd mmm yyyy. The
point
is, you can't rely on using dd/mm/yyyy format, because Access will assume
it's mm/dd/yyyy and misinterpret it for the first 12 days of each month.
In
other words, it will always interpret 12/04/2006 as 04 Dec, 2006, not as
12
April, 2006.)



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas,

Thanks for your explanation.

In your example, it appears to have two criteria:

"CustomerId = '" & strCustomerId & "'
Year([InvoiceDate]) = 2006"

They are connected with AND.

The 2nd one is easy to understand while I don't understand the 1st one.

Is CustomerId a field name? What does the condition of strCustomerId mean?
Why does the condition is enclosed with &, double quote and sigle quote?
According to your explanation below, the text data requires enclosed with &,
double quote and sigle quote, the numeric data requires enclosed with & and
double quote and assume date & time requires none of above.

Thanks,

Scott

The criteria for DMax (and for all the other domain aggregate
functions,
for
that matter) should like like a WHERE clause without the word WHERE in
front
of it. The following assumes that CustomerId is a text field, and that the
customer id of interest is stored in variable strCustomerId. If CustomerId
was numeric, you would remove the single quotes in the string:

DMax("InvoiceAmount", "MyTable", "CustomerId = '" & strCustomerId & "' And
Year([InvoiceDate]) = 2006")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I need to use DMax() function in event procedures but do not
understand
the
syntax of criteria part especially with two criteria. Can someone
explain
it to me.

Thanks,

Scott

 

[Douglas J Steele]

Are the fields in Table1 also named IDate, report and version? I'd suggest
renaming the text box controls on your form to ensure there's no confusion
as to whether you're referring to the text box or the field in the
underlying recordsource. I always rename all text boxes so that they start
with txt, so in what I give below, I'll refer to the text boxes on the form
as txtIDate, txtReport and txtVersion. If you don't want to change your text
box names, remove the txt from my solution.

You're trying to compare the year that's in txtIDate to the year that's in
the IDate field in your table. You're trying to compare the value in
txtReport to the value that's in the Report field in your table. Names of
fields in the table must be in quotes. However, the aggregate functions
don't know anything about variables or controls on forms, so they have to be
outside of the quotes so that you get their values, not their names.

If Report is a numeric field, you want:

Me.txtVersion = Nz(DMax("Version", "Table1", _
"Year(IDate) = " & Year(Me.txtIDate) & _
" AND Report = " & Me.txtReport),0)+1

If Report is a text field, you need quotes around the value you're passing
to it:

Me.txtVersion = Nz(DMax("Version", "Table1", _
"Year(IDate) = " & Year(Me.txtIDate) & _
" AND Report = '" & Me.txtReport & "'"),0)+1

where the last line, exagerated for clarity, is " AND Report = ' " &
Me.txtReport & " ' "),0)+1

Other ways of doing that last line are:

" AND Report = """ & Me.txtReport & """"),0)+1

(again exagerated for clarity " AND Report = " " " & Me.txtReport & " " "
" ),0)+1 )

or

" AND Report = " & Chr$(34) & Me.txtReport & Chr$(34)),0)+1


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas,

I tried to use what you have told me on my today's issue but seems the
scenario is more complex than I can handle.

I have a form having three fields: IDate, report and version. I would like
to automatically generate the version so I try to construct a DMax function
in After update event procedure of report as below:-

Me.Version = Nz(DMax("Version", "Table1", Year([IDate]) = Year([IDate]) AND
Report = Report),0)+1

I do not know how to reference to the values just entered into the fields as
the criteria. Is it a correct syntax for DMax or it is not inapplicable to
this function?

Thanks,

Scott

As I said in my explanation, strCustomerId is a variable that holds the
Customer Id for the customer of interest.

What that code is doing is concatenating the value of the Customer Id into
the Where clause. Since it's a text value, it must be enclosed in quotes.
if
strCustomerId was STEE123, you'd end up with:

CustomerID = 'STEE123' And Year([InvoiceDate]) = 2006

It's not a case of "the text data requires enclosed with &, double quote
and
sigle quote, the numeric data requires enclosed with & and double quote
and
assume date & time requires none of above."

The & is there strictly for concatenation purposes.

Text data requires quotes. You can use either single or double quotes: I
could have written that as

"CustomerId = """ & strCustomerId & """ And Year([InvoiceDate]) = 2006"

(to represent a single double quote, you need to put two in a row) or

"CustomerId = " & Chr$(34) & strCustomerId & Chr$(34) & " And
Year([InvoiceDate]) = 2006"

(Chr$(34) represents ")

In fact, there are some issues using a single quote as I did in the
original
example. If the text value contained a single quote in it (say it was a
name, not an Id, and the name was O'Reilly), you'd run into problems. I
talk
about this in my May, 2004 "Access Answers" column in Pinnacle
Publication's
"Smart Access". You can download the column (and sample database) for free
at http://www.accessmvp.com/DJSteele/SmartAccess.html

Numeric data doesn't require quotes. If CustomerId was a numeric field,
you'd use:

"CustomerId = " & lngCustomerId & " And Year([InvoiceDate]) = 2006"

The quotes are there because you're creating a string.

Dates require # delimiters (as opposed to quotes). As well, the date needs
to be in mm/dd/yyyy format, regardless of what you may have set your Short
Date format to in Regional Settings. (Okay, this isn't strictly true. You
can use any unambiguous format, such as yyyy-mm-dd or dd mmm yyyy. The
point
is, you can't rely on using dd/mm/yyyy format, because Access will assume
it's mm/dd/yyyy and misinterpret it for the first 12 days of each month.
In
other words, it will always interpret 12/04/2006 as 04 Dec, 2006, not as
12
April, 2006.)



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas,

Thanks for your explanation.

In your example, it appears to have two criteria:

"CustomerId = '" & strCustomerId & "'
Year([InvoiceDate]) = 2006"

They are connected with AND.

The 2nd one is easy to understand while I don't understand the 1st one.

Is CustomerId a field name? What does the condition of strCustomerId mean?
Why does the condition is enclosed with &, double quote and sigle quote?
According to your explanation below, the text data requires enclosed

with
&,

double quote and sigle quote, the numeric data requires enclosed with & and
double quote and assume date & time requires none of above.

Thanks,

Scott

The criteria for DMax (and for all the other domain aggregate
functions,
for
that matter) should like like a WHERE clause without the word WHERE in
front
of it. The following assumes that CustomerId is a text field, and

that
the

customer id of interest is stored in variable strCustomerId. If CustomerId
was numeric, you would remove the single quotes in the string:

DMax("InvoiceAmount", "MyTable", "CustomerId = '" & strCustomerId &

"'
And

Year([InvoiceDate]) = 2006")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I need to use DMax() function in event procedures but do not
understand
the
syntax of criteria part especially with two criteria. Can someone
explain
it to me.

Thanks,

Scott

 

[Scott]

Doug,

Many thanks for your detail explanation. The suggestion works like a charm!

As regards to quoting part, you spent a large portion to explain it. That
is important part to understand for future construction of expression.
However, I am unsure to understand it fully. May I dare to seek you further
advice on the following.

How to determine the pair of quotes of each module/condition including the
space? Referencing the last example, there are two conditions that are
connected with AND.

The control name should not use the same name of the field. I have learnt
it before but not always implement. The major reason is it works in most
circumstances. In addition, when we use a wizard to create a form, the
system automatically generates the control name same as the field name. It
does not change from Access 97 to 2003.

Scott

Are the fields in Table1 also named IDate, report and version? I'd suggest
renaming the text box controls on your form to ensure there's no confusion
as to whether you're referring to the text box or the field in the
underlying recordsource. I always rename all text boxes so that they start
with txt, so in what I give below, I'll refer to the text boxes on the
form
as txtIDate, txtReport and txtVersion. If you don't want to change your
text
box names, remove the txt from my solution.

You're trying to compare the year that's in txtIDate to the year that's in
the IDate field in your table. You're trying to compare the value in
txtReport to the value that's in the Report field in your table. Names of
fields in the table must be in quotes. However, the aggregate functions
don't know anything about variables or controls on forms, so they have to
be
outside of the quotes so that you get their values, not their names.

If Report is a numeric field, you want:

Me.txtVersion = Nz(DMax("Version", "Table1", _
"Year(IDate) = " & Year(Me.txtIDate) & _
" AND Report = " & Me.txtReport),0)+1

If Report is a text field, you need quotes around the value you're passing
to it:

Me.txtVersion = Nz(DMax("Version", "Table1", _
"Year(IDate) = " & Year(Me.txtIDate) & _
" AND Report = '" & Me.txtReport & "'"),0)+1

where the last line, exagerated for clarity, is " AND Report = ' " &
Me.txtReport & " ' "),0)+1

Other ways of doing that last line are:

" AND Report = """ & Me.txtReport & """"),0)+1

(again exagerated for clarity " AND Report = " " " & Me.txtReport & " "
"
" ),0)+1 )

or

" AND Report = " & Chr$(34) & Me.txtReport & Chr$(34)),0)+1


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas,

I tried to use what you have told me on my today's issue but seems the
scenario is more complex than I can handle.

I have a form having three fields: IDate, report and version. I would like
to automatically generate the version so I try to construct a DMax function
in After update event procedure of report as below:-

Me.Version = Nz(DMax("Version", "Table1", Year([IDate]) = Year([IDate]) AND
Report = Report),0)+1

I do not know how to reference to the values just entered into the fields as
the criteria. Is it a correct syntax for DMax or it is not inapplicable to
this function?

Thanks,

Scott

As I said in my explanation, strCustomerId is a variable that holds the
Customer Id for the customer of interest.

What that code is doing is concatenating the value of the Customer Id into
the Where clause. Since it's a text value, it must be enclosed in quotes.
if
strCustomerId was STEE123, you'd end up with:

CustomerID = 'STEE123' And Year([InvoiceDate]) = 2006

It's not a case of "the text data requires enclosed with &, double
quote
and
sigle quote, the numeric data requires enclosed with & and double quote
and
assume date & time requires none of above."

The & is there strictly for concatenation purposes.

Text data requires quotes. You can use either single or double quotes:
I
could have written that as

"CustomerId = """ & strCustomerId & """ And Year([InvoiceDate]) = 2006"

(to represent a single double quote, you need to put two in a row) or

"CustomerId = " & Chr$(34) & strCustomerId & Chr$(34) & " And
Year([InvoiceDate]) = 2006"

(Chr$(34) represents ")

In fact, there are some issues using a single quote as I did in the
original
example. If the text value contained a single quote in it (say it was a
name, not an Id, and the name was O'Reilly), you'd run into problems. I
talk
about this in my May, 2004 "Access Answers" column in Pinnacle
Publication's
"Smart Access". You can download the column (and sample database) for free
at http://www.accessmvp.com/DJSteele/SmartAccess.html

Numeric data doesn't require quotes. If CustomerId was a numeric field,
you'd use:

"CustomerId = " & lngCustomerId & " And Year([InvoiceDate]) = 2006"

The quotes are there because you're creating a string.

Dates require # delimiters (as opposed to quotes). As well, the date needs
to be in mm/dd/yyyy format, regardless of what you may have set your Short
Date format to in Regional Settings. (Okay, this isn't strictly true. You
can use any unambiguous format, such as yyyy-mm-dd or dd mmm yyyy. The
point
is, you can't rely on using dd/mm/yyyy format, because Access will assume
it's mm/dd/yyyy and misinterpret it for the first 12 days of each
month.
In
other words, it will always interpret 12/04/2006 as 04 Dec, 2006, not
as
12
April, 2006.)



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Douglas,

Thanks for your explanation.

In your example, it appears to have two criteria:

"CustomerId = '" & strCustomerId & "'
Year([InvoiceDate]) = 2006"

They are connected with AND.

The 2nd one is easy to understand while I don't understand the 1st
one.

Is CustomerId a field name? What does the condition of strCustomerId
mean?
Why does the condition is enclosed with &, double quote and sigle quote?
According to your explanation below, the text data requires enclosed with
&,
double quote and sigle quote, the numeric data requires enclosed with
&
and
double quote and assume date & time requires none of above.

Thanks,

Scott

message
The criteria for DMax (and for all the other domain aggregate
functions,
for
that matter) should like like a WHERE clause without the word WHERE in
front
of it. The following assumes that CustomerId is a text field, and that
the
customer id of interest is stored in variable strCustomerId. If
CustomerId
was numeric, you would remove the single quotes in the string:

DMax("InvoiceAmount", "MyTable", "CustomerId = '" & strCustomerId & "'
And
Year([InvoiceDate]) = 2006")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I need to use DMax() function in event procedures but do not
understand
the
syntax of criteria part especially with two criteria. Can someone
explain
it to me.

Thanks,

Scott

 

[Douglas J. Steele]

Text values always need to have quotes around them. The keyword AND always
needs at least one space before and after it. Not sure whether that's what
you're asking or not.

And yes, I find it very annoying that Access automatically names bound
controls to the same as the field to which they're bound. It's possible to
write code that renames the controls, or you can simply go back and rename
them manually.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Doug,

Many thanks for your detail explanation. The suggestion works like a
charm!

As regards to quoting part, you spent a large portion to explain it. That
is important part to understand for future construction of expression.
However, I am unsure to understand it fully. May I dare to seek you
further advice on the following.

How to determine the pair of quotes of each module/condition including the
space? Referencing the last example, there are two conditions that are
connected with AND.

The control name should not use the same name of the field. I have learnt
it before but not always implement. The major reason is it works in most
circumstances. In addition, when we use a wizard to create a form, the
system automatically generates the control name same as the field name.
It does not change from Access 97 to 2003.

Scott

Are the fields in Table1 also named IDate, report and version? I'd
suggest
renaming the text box controls on your form to ensure there's no
confusion
as to whether you're referring to the text box or the field in the
underlying recordsource. I always rename all text boxes so that they
start
with txt, so in what I give below, I'll refer to the text boxes on the
form
as txtIDate, txtReport and txtVersion. If you don't want to change your
text
box names, remove the txt from my solution.

You're trying to compare the year that's in txtIDate to the year that's
in
the IDate field in your table. You're trying to compare the value in
txtReport to the value that's in the Report field in your table. Names of
fields in the table must be in quotes. However, the aggregate functions
don't know anything about variables or controls on forms, so they have to
be
outside of the quotes so that you get their values, not their names.

If Report is a numeric field, you want:

Me.txtVersion = Nz(DMax("Version", "Table1", _
"Year(IDate) = " & Year(Me.txtIDate) & _
" AND Report = " & Me.txtReport),0)+1

If Report is a text field, you need quotes around the value you're
passing
to it:

Me.txtVersion = Nz(DMax("Version", "Table1", _
"Year(IDate) = " & Year(Me.txtIDate) & _
" AND Report = '" & Me.txtReport & "'"),0)+1

where the last line, exagerated for clarity, is " AND Report = ' " &
Me.txtReport & " ' "),0)+1

Other ways of doing that last line are:

" AND Report = """ & Me.txtReport & """"),0)+1

(again exagerated for clarity " AND Report = " " " & Me.txtReport & "
" "
" ),0)+1 )

or

" AND Report = " & Chr$(34) & Me.txtReport & Chr$(34)),0)+1


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas,

I tried to use what you have told me on my today's issue but seems the
scenario is more complex than I can handle.

I have a form having three fields: IDate, report and version. I would like
to automatically generate the version so I try to construct a DMax function
in After update event procedure of report as below:-

Me.Version = Nz(DMax("Version", "Table1", Year([IDate]) = Year([IDate]) AND
Report = Report),0)+1

I do not know how to reference to the values just entered into the
fields as
the criteria. Is it a correct syntax for DMax or it is not inapplicable to
this function?

Thanks,

Scott

As I said in my explanation, strCustomerId is a variable that holds
the
Customer Id for the customer of interest.

What that code is doing is concatenating the value of the Customer Id into
the Where clause. Since it's a text value, it must be enclosed in quotes.
if
strCustomerId was STEE123, you'd end up with:

CustomerID = 'STEE123' And Year([InvoiceDate]) = 2006

It's not a case of "the text data requires enclosed with &, double
quote
and
sigle quote, the numeric data requires enclosed with & and double
quote
and
assume date & time requires none of above."

The & is there strictly for concatenation purposes.

Text data requires quotes. You can use either single or double quotes:
I
could have written that as

"CustomerId = """ & strCustomerId & """ And Year([InvoiceDate]) =
2006"

(to represent a single double quote, you need to put two in a row) or

"CustomerId = " & Chr$(34) & strCustomerId & Chr$(34) & " And
Year([InvoiceDate]) = 2006"

(Chr$(34) represents ")

In fact, there are some issues using a single quote as I did in the
original
example. If the text value contained a single quote in it (say it was
a
name, not an Id, and the name was O'Reilly), you'd run into problems.
I
talk
about this in my May, 2004 "Access Answers" column in Pinnacle
Publication's
"Smart Access". You can download the column (and sample database) for free
at http://www.accessmvp.com/DJSteele/SmartAccess.html

Numeric data doesn't require quotes. If CustomerId was a numeric
field,
you'd use:

"CustomerId = " & lngCustomerId & " And Year([InvoiceDate]) = 2006"

The quotes are there because you're creating a string.

Dates require # delimiters (as opposed to quotes). As well, the date needs
to be in mm/dd/yyyy format, regardless of what you may have set your Short
Date format to in Regional Settings. (Okay, this isn't strictly true. You
can use any unambiguous format, such as yyyy-mm-dd or dd mmm yyyy. The
point
is, you can't rely on using dd/mm/yyyy format, because Access will assume
it's mm/dd/yyyy and misinterpret it for the first 12 days of each
month.
In
other words, it will always interpret 12/04/2006 as 04 Dec, 2006, not
as
12
April, 2006.)



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Douglas,

Thanks for your explanation.

In your example, it appears to have two criteria:

"CustomerId = '" & strCustomerId & "'
Year([InvoiceDate]) = 2006"

They are connected with AND.

The 2nd one is easy to understand while I don't understand the 1st
one.

Is CustomerId a field name? What does the condition of strCustomerId
mean?
Why does the condition is enclosed with &, double quote and sigle quote?
According to your explanation below, the text data requires enclosed with
&,
double quote and sigle quote, the numeric data requires enclosed with
&
and
double quote and assume date & time requires none of above.

Thanks,

Scott

message
The criteria for DMax (and for all the other domain aggregate
functions,
for
that matter) should like like a WHERE clause without the word WHERE in
front
of it. The following assumes that CustomerId is a text field, and that
the
customer id of interest is stored in variable strCustomerId. If
CustomerId
was numeric, you would remove the single quotes in the string:

DMax("InvoiceAmount", "MyTable", "CustomerId = '" & strCustomerId & "'
And
Year([InvoiceDate]) = 2006")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I need to use DMax() function in event procedures but do not
understand
the
syntax of criteria part especially with two criteria. Can someone
explain
it to me.

Thanks,

Scott