Criteria search by month on several form/table fields at once (1 Viewer)

[hardhitter06]

Hi All,

Access 2003.

I know this is cumbersome, but please bear with me, I’m trying to be as thorough as possible.

My question will basically be how to design this search/calculation, but let me explain my set up first.

I have 5 tables with 5 corresponding input forms. Each input form is specific to the end user. For example, only 2 of the same users will enter into Form A throughout the month. Only 1 static user will enter into Form B and so on and so forth.

At the end of the month, I need to run a search query where I calculate the total of each field per form/table and group them in one giant report. Let me explain this further with an example:

Form A is ReqsInput. One of its fields is called ReqsProcessed. Throughout February and March, those 2 same users entered 5 records (1 in February, 4 in March). I want to run a query where that little search criteria box pops open and asks me to enter a month and year. In this case, I would like to run the query and enter 03/2010. I then want this query to total all of the ReqsProcessed for March (So ReqsProcessed in each of the 4 records will need to be added).

For each field in each form I need this calculation to take place so that when the report is ran, I have every Form and its fields totaled together and displayed in one place.

So here are my actual questions:

1. Since I have 5 forms, I have 5 different Date names appropriate to the name of the Form. So in this situation, the date field is called ReqInputDate. Now since I have 5 tables with all of the fields in one query, will this work? In other words, will I be able to collect records from each form for a certain month with 5 different date field names? Or will the all need to be the same?

With my limited knowledge, I think if I put the same search criteria under each different date field name, I’m going to get 5 of those little search dialog boxes asking me to enter a month and year. I’m not sure if using the same Date name for each form would change this or if I would have to do something different?? So I need help with this design question.

2. I’ve only done a criteria search where I collected records from a year span from today’s date:

 Between DateAdd("yyyy",-1,Date()) And Date()

(shown here)

This isn’t the case in this example, I just need help having the query collect data for all the records from a month and year search (March of 2010). Could someone show me how to alter this to make that happen?

Once I get these two things, I will work on the calculation part.

I appreciate any insight I can get, and I also thank you for your time.

-Josh

 

[Brianwarnock]

To avoid being asked to repeat a parameter use a form, the user can enter the parameter in the form and then use a command button to run the query/report.
You reference the form by
Forms!formname!controlname


Brian

 

[hardhitter06]

Ok, I'm trying to do one form's worth of records before combining them all but I am running into trouble.

Under ReqInputDate in the criteria section of this field, I put: [forms].[frmDashboard].[text25].

Text25 is on my Dashboard(menu) screen with a command button to activate the qry that is shown in a form.

Here is the on click code:

Private Sub Command27_Click()
On Error GoTo Err_Command27_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmMainRpt"
    strIdNumber = Me.Text25
 
Exit_Command27_Click:
    Exit Sub

Err_Command27_Click:
    MsgBox Err.Description
    Resume Exit_Command27_Click
End Sub

I'm assuming I need code that tells the query to search within the month and year entered in the "Text25" box on the dashboard.

How would I go about doing this? And where does it go?

 

[Brianwarnock]

Do you know what kind of search criteria I would need to display all records that fall within a certain month of the year (i.e. 3/10)?

It depends a little on your users , if they are happy with Month numbers then 2 controls say mth and yr, to avoid using reserved words, then you can use Month(yourdatefield) and Year(yourdatefield) to search on or just search on the date field usingdateserial
Between dateserial(forms!frmnme!yr,forms!frnme!mth,1) and dateserial(forms!frmnme!yr,forms!frnme!mth+1,0)
this avoids haing to create the extra fields , but you don't have to select them for the output so its upto you.

If the users are not happy with date numbers then you may have to get into a Combobox scenario showing the text name but passing the number to the query, I don't think thats difficult but the old brain box is not functioning to allow me to spell it out.

Brian

 

[hardhitter06]

Brian,

Thank you so far for your help. This last post has confused me.

I want the users to use numbers for the date. I think your suggested me to create two text boxes with one being for the month and one being for the year?

And the code you suggested is to go in the On Click of the command button to run the query?

 

[Brianwarnock]

The code goes in the query criteria, the command button code is easily generated using the wizard.

Yes 2 textboxes as 3/10 is not a Date we start to get into parsing of fields to get at the month and year.

Brian

 

[hardhitter06]

I need to attach my database because I don't think I'm doing this right.

If anyone can help...feel free to jump in.

I'm using qryMainRpt with just one of the form's fields to make sure it is displaying properly (will add the rest after that)

In the criteria of this query, I added:

 usingdateserial Between dateserial(forms!frmDashboard!yr,forms! frmDashboard!mth,1) and dateserial(forms!frmDashboard!yr,forms!frmDashboard!yr+1,0)

I'm getting closer, just need a little more help.

 

[Brianwarnock]

1 The correct criteria is
Between dateserial(forms!frmDashboard!yr,forms! frmDashboard!mth,1) and dateserial(forms!frmDashboard!yr,forms!frmDashboard!mth+1,0)
The 0 days give the last day of the previous month

I'm sure that yr was a simple error on your part.

2 This goes in the criteria for a Date field such as ActivityDate when I do that and remove all of the other tables to remove the cartesian join then I get 2 records which is ciorrect for the 1 table.

3 I have read post 1 again in the light of looking at your DB and I thought originally that the Tables would be joined, now I'm not sure, if not I think that you are looking at separate queries per table. I could be wrong.

Brian

 

[hardhitter06]

Brian,

That expression isn't working. Says too many arguments or something like that. I fixed the misspelling of Dashboard too.

I don't want to see the last day of the previous month? Why would I?

They are all seperate tables because ultimately it is just going to be a giant query with calculations. I was trying to combine them because I want the user to be able to see all of the information at once while only having to input the date once.

Format([ActivityDate],"mm/yyyy") = [forms].[frmDashboard].[mth] I tried this because I wasn't able to get yours to work, and it works for just one table. I don't know if I need multiple where statements, or Ands to put this under each date's criteria, while changing the name part.

mth= in this example, you enter both month and year. I just didn't change the name because I was messing around with this.

 

[hardhitter06]

The actual message is: This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifyting the expression by assigning parts of the expression to variables. This is my expression:

Between DateSerial([forms]![frmDashboard]![yr],"forms! frmDashboard!mth",1) And DateSerial([forms]![frmDashboard]![yr],[forms]![frmDashboard]![mth]+1)

and tried adding the brackets

Between DateSerial([forms]![frmDashboard]![yr],[forms]![frmDashboard]![mth],1) And DateSerial([forms]![frmDashboard]![yr],[forms]![frmDashboard]![mth]+1)

 

[Brianwarnock]

I don't want to see the last day of the previous month? Why would I?

Thats why you add 1 to the Month

Between dateserial(forms!frmDashboard!yr,forms! frmDashboard!mth,1) and dateserial(forms!frmDashboard!yr,forms!frmDashboard!mth+1,0)

The Format approach will work, I didn't suggest it because it adds another column to the query.

The separate queries can be run from the form, the command button will either fire a macro that runs them or, which the experts seem to prefer , code.
I've only used a macro.
Brian

 

[hardhitter06]

Ok, i didn't understand your wording to the previous month thing.

That criteria line is still not working for me. Would it be possible for you to send me back my database with it working since you said you accomplished that?

Ok, so you see I actually have 8 forms, if I set up 8 of those queries, can I hit a macro button to run them all together?

And then display them all in the same place?

 

[Brianwarnock]

Here is your DB with the modified query.

The answer to your post is yes upto the last line then I guess it depends.
I'm not sure as to whether some on here might not provide you with a better solution, it appears to me that you would have to bring all the results together in a report/subreports or maybe a final query but I don't see what you are joining each table/query on, if the data layout is the same and the headings are then Union queries maybe an answer.

Its the sort of thing I would have to play with, I cannot visualise the answer.
Sorry if this appears to be a cop out but I don't want to misadvise.

Brian

 

[hardhitter06]

Thank you, that one works haha.

Well the tables aren't related at all...except that when I'm running this final report I want all of the information to be displayed by the month search.

As I explained in the first post, given the user, they will input to a specific form to track their work related activities on a daily or weekly basis. I then need to add each of the records per month to get an overal number of all the activities done by that given form/user.

LASTLY, think of it this way, 8 different forms, 8 different activies, at the end of the month, I want an activity list showing me each of the forms totals given the month. This is just to group everything together.

Like I said, these forms do not relate to each other in the slightest..I just figured running this large query would elimate the end user from having to search one activity at a time, or have to put in a month and year 8 times to see all of the information.

Does that make more sense?

 

[Brianwarnock]

I think that if you had a mainreport with a title and a bit of other relevent info that had 8 subreports each based on one of the queries, then the command button would run the main report causing all of the sub reports to run causing all of the queries to run.

Try it with 2 and see.

It late evening here and my old eyes are tiring so I will go now but will check back tomorrow and if I find time I might give it a gi, but with a bit of luck you will have cracked it.

Brian

 

[hardhitter06]

I will give this a try..may not have time today though. I will keep you posted and let you know if I need a "S.O.S" haha

 

[Brianwarnock]

I see that you are on, how is it going. I have done an extremely rough reort with queries on just 2 tables if it is any use.

Brian