How To Extract Month Value 1

[ZPBJ]

I am using a Calendar control on a report selection form and want to make it able to extract the m value from the user's calendar selection for a MTD Report.

My attempt was setting the date field in the query to the following
=between m/1/yyyy and [Forms]![frmReportControlForm]![Calendar]

But know that the code isn't specific enough to capture the desired output.

Thanks for your help

__________________________________
Remember that time when I took the box? - Peter

 

[PHV]

Something like this (SQL code)?
WHERE Format([your date field],'yyyymm')=Format([Forms]![frmReportControlForm]![Calendar],'yyyymm')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dhookom]

You can't use "=between". It must be one or the other. Try:
Between DateAdd("d",1-Day([Forms]![frmReportControlForm]![Calendar]),[Forms]![frmReportControlForm]![Calendar] ) and [Forms]![frmReportControlForm]![Calendar]

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ZPBJ]

Thanks Duane, I am still getting a parameter prompt though

__________________________________
Remember that time when I took the box? - Peter Griffin

 

[dhookom]

Is the form open? Is there a control/textbox on the form named Calendar? Could you share the "parameter prompt"?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ZPBJ]

I discovered the reason for the parameter prompt was a grouping issue on the report. Thanks for your help!

__________________________________
Remember that time when I took the box? - Peter Griffin

 

[ZPBJ]

One last piece on this same topic. Here is the code I ended with

Between DateSerial(Year([Forms]![frmReportControlForm]![Calendar]),Month([Forms]![frmReportControlForm]![Calendar]),1) And [Forms]![frmReportControlForm]![Calendar]

For YTD would it be

Between DateSerial(Year([Forms]![frmReportControlForm]![Calendar]),1,1) And [Forms]![frmReportControlForm]![Calendar]

?

__________________________________
Remember that time when I took the box? - Peter Griffin

 

[dhookom]

You are correct.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]