Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 18th, 2005, 10:28 AM
Registered User
 
Join Date: Jan 2005
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default Help needed with calculation in a query

I've set up a query to report Performance Indicators on my database.

The fields are as follows:
Ref
Premises
Paperwork Received
Date Received
Sufficient Paperwork Received
Date Outstanding Paperwork Received
Sent to Education
Received from Education
Education Turnaround Time
Date Comments Returned
Turnaround Time

I have set up a sum to calculate the Education Turnaround Time and that works fine.

I have then basically used the following calculation:
[Date Comments Returned]-[Date Received]-[Education Turnaround Time]
and this works well.

The problem I have is sometimes the calculation needs to be based on
[Date Comments Returned]-[Outstanding Information Received]-[Education Turnaround Time]. This must only be used when the Outstanding Info field is populated.

I'm sure this must be fairly simple to all you SQL experts out there, but I'm fairly new to Access and my knowledge of code is limited.

 
Old August 18th, 2005, 10:33 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Where are you executing the query from?

Kevin

dartcoach
 
Old August 18th, 2005, 10:43 AM
Registered User
 
Join Date: Jan 2005
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I've entered the calculation directly into the control source of a report.

 
Old August 18th, 2005, 10:46 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

I would try checking to see if the Outstanding Info is null, if it is, use your current calculation else use the other one.

Kevin

dartcoach
 
Old August 18th, 2005, 10:49 AM
Registered User
 
Join Date: Jan 2005
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

How would I go about asking if the Outstanding Info field is null before it carrying out the original calculation?

 
Old August 18th, 2005, 10:56 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

I would use the IIF function.
In you control source:

=IIf(isnull(me.Outstanding info,(Calc1),(Calc2)

if Outstanding Info is null, it carries out calc1 otherwise it carries out calc2.

Use the help for IIF to insure you utilize the correct syntax.

Kevin

dartcoach
 
Old August 18th, 2005, 10:57 AM
Registered User
 
Join Date: Jan 2005
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks very much for the help - I'll let you know if it works.






Similar Threads
Thread Thread Starter Forum Replies Last Post
Quotes in query; help needed! Hylmar MySQL 3 December 21st, 2006 11:37 PM
Help needed with JOIN Query icecrew SQL Server 2000 3 October 16th, 2006 11:59 AM
Nested Query Help Needed Corey Access 2 April 26th, 2006 02:17 PM
Query for two conditions needed doosti SQL Language 2 November 9th, 2004 06:24 AM
Query based on date calculation? skinny Access 5 September 30th, 2003 03:20 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.