Wrox Programmer Forums
|
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 February 23rd, 2009, 09:54 PM
Friend of Wrox
 
Join Date: Jan 2004
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default Access SQL statement help

Having toruble doing this in access 2003:

SELECT......., IIf([Days Opened]>[SLA Agreement],"Yes","No") AS [Has Meet SLA]
FROM tbleName.....


Specifically my issue is with:

IIf([Days Opened]>[SLA Agreement],"Yes","No") AS [Has Meet SLA]
I am getting an 'out of disk space in temp location error' I know I am not out of disk space. Does it matter if there are no values in some of the 'Days Opened' or 'SLA Agreement' fields? Any assitance getting this working while allowing empty field values would be great. NOTE they are both number data types.

TYIA
__________________
Wind is your friend
Matt
 
Old February 24th, 2009, 12:56 AM
Authorized User
 
Join Date: Jul 2008
Posts: 38
Thanks: 1
Thanked 2 Times in 2 Posts
Default

Try this instead:

Code:
IIf(Nz([Days Opened],0)>Nz([SLA Agreement],0),"Yes","No")
__________________
Thanks,

Bob Larson
Access MVP (2008-2010, 2011)
Free Access tutorials, samples, tools
 
Old February 24th, 2009, 06:28 PM
Friend of Wrox
 
Join Date: Jan 2004
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

I ended up getting this working:

SWITCH
(
[Days Opened] <= TBL_SLA_Activity.[Number of Days] , 'YES',
[Days Opened] > TBL_SLA_Activity.[Number of Days] , 'NO',
TRUE, 'N/A'
) AS [Has Meet SLA]

Thankyou for your time. May I trouble you for a question...

I have this in my query:

datediff("d",[DateTime],[mmmm.maxdate]) AS [Days Opened]

I would like to exclude any sat or sun in the date range. Any ideas on this?

TYIA
__________________
Wind is your friend
Matt
 
Old March 2nd, 2009, 05:58 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Which date field would you like to exclude from the weekend? Is it [DateTime] or [mmmm.maxdate] or both?

For example, if you wish to exclude weekends for [DateTime] then create another query field called

WD: WeekDay([DateTime])

Then for the criteria, say

Between 2 and 6

This will force the weekday to fall between Monday and Friday (assuming 1 = Sunday and 7 = Saturday for your computer).
__________________
Greg Serrano
Michigan Dept. of Environmental Quality
Air Quality Division
 
Old March 2nd, 2009, 06:27 PM
Friend of Wrox
 
Join Date: Jan 2004
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

Thanks for your time however I no longer have to work with this **&&^^% access system. I have only ever used Access as a datastore therefore my ASP allows me to do what I want with recordsets. I found it very difficult to get the 'exact' result set using SQL in these access reports. I will try to only ever use Access a a database in the future, give me a .asp page anyday....
__________________
Wind is your friend
Matt
 
Old March 3rd, 2009, 03:50 AM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

LOL! I think I have to agree with your attitude on that. I use Access as a DB all the time, but I shudder whenever I have to use Access-the-program.





Similar Threads
Thread Thread Starter Forum Replies Last Post
convert a SQL Statement from MS Access to a SQL Corey Access 6 March 28th, 2007 12:33 PM
SQL Statement??? carswelljr SQL Server 2000 2 August 25th, 2006 12:40 PM
Iif statement in Access rangeview Access 7 March 28th, 2006 01:14 PM
YYYY and MM in access' SQL select statement Condor76 Access VBA 1 February 15th, 2006 02:47 PM
Access SQL Statement to Format a Dropdown List jonesl3 Access 3 November 24th, 2004 10:08 AM





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