Option Group answer used to filter a query (1 Viewer)

[RJW]

On a form I have an option group with three choices:
Current Month
Last Month
Year to Date

I need the users choice to filter a date field in a query.

I tried ...

After Update:
If Me.Frame29 = 3 Then
Me.Text38 = "Year((Now()))"

And then in the query I had the criteria for the date field as ..

[Forms]![BuildAReport]![Text38]

When opening the query it displayed an error that started ...
"This expression is typed incorrectly, or is too complex ..."

Obviously, Help.

Thanks.

 

[Minty]

I would create two hidden texboxes (Or visible ones...) txtDateFrom and txtDateTo. Then in your after update code do something like

Select Case Me.Frame29 
    Case 3 
       Me.txtDateFrom = Dateserial (Year(Date),1,1)
       Me.txtDateTo = Date()
    Case 2 
       Me.txtDAteFrom = DateSerial(Year(Date), Month(Date) - 1, 1)
       Me.txtDateTo = DateSerial(Year(Date), Month(Date), 1)-1
    Case 1
       ' Your turn to work it out :)
End Select

And refer to the two boxes in your criteria like

Between [Forms]![BuildAReport]![txtxDateFrom] And [Forms]![BuildAReport]![txtDateTo]

 

[bob fitz]

And then in the query I had the criteria for the date field as ..

The column on which you have set the criteria would need to be a calculated field returning the year from your date field, not the actual date.

 

[CJ_London]

you have converted your date to text. try

Me.Text38 = Year(Date())

you haven't shown what your criteria is working on - it needs to be something like

Expr1: Year([mydatefield])

 

[RJW]

Minty:

I like your answer.

But the query comes back empty.

I made the fields visible. For option 3, I see 1/12017 and 7/24/2017 in the text fields.

I even fixed your typo for "between"

But, still empty.

Any ideas?

RJ

 

[RJW]

typo on the first date. The field shows 1/1/2017

 

[RJW]

I found code for query

>=CDate([Forms]![BuildAReport]![txtDateFrom]) And <=CDate([Forms]![BuildAReport]![txtDateTo])

Now "my turn" the first Select Case code.

Thanks

 

[Minty]

I found code for query

>=CDate([Forms]![BuildAReport]![txtDateFrom]) And <=CDate([Forms]![BuildAReport]![txtDateTo])

Now "my turn" the first Select Case code.

Thanks

Glad you got it working.
Not sure why you needed the CDate though. Did you format the textboxes as dates? I use that exact syntax frequently without issues.

Sent from my Nexus 7 using Tapatalk