• Status: Solved
• Priority: Medium
• Security: Public
• Views: 642

# 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
Me.Text14 = IIf(overdue > 30, Me.Amount * 0.02 / 30 * overdue, 0)

John
0
John Account
• 5
• 4
1 Solution

Commented:
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:

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

Author 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?
John
0

Commented:
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

Author Commented:
Hmmmmnnn....I'll be trying it out. Wish me luck! :-)
0

Commented:
Any problems, don't hesitate to post back!
0

Author Commented:
Thank, you shanesuebsahakarn. It's working. Is there a way to format it in currency--or should this be in another question?
0

Commented:
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 Commented:
You're a really great guy! Thank you very much, shanesuebsahakarn!
0

Commented:
No probs, glad I could help :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Featured Post

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