Form to Report - Between two Dates

[tangledball]

I would be gratful if anyone could advise how i can open a report but only show entries between two dates using a command button?

I have the following expression which enables me to use a command button to open a report based on the current open record:

DoCmd.OpenReport stDocName, acViewPreview, , "[RecordID]= " & Me.[RecordID]

but how do i now extend that to allow me to open the same report, but only show entries between two dates that are recorded on a seperate form (Form Name Registration)? these are entered into individual fields and are manually input, so for agruments sake open and close dates, but i need my report to show me all the entries made between the open and close dates and relevent to the current open record and not all records.

At the moment I have a query that will allow me to enter the From - To dates, but this can get a bit messy as there are calculation fields in the report and in effect would calculate the wrong info if wrong dates are input, so would be useful if this can be done by just clicking a button with no manual intervention.

If anyone could provide a bit of an idiots guide how i can achieve this, I would be extremely grateful, as I have been working on some code that was provided as per below, but i makes no sense to me and I havent a clue what to do and am losing the will to live.

1) Place 2 textboxes viz. txtbegDate and txtendDate (Where?)

2) In properties sheet of these 2 boxes Select Short Date under Format (What for? and wheres the relationship between the text fields on the form and wherever i am supposed to put these text boxes?)

3) in click event of the command button use this code :

DoCmd.OpenReport stDocName, acViewPreview, , "[urdtabledatefieldname]>=# " & Me.txtbegDate _
" # And [urdtabledatefieldname]<=# " & Me.txtEndDate & "#"
(Tried this and many many variations, but I now only have three strands of hair left on my head)

Just for clarification, I have two forms... Registration Form which is where all the setup data goes and Absence Management Form which has a number of subforms for different types of absence. The Start and End Dates that I refer to are located on the registration form, but the data i wish to extract on the report is on the subform. I have my Report set up and the code above does open for the specific record that is being worked on... just need to incorporate the start and end date.

Hope I have been thorough in my explanation, but if any of you could help me out I'd be real happy. P.s I am in the United Kingdom.

Thanks for any help.

 

[RuralGuy]

How about:

Dim strWhere As String
strWhere = "[RecordID]= " & Me.[RecordID]
strWhere = strWhere & " AND ([urdtabledatefieldname] Between #" & _
                Me.txtbegDate & "# and #" & Me.txtEndDate & "#"
DoCmd.OpenReport stDocName, acViewPreview, , strWhere

 

[tangledball]

Hi where would i put the txtBeginDate and txtEndDate? I am assuming that I will use my orginal fields on the Registration form which would be Start Date and End Date, which has been referred to as [urtabledatefieldname] it think.. but where do the other two txt boxes go and is there anything i need to do with them once they are where they are supposed to be? Seems strange just having two txt Boxes somewhere with no info for them to relate to?

Sorry, i'm not an expert on access, so any type of idiots guide to explain would be useful.

Thanks

 

[tangledball]

Hi thanks for your response.

But I am still confused as to where these txt Begin and End go? what is there purpose and where exactly are they supposed to placed. Seems strange to have two txt boxes that don't appear they would ever be populated, which is probably why i am getting confused?

Sorry I'm not an access pro, so if you could provide a bit of an idiots guide, that would be really helpful.

Thanks

 

[RuralGuy]

I thought you already had these two controls. If not then use the controls you have to limit the selection if they will work for you.

 

[RuralGuy]

Is the Form Name Registration form still open?

 

[tangledball]

Hi...

Not sure what you mean by is the Registration Form still Open?

Registration is the name of one of the Forms, which is where the two dates I will need to use are stored. The Command Button is on the Absence Management Form, which at the moment will open up the report for the current open record on , but obviously, this only shows me all entries for the current record and not the entries that i need to report on which are entries between the start and end date, which are stored on the registration form.

 

[tangledball]

I will try and explain how the database works.

There are two Main Forms. Registration and Absence Management

Registration contains employment details which also contains Annual Leave Start and End Date.

Absence Management contains a number of subforms that relate to different types of absence such as Annual Leave/Sickness/Study etc. I have command button on my annual leave subreport (Holidays). That opens up the Holiday Report, which in turn shows me all the absence entered for that individual, however, I only want to show entries between the start date and end date that are recorded on the Registration form.

I haven't a clue where i am supposed to be putting these txtStartDate and txtEndDate. I am assuming that the [urdtablefieldname] relates the the start and end dates of the registration but this is only an assumption. but where to the Me.txtStartDate and Me.txtEndDate go? Are these supposed to be on the report or somewhere else?

Sorry for being a pain and any help on my problem is much appreciated.

Thanks

 

[RuralGuy]

We should start by getting some terms in common. So that you know, data is not stored in forms. It is stored in tables. Forms are simply a graphical means of viewing tables and are like a window into the table. SubForms are forms that are displayed on other form and *not* forms that are opened by other forms. The latter are just other form. Since it seems these dates are important to your report then we should find a way to get them from the table that is displayed by the Registration form. Would you agree? If so, then what is the name of the table that has the dates we need and how and I suspect it is related to the table used by this Absence Management form so we could join the two together in a query if we wanted to.

 

[DCrake]

Try this out

Link

David

 

[tangledball]

Hi I have attached a sample of the database, just to show what the layout is like. Hopefully this will give you an idea of what i am trying to do.

Thanks

 

[RuralGuy]

Normally, each table would have its own PrimaryKey (hopefully an AutoNumber field) and the many side table would have a ForeignKey field with the one side table PK in it. Your table structure is a bit strange to me. It also appears that the Holiday Start and End dates are exactly a year. If that is true then you do not need the EndDate field since it can be calculate when needed. Are these dates a Review period or something? They are certainly not the dates a person starts and ends a holiday.

 

[tangledball]

Hi Sorry,

I just threw this sample together to give you an overview of the layout. The actual db does contain IDRecord which is an autonumber, which is also the PK. Each table i have also contains IDRecord with PK assigned.

The Start and End Date on the Registration table relate to the review period, which would be the dates that I would like use in order to pull my report and yes this is exactly 1 calendar year, i would like to be able to auto calc this, but if i need to go in and change the start date each year, then it's no hardship to change the end date at the same time.

The Subtable on the Absence Maintainance form is used to record the actual holidays booked/taken and is the information i need to be produced on the report. The command button on the Holiday subform does this, but as the records will be continuous from year to year, i need to only show the current years results, but still keep the old records there for audit trail.

I hope I am making it a little clearer now.

Thanks
Mike

 

[RuralGuy]

So you need something like the attached?

 

[tangledball]

Hi RuralGuy,

Thanks that's great, sorry it's been hard work.

Cheers
Mike

 

[RuralGuy]

It has not been hard work, we just needed to get synchronized.