Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 637
  • Last Modified:

Convert VBA to DateDiff calculation query

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
John Account
Asked:
John Account
  • 5
  • 4
1 Solution
 
shanesuebsahakarnCommented:
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
 
John AccountAuthor Commented:
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
 
shanesuebsahakarnCommented:
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
Technology Partners: 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!

 
John AccountAuthor Commented:
Hmmmmnnn....I'll be trying it out. Wish me luck! :-)
0
 
shanesuebsahakarnCommented:
Any problems, don't hesitate to post back!
0
 
John AccountAuthor Commented:
Thank, you shanesuebsahakarn. It's working. Is there a way to format it in currency--or should this be in another question?
0
 
shanesuebsahakarnCommented:
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
 
John AccountAuthor Commented:
You're a really great guy! Thank you very much, shanesuebsahakarn!
0
 
shanesuebsahakarnCommented:
No probs, glad I could help :)
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now