Between dates in code

[Guest]

Hi

I have an option group with Jan, Apr, Jul, Oct and ALL (MonthFrame) on a
form and an unbound text box containing the Year (YearFilter). The Values are
1, 4, 7, 10 and 13 for the toggle buttons.

I want the records on a form to filter based upon the month and Year
selected by the user.

I have tried the following but it prompts for YearFilter and MonthFrame as
parameters and doesn't work? I have tried to debug it and the values are
correct in the variables so I am not sure why it prompts for them as
parameters.

Dim MonthChoice, YearChoice

MonthChoice = Me![MonthFrame]
YearChoice = Me![YearFilter]

If MonthChoice = 13 Then
Forms![frm QuarterlyPlan].FilterOn = False
Else
Me.Filter = "[ReviewDate] between " & DateSerial(YearChoice,
MonthChoice, 1) & "And DateSerial(YearChoice, MonthChoice + 3, 0)"
Me.FilterOn = True
End If

Thanks in advance for any help.
Sue

 

[Guest]

When using date as criteria you need to add # before and after the date
Try this

Me.Filter = "[ReviewDate] between #" & DateSerial(YearChoice, MonthChoice,
1) & "# And #" & DateSerial(YearChoice, MonthChoice + 3, 0) & "#"

 

[Guest]

I had tried doing this but couldn't get the syntax quite right. Works now
thanks, I just had to add the format function too so I could get the dates
right being in the UK.

Cheers
Sue

When using date as criteria you need to add # before and after the date
Try this

Me.Filter = "[ReviewDate] between #" & DateSerial(YearChoice, MonthChoice,
1) & "# And #" & DateSerial(YearChoice, MonthChoice + 3, 0) & "#"

--
Good Luck
BS"D

Hi

I have an option group with Jan, Apr, Jul, Oct and ALL (MonthFrame) on a
form and an unbound text box containing the Year (YearFilter). The Values are
1, 4, 7, 10 and 13 for the toggle buttons.

I want the records on a form to filter based upon the month and Year
selected by the user.

I have tried the following but it prompts for YearFilter and MonthFrame as
parameters and doesn't work? I have tried to debug it and the values are
correct in the variables so I am not sure why it prompts for them as
parameters.

Dim MonthChoice, YearChoice

MonthChoice = Me![MonthFrame]
YearChoice = Me![YearFilter]

If MonthChoice = 13 Then
Forms![frm QuarterlyPlan].FilterOn = False
Else
Me.Filter = "[ReviewDate] between " & DateSerial(YearChoice,
MonthChoice, 1) & "And DateSerial(YearChoice, MonthChoice + 3, 0)"
Me.FilterOn = True
End If

Thanks in advance for any help.
Sue