Simple date diff question

[KeithIT]

I'm trying to create a query where the informaiton returned is based on a date where the month (and only the month) is the month (tblMeetingInformation.dtmMeetingDate) immediately prior to the month entered on a form (frmSendList.txtDateSelect). In other words, if the date selected is in march, I would need the results to be from any date in February.

I tried using DateAdd("m",-1,Me.txtDateSelect) as part of the query, however it does not return the information as needed, only that information which is 30 days (or so) prior to the date selected.

 

[Mile-O]

Where x is your textbox

Between DateAdd("m",-1,DateSerial(Year(x),Month(x),1)) And DateAdd("d",-1,DateSerial(Year(x),Month(x),1))

 

[Sergeant]

Would this work?
...WHERE (((Month([MyDateField]))=Month(DateAdd("m",-1,[Forms]![MyFormName]![MyControlName]))))

 

[KeithIT]

Data type mismatch error

For some reason when I try to run the query now I'm getting a "datatype mismatch error" message. Could this be because I am trying to filter a Date/Time field in a table using a Text Box field on a form?

 

[Mile-O]

Would this work?
...WHERE (((Month([MyDateField]))=Month(DateAdd("m",-1,[Forms]![MyFormName]![MyControlName]))))

No. If it were run today it would select every March regardless of the year.

 

[Sergeant]

Oh yeah! That point eluded me because it was right in front of my face! If 'twer farther away, I'd have spotted it immediately.