Month to date Calculation 2

[mpnorris]

I have a problem and I hope someonecan help me out. I am trying to calculate Month to Date information in my database.

I have created a table that collects application information on a daily basis. I want to be able to calculate the # of applications Month-to-Date. I am not sure how to write a query that is looking to select rows where the date only contains the month that is equeal to the month of the current date. I believe that is the logic but I am just not sure how to write the query.

Any help would be greatly appreciated.

Thanks.

 

[Golom]

Something like

Where DateField BETWEEN DateSerial (Year(Date), Month(Date), 1) AND Date

Assuming that your date field contains only the date and not the date and time.

 

[PaulBricker]

Create a query and add your Date Field from the table. Turn on the Totals (on the Menu, View...Totals). Add the Date field the field you want to count. Set the criteria for the date field to
Month([DateField])=Month(Date()) And Year([DateField])=Year(Date())

Set the Totals line for the field you want to count to Sum

That should give you Month to Date.

Paul

 

[PaulBricker]

Sorry, set the Totals line for the field your want to count to Count. Duh!!!

Paul

 

[mpnorris]

I have figured out the query to get the data that I need. I now have the problem of taking the results from the query and displaying one of the fields in a text box on my form.

I need to call the query to run after a certain field on my form is updated. I am not sure how to do that.

For Example: I have a query called "Query1" and the results of the query give Region and Total. I want to be able to take the information in "Total" and have it displayed on my form in text box "Region Total". I want this to happen after text box "Applications" is updated.

I hope this is clear enough. Any help is greatly appreciated.

Thanks.

 

[PaulBricker]

If you want the new data to be counted in the Total, you will need to Save the Current Record to get the value into the underlying table so it shows in the Query or you can use a DLookUp() Function to get the current total(before a save) and then increment it by 1 manually. I think the first way is probably preferable and lest prone to trouble. If you only want the value to display in a new Record, you will have to test for NewRecord in the Current Event of the Form and then grab the value you want, otherwise you can set the visible property of the textbox to No.
Look it over and then post back specific questions about any problems you are having.

Paul

 

[mpnorris]

I am not familiar with the DLookUP() function.

I have a form where I am entering information on a daily basis. One of the fields is the # of applications. I also need to calculate the month to date for the # of applications.

I have created a query to search the Application table and summarize the number of applications that are on the table where the date has the same month as the current date.

My thought was to bring the number of applications for the month from the query onto the form (frmApplications) and then add the current days # of applications to it to create the MTD_Apps. The new number would be show on the form at MTD_Appliocations.

The way I have setup my form is that I have unbound fields for data entry and the when that is complete you click a button to add the record. On the click event is where I set the unbound fields to fieldsa bound to the database. My goal was to perform all of the calculations on the form so that once the button is clicked the MTD calculation can be added to the row alongside the current date Applications.

So what I am still not sure of is how to get the result from the query onto the form so that I can then take that number and add it to the daily application # and create the MTD_App field.

I hope this is clear and make sense. Any help is appreciated.

Thanks

 

[PaulBricker]

Well, lets start with the easy solutions and go from there. Let's try a DSum() function on your form and see if that gives you a value close to what you need.
Put an unbound textbox on your form and on the line for Control Source put this expression

=DSum("[NumOfApplicantsField]","[Table/QueryName]","Month([DateField])= " & Month(Date()) & "And Year([DateField]) =" & Year(Date()))

Now, the value [NumOfApplicatnsField] is the name of the field you want to sum up. [Table/QueryName] is the name of the Table or Query the Field is in and the last section you need to change [DateField] to the name of the field that holds your dates.

Put this expression in the unbound textbox, and open the form. See what you get for a value. If it's correct for the current MTD balance, then try adding a record and click the button. See if the value changes. If it doesn't , then we may need to requery the underlying table in the Click Event of the button. To do that, you would add something like the following.

Button Code to Save Record

Dim rst as DAO.Recordset
Set rst = CurrentDb.OpenRecordset("TableNameHere",dbOpenDynaset)
rst.Requery
Set rst = Nothing

Here you just change "TableNameHere" to the name of your table.

Simple huh? Try it out and post back with problems.

Paul

 

[mpnorris]

I am a little confused. Are you bypassing the query that I created and putting the query into the control source of the text box?

Can you explain the expression in a little more detail.

Do I put the code exactly as you have it with the new names? More specifically the "" marks?

 

[PaulBricker]

All the "" marks are necessary in the DSum() Function. You would just change the names, and leave the punctuation as it is.
Because your Form is Unbound, you have to "LookUp" the values you need from another source. In this case, we are "looking" at the table that stores your values from the form without actually joining it to the Record Source of the Form. The DLookUp() Function "looks up" a value in a single record and allows you to display in a textbox. The DSum() Function allows you to Sum values from a group of records in a table/query based on criteria you provide. In our case, records that give us the MTD value.
I am bypassing the query, but not for any particular reason other than I wasn't completely sure how your info is set up. If you have a Totals query that returns the MTD value then you would just use a DLookup() Function instead of the DSum to "grab" that value from your query. Something like

=DLookup("FieldName","QueryName")

Paul

 

[mpnorris]

I have two questions. I haven't decided which way to go yet.
First, in the DSum expression can you limit the lookup to another value. For example by Region_ID. Currently the way you have it looking up is just by the date. I need date and region. Can this be done with the DSum Function.

Second, I tried the DLookup function and it didn't work. Below is what I wrote in the Control source for the field.

= DLookup("Sum Of Applications_Processed", "Current Month Query-NE")

I got an error in the field. Is there something with the syntax of the field and query names that could be throwing it off.

Let me know what you think. Again thank you for all your help.

Mike

 

[PaulBricker]

If you have spaces in your names, that's a no-no but most of us do it, you need to surround them with square brackets, [].

=DLookup("[Sum of Applications_Processed]","[Current Month Query-NE]")

As for the DSum, yes you can also filter it by Region_ID. You don't say where you will get the value so I'll post it two ways. Assuming this value is a string it would be like this if you are pulling the value from a form field

=DSum("[NumOfApplicantsField]","[Table/QueryName]","Month([DateField])= " & Month(Date()) & "And Year([DateField]) =" & Year(Date())& " And Region_ID = '" & Me.TextboxName & "'")

If you are hardcoding your value it would be

=DSum("[NumOfApplicantsField]","[Table/QueryName]","Month([DateField])= " & Month(Date()) & "And Year([DateField]) =" & Year(Date())& " And Region_ID = 'Mid-Atlantic'")

Paul

 

[PHV]

Anyway it's usually a bad idea to store calculated/derived values in a database, as you can retrieve the (b]accurate[/b] values by queries.
Take a look here:

http://www.abcdataworks.com/fundamentals.htm

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

 

[PaulBricker]

I see a square bracket in front of the DSum [DSum
Could that be it?

Paul