 |
| 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
|
|
|
|
February 23rd, 2009, 09:54 PM
|
|
Friend of Wrox
|
|
Join Date: Jan 2004
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
|
|
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
|
|
February 24th, 2009, 12:56 AM
|
|
Authorized User
|
|
Join Date: Jul 2008
Posts: 38
Thanks: 1
Thanked 2 Times in 2 Posts
|
|
Try this instead:
Code:
IIf(Nz([Days Opened],0)>Nz([SLA Agreement],0),"Yes","No")
|
|
February 24th, 2009, 06:28 PM
|
|
Friend of Wrox
|
|
Join Date: Jan 2004
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
|
|
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
|
|
March 2nd, 2009, 05:58 PM
|
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
|
|
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
|
|
March 2nd, 2009, 06:27 PM
|
|
Friend of Wrox
|
|
Join Date: Jan 2004
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
|
|
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
|
|
March 3rd, 2009, 03:50 AM
|
|
Friend of Wrox
|
|
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
|
|
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.
|
|
|
