Solved

Convert VBA to DateDiff calculation query

Posted on 2004-08-26
9
606 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
  • 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
 

Author Comment

by:John Account
ID: 11909274
Hmmmmnnn....I'll be trying it out. Wish me luck! :-)
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

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

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

759 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now