Using Dmax with years, criteria match to current year

[Guest]

Access 2000 and 2003

I have data that looks like this:
Fields: ID (autonumber), year2dig (date field formatted "yy"), studynum
(long integer)
Data:
1,99,1
2,99,2
3,99,3
4,00,1
5,00,2
6,05,1
7,05,2
8,05,3
etc.

My users want to be able to continue to automatically increment the new
record studynum by one during a given year. Counting would start again at
the beginning of a new year.

As a beginning to work out out the code to do this, I am trying to simply
create a calculated field that will list the maximum studynum for the current
year. So since it is currently the year 2005, the maximum study number
should show as 3. I am using the DMAX function like this:

=dmax("[studynum]","tblMain","DatePart("yyyy",[year2dig])=DatePart("yyyy",Now())")

I know each part of the criteria portion of the dmax function works
individually, but strung together I'm getting an invalid syntax message.
I'll admit I have trouble with knowing where the quotes go.

I'd appreciate any suggestions on how to correct that code to show the
maximum study number for the current year (which I plan to add 1 to to get
the next study number).

Thank you,

Nicole

 

[Guest]

Two options
=dmax("[studynum]","tblMain","DatePart('yyyy',[year2dig])=DatePart('yyyy',Now())")
change the double quote to single before and after the yyyy
Or use the function year
=dmax("[studynum]","tblMain","year([year2dig])=year(Now())")

 

[Guest]

Thank you--I used the second option and it works. Guess I was making it
harder than I needed to by using the datepart function! And those quotes
always throw me off.

I thought I had posted to the Forms group, sorry for using the general
questions group!

Nicole

Two options
=dmax("[studynum]","tblMain","DatePart('yyyy',[year2dig])=DatePart('yyyy',Now())")
change the double quote to single before and after the yyyy
Or use the function year
=dmax("[studynum]","tblMain","year([year2dig])=year(Now())")

Access 2000 and 2003

I have data that looks like this:
Fields: ID (autonumber), year2dig (date field formatted "yy"), studynum
(long integer)
Data:
1,99,1
2,99,2
3,99,3
4,00,1
5,00,2
6,05,1
7,05,2
8,05,3
etc.

My users want to be able to continue to automatically increment the new
record studynum by one during a given year. Counting would start again at
the beginning of a new year.

As a beginning to work out out the code to do this, I am trying to simply
create a calculated field that will list the maximum studynum for the current
year. So since it is currently the year 2005, the maximum study number
should show as 3. I am using the DMAX function like this:

=dmax("[studynum]","tblMain","DatePart("yyyy",[year2dig])=DatePart("yyyy",Now())")

I know each part of the criteria portion of the dmax function works
individually, but strung together I'm getting an invalid syntax message.
I'll admit I have trouble with knowing where the quotes go.

I'd appreciate any suggestions on how to correct that code to show the
maximum study number for the current year (which I plan to add 1 to to get
the next study number).

Thank you,

Nicole

 

[Guest]

No problem, have a nice weekend

Thank you--I used the second option and it works. Guess I was making it
harder than I needed to by using the datepart function! And those quotes
always throw me off.

I thought I had posted to the Forms group, sorry for using the general
questions group!

Nicole

Two options
=dmax("[studynum]","tblMain","DatePart('yyyy',[year2dig])=DatePart('yyyy',Now())")
change the double quote to single before and after the yyyy
Or use the function year
=dmax("[studynum]","tblMain","year([year2dig])=year(Now())")

Access 2000 and 2003

I have data that looks like this:
Fields: ID (autonumber), year2dig (date field formatted "yy"), studynum
(long integer)
Data:
1,99,1
2,99,2
3,99,3
4,00,1
5,00,2
6,05,1
7,05,2
8,05,3
etc.

My users want to be able to continue to automatically increment the new
record studynum by one during a given year. Counting would start again at
the beginning of a new year.

As a beginning to work out out the code to do this, I am trying to simply
create a calculated field that will list the maximum studynum for the current
year. So since it is currently the year 2005, the maximum study number
should show as 3. I am using the DMAX function like this:

=dmax("[studynum]","tblMain","DatePart("yyyy",[year2dig])=DatePart("yyyy",Now())")

I know each part of the criteria portion of the dmax function works
individually, but strung together I'm getting an invalid syntax message.
I'll admit I have trouble with knowing where the quotes go.

I'd appreciate any suggestions on how to correct that code to show the
maximum study number for the current year (which I plan to add 1 to to get
the next study number).

Thank you,

Nicole