Jump to content
  • 0

Use Date Criteria to Calculate Sum


Question

I have a datapage (details) referencing a table called "UserTips2". I am trying to calculate the current month's tips only. I have a field that is named "itemamount" that contains all of the dollar amounts of the tips received for that user. Then I have a field called "transactiondate" that records the time and date the tip was received and it is in the following format: "20:15:03 Feb 02, 2018". My goal is to show the amount of tips received for the current month in a details page. I tried using criteria, but it did not work. I need some help trying to figure this out. Also I would like to know if I should be using a formula to accomplish this task and if so what should the formula look like?

Link to post
Share on other sites

1 answer to this question

Recommended Posts

  • 0

Hi TippyTV2018,

I can suggest creating a View based on UserTips2 table in order to filter records based on current month.

Screenshot.JPG

You can reference the View in your SQL query in order to aggregate total of itemamount for the user.

The following syntax can be used to reference a view:  _v_viewname

Here is an example of SQL query: 

SELECT Sum(My_table_Sales) FROM _v_Current_month WHERE My_table_Sale_rep = target.[@field:Sales_rep]

 

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...