Making a selection based on a month

[Robert C]

Hi

I have a subform on which I log the times spent on any particular project.

I have another subform on which I want to select a month and use this to select all the instances on which work was done on any given project for a particular month. I would prefer to use a pop up calender to select the month (i.e. by slecting the last day of the month in question)

Has anyone got any idea how I might do this - I am completely stumped.

Thanks in advance

Cheers

Rob

 

[RuralGuy]

Are you just looking for some popup calendars? Calendar Links

 

[Robert C]

Hi RG

No, sorry, I have the calander, what I need is to be able to select records based on when they occured.

IE if I spent a certain number of hours working on a project in various different days in Jan 06 and then spend some more hours on the same project in Feb 06, I want, at the end of Jan 06 to be able to list all the hours I worked on the project in Jan 06 so that I can create an invoice for work carried out in Jan 06.

I want to be able to select the month in question via the pop up calander I have.

So if I select 31-1-06 on the calander, my query will include all the occasions (days/hours) I have worked on a project in Jan 06.

Sorry if this is a bit garbled - hope I have made myself understood.

Thanks for getting this far.

Cheers

Rob

 

[RuralGuy]

So you want all of the records WHERE Month([RecordDate]) = 1 AND Year([RecordDate]) = 2006 , right?

 

[Robert C]

Yes, but only when I pick 31-1-06 from the calander. If I pick 28-2-06 then I want only those records which are from February 06.

So, the query will need to look at the unbound field that contains this information - but I am not sure how to achieve this. Thanks for your help so far.

Cheers

Rob

 

[RuralGuy]

WHERE Month([RecordDate]) = Month(Forms!YourFormName!YourControl) AND Year([RecordDate]) = Year(Forms!YourFormName!YourControl)

or set up two invisible textboxes on your form with their ControlSource set to:
=Year(Me.YourDateControl) AND =Month(Me.YourDateControl) then the SQL is

WHERE Month([RecordDate]) = Forms!YourFormName!YourMonthControl AND Year([RecordDate]) = Forms!YourFormName!YourYearControl

 

[Robert C]

Hi RG

Thanks for that - I tried the option using the two invisible text boxes, but just got the #Name? error message.

When I pick the date it goes into a field call InvoiceMonth, so I put the control source in my invisible text box to:-

=Year(Me.InvoiceMonth) and =Month(Me.InvoiceMonth), but just got the error message.

Do you have any clues as to why this is happening?

Thanks and sorry to be dense.

Cheers

Rob

 

[RuralGuy]

Name your control txtInvoiceMonth then change the 2 ControlSources to:
=Year(Me.txtInvoiceMonth) and =Month(Me.txtInvoiceMonth)

 

[SammyJ]

Try =Year(Form.InvoiceMonth)

 

[SammyJ]

Or =Year(Form!InvoiceMonth)

 

[RuralGuy]

Sammy,
I think we will find that there is a Control on the form named the same as the Field to which it is bound and Access does not know which one to reference. That is why I recommended changing the name of the Control.

 

[Robert C]

OK, I know I need words of one syllabal, so please be patient.

The field into which my selected date is going is now named txtInvoiceMonth and has the Control source - InvoiceMonth.

My invisible unbound text boxes have the control sources =Year(Me.txtInvoiceMonth) and =Month(Me.txtInvoiceMonth) and yet the #Name? error still pops up.

Sorry about this - and we haven't even touched on the SQL yet.....aaaaggghhhh.

Cheers

Rob

 

[RuralGuy]

Hmm, ok I stand corrected. As a test, set the ControlSource of the two TextBoxes to =Me.txtInvoiceMonth and see if the #Name? goes away. When you type Me.t does Intellisense bring up txtInvoiceMonth? Something is wrong here!

Edit: Boing!! <slaps forehead> Don't use the Me qualifier in the ControlSource:
=Year(txtInvoiceMonth) and =Month(txtInvoiceMonth)
Sorry!

 

[Robert C]

Hi RG

Sorry for the hiatus, other things keep getting in the way.

Thanks for all you help so far, I knew I'd get into trouble with the SQL part:-

WHERE Month([RecordDate]) = Forms!YourFormName!YourMonthControl AND Year([RecordDate]) = Forms!YourFormName!YourYearControl

First of all, can you tell me what the [RecordDate] field refers to? I know this must be really basic, but I only dip into this stuff efery once in a while.

Basically, I have my two invisible fields on the form now. All I want to do is tell the report which I have created to show the times spent on any given project, in the month and year which appear in the invisible fields. But I have no idea how to do this.

Again, sorry to ask dumb questions, but I could really do with some more help here.

Thanks very much.

Rob

 

[RuralGuy]

Hi Rob,
[RecordDate] is *my* generic name for the field in your record that contains the date you are comparing. Replace [RecordDate] with *your* field name.