Auto Number based on Date

[Guest]

I have a table that has a date field and a count field. What I want is to
count how many days are being used for each month and if its a new month
start over at 1. So example:
I have 4 entries for the month Feb, so I would want the following.
Date Count
2/5/2006 1
2/5/2006 2
2/5/2006 3
2/7/2006 4
3/2/2006 1
and so on.....

I want this to work in the count field for a FORM in access. Thanks!

 

[kingston via AccessMonster.com]

I'm not sure what you want [Count] to do, but if you have a list of dates,
you can count the number in each month by creating a totals query:

Group by Year(yourDate)
Group by Month(yourDate)
Count (yourDate)

Otherwise, for a specific month, you can return one result using:

DCount("*","yourTable","Year(yourDate) = desiredYear AND Month(yourDate) =
desiredMonth")

 

[Guest]

sorry for not being so specific, but what I want is to have an AUTO NUMBER on
the FORM that will continue to count the amount of days that are being put in
the form for each month as the table I put below. So lets say the month of
May could have 1826 entries just for that month and for June it could be 28.
But each month should start with the number 1.

I'm not sure what you want [Count] to do, but if you have a list of dates,
you can count the number in each month by creating a totals query:

Group by Year(yourDate)
Group by Month(yourDate)
Count (yourDate)

Otherwise, for a specific month, you can return one result using:

DCount("*","yourTable","Year(yourDate) = desiredYear AND Month(yourDate) =
desiredMonth")

I have a table that has a date field and a count field. What I want is to
count how many days are being used for each month and if its a new month
start over at 1. So example:
I have 4 entries for the month Feb, so I would want the following.
Date Count
2/5/2006 1
2/5/2006 2
2/5/2006 3
2/7/2006 4
3/2/2006 1
and so on.....

I want this to work in the count field for a FORM in access. Thanks!

 

[kingston via AccessMonster.com]

I'm not sure what you mean by AUTO NUMBER. Automatic IDs are created during
table design and are incrementing integers only. You can still use the query
or the DCount function I provided to specify the count of records that match
the month and date of the records on the form. You just have to refresh the
count control (and its data) whenever a new entry is made, probably via the
date control's After Update event. There is no way to correctly predict the
count for a new entry until a date has been entered so leave it blank or set
to 0 or 1.

sorry for not being so specific, but what I want is to have an AUTO NUMBER on
the FORM that will continue to count the amount of days that are being put in
the form for each month as the table I put below. So lets say the month of
May could have 1826 entries just for that month and for June it could be 28.
But each month should start with the number 1.

I'm not sure what you want [Count] to do, but if you have a list of dates,
you can count the number in each month by creating a totals query:

[quoted text clipped - 21 lines]

 

[Guest]

An Autonumber field will not work for this. You need to create your own
incrementing routine.
Since I don't know if the date is entered by the user or it is the Default
Value for the date control, I can't be sure where to put the code, but this
is the basic concept:

If Me.NewRecord Then
Me.txtCount = Nz(DMax("[CountField]", "MyTable", "[DateField] = #" & _
Me.txtDate & "#"),0) + 1
End If

sorry for not being so specific, but what I want is to have an AUTO NUMBER on
the FORM that will continue to count the amount of days that are being put in
the form for each month as the table I put below. So lets say the month of
May could have 1826 entries just for that month and for June it could be 28.
But each month should start with the number 1.

I'm not sure what you want [Count] to do, but if you have a list of dates,
you can count the number in each month by creating a totals query:

Group by Year(yourDate)
Group by Month(yourDate)
Count (yourDate)

Otherwise, for a specific month, you can return one result using:

DCount("*","yourTable","Year(yourDate) = desiredYear AND Month(yourDate) =
desiredMonth")

I have a table that has a date field and a count field. What I want is to
count how many days are being used for each month and if its a new month
start over at 1. So example:
I have 4 entries for the month Feb, so I would want the following.
Date Count
2/5/2006 1
2/5/2006 2
2/5/2006 3
2/7/2006 4
3/2/2006 1
and so on.....

I want this to work in the count field for a FORM in access. Thanks!

 

[Guest]

Thanks anyway, I just thought that there could have been a way to do this.

I'm not sure what you mean by AUTO NUMBER. Automatic IDs are created during
table design and are incrementing integers only. You can still use the query
or the DCount function I provided to specify the count of records that match
the month and date of the records on the form. You just have to refresh the
count control (and its data) whenever a new entry is made, probably via the
date control's After Update event. There is no way to correctly predict the
count for a new entry until a date has been entered so leave it blank or set
to 0 or 1.

sorry for not being so specific, but what I want is to have an AUTO NUMBER on
the FORM that will continue to count the amount of days that are being put in
the form for each month as the table I put below. So lets say the month of
May could have 1826 entries just for that month and for June it could be 28.
But each month should start with the number 1.

I'm not sure what you want [Count] to do, but if you have a list of dates,
you can count the number in each month by creating a totals query:

[quoted text clipped - 21 lines]

I want this to work in the count field for a FORM in access. Thanks!

 

[Guest]

The date is entered by the user.

An Autonumber field will not work for this. You need to create your own
incrementing routine.
Since I don't know if the date is entered by the user or it is the Default
Value for the date control, I can't be sure where to put the code, but this
is the basic concept:

If Me.NewRecord Then
Me.txtCount = Nz(DMax("[CountField]", "MyTable", "[DateField] = #" & _
Me.txtDate & "#"),0) + 1
End If

sorry for not being so specific, but what I want is to have an AUTO NUMBER on
the FORM that will continue to count the amount of days that are being put in
the form for each month as the table I put below. So lets say the month of
May could have 1826 entries just for that month and for June it could be 28.
But each month should start with the number 1.

I'm not sure what you want [Count] to do, but if you have a list of dates,
you can count the number in each month by creating a totals query:

Group by Year(yourDate)
Group by Month(yourDate)
Count (yourDate)

Otherwise, for a specific month, you can return one result using:

DCount("*","yourTable","Year(yourDate) = desiredYear AND Month(yourDate) =
desiredMonth")


jac007 wrote:
I have a table that has a date field and a count field. What I want is to
count how many days are being used for each month and if its a new month
start over at 1. So example:
I have 4 entries for the month Feb, so I would want the following.
Date Count
2/5/2006 1
2/5/2006 2
2/5/2006 3
2/7/2006 4
3/2/2006 1
and so on.....

I want this to work in the count field for a FORM in access. Thanks!

 

[Guest]

Then put the code in the After Update event of the control where the user
enters the date.

The date is entered by the user.

An Autonumber field will not work for this. You need to create your own
incrementing routine.
Since I don't know if the date is entered by the user or it is the Default
Value for the date control, I can't be sure where to put the code, but this
is the basic concept:

If Me.NewRecord Then
Me.txtCount = Nz(DMax("[CountField]", "MyTable", "[DateField] = #" & _
Me.txtDate & "#"),0) + 1
End If

sorry for not being so specific, but what I want is to have an AUTO NUMBER on
the FORM that will continue to count the amount of days that are being put in
the form for each month as the table I put below. So lets say the month of
May could have 1826 entries just for that month and for June it could be 28.
But each month should start with the number 1.

:

I'm not sure what you want [Count] to do, but if you have a list of dates,
you can count the number in each month by creating a totals query:

Group by Year(yourDate)
Group by Month(yourDate)
Count (yourDate)

Otherwise, for a specific month, you can return one result using:

DCount("*","yourTable","Year(yourDate) = desiredYear AND Month(yourDate) =
desiredMonth")


jac007 wrote:
I have a table that has a date field and a count field. What I want is to
count how many days are being used for each month and if its a new month
start over at 1. So example:
I have 4 entries for the month Feb, so I would want the following.
Date Count
2/5/2006 1
2/5/2006 2
2/5/2006 3
2/7/2006 4
3/2/2006 1
and so on.....

I want this to work in the count field for a FORM in access. Thanks!

 

[Guest]

Thanks!! Now, will that data be stored in the table or query where the fields
from the FORM are coming from?

Then put the code in the After Update event of the control where the user
enters the date.

The date is entered by the user.

An Autonumber field will not work for this. You need to create your own
incrementing routine.
Since I don't know if the date is entered by the user or it is the Default
Value for the date control, I can't be sure where to put the code, but this
is the basic concept:

If Me.NewRecord Then
Me.txtCount = Nz(DMax("[CountField]", "MyTable", "[DateField] = #" & _
Me.txtDate & "#"),0) + 1
End If

:

sorry for not being so specific, but what I want is to have an AUTO NUMBER on
the FORM that will continue to count the amount of days that are being put in
the form for each month as the table I put below. So lets say the month of
May could have 1826 entries just for that month and for June it could be 28.
But each month should start with the number 1.

:

I'm not sure what you want [Count] to do, but if you have a list of dates,
you can count the number in each month by creating a totals query:

Group by Year(yourDate)
Group by Month(yourDate)
Count (yourDate)

Otherwise, for a specific month, you can return one result using:

DCount("*","yourTable","Year(yourDate) = desiredYear AND Month(yourDate) =
desiredMonth")


jac007 wrote:
I have a table that has a date field and a count field. What I want is to
count how many days are being used for each month and if its a new month
start over at 1. So example:
I have 4 entries for the month Feb, so I would want the following.
Date Count
2/5/2006 1
2/5/2006 2
2/5/2006 3
2/7/2006 4
3/2/2006 1
and so on.....

I want this to work in the count field for a FORM in access. Thanks!

 

[Guest]

assuming it is a bound form, yes

Thanks!! Now, will that data be stored in the table or query where the fields
from the FORM are coming from?

Then put the code in the After Update event of the control where the user
enters the date.

The date is entered by the user.

:

An Autonumber field will not work for this. You need to create your own
incrementing routine.
Since I don't know if the date is entered by the user or it is the Default
Value for the date control, I can't be sure where to put the code, but this
is the basic concept:

If Me.NewRecord Then
Me.txtCount = Nz(DMax("[CountField]", "MyTable", "[DateField] = #" & _
Me.txtDate & "#"),0) + 1
End If

:

sorry for not being so specific, but what I want is to have an AUTO NUMBER on
the FORM that will continue to count the amount of days that are being put in
the form for each month as the table I put below. So lets say the month of
May could have 1826 entries just for that month and for June it could be 28.
But each month should start with the number 1.

:

I'm not sure what you want [Count] to do, but if you have a list of dates,
you can count the number in each month by creating a totals query:

Group by Year(yourDate)
Group by Month(yourDate)
Count (yourDate)

Otherwise, for a specific month, you can return one result using:

DCount("*","yourTable","Year(yourDate) = desiredYear AND Month(yourDate) =
desiredMonth")


jac007 wrote:
I have a table that has a date field and a count field. What I want is to
count how many days are being used for each month and if its a new month
start over at 1. So example:
I have 4 entries for the month Feb, so I would want the following.
Date Count
2/5/2006 1
2/5/2006 2
2/5/2006 3
2/7/2006 4
3/2/2006 1
and so on.....

I want this to work in the count field for a FORM in access. Thanks!