Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 844
  • Last Modified:

Access/SQL IIF '>' Syntax differences

I have the following SQL query that when i run it in access it works perfectly.
but when i use this same syntax in an oleadapter in my .net app i get the following error.

"Error in list of function arguments: '>' not recognized.
Unable to parse query text."

Is there additional brackets or quotes that i'm missing?


SELECT IIf(A.ASSIGN_DT>A.EXP_DT,DateDiff('d',A.ASSIGN_DT,Now()),DateDiff('d',A.EXP_DT,Now())) AS AGE, A.CASE_ID, A.CASE_TYPE, A.INSURED, A.INS_DBA, A.ASSIGN_DT, A.EXP_DT, A.SENT_DT, A.REP_DUE_DT, A.CASE_READ, A.CON_CITY, A.INS_PHONE, B.STAT_COMMENTS AS Scheduled, B.STATUS_DESC AS Description
FROM [SELECT     dattbl_Case.CASE_ID, dattbl_Case.CASE_TYPE, dattbl_Case.INSURED, dattbl_Case.INS_DBA, dattbl_Case.ASSIGN_DT,
                                              dattbl_Case.REP_DUE_DT, dattbl_Case.EXP_DT, dattbl_Tracker.CASE_READ, dattbl_Tracker.SENT_DT, dattbl_Case.CON_CITY,
                                              dattbl_Case.INS_PHONE
                       FROM          dattbl_Case INNER JOIN
                                              dattbl_Tracker ON dattbl_Case.CASE_ID = dattbl_Tracker.CASE_ID
                       WHERE      (dattbl_Tracker.FOLDER = 'INBOX')]. AS A LEFT JOIN [SELECT     CASE_ID, STAT_CODE, STAT_COMMENTS, STAT_ID, STAT_NEXT_DT, STATUS_DESC
                            FROM          dattbl_Status
                            WHERE      (STAT_CODE = 78 OR
                                                   STAT_CODE = 79 OR
                                                   STAT_CODE = 80 OR
                                                   STAT_CODE = 81)]. AS B ON A.CASE_ID = B.CASE_ID;
0
DTRON04
Asked:
DTRON04
  • 7
  • 4
  • 4
2 Solutions
 
jdlambert1Commented:
Try this:

SELECT CASE WHEN A.ASSIGN_DT > A.EXP_DT THEN DateDiff('d',A.ASSIGN_DT,Now()) ELSE DateDiff('d',A.EXP_DT,Now()) END AS AGE,
 A.CASE_ID, A.CASE_TYPE, A.INSURED, A.INS_DBA, A.ASSIGN_DT, A.EXP_DT, A.SENT_DT, A.REP_DUE_DT, A.CASE_READ,
 A.CON_CITY, A.INS_PHONE, B.STAT_COMMENTS AS Scheduled, B.STATUS_DESC AS [Description]
FROM
(SELECT c.CASE_ID, c.CASE_TYPE, c.INSURED, c.INS_DBA, c.ASSIGN_DT,
 c.REP_DUE_DT, c.EXP_DT, t.CASE_READ, t.SENT_DT, c.CON_CITY,
 c.INS_PHONE
 FROM dattbl_Case c
 INNER JOIN dattbl_Tracker t ON c.CASE_ID = t.CASE_ID
 WHERE t.FOLDER = 'INBOX') AS A
LEFT JOIN (
 SELECT CASE_ID, STAT_CODE, STAT_COMMENTS, STAT_ID, STAT_NEXT_DT, STATUS_DESC
 FROM dattbl_Status
 WHERE STAT_CODE IN (78,79,80,81) AS B ON A.CASE_ID = B.CASE_ID
0
 
monosodiumgCommented:
Those dots again!!!
Access sometimes lsplis in some extra full stops here and there when you look at the SQL .
E.g. in your last line the first dot should not be there (between $$$):
TAT_CODE = 81)]$$$.$$$ AS B ON A.CASE_ID = B.CASE_ID;

Also in this line:
WHERE      (dattbl_Tracker.FOLDER = 'INBOX')]$$$.$$$

That could be breaking it.
0
 
monosodiumgCommented:
You also need to reaplce the [] round the subqueries with (). Another Access quirk.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
DTRON04Author Commented:
yeah that query is what access did to my original syntax when i copy pasted it from the .Net query builder
that syntax actually worked in access as shown in the question.

Sorry here is what i am trying to feed the query builder in .net against the accessdb

SELECT CASE WHEN A.ASSIGN_DT > A.EXP_DT THEN DateDiff('d',A.ASSIGN_DT,Now()) ELSE DateDiff('d',A.EXP_DT,Now()) END AS AGE, A.CASE_ID, A.CASE_TYPE, A.INSURED, A.INS_DBA, A.ASSIGN_DT, A.EXP_DT, A.SENT_DT, A.REP_DUE_DT, A.CASE_READ, A.CON_CITY, A.INS_PHONE, B.STAT_COMMENTS AS Scheduled, B.STATUS_DESC AS Description
FROM         (SELECT     dattbl_Case.CASE_ID, dattbl_Case.CASE_TYPE, dattbl_Case.INSURED, dattbl_Case.INS_DBA, dattbl_Case.ASSIGN_DT,
                                              dattbl_Case.REP_DUE_DT, dattbl_Case.EXP_DT, dattbl_Tracker.CASE_READ, dattbl_Tracker.SENT_DT, dattbl_Case.CON_CITY,
                                              dattbl_Case.INS_PHONE
                       FROM          dattbl_Case INNER JOIN
                                              dattbl_Tracker ON dattbl_Case.CASE_ID = dattbl_Tracker.CASE_ID
                       WHERE      (dattbl_Tracker.FOLDER = 'INBOX')) A LEFT OUTER JOIN
                          (SELECT     CASE_ID, STAT_CODE, STAT_COMMENTS, STAT_ID, STAT_NEXT_DT, STATUS_DESC
                            FROM          dattbl_Status
                            WHERE      (STAT_CODE = 78 OR
                                                   STAT_CODE = 79 OR
                                                   STAT_CODE = 80 OR
                                                   STAT_CODE = 81)) B ON A.CASE_ID = B.CASE_ID

this is the error
Error in SELECT clause: expression near 'A'.
Missing FROM clause.
Error in SELECT clause: expression near ','.
Error in list of function arguments: ')' not recognized.
Error in list of function arguments: ',' not recognized.
Unable to parse query text.

Now i tried to feed it jd's recomendation and got this

Error in SELECT clause: expression near 'A'.
Missing FROM clause.
Error in SELECT clause: expression near ','.
Error in list of function arguments: ')' not recognized.
Error in list of function arguments: ',' not recognized.
Unable to parse query text.

this one is kicking my a**

0
 
DTRON04Author Commented:
here is the original without access doing what it does

SELECT     IIf(A.ASSIGN_DT > A.EXP_DT, DateDiff('d', A.ASSIGN_DT, Now()), DateDiff('d', A.EXP_DT, Now())), A.CASE_ID, A.CASE_TYPE, A.INSURED, A.INS_DBA,
                      A.ASSIGN_DT, A.EXP_DT, A.SENT_DT, A.REP_DUE_DT, A.CASE_READ, A.CON_CITY, A.INS_PHONE, B.STAT_COMMENTS AS Scheduled,
                      B.STATUS_DESC AS Description FROM  
      (SELECT     dattbl_Case.CASE_ID, dattbl_Case.CASE_TYPE, dattbl_Case.INSURED, dattbl_Case.INS_DBA, dattbl_Case.ASSIGN_DT,
                                              dattbl_Case.REP_DUE_DT, dattbl_Case.EXP_DT, dattbl_Tracker.CASE_READ, dattbl_Tracker.SENT_DT, dattbl_Case.CON_CITY,
                                              dattbl_Case.INS_PHONE
                       FROM          dattbl_Case INNER JOIN
                                              dattbl_Tracker ON dattbl_Case.CASE_ID = dattbl_Tracker.CASE_ID
                       WHERE      (dattbl_Tracker.FOLDER = 'INBOX')) A LEFT OUTER JOIN
                          (SELECT     CASE_ID, STAT_CODE, STAT_COMMENTS, STAT_ID, STAT_NEXT_DT, STATUS_DESC
                            FROM          dattbl_Status
                            WHERE      (STAT_CODE = 78 OR
                                                   STAT_CODE = 79 OR
                                                   STAT_CODE = 80 OR
                                                   STAT_CODE = 81)) B ON A.CASE_ID = B.CASE_ID

i get this error with this

Error in list of function arguments: '>' not recognized.
Unable to parse query text.
0
 
DTRON04Author Commented:
these guys are talking about what i am i think

http://p2p.wrox.com/topic.asp?TOPIC_ID=5912

0
 
monosodiumgCommented:
Last version of Access I used was 2K. It didn't have a CASE statement if I remember correctly. Iif was a close as you got.
You need to find out what SQL language is required. Now()  is a VB function, not an SQL server function. YOu would need to use GetDate if this is (as  I suspect) TSQL.

Also the interval constants in sqL server are different:
'd' -> d (no quotes)

Description is a reserverd word I think . Wrap it up in []: [Description].
0
 
DTRON04Author Commented:
i did as you recommended with the [description] and removing the 'd'

the issue has to be in the IIF statement
the query works fine without it and just a straight DATEDIFF

how can i tell if I am using TSQL.

Sorry so clueless i have never developed against access only SQL 7 & 2000

0
 
jdlambert1Commented:
Access still doesn't have a CASE function. Here's a summary of changes necessary:

 [...] to (...)
 Now() to GetDate()
 IIF to CASE
0
 
DTRON04Author Commented:
All of that is done except for CASE because Access does not have this function
here is what i have now, am i screwed or am i not seeing what your trying to show me.

SELECT     IIf(A.ASSIGN_DT > A.EXP_DT, DateDiff(d, A.ASSIGN_DT, GetDate()), DateDiff(d, A.EXP_DT, GetDate())), A.CASE_ID, A.CASE_TYPE, A.INSURED,
                      A.INS_DBA, A.ASSIGN_DT, A.EXP_DT, A.SENT_DT, A.REP_DUE_DT, A.CASE_READ, A.CON_CITY, A.INS_PHONE, B.STAT_COMMENTS AS Scheduled,
                      B.STATUS_DESC AS [Description]
FROM         (SELECT     dattbl_Case.CASE_ID, dattbl_Case.CASE_TYPE, dattbl_Case.INSURED, dattbl_Case.INS_DBA, dattbl_Case.ASSIGN_DT,
                                              dattbl_Case.REP_DUE_DT, dattbl_Case.EXP_DT, dattbl_Tracker.CASE_READ, dattbl_Tracker.SENT_DT, dattbl_Case.CON_CITY,
                                              dattbl_Case.INS_PHONE
                       FROM          dattbl_Case INNER JOIN
                                              dattbl_Tracker ON dattbl_Case.CASE_ID = dattbl_Tracker.CASE_ID
                       WHERE      (dattbl_Tracker.FOLDER = 'INBOX')) A LEFT OUTER JOIN
                          (SELECT     CASE_ID, STAT_CODE, STAT_COMMENTS, STAT_ID, STAT_NEXT_DT, STATUS_DESC
                            FROM          dattbl_Status
                            WHERE      (STAT_CODE = 78 OR
                                                   STAT_CODE = 79 OR
                                                   STAT_CODE = 80 OR
                                                   STAT_CODE = 81)) B ON A.CASE_ID = B.CASE_ID
0
 
jdlambert1Commented:
>All of that is done except for CASE because Access does not have this function

I thought you were trying to pass this to SQL Server...

SQL Server and Access don't use the same flavor of SQL, so there are a lot of things (like in this query) where one command can't work in both.
0
 
DTRON04Author Commented:
i am aware they cannot work in both that is why i am asking the question.

but this is TSQL against an access db, which is the reason why we cannot use CASE.
this is for a .net app against an access db

should i post this in the access forum?


sorry if i didnt explain that b4.

0
 
monosodiumgCommented:
If you are hitting Access, then you need Now and Iff and excepting the original issues with [] and "." your code was OK.

I would go back to that.

I have had problems in the past calling function like iif etc because the driver choked on them. At least that was my understanding .
I suggest you try a few very simple queries just to make sure we can properly use iif() and now() and datediff().
Queires such as :

select iif(1=2, "a", "b") from mytable

Let us know how these tests go.
0
 
DTRON04Author Commented:
thanks to both for the guidance im taking a break for a bit, i will let you know how it goes
0
 
jdlambert1Commented:
>should i post this in the access forum?

Yes, if monosodiumg suggestions aren't sufficient. You can post a request in Community Support, but make sure you cite the Question number in the URL so they know which Q you're talking about. They could just move this, but I suggest you ask them to delete this one and post a new one based on what you've learned here so far. Or, if you're a premium member and have unlimited points, just award this to monosodiumg for one of his posts and post the new question.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now