Results 1 to 8 of 8
  1. #1
    ezeanya is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2016
    Posts
    9

    FROM keyword not found where expected

    I have this code (part of it) and i get this "From Keyword not found where expected". i tried to put spaces btw From but no result. any help?


    strSQL = " select walitm as 2nd Item Number, Case when wlqmto>0 then wlqmto Else wlsoqs End as RackWind Start,"
    strSQL = strSQL & " walotn as Lot / Serial, wadcto as Type,"
    strSQL = strSQL & " wadl01 as Item Description, to_date(watrdj + 1900000,'YYYYDDD') as Order Date, wadoco as Order No, wasrst as Status,"
    strSQL = strSQL & " wawr01 as Line, wawr02 as Cell, wauom as UOM, wamcu as Branch / Plant, to_date(wadrqj + 1900000,'YYYYDDD') as Request Date, to_date(wastrt + 1900000,'YYYYDDD') Start Date"
    strSQL = strSQL & " From proddta.f4801, proddta.f4102, proddta.f3112 WHERE walitm = Iblitm"
    strSQL = strSQL & " And WLDOCO = WADOCO"
    strSQL = strSQL & " And wlopsq = '1000'"
    strSQL = strSQL & " And wadcto <'WZ'"
    strSQL = strSQL & " And wasrst <> '98'"
    strSQL = strSQL & " And wasrst <> 'E1'"
    strSQL = strSQL & " And wamcu = ' USE03M'"
    strSQL = strSQL & " And Ibmcu = ' USE03M'"
    strSQL = strSQL & " And wawr01 in ('001','006','016')"
    strSQL = strSQL & " And to_date(watrdj + 1900000,'YYYYDDD') > sysdate - 7"

  2. #2
    nhorton79 is offline Advanced Beginner
    Windows 8 Access 2007
    Join Date
    Apr 2015
    Posts
    34
    Maybe try throwing a MsgBox in at the end of your strSQL:

    MsgBox "[strSQL]",vbOKOnly

    This will produce a msgbox displaying your entire SQL string which you can then read and see if it makes sense.

    Give it a go - you might instantly recognise something wrong with your statement.

    Also:
    Don't you need some kind of quotation mark around your aliases?
    E.g. "SELECT walitm AS '2nd Item Number',...etc"

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    or use debug.print strSQL to see the code in the immediate window - then copy /paste to a new query to see what errors are generated

    NH is almost right you need square brackets when you have names with spaces or non alphanumeric characters

    SELECT walitm AS [2nd Item Number...................wamcu as [Branch / Plant]

    This looks like TSQL - I presume this is for running in SQL Server? if Access then access does not support the case statement - use the switch function instead.

    and shouldn't this be a join?

    strSQL = strSQL & " From proddta.f4801, proddta.f4102, proddta.f3112 WHERE walitm = Iblitm"
    strSQL = strSQL & " And WLDOCO = WADOCO"

    you have three tables but only two potential joins which implies this is a Cartesian query - is this what is intended?

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    ezeanya,

    What are you trying to do in plain English?
    This looks quite Oracle-like to_date(watrdj + 1900000,'YYYYDDD') definitely not Access. Case when wlqmto>0 is not available/supported in Access

    How about providing readers with some context?
    What dbms is this sql for?

  5. #5
    ezeanya is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2016
    Posts
    9
    I'm trying to automate my process. I usually run this on my Oracle but I want to be able to run it directly into Access. that is why I was using the "strSQL"

    pretty much to open a recordset from my database and display in a table in access

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I really do not understand your terminology - what does 'I want to be able to run it directly into Access' and 'display in a table in access' mean? do you mean import to a table access?

  7. #7
    ezeanya is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2016
    Posts
    9
    Yes. the ability to open a recordset and import into a table access. I tried eliminating the space i have btw fields (eg Order No to Order_No) but still getting the same error

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    so have you considered the other comments in posts 3 and 4? Because your sql is not the sql used in Access

    and also clarify

    is this sql being run in Oracle or Access?
    do you have an odbc link to the tables in Oracle?
    and if so is that what your sql is referencing?
    or what?

    And why populate a recordset to then presumably insert into a table when you can have the sql to the whole thing in one go?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 14
    Last Post: 04-22-2015, 06:57 PM
  2. keyword search
    By Mbakker71 in forum Access
    Replies: 5
    Last Post: 02-05-2014, 06:03 AM
  3. Replies: 24
    Last Post: 06-14-2013, 03:13 PM
  4. Invalid use of Me keyword
    By tariq1 in forum Programming
    Replies: 3
    Last Post: 08-15-2012, 10:58 AM
  5. SQL Parameters keyword?
    By Buakaw in forum Queries
    Replies: 1
    Last Post: 08-04-2011, 06:53 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums