Return Month And Year

[RaptureReady]

Hey all, I have a form that has two textboxes, one box is for month and the other is for the year. After I enter the month and year I want, I click on a command button that previews a report. Well, the report is not giving me the information for that particular month and year, actually it gives me nothing.

The report gets its information from a query. In that queries criteria field is the below expression that I have.

Like [forms]![frmPEL]![cboMonth] & "/*/" & Right([forms]![frmPEL]![txtYear],2)

Can anyone see anything wrong with this? I should be able to enter the month and year and click on the report button I want and preview that report, but no joy.

Your help is greatly appreciated.

RR

 

[boblarson]

Create a new field in your query that is just month year:

MyMonthYear: Cstr(Format([YourDateFieldNameHere],"mm/yy"))


And in the criteria then you can use:

[Forms]![frmPEL]![cboMonth] & "/" & Right([Forms]![frmPEL]![txtYear],2))

 

[RaptureReady]

Thanks bob, that worked.

One other question is, this expression obviously returns the month and year as you fixed it too. Now I would like to return the same thing, except minus one year. Here is the expression that I have for that using the Val function, but not working for me. Can you take a look please?

Like [forms]![frmPEL]![cboMonth] & "/*/" & (Val(Right([forms]![frmPEL]![txtYear],2))-1)

Thanks for the help.
RR

 

[RaptureReady]

Just a little bump...

 

[boblarson]

Change this part:
(Val(Right([forms]![frmPEL]![txtYear],2))-1)

to this:

Val(Right(DateAdd("yyyy", - 1, [forms]![frmPEL]![txtYear]),2

 

[RaptureReady]

Bob, I get a error when I try using this. The result is #error.

Like [forms]![frmPEL]![cboMonth] & "/*/" & Val(Right(DateAdd("yy",-1,[forms]![frmPEL]![txtYear]),2))