Help with Dates: 1/1/01 = January

[BukHix]

I am opening up a recordset and trying to get all the records that match the current selected date from a combo box. The values in the combo are listed as:

January
February
March
...and so on...

The dates in the table are stored like this, 1/1/01 etc. I am trying to pull the matches with this but so far I have not been sucessful.

Set rsMonths = CurrentDb.OpenRecordset("queLastReviewDate")
          
Do While Not rsMonths.EOF
        
strGetDate = Format(rsMonths("LastDate").Value, "m")
strDate = Me.cboMonth
    
    If strDate = strGetDate Then
        MsgBox (strDate & " " & strGetDate)
    Else
        MsgBox "Nothing to report"
        Exit Sub
    End If

What am I doing wrong?

 

[Fornatian]

Bux,

Why don't you format the month in the query & add the criteria "January" to the query and ? Should save on processing speed as you won't have to keep reassigning variables.

Ian

Oh, it's just come to me, if think the format should be "mmmm" if you want to return the entire month name.

 

[RichMorrison]

If you want the Format function to return "January" then the operand is "mmmm", that is 4 m's

RichM (one M)

BTW, it's "pursuit"

 

[BukHix]

Got it now, thanks!!!

Oh yea and thanks for the spelling correction also. I need a spell checker for my head.

 

[BukHix]

Rather than using a code loop, use a parameter query that selects ONLY the records that satisfy the date criteria.

Actually the loop is for a mass email that goes along with this code. I just cut that part of the paste to make it easier to understand.

However I did add a field to my query like this,

Month: Format([LastDate],"mmmm")

and then adjusted the form code to look like this,

Dim strMonth As String

strMonth = Me.cboMonth

DoCmd.OpenReport "repLastReviewDate", acViewPreview, , _
"[Month] = '" & strMonth & "'"

Now all I have to do is get my email loop to work with this and I will be all set (which shouldn't be to awful bad). Thanks for all the help.

If your table contains records from multiple years, checking only the month will not necessarily produce the desired results.

I haven't quite figured out how I am going to handle this part yet but the table will have multiple years and the code will need to just grab the month from the most recent year previous to the current one.