Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
BOOK: Microsoft Access 2010 24-Hour Trainer
This is the forum to discuss the Wrox book Microsoft Access 2010 24-Hour Trainer by Geoffrey L. Griffith, Truitt L. Bradly; ISBN: 978-0-470-59167-3
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Microsoft Access 2010 24-Hour Trainer section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old April 29th, 2013, 09:46 AM
Registered User
Points: 5, Level: 1
Points: 5, Level: 1 Points: 5, Level: 1 Points: 5, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2013
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Access SQL

I'm trying to recode this access code into sql but getting stocked with these IIF, please help:
--------------

SELECT M_accdi.wh_acc_no, (M_accdi.ACCRD_INT+M_accdi.dr_bal) AS Balance
, M_accdi.IntOnlyInd AS Int_Only_Ind
, IIF(M_accdi.REPAYMT_INTERVAL=3,4
,IIF(M_accdi.REPAYMT_INTERVAL=6,2
,IIF(M_accdi.REPAYMT_INTERVAL=7,52
,IIF(M_accdi.REPAYMT_INTERVAL=12,1
,IIF(M_accdi.REPAYMT_INTERVAL=14,26,12))))) AS Divisor
,((IIF(M_accdi.CNTRACT_MATURITY_DTE IS NULL,M_accdi.NXT_REPRICING_DTE
,M_accdi.CNTRACT_MATURITY_DTE)-#12/31/2012#)/365.25) AS Resid_Mat
, IIF(((IIF(M_accdi.CNTRACT_MATURITY_DTE IS NULL
,M_accdi.NXT_REPRICING_DTE,M_accdi.CNTRACT_MATURIT Y_DTE)-#12/31/2012#)/365.25)<=0,1,
CInt(IIF(M_accdi.REPAYMT_INTERVAL=3,4,IIF(M_accdi. REPAYMT_INTERVAL=6,2
,IIF(M_accdi.REPAYMT_INTERVAL=7,52,IIF(M_accdi.REP AYMT_INTERVAL=12,1
,IIF(M_accdi.REPAYMT_INTERVAL=14,26,12)))))*((IIF( M_accdi.CNTRACT_MATURITY_DTE IS NULL
,M_accdi.NXT_REPRICING_DTE,M_accdi.CNTRACT_MATURIT Y_DTE)-#12/31/2012#)/365.25)
)) AS Num_Payments, M_accdi.acc_dr_int_rte/100 AS acc_dr_int_rte
, M_accdi.repaymt_amt, M_accdi.Var_Ind, M_accdi.DEFAULT
FROM M_accdi;

-----------
Reply With Quote
  #2 (permalink)  
Old August 22nd, 2013, 03:43 AM
gjgriffith's Avatar
Wrox Author
Points: 517, Level: 8
Points: 517, Level: 8 Points: 517, Level: 8 Points: 517, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2009
Location: Boulder, CO
Posts: 110
Thanks: 5
Thanked 14 Times in 14 Posts
Default Can you send me the database?

Hello menssanvi,

Thank you for posting your question in the "Microsoft Access 2010 24-Hour Trainer" book forum. I see the code you have provided here and I'm wondering if you can send me the actual database so that I can take a look at the problem? Please let me know when you have a chance!

Best Regards,
__________________
Geoffrey L. Griffith
http://www.ImagineThought.com

Wrox Author of:
Microsoft Access 2010 24-Hour Trainer
Access 2010 Programmer's Reference
Access 2007 VBA Programmer's Reference

*** Please click the THANKS button (to the right) if this post helped you! *** ---------------------------------------------------------------------------------------------------------->
Reply With Quote
  #3 (permalink)  
Old August 23rd, 2013, 10:26 AM
Registered User
Points: 21, Level: 1
Points: 21, Level: 1 Points: 21, Level: 1 Points: 21, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2012
Location: england
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default access sql to SQL server SQL

My first post - hope it works.
sounds like you may not understand the IIF(a,b,c) construct.
it is known as the inline If.
you can view it in builder in the query design part of access.
basically it says if(any condition,value if true,value if false)
complexity is added by nesting them.
iif(day of week = 6 or 7, "weekend", "weekday" ) [assuming that was right]
iif(dow=1,"mon",iif(dow=2,"tue",iif(dow=3,"wed", "thu onwards")))
anyway - you can replace that in SQL using CASE when then construct.

case dow when 1 then "mon"
when 2 then "tue"
when 3 then "wed"
else "something else"
end case

- I've forgotten the actual syntax and you may not need end case
test with just 2 nested conditions before you write it all.
good luck from
ozzzy
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to pass multiple value as parameters to MS access Report from MS access sql code mukta Access 0 April 13th, 2012 08:43 AM
convert a SQL Statement from MS Access to a SQL Corey Access 6 March 28th, 2007 01:33 PM
SQL Access/ASP.NET data access issue saeta57 SQL Server ASP 1 July 4th, 2004 05:29 PM
SQL Access/ASP.NET data access issue saeta57 Classic ASP Databases 1 July 4th, 2004 04:32 PM
SQL in Access and in SQL Server mikko SQL Server 2000 1 July 29th, 2003 07:17 AM



All times are GMT -4. The time now is 08:58 AM.


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.