What's the correct syntax for searching on current month?

[upsman via AccessMonster.com]

I'm attempting to lookup a record and open a form based on the student id of
the form I'm currently on and the current month but I can't get it to work.
Here's what I've got:

Dim LDate As Integer
LDate = DatePart("m", Date)
DoCmd.OpenForm "Achievement", , , "[StudentID]= " & Me!StudentID & " And
[CurrentMonth]= " & "LDate"

When I open the form it asks me for the parameter LDate. It's not recognizing
my "LDate =" statement. I've tried removing the quotes around LDate in the
Open statement but I get an error telling me the OpenForm function has been
cancelled. How do I code this so it uses the current month?


I'm also trying to do something similiar on another screen where the user
enters the month they want to find. I have a combo box called cboCurrMonth
and they select the month they want to see. When I try that I get the error
"Object Required". That's the only error I get, it doesn't give me the option
to debug. What object does it need? Here's the code:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Achievement"
stLinkCriteria = "[StudentID]= " & Achievement.StudentID & " And
[cboCurrentMonth]= " & "'" & Me!CurrMonth.Column(0) & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

DoCmd.Close acForm, "AchieveFind"

Any help would be appreciated.

Rod

 

[John Vinson]

I'm attempting to lookup a record and open a form based on the student id of
the form I'm currently on and the current month but I can't get it to work.
Here's what I've got:

Dim LDate As Integer
LDate = DatePart("m", Date)
DoCmd.OpenForm "Achievement", , , "[StudentID]= " & Me!StudentID & " And
[CurrentMonth]= " & "LDate"

When I open the form it asks me for the parameter LDate. It's not recognizing
my "LDate =" statement. I've tried removing the quotes around LDate in the
Open statement but I get an error telling me the OpenForm function has been
cancelled.

Two ways. Right now it's searching for the literal text string LDate
(and since that's not recognized by the JET query engine, it's asking
for a parameter).

Removing the quotes should work:

Dim LDate As Integer
LDate = DatePart("m", Date)
DoCmd.OpenForm "Achievement", , , "[StudentID]= " & Me!StudentID & " And
[CurrentMonth]= " & LDate

provided CurrentMonth is an integer field in your table with values 1
to 12; or, if your table has a date field that you want to search, you
can avoid the code altogether by simply basing the Form on a query
with a criterion on the date field of

= DateSerial(Year(Date()), Month(Date()), 1) AND < DateSerial(Year(Date()), Month(Date()) + 1, 1)

John W. Vinson[MVP]