422,024 Members | 1,010 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 422,024 IT Pros & Developers. It's quick & easy.

DSum Function With Multiple Criteria in Query

P: 5
Amount Date-of-payment
450 8/1/11
600 8/2/11
400 8/3/11
870 8/5/11
200 8/1/11
900 8/2/11

I want to get total amount between 8/1/11 to 8/3/11
Oct 2 '17 #1
Share this Question
Share on Google+
9 Replies


NeoPa
Expert Mod 15k+
P: 30,549
I'll assume for now that you're posting from America and the dates are all for August rather than the 8th day of various months. Bear in mind that dates written in that form on an international site are ambiguous and lead to confusion and misunderstanding. There is also no mention of the name of the table or other data source in the question. That's necessary information, of course. Please include all necessary information in any questions.

So, if using DSum(), then you'd want something like :
Expand|Select|Wrap|Line Numbers
  1. X = DSum("[Amount]" _
  2.        , "[NameOfTable]" _
  3.        , "[Date-of-Payment] Between #2011-8-1# And #2011-8-3#")
Oct 2 '17 #2

P: 5
Amount Date-of-payment
450 8/aug/11
600 6/feb/11
400 4/jan/11
870 8/sep/11
200 8/mar/11
900 7/feb/11
table name is "PAYMENTS"

I want to get total amount between ANY 2 DATES. PLZ
Oct 3 '17 #3

NeoPa
Expert Mod 15k+
P: 30,549
I have no conception of why you'd ask for two specific dates when what you want is to use any two dates. However, you can replace the date literals with prompts if that's your requirement.
Expand|Select|Wrap|Line Numbers
  1. X = DSum("[Amount]" _
  2.        , "[PAYMENTS]" _
  3.        , "[Date-of-Payment] Between [First Date] And [Last Date])
Oct 4 '17 #4

P: 5
Respectd sir, thanks for helping me and giving your precious time to me. but unfortunately this query shows blank "Total" column and worng dates. what should i do sir please?
Oct 6 '17 #5

NeoPa
Expert Mod 15k+
P: 30,549
BFSChung:
What should I do sir please?
I expect the next step for you would be to include the code you have. If this is VBA that creates SQL please include the SQL produced by the VBA too. If just SQL then please format it as well as you can in a way that's legible (See Before Posting (VBA or SQL) Code).
Oct 6 '17 #6

P: 5
Hi sir,
Sir if i may send you my database, would you pleaze create a query to solve my problem?
Oct 10 '17 #7

NeoPa
Expert Mod 15k+
P: 30,549
That wouldn't help. Until I have a clear understanding of what you actually require I can't provide a solution for you.

Furthermore, saving yourself the effort of writing out your question fully and properly simply means you expect others to work even harder to help you. I expect you can see why this sort of attitude is not appreciated.

You asked earlier what you should do next, but after I replied you still haven't done that. Even worse, you posted your reply in an article thread - thus damaging both and requiring work to tidy up the mess. You expect a lot but offer little, and this is on your own question.
Oct 11 '17 #8

P: 5
Respected sir,
I want to explain my problem. i have an installment shop. i sell electronics on installments. and all my receivings goes in a table names"PAYMENTS". This table has fields. "ACCOUNT", "NAME", "AMNTRCVD", "DATED". I want to find receivings of a specific period of times, e.g. month of june, year 2016, etc,. so i may be able to check my balance and receive cash from my manager. i hope you may follow my problem.
Oct 12 '17 #9

NeoPa
Expert Mod 15k+
P: 30,549
In that case I'll refer you back to my answer in post #4. You just need to change [Amount] to [AMNTRCVD] and [Date-of-Payment] to [DATED] :
Expand|Select|Wrap|Line Numbers
  1. X = DSum("[AMNTRCVD]" _
  2.        , "[PAYMENTS]" _
  3.        , "[DATED] Between [First Date] And [Last Date])
Oct 31 '17 #10

Post your reply

Sign in to post your reply or Sign up for a free account.