By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,009 Members | 1,643 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,009 IT Pros & Developers. It's quick & easy.

Calculated field from calculated fields

P: 3
Hi and thanks in advance,
I am using DMin and DMax to calculate date fields in a form.
I need to use these calculated dates in another calculated field.

=DMax("[B&MPayPeriodStartDate]","B&MPayPeriod"," [B&MSalesDate] >= [B&MPayPeriodStartDate] ")
This code calculates the starting date for a pay period and places it in a text box in my form.

=DMin("[B&MPayPeriodEndDate]","B&MPayPeriod"," [B&MSalesDate] <= [B&MPayPeriodEndDate] ")
This code calculates the ending date for a pay period and places it in a text box in my form.

I now need to calculate the sum of sales between the two dates listed above. I've tried everything I can think of. And spent hours searching Access help and google but keep coming up blank. Any help will be greatly appreciated.


Oct 26 '06 #1
Share this Question
Share on Google+
2 Replies

Expert Mod 15k+
P: 31,471
If you want to use the D-series functions (rather than SQL) in code (and guessing the sales field is something like [B&MPaySales]) then :-

Expand|Select|Wrap|Line Numbers
  1. Dim    datStart As Date, datEnd As Date
  2. Dim    strStart As String, strEnd As String
  3. Dim    curSales As Currency
  5. datStart = DMax("[B&MPayPeriodStartDate]", _
  6.                 "[B&MPayPeriod]", _
  7.                 "[B&MSalesDate] >= [B&MPayPeriodStartDate]")
  8. datEnd = DMin("[B&MPayPeriodEndDate]", _
  9.               "[B&MPayPeriod]", _
  10.               "[B&MSalesDate] <= [B&MPayPeriodEndDate]")
  11. strStart = Format(datStart, "\#MM/DD/YYYY\#")
  12. strEnd = Format(datEnd, "\#MM/DD/YYYY\#")
  13. curSales = DSum("[B&MPaySales]", _
  14.                 "[B&MPayPeriod]", _
  15.                 "[B&MSalesDate] Between " & strStart & " And " & strEnd)
Bearing in mind - SQL always expects dates in USA format (MM/DD/YYYY) and the '#' character is the 'surrounder' for Date literals just as the single-quote (') character is for SQL string literals.
Oct 26 '06 #2

P: 3
Hi and thanks for the very helpful reply,

While waiting, hoping, for a reply I tried once more to do this with DSum in a calculated field with Expression Builder. I must have had a typo that I could not find the first time I tried because it worked fine this time. I've listed the code I used below.

=DSum("[B&MSalesAmt]","B&MFleaMkt","[B&MSalesDate] >= PayPeriodStart AND [B&MSalesDate] <= PayPeriodEnd")

B&MSalesAmt is the field, in a table, that holds the amount of each sale. PayPeriodStart is the name of the calculated field for my starting date. PayPeriodEnd is the name of the calculated field for the ending date.

I took this one more step and created another calculated field that shows the amount due for any given pay period. There is a 3% deduction from total sales for pay periods that end after 9-1-06. There is a 2% deduction from total sales for pay periods that end before 9-1-06. Here is the Expression Builder code I used for this field.

=IIf([PayPeriodEnd]>=9-1-6,DSum("[B&MSalesAmt]","B&MFleaMkt","[B&MSalesDate] >= PayPeriodStart AND [B&MSalesDate] <= PayPeriodEnd")*0.97,DSum("[B&MSalesAmt]","B&MFleaMkt","[B&MSalesDate] >= PayPeriodStart AND [B&MSalesDate] <= PayPeriodEnd")*0.98)

If there is something wrong with what I have done please do not hesitate to tell me. As you have probably guessed; I don't know much about what I am doing. My normal method of operation is to search Access help until I find something
that looks like it will work. The D series functions were the first I ran across that
appeared to be what I needed. That was the only reason for using them.

Thanks again for the reply,

Oct 26 '06 #3

Post your reply

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