query a date format within an input box

[pablavo]

Hi folks

This will be my first ask for help here;)

I'm working on a database at the moment and have exhausted my resources on this one.

I have a report with an underlying muti-table query. The report uses an input box to set the date criteria rather than Parameter boxes to show the relevant records from to these dates.

and then I've used

Between [Forms]![frmMonthlyDisbursalDialog]![StartDate] And [Forms]![frmMonthlyDisbursalDialog]![EndDate]

within the query to reference to the dates seleted within the input box All is working well here.

My question is. If I wanted to use just the Month and Year as criteria, so that I would, perhaps, just have a drop down month combo box and a control for the year. Is it possible to extract the records for that month rather than typing in the exact date to that day?. Or maybe something similar to this technique?

All my other attemps have failed. Hope someone can help.

[kb.net]

Hi,

The easiest way is using the function DateSerial so that it would look like Between DateSerial([Forms]![frmCriteria]![txtFrmYr],[Forms]![frmCriteria]![comFrmMth],1) And DateSerial([Forms]![frmCriteria]![txtToYr],[Forms]![frmCriteria]![comToMth],1) . I have included an example that demonstrates this case, hope it is what you are looking for.

[pablavo]

Kb.Net wrote
The easiest way is using the function DateSerial so that it would look like

That's brilliant Kb.net! thankyou! I just have one more question. If I use use the input box and put Dec 2006 to Jan 2007 will that just give me the month of December without pulling records from January?

Thanks again.

[pablavo]

Ah, Ok it gives me the first day of the "To Month" and then I place a minus 1 " -1)" like so, and that takes away the first day of the "To Month.

Between DateSerial([Forms]![frmCriteria]![txtFrmYr],[Forms]![frmCriteria]![comFrmMth],1) And DateSerial([Forms]![frmCriteria]![txtToYr],[Forms]![frmCriteria]![comToMth],1-1)

Great!

[Milster]

Hello. I'm working on a similar project. Is it possible to have the results returned in another form, Rather than a report? I want to do a Date range search and then be able to edit the data in my resulting records. Thanks

[boblarson]

Hello. I'm working on a similar project. Is it possible to have the results returned in another form, Rather than a report? I want to do a Date range search and then be able to edit the data in my resulting records. Thanks

Yes, you would do similar to what was shown for the report but base the criteria of the recordsource of the other form as shown above.