Counting records based on date input

kasceus

New member
Local time
Today, 11:43
Joined
Mar 21, 2013
Messages
9
Hello,
I am having a problem with my VB code. I am trying to count the records in a table based on what date they were input. I can get it to count all records, but it doesn't want to count specific records. I have tried several ways of doing the if statements, and ways of entering in parameters in the dcount function.

Here is my code as it is now:
Code:
Private Sub Cmdref_Click()
DoCmd.GoToRecord , , acNewRec
 Dim current_year As String
 Dim store_yy
 Dim me_yy As String
    [YY].SetFocus
    me_yy = Me.YY.Text
    current_year = (Format(Now(), "yy"))
    
    
If me_yy = current_year Then
   store_yy = DCount("[yy]", "tblQA_FOD", "[yy] =" & current_year)
    
    Me.RID = Me.Wing & current_year & "-" & (Format(store_yy + 1, "#,000"))
   
   'Me.RID = store_yy
    End If
End Sub

The default value of [yy] is Date() and is formatted to yy. The default value of [wing] is 18FW. I am trying to get me.RID to equal 18FW13-001. I can get it to add to the 001, but it does not start over again at a new year. i guess, what i'm trying to do is once [yy] changes to 14, as it will next year, i want it to start back at 001. As i said before, i can get it to count all of the records, but not what records are from this year only. Any advice on this would be greatly appreciated. Also, if you know of a way to do this without using VB, that would work as well.
 
ok, so i figured out how to do it. it took a few more lines, but it works just as i hopped. Here's how i did it... in case someone may need this in the future.
Code:
Option Compare Database

Private Sub Cmdref_Click()
DoCmd.GoToRecord , , acNewRec
 Dim me_yy As String
 Dim Day_count
 Dim count_ly
 Dim count_ty
    [YY].SetFocus
    me_yy = Me.YY.Text
    
    'set the two digit date format for this year
      current_year = (Format(Year(Date), "yy"))
    
    'count how many days there are from january 1st to now.
       Day_count = DateDiff("d", DateSerial(Year(Date), 1, 1), Now())
    
    'count the records from before current year
   count_ly = DCount("[YY]", "tblQA_FOD", " [YY] < (now()-" & Day_count & ") ")
   'count the records from this year
   count_ty = DCount("[YY]", "tblQA_FOD", " [YY] < now() ")
   'Subtract last year's record count from this year's, and add 1
     count_ty = count_ty - count_ly + 1
     'Output report id in the correct format
    Me.RID = Me.Wing & me_yy & "-" & (Format(count_ty, "#,000"))
End Sub
 

Users who are viewing this thread

Back
Top Bottom