Results 1 to 5 of 5

Thread: SQL Error

  1. #1
    Join Date
    Mar 2009
    Posts
    37

    Unanswered: SQL Error

    this is driving me bonkers (don't think you are allowed to swear on this site so that's why the restrain....)

    what is wrong with this code:

    docmd****nSQL("INSERT INTO [(SE)_tbl_HR_Data_Master] ( [Division Description], [Extended Short Code], [SubDivision 1], [SubDivision 2], [Work Location - HR System], [Work Location - Short Code], [Work Location - Location Code], [Group Employee Snapshot FTE (SUM)], [Group Employee Snapshot FTE (COUNT)], [Active FTE], [Active Headcount], [Work Location - Address 1], [Work Location - Address 2], [Work Location - Address 3], [Work Location - Address 4], [Work Location - Post Town], [Work Location - Post Code], [Work Location - Depot], [Work Location - Country], [Work Location - Sub Country], [Work Location - Region] )
    SELECT [(SE)_tbl_HR_Data_Raw].[Division Description], IIf(IsNull([Work Location - Address 1]),"0" & "_" & [Work Location - Short Code],[Work Location - Short Code] & "_" & [Work Location - Address 1]) AS identifier, [(SE)_tbl_HR_Data_Raw].[SubDivision 1], [(SE)_tbl_HR_Data_Raw].[SubDivision 2], [(SE)_tbl_HR_Data_Raw].[Work Location - HR System], [(SE)_tbl_HR_Data_Raw].[Work Location - Short Code], [(SE)_tbl_HR_Data_Raw].[Work Location - Location Code], [(SE)_tbl_HR_Data_Raw].[Group Employee Snapshot FTE (SUM)], [(SE)_tbl_HR_Data_Raw].[Group Employee Snapshot FTE (COUNT)], [(SE)_tbl_HR_Data_Raw].[Active FTE], [(SE)_tbl_HR_Data_Raw].[Active Headcount], [(SE)_tbl_HR_Data_Raw].[Work Location - Address 1], [(SE)_tbl_HR_Data_Raw].[Work Location - Address 2], [(SE)_tbl_HR_Data_Raw].[Work Location - Address 3], [(SE)_tbl_HR_Data_Raw].[Work Location - Address 4], [(SE)_tbl_HR_Data_Raw].[Work Location - Post Town], [(SE)_tbl_HR_Data_Raw].[Work Location - Post Code], [(SE)_tbl_HR_Data_Raw].[Work Location - Depot], [(SE
    )_tbl_HR_Data_Raw].[Work Location - Country], [(SE)_tbl_HR_Data_Raw].[Work Location - Sub Country], [(SE)_tbl_HR_Data_Raw].[Work Location - Region]
    FROM [(SE)_tbl_HR_Data_Raw];")

    it is telling me that there is a missing list separator...
    Last edited by Tiggerandpoo; 07-02-09 at 06:57.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    h'mm lets see a missing list separator
    thats going to be a missing comma off hand I bet a penny to a pound of shit that its using the " symbol in the middle of your SQL

    so if you are not using the ' symbol

    Code:
    docmd****nSQL('INSERT INTO [(SE)_tbl_HR_Data_Master] ( [Division Description], [Extended Short Code], [SubDivision 1], [SubDivision 2], [Work Location - HR System], [Work Location - Short Code], [Work Location - Location Code], [Group Employee Snapshot FTE (SUM)], [Group Employee Snapshot FTE (COUNT)], [Active FTE], [Active Headcount], [Work Location - Address 1], [Work Location - Address 2], [Work Location - Address 3], [Work Location - Address 4], [Work Location - Post Town], [Work Location - Post Code], [Work Location - Depot], [Work Location - Country], [Work Location - Sub Country], [Work Location - Region] )
    SELECT [(SE)_tbl_HR_Data_Raw].[Division Description], IIf(IsNull([Work Location - Address 1]),"0" & "_" & [Work Location - Short Code],[Work Location - Short Code] & "_" & [Work Location - Address 1]) AS identifier, [(SE)_tbl_HR_Data_Raw].[SubDivision 1], [(SE)_tbl_HR_Data_Raw].[SubDivision 2], [(SE)_tbl_HR_Data_Raw].[Work Location - HR System], [(SE)_tbl_HR_Data_Raw].[Work Location - Short Code], [(SE)_tbl_HR_Data_Raw].[Work Location - Location Code], [(SE)_tbl_HR_Data_Raw].[Group Employee Snapshot FTE (SUM)], [(SE)_tbl_HR_Data_Raw].[Group Employee Snapshot FTE (COUNT)], [(SE)_tbl_HR_Data_Raw].[Active FTE], [(SE)_tbl_HR_Data_Raw].[Active Headcount], [(SE)_tbl_HR_Data_Raw].[Work Location - Address 1], [(SE)_tbl_HR_Data_Raw].[Work Location - Address 2], [(SE)_tbl_HR_Data_Raw].[Work Location - Address 3], [(SE)_tbl_HR_Data_Raw].[Work Location - Address 4], [(SE)_tbl_HR_Data_Raw].[Work Location - Post Town], [(SE)_tbl_HR_Data_Raw].[Work Location - Post Code], [(SE)_tbl_HR_Data_Raw].[Work Location - Depot], [(SE
    )_tbl_HR_Data_Raw].[Work Location - Country], [(SE)_tbl_HR_Data_Raw].[Work Location - Sub Country], [(SE)_tbl_HR_Data_Raw].[Work Location - Region]
    FROM [(SE)_tbl_HR_Data_Raw];')
    or better yet assing the sql to a variable prior to using it
    eg
    Code:
    strSQl = "INSERT INTO [(SE)_tbl_HR_Data_Master] ( [Division Description], [Extended Short Code], [SubDivision 1], [SubDivision 2], [Work Location - HR System], [Work Location - Short Code], [Work Location - Location Code], [Group Employee Snapshot FTE (SUM)], [Group Employee Snapshot FTE (COUNT)], [Active FTE], [Active Headcount], [Work Location - Address 1], [Work Location - Address 2], [Work Location - Address 3], [Work Location - Address 4], [Work Location - Post Town], [Work Location - Post Code], [Work Location - Depot], [Work Location - Country], [Work Location - Sub Country], [Work Location - Region] )
    SELECT [(SE)_tbl_HR_Data_Raw].[Division Description], IIf(IsNull([Work Location - Address 1]),"0" & "_" & [Work Location - Short Code],[Work Location - Short Code] & "_" & [Work Location - Address 1]) AS identifier, [(SE)_tbl_HR_Data_Raw].[SubDivision 1], [(SE)_tbl_HR_Data_Raw].[SubDivision 2], [(SE)_tbl_HR_Data_Raw].[Work Location - HR System], [(SE)_tbl_HR_Data_Raw].[Work Location - Short Code], [(SE)_tbl_HR_Data_Raw].[Work Location - Location Code], [(SE)_tbl_HR_Data_Raw].[Group Employee Snapshot FTE (SUM)], [(SE)_tbl_HR_Data_Raw].[Group Employee Snapshot FTE (COUNT)], [(SE)_tbl_HR_Data_Raw].[Active FTE], [(SE)_tbl_HR_Data_Raw].[Active Headcount], [(SE)_tbl_HR_Data_Raw].[Work Location - Address 1], [(SE)_tbl_HR_Data_Raw].[Work Location - Address 2], [(SE)_tbl_HR_Data_Raw].[Work Location - Address 3], [(SE)_tbl_HR_Data_Raw].[Work Location - Address 4], [(SE)_tbl_HR_Data_Raw].[Work Location - Post Town], [(SE)_tbl_HR_Data_Raw].[Work Location - Post Code], [(SE)_tbl_HR_Data_Raw].[Work Location - Depot], [(SE
    )_tbl_HR_Data_Raw].[Work Location - Country], [(SE)_tbl_HR_Data_Raw].[Work Location - Sub Country], [(SE)_tbl_HR_Data_Raw].[Work Location - Region]
    FROM [(SE)_tbl_HR_Data_Raw];"
    docmd****nSQL(strSQL)
    ...why
    becuase it measn you cna then debug your SQL and make sure what you are sending to the SQL engine is what you think you are sending

    if you need to use the " symbol as part of your sql then I'd suggest you use chr$(34) instead

    eg
    Code:
    strSQL = "select blah from developers where UserName like (" & chr$34 & "Tigger*" & chr$(34) & ";")
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2009
    Posts
    37
    thanks for the reply - the append now works but it does however not seem to like the chr$(34)!
    this is my code now:

    strSQL = "INSERT INTO [(SE)_tbl_HR_Data_Master] ( [Division Description], [Extended Short Code], [SubDivision 1], [SubDivision 2], [Work Location - HR System], [Work Location - Short Code], [Work Location - Location Code], [Group Employee Snapshot FTE (SUM)], [Group Employee Snapshot FTE (COUNT)], [Active FTE], [Active Headcount], [Work Location - Address 1], [Work Location - Address 2], [Work Location - Address 3], [Work Location - Address 4], [Work Location - Post Town], [Work Location - Post Code], [Work Location - Depot], [Work Location - Country], [Work Location - Sub Country], [Work Location - Region] )" & _
    "SELECT [(SE)_tbl_HR_Data_Raw].[Division Description]," & _
    "IIf(IsNull([Work Location - Address 1]),'0' & 'chr$34' & [Work Location - Short Code],[Work Location - Short Code] & 'chr$34' & [Work Location - Address 1]) AS identifier, [(SE)_tbl_HR_Data_Raw].[SubDivision 1], [(SE)_tbl_HR_Data_Raw].[SubDivision 2], [(SE)_tbl_HR_Data_Raw].[Work Location - HR System], [(SE)_tbl_HR_Data_Raw].[Work Location - Short Code]," & _
    "[(SE)_tbl_HR_Data_Raw].[Work Location - Location Code], [(SE)_tbl_HR_Data_Raw].[Group Employee Snapshot FTE (SUM)], [(SE)_tbl_HR_Data_Raw].[Group Employee Snapshot FTE (COUNT)], [(SE)_tbl_HR_Data_Raw].[Active FTE], [(SE)_tbl_HR_Data_Raw].[Active Headcount], [(SE)_tbl_HR_Data_Raw].[Work Location - Address 1], [(SE)_tbl_HR_Data_Raw].[Work Location - Address 2], [(SE)_tbl_HR_Data_Raw].[Work Location - Address 3], [(SE)_tbl_HR_Data_Raw].[Work Location - Address 4], [(SE)_tbl_HR_Data_Raw].[Work Location - Post Town], [(SE)_tbl_HR_Data_Raw].[Work Location - Post Code], [(SE)_tbl_HR_Data_Raw].[Work Location - Depot]," & _
    "[(SE)_tbl_HR_Data_Raw].[Work Location - Country], [(SE)_tbl_HR_Data_Raw].[Work Location - Sub Country], [(SE)_tbl_HR_Data_Raw].[Work Location - Region]" & _
    "FROM [(SE)_tbl_HR_Data_Raw];"
    DoCmd****nSQL (strSQL)

    Instead of a _ it puts the words chr$34 into the cell now!

  4. #4
    Join Date
    May 2009
    Posts
    258
    I'm pretty sure jaded actually meant for you to put the chr$(34) in place of the double quote characters inside the SQL statement:
    Code:
    "IIf(IsNull([Work Location - Address 1])," & chr$(34) & "0" & chr$(34) & " & " & chr$(34) & "_" & chr$(34) & " & [Work Location - Short Code]...
    But now that you've corrected your double quotes issue, you could just as well do the following:
    Code:
    "IIf(IsNull([Work Location - Address 1]),'0' & '_' & [Work Location - Short Code]...
    Ax

  5. #5
    Join Date
    Mar 2009
    Posts
    37
    thanks for all the help! worked a treat!

    Just one more question ------

    Being completely new to writing sql in Access - is it possible to "nested" queries in sql?

    Here is an example of what I want to do:

    I have a table with customer numbers and the number of items purchased by these customers. There is more than one entry against each customer number.
    I have a second table with my list of customer numbers processed to date.
    I have a third table which lists all the customer numbers which have not yet been processed.
    Every time i get new data in I want to check whether the customer numbers have already been processed and if not I want to make an entry of the numbers and the "total number of purchases" in the third table.

    What I did in access originally is the following:
    I wrote one query which checks whether there is an entry of the customer numbers of the first table in the second table. And sums the number of purchases against each customer number.
    Then I wrote a second query and appended the result of the first query to the third table.

    Now I am trying to put all this into a long bit of VB which I have attached to a button on a form. Effectively it is for our processing staff to run. I know I can just run the queries I have written them but I am wondering whether I can just make do without the queries and just put it all into vb as embedded sql? As far as I can see I can not do this in one step. There is my problem....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •