Solved

# Convert VBA to DateDiff calculation query

Posted on 2004-08-26
619 Views
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
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
• 5
• 4

LVL 41

Expert Comment

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:

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

Author Comment

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

LVL 41

Expert Comment

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

Author Comment

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

LVL 41

Expert Comment

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

Author Comment

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

shanesuebsahakarn earned 200 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

ID: 11909532
You're a really great guy! Thank you very much, shanesuebsahakarn!
0

LVL 41

Expert Comment

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

## Featured Post

Question has a verified solution.

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

Itâ€™s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrickâ€™s Day holiday can be felt throughout the world.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which â€¦
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as formâ€¦
###### Suggested Courses
Course of the Month4 days, 5 hours left to enroll