Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Convert VBA to DateDiff calculation query

Posted on 2004-08-26
9
Medium Priority
?
634 Views
Last Modified: 2008-02-26
Hello Experts.
Can you convert the following VBA to a calcuation query---ie., Expr1:iif( DateDiff("...---for me, please?:
Dim overdue As Integer
 overdue = DateDiff("d", Me.DateReceived, Now)
 Me.Text14 = IIf(overdue > 30, Me.Amount * 0.02 / 30 * overdue, 0)

Many kind thanks in advance,
John
0
Comment
Question by:John Account
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11908939
You can't convert an expression into a query. A query has multiple records. However, if you're asking how to put the expression into a query so you get the calculation for each record in the query, it's straightforwards. Replace the Me.<etc> with the actual field names in your table. For example:

Overdue: DateDiff("d",[DateReceived],Now())

and:
AmountDue: IIf([Overdue]>30,[Amount]*0.02/30*[Overdue],0)
0
 

Author Comment

by:John Account
ID: 11909119
Okay, so, by your example, I would need the following fields: Overdue, DateReceived, AmountDue, Amount
Moreover, are you saying that these two strings go into the criteria of different fields, and if so, let me know if I figure right:
Overdue: DateDiff("d",[DateReceived],Now())    ----- goes into the Amount field criteria
AmountDue: IIf([Overdue]>30,[Amount]*0.02/30*[Overdue],0)    ----- goes into the DateReceived field criteria

I'm very new to this, so could you please elaborate?
Many kind thanks in advance,
John
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11909241
No, they don't go into the criteria - they actually go into "Field" row, as they are calculated fields (in other words, new fields in the query that get their values based on other fields).
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:John Account
ID: 11909274
Hmmmmnnn....I'll be trying it out. Wish me luck! :-)
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11909285
Any problems, don't hesitate to post back!
0
 

Author Comment

by:John Account
ID: 11909503
Thank, you shanesuebsahakarn. It's working. Is there a way to format it in currency--or should this be in another question?
0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 800 total points
ID: 11909518
To format as a currency is dead easy - change AmountDue to read:
AmountDue: Format(IIf([Overdue]>30,[Amount]*0.02/30*[Overdue],0),"Currency")
0
 

Author Comment

by:John Account
ID: 11909532
You're a really great guy! Thank you very much, shanesuebsahakarn!
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11909539
No probs, glad I could help :)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question