asked on

Access condition not being met

Still working in Access.

My customer has a pre-existing app that needs to be updated so the library title list is updated with various verbiage.

Here is the code:

INSERT INTO NEw_Title_List (title, author, isbn,published_date, importedtitles, titlereceivedwithin10years, titlereceiptdate)
SELECT otitle, oauthor, oisbn,opublished_date, oimportedtitles, otitlereceivedwithin10years, otitlereceiptdate
Iif (importedtitles = 'Y' AND titlereceiptdate < DateAdd("yyyy", +1, Date()), '10 percent upcharge,
Iif (importedtitles = 'Y' AND titlereceivedwithin10years= 'Y' AND titlereceiptdate < DateAdd("yyyy", -1, Date()), 'no charge'))
FROM old_list1, left join oldlist2 on old_list1.ISBN = oldlist2.ISBN
FROM tbl_library

My first condition works - but, my second condition that needs to print "no charge" doesn't!

What am I doing wrong?

My dates are formatted 'mm/dd/yyyy'

Kent Dyer

First thing I notice, you cannot have two from statements..

I would get your SELECT statement sorted out..

Then, add it to your INSERT..

HTH,

Kent

PortletPaul

your code has a syntax error, a missing single quote after upcharge
see this
try this

INSERT INTO NEw_Title_List (
       title
     , author
     , isbn
     , published_date
     , importedtitles
     , titlereceivedwithin10years
     , titlereceiptdate
     )
SELECT
       otitle
     , oauthor
     , oisbn
     , opublished_date
     , oimportedtitles
     , otitlereceivedwithin10years
     , otitlereceiptdate Iif(importedtitles = 'Y'
          AND titlereceiptdate < DateAdd("yyyy", + 1, DATE ()), '10 percent upcharge',
  Iif (importedtitles = ' Y ' AND   titlereceivedwithin10years= ' Y '  AND titlereceiptdate < DateAdd("yyyy", -1, Date()), ' no charge '))
FROM old_list1, left join oldlist2 on old_list1.ISBN = oldlist2.ISBN
FROM tbl_library')

Open in new window

PortletPaul

mmm, sorry, still seems to have syntax errors
see this

als check those ' Y ' references, do you really have both a leading and trailing space around the Y ?

Kent Dyer

Yep.. Just started to look at the SELECT and result is:

SELECT otitle
, oauthor
, oisbn
,opublished_date
, oimportedtitles
,  otitlereceivedwithin10years
, otitlereceiptdate
, Iif (importedtitles = 'Y' AND titlereceiptdate < DateAdd("yyyy", +1, Date())
, '10 percent upcharge
, Iif (importedtitles = 'Y' AND titlereceivedwithin10years= 'Y'  AND
 titlereceiptdate < DateAdd("yyyy", -1, Date()), 'no charge'))
FROM old_list1
 left join oldlist2 on old_list1.ISBN = oldlist2.ISBN

Open in new window

Still need to include: FROM tbl_library as well probably as a JOIN..

HTH,

Kent

SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

programmher

ASKER

In Access, the Iifs are supposed towork the way CASE WHEN THEN statements work in SQL. If the conditions are met, the corresponding verbiage is printed in the field. The first condition works but the second condition doesn't.

The extra spaces don't exist in the actual code. It must have happened when I copied and pasted.

curious that my first condition works but not the second...

PortletPaul

sorry, that's not what I meant - I know what IIF's do :)

the point was, are those IIF's supposed to be column 7 ?
or, is there a missing column 8 in the upper portion?

programmher

ASKER

Sorry!

The Iifs are supposeded to be column 7.

programmher

ASKER

Here is the corrected logic with the additional missing fields and the Iif statements:

INSERT INTO NEw_Title_List (
title
, author
, isbn
, published_date
, importedtitles
, titlereceivedwithin10years
, titlereceiptdate,
,importedtitles
,titlelistpricemaster
)
FROM (
SELECT otitle
      , oauthor
      , oisbn
      , opublished_date
      , oimportedtitles
      , otitlereceivedwithin10years
      , otitlereceiptdate
      , Iif(importedtitles = 'Y'
            AND titlereceiptdate < DateAdd("yyyy", + 1, DATE ()), '10 percent upcharge', Iif(importedtitles = 'Y'
                  AND titlereceivedwithin10years = 'Y'
                  AND titlereceiptdate < DateAdd("yyyy", - 1, DATE ()), 'no charge'))
FROM old_list1
LEFT JOIN oldlist2 ON old_list1.ISBN = oldlist2.ISBN
)

SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

programmher

ASKER

When I run the original SELECT statement, it works.

I have had to substitute actual column and table names for substitute column and table names due to client confidentiality. probably need to take a break as I have been tacking these lovely access issues for several hours now. (Trying to convince this customer to let me convert this to a more versatile language with a web frontend.)

My problem was that I had two Iif statements. the first one worked but the second does not work, I tried putting parens around the three criterion in the conditions to see if perhaps Access was getting confused.

I appreciate all your comments and all of your help. will take a break, review everything again, award points and close out the question.

Thank you again!

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial