Open form to month or year

[Ruegen]

I'm hoping to make a form open to a specific month, current month or the year typed in an unbound textbox on a form

I have one unbound textbox

one button with current month

and one with current year

not sure on the vba

DoCmd.OpenForm "frmSchoolCatalogues", , , "CatalogueLabelPrinted = Month(Now())"



CatalogyeLabelPrinted is a date field.

I'm hoping that something like

DoCmd.OpenForm "frmSchoolCatalogues", , , "CatalogueLabelPrinted = Year(Now())"

will work for year

[June7]

Need to extract the month or year from the field value.

DoCmd.OpenForm "frmSchoolCatalogues", , , "Month(CatalogueLabelPrinted) = Month(Now())"

But that will pull records from every year. Consider:

DoCmd.OpenForm "frmSchoolCatalogues", , , "Year(CatalogueLabelPrinted)=Year(Date()) AND Month(CatalogueLabelPrinted) = Month(Now())"

[Ruegen]

ok makes sense

[Ruegen]

Works like a charm thanks!

[Ruegen]

Need to extract the month or year from the field value.

DoCmd.OpenForm "frmSchoolCatalogues", , , "Month(CatalogueLabelPrinted) = Month(Now())"

But that will pull records from every year. Consider:

DoCmd.OpenForm "frmSchoolCatalogues", , , "Year(CatalogueLabelPrinted)=Year(Date()) AND Month(CatalogueLabelPrinted) = Month(Now())"

How would you pass the value from the unbound textbox on the form?

DoCmd.OpenForm "frmSchoolCatalogues", , , "Year(CatalogueLabelPrinted)=Year(forms!frmDateCat alogues!Text4)"

or DoCmd.OpenForm "frmSchoolCatalogues", , , "Year(CatalogueLabelPrinted)= & Year(me.Text4)"

??

[June7]

Do not place variables within quote marks. Concatenate variables. Reference to textbox is a variable.

DoCmd.OpenForm "frmSchoolCatalogues", , , "Year(CatalogueLabelPrinted)=" & Year(forms!frmDateCatalogues!Text4)

DoCmd.OpenForm "frmSchoolCatalogues", , , "Year(CatalogueLabelPrinted)=" & Year(Me.Text4)

[Ruegen]

DoCmd.OpenForm "frmSchoolCatalogues", , , "Year(CatalogueLabelPrinted)=" & Me.Text4

I used this and it works great (I'm still yet to figure out date range - I will see if I can work that out myself first before asking)


eh,

this is as far as I got and it's not working...

DoCmd.OpenForm "frmSchoolCatalogues", , , "CatalogueLabelPrinted BETWEEN" & Me.txtBefore & "AND" & Me.txtAfter

[June7]

Date values need # delimiter. And don't forget spaces so text doesn't run together when concatenated.


DoCmd.OpenForm "frmSchoolCatalogues", , , "CatalogueLabelPrinted BETWEEN #" & Me.txtBefore & "# AND #" & Me.txtAfter & "#"

[Ruegen]

oh ok, I did see that online but didn't realise... thanks!