My Tracking Number

[Guest]

Objects are “Userformâ€, Textbox for Date(TxtDate), Textbox for
TrackNo(TxtTrackNo), Record Source(MyData) with TrackNo and Date as fields.

When user enters the Date on userform I need VBA procedure to count or sum
from the record source where the dates in table equal the year and month of
the date entered by user. This currently returns a number based on the entire
date and not the year/month part of the date.

IndexNo = DCount([Date], "MyData", [Date] = TxtDate.Value) + 1

Dates entered in record table field named Date
6/1/2005, 6/8/2005, 6/12/2005, 6/15/2006, 6/9/2006, 6/18/2006, 6/23/2006

User enters 6/25/2005 and I want the program to return 4 because there are 3
dates that equal the year 2005 and month 6 then adds 1 to the count/sum. My
goal is to use this to make tracking number for example: T0606-004 =
“Tâ€&Format(TxtDate.Value,â€yymmâ€)&â€-“& Format(IndexNo,â€000â€). If the user
entered 6/2/2007 then the IndexNo will equal 1 because there are no 2007 and
6 dates in the record table, it resets the TrackNo. I have been able do this
by year but cannot seem to figure how to do this by year and month.

I am highly grateful for any help you can offer. I am using Access 2003.

Thanks

 

[John Spencer]

IndexNo = DCount("*","MyData","Format([Date],""yyyymm"") =""" &
Format(TxtDate,"yyyymm") + 1

Date is a bad name for a field. It really needs to be something like
ActivityDate, TrackDate, or ...

Date is a function that will return the system date and you could end up with
Access confusing one with the other (not to mention any poor humans.)

The above is not all that efficient and you might want to use the following if
your date FIELD is indexed

IndexNo = 1 + DCount("*","MyData", _
"[Date]>=" & _
Format(DateSerial(Year(txtDate),Month(txtDate),1),"#yyyy-mm-dd#") & _
" AND [Date] < " & _
Format(DateSerial(Year(txtDate),Month(txtDate) +1 ,1),"#yyyy-mm-dd#") )

 

[Guest]

Thank you John,
I think I can play with this one. Duh!, never thought about the teay being
"yyyy".

The use of Date can be confusing so I will change to DateOpen to compare
with DateClosed field. Then i can use the Days formula later in my reports to
see how long a trackitem is open by users.

Thanks again
George


--
George G

IndexNo = DCount("*","MyData","Format([Date],""yyyymm"") =""" &
Format(TxtDate,"yyyymm") + 1

Date is a bad name for a field. It really needs to be something like
ActivityDate, TrackDate, or ...

Date is a function that will return the system date and you could end up with
Access confusing one with the other (not to mention any poor humans.)

The above is not all that efficient and you might want to use the following if
your date FIELD is indexed

IndexNo = 1 + DCount("*","MyData", _
"[Date]>=" & _
Format(DateSerial(Year(txtDate),Month(txtDate),1),"#yyyy-mm-dd#") & _
" AND [Date] < " & _
Format(DateSerial(Year(txtDate),Month(txtDate) +1 ,1),"#yyyy-mm-dd#") )

Objects are “Userformâ€*, Textbox for Date(TxtDate), Textbox for
TrackNo(TxtTrackNo), Record Source(MyData) with TrackNo and Date as fields.

When user enters the Date on userform I need VBA procedure to count or sum
from the record source where the dates in table equal the year and month of
the date entered by user. This currently returns a number based on the entire
date and not the year/month part of the date.

IndexNo = DCount([Date], "MyData", [Date] = TxtDate.Value) + 1

Dates entered in record table field named Date
6/1/2005, 6/8/2005, 6/12/2005, 6/15/2006, 6/9/2006, 6/18/2006, 6/23/2006

User enters 6/25/2005 and I want the program to return 4 because there are 3
dates that equal the year 2005 and month 6 then adds 1 to the count/sum. My
goal is to use this to make tracking number for example: T0606-004 =
“Tâ€*&Format(TxtDate.Value,â€*yymmâ€*)&â€*-“& Format(IndexNo,â€*000â€*). If the user
entered 6/2/2007 then the IndexNo will equal 1 because there are no 2007 and
6 dates in the record table, it resets the TrackNo. I have been able do this
by year but cannot seem to figure how to do this by year and month.

I am highly grateful for any help you can offer. I am using Access 2003.

Thanks