Want checkbox selection [TRUE] to fill in date of earliest project in table.

[wily_wolf]

Hey, I currently have a checkbox that when checked [TRUE] fills in two textboxes with the dates 1/1/2016 and 12/31/2016.




I would like the checkbox, when checked [TRUE], to fill in the first textbox with the earliest project date from my table, and the second textbox with the latest project date from my table. All in that certain year too. So, 2016 for instance.

Here is the code I have so far. Need help with what the value would/could be.

Private Sub chk2016_Click()
    If Me.chk2016 = True Then
        Me.txtStart.Value = #1/1/2016#
        Me.txtEnd.Value = #12/31/2016#
    ElseIf Me.chk2016 = False Then
        Me.txtStart.Value = Null
        Me.txtEnd.Value = Null
        
    End If
    
End Sub

Thanks in advance.

[ranman256]

make a form, on the form is a textbox called txtYr to put the current year you want.
make a query called , qs1Year, this reads the text box txtYr off the form and pulls the data.
add an extra field to the query called Year, = year(projDate)

the query would look like:
select *, year(projDate) as Year from table where year(projDate) = forms!myForm!txtYr

then

Private Sub chk2016_Click()
    If Me.chk2016 = True Then
        Me.txtStart.Value = dMin("[ProjDate]","qs1Year", "[year]=" & txtYr)
        Me.txtStart.Value = dMax("[ProjDate]","qs1Year", "[year]=" & txtYr)
    Else
        Me.txtStart.Value = Null
        Me.txtEnd.Value = Null
    End If    
End Sub

[John_G]

Those functions should be DMin and DMax, not Min and Max.

[ranman256]

Oops,thx JohnG

[wily_wolf]

Great, I'll give that a try and get back to you.

[wily_wolf]

I found that this was a better approach to what I was thinking originally. I didn't really want to have people have to input a year into a textbox. Rather, I wanted them to just be able to select the "2016" checkbox and auto-fill the Start and End Date textboxes.

Here's what I have for future reference:

Private Sub chk2016_Click()
    
    If Me.chk2016 = True Then
        Me.txtStart.Value = DMin("[ProjectDate]", "tblTimeTracker", "Year([ProjectDate])=" & "2016")
        Me.txtEnd.Value = DMax("[ProjectDate]", "tblTimeTracker", "Year([ProjectDate])=" & "2016")
    Else
        Me.txtStart.Value = Null
        Me.txtEnd.Value = Null
    End If
End Sub

Thanks for leading me to the answer though.

Take care.

[ranman256]

make a form, on the form is a textbox called txtYr to put the current year you want.
make a query called , qs1Year, this reads the text box txtYr off the form and pulls the data.
add an extra field to the query called Year, = year(projDate)

the query would look like:
select *, year(projDate) as Year from table where year(projDate) = forms!myForm!txtYr

then

Private Sub chk2016_Click()
    If Me.chk2016 = True Then
        Me.txtStart.Value = Min("[ProjDate]","qs1Year", "[year]=" & txtYr)
        Me.txtStart.Value = Max("[ProjDate]","qs1Year", "[year]=" & txtYr)
    Else
        Me.txtStart.Value = Null
        Me.txtEnd.Value = Null
    End If    
End Sub

[ranman256]

what are you gonna do in 2017?

[ssanfu]

Much better would be a table... maybe "tblAvailableYears".
Or a query that gets the distinct dates in field "ProjDate".
Use an unbound combo box so the year could be selected. The combo box after update event could run code to get the min/max ProjDate dates for the year selected.
You could even default the combo box to default to the current year and have a button execute the code to get the min/max ProjDate dates.


Using a check box with the year as the field name means you have to modify forms, queries, reports & code when the year changes.