Year & Month

[AN60]

I wish to creat two queries;
1. To view data entered in the current month
2. View data for the current year

What should I use in my query to do this?

 

[Mile-O]

1. Between DateSerial(Year(Date()), Month(Date()), 1) And Date()
2. Between DateSerial(Year(Date()), 1, 1) And DateSerial(Year(Date()), 12, 31)

 

[AN60]

Thank you M-O-P

 

[AN60]

More Yearly & Monthly

I'm working on a rainfall db. I have planned for a date field & amount (precipitation) field. I want to view data;
1. Yearly, and
2. Monthly


Any ideas?

 

[Mile-O]

Just a suggestion for you:

Download my calendar example on this thread and use it within your database.

Change your query criteria (with correct names) to:

Between [Forms]![MyForm]![txtStartDate] And [Forms]![MyForm]![txtEndDate]

You'll be able to select any date range you want.

 

[AN60]

Thanks for your suggestion.
I still need to be able to get yearly and monthly data so I can get yearly & monthly averages.

I would like to use your calendar as well but unfortunately I do not yet understand how to intergrate it in my db. I've imported the module and calendar form but after that I'm unsure of what to do.

 

[Mile-O]

I still need to be able to get yearly and monthly data so I can get yearly & monthly averages.

By selecting the dates from the calendar and using them as a range in a query then you can get these averages....I would have thought. Unless you are wanting to present annual averages as a series of monthly averages...

I would like to use your calendar as well but unfortunately I do not yet understand how to intergrate it in my db. I've imported the module and calendar form but after that I'm unsure of what to do.

Look at the code on either of the buttons and copy that verbatim... All you need to change is the part that says ";txtStart" or something to the control that you want to put the date into.

 

[AN60]

M-O-P
I think I should provide you with more information at this point.

I have a tbl Rainfall which has three fields
1. raindate
2. precipitation
3. rainID

One form Rain with two txt boxes for raindate & precipitation

One Query using the above fields. This query will be used for a report which is yet to be built.

I copied Between [Forms]![MyForm]![txtStartDate] And [Forms]![MyForm]![txtEndDate] into the criteria of the query and when run I get msgboxes asking for the beginning & enddates. So far I follow things.

I don't know what should be happening from here ie. where do I copy the calendar buttons to? How can I get the calendar to select the msgbox dates?

I think you might have given me too much credit and in reality I am an Access "Pigmy". I could just type the dates in and get the desired results but the calendar is a nice way to go.

 

[Mile-O]

I copied Between [Forms]![MyForm]![txtStartDate] And [Forms]![MyForm]![txtEndDate] into the criteria of the query and when run I get msgboxes asking for the beginning & enddates.

So, you copied it literally? The MyForm should be replace by the name of your form the two textboxes are on, and the txt... parts should be replaced by the name of either textbox.

I don't know what should be happening from here ie. where do I copy the calendar buttons to?

Create your own on your form.

 

[AN60]

Thank you MOP (patience is a virtue)
"One small step for man" one Quantum Leap for me.


With the date range selection Form open and then running the query I can view the selected data. How should I go about getting this data into a report?

 

[Mile-O]

Base the report on the same query.

 

[AN60]

M-O-P
I thought I would use your calendar on another db to enter just one date in a date field. The date field is on a sub form and it workd ok when I edited the form to add the button etc. however when I opened the main form & clicked the button I got an error telling me that the sub form can't be found. Have I missed something in the editing to do with naming the sub?


Thank you for your help on this matter I appreciate your time & effort.

 

[AN60]

I am using this in the "on click" to enter the date via the calendar;

Private Sub cmdDate_Click()
DoCmd.OpenForm "frmCalendar", , , , , acDialog, Me.Name & ";Date"
End Sub

This is on a sub form called Events SubForm.

This will work if used with the sub open only but as the sub is run off the main form (obviously) that's when it will not go. Any suggestions?