Solved

Convert VBA to DateDiff calculation query

Posted on 2004-08-26
9
612 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

840 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