Results 1 to 13 of 13
  1. #1
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185

    Adding Non Reserved Fields

    Hi Guy's, I have a problem with an SQL Statement, I do have field named from a long time ago when adding these fields called Year, Quarter, Month, Week

    Since the initial design, these fields haven't really been in use but now are looking to use these fields, it would cause too many changes to rename the fields

    So I believe now when trying to use these fields that they are reserved words/functions

    Without changing these fields, how and where do I create my own variable within the SQL Statement as an alternative ?

    Just to eliminate the keywords

    What is the best way around this SQL ?



    Code:
    strSrc = Forms!frmStats!cboSourcestrCust = Forms!frmStats!cboCustomer
    strItemType = Forms!frmStats!cboItem
        
    
    
        strSQL = "SELECT qryDelQty.Customer, qryDelQty.Year, qryDelQty.Month, qryDelQty.ItemType, Count(qryDelQty.ItemType) as TotalItems, " _
                        & "FROM qryDelQty WHERE Customer = '" & strCust & "' " _
                        & "AND Source = '" & strSrc & "' " _
                        & "AND ItemType = '" & strItemType & "' ORDER BY Year, Month DESC"

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    Surround them with [ ] ?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Hokie Dokie WGM, that creates it as field name rather than a function ?

  4. #4
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Still receiving message the SELECT Statements has reserve words

    Changed to this code, I can easily generate queries and set that to SQL, I am wanting to add more options such as sorting and different fields to calculate so want to try and stick to SQL statement's so i don't a new query for every calculation option

    Once i can get this one working, i can add varOptions before it then change each SQL to coincide with varOption

    Adding my own variable / field into an SQL i haven't really attempted before

    Code:
        strSQL = "SELECT qryDelQty.Customer, qryDelQty.[Year], qryDelQty.[Month], qryDelQty.[ItemType], Count(qryDelQty.ItemType) as [TotalItems], " _
                        & "FROM qryDelQty WHERE Customer = Like ""*" & strCust & "*""" _
                        & "AND Source = '" & strSrc & "' " _
                        & "AND ItemType = '" & strItemType & "' ORDER BY qryDelQty.[Year], qryDelQty.[Month] DESC"

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    You cannot have = and Like ? Stop changing the SQL until you get it working, else you are going to be all over the place. Small steps, small steps.
    Choose one or the other.
    I'd probably surround the query name with [] just in case, but try without.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You also need to remove the comma after TotalItems and add a space at the end of line 2. In my opinion its better to put the spaces at the start of a line as its easier to see them.
    When you have syntax issues, the First step should be to do a Debug. Print and examine the SQL
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    ... and make sure the problem isn't in the query you're calling with that sql.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    aside from the other comments your sql shows a count so it is an aggregate query, but you are not grouping on anything.

  9. #9
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Without changing these fields, how and where do I create my own variable within the SQL Statement as an alternative ?
    Personally I wouldn't compound the problem. It already caught you once. I'd spend some time searching and replacing with proper field names.
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Sometimes you just can't fix what you're handed. Sometimes it's not worth the time and effort to fix a design mistake if it's not causing problems. If either of these constraints are at play and these field names raise errors, I agree, just wrap in brackets and the name will be treated as a field, not a function or method. One has to get the sql syntax correct first before blaming any particular name as being the issue. For that, the query builder is likely the way to start but the need to correctly form a sql statement in code cannot be over emphasized either.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Thank s guy's I think i will rename query fields so that the tables are not disturbed which is room for many errors to pop up, may just call the fields that can be taken as functions with a 'q' like (qYear: Year), qMonth:Month etc, this should help i guess by being unique

    copy the SQL query to a procedure and make some adaptions accordingly

  12. #12
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Hi Guy's, got this working great now, only one more question is, where I have Like or other criteria's in the statement, how do i change that to a string rather rather than double quotes on a specific customer ?

    ie: where i have highlighted red, can this be changed to a string for user input ?

    Also once where it says Dave, i may well be changing the other criteria's to a 'Like' rather than a 'specific'


    Code:
    strSQL = "SELECT tblAssign.Customer, tblAssign.[Year], tblAssign.[Month], tblAssign.Source, tblAssign.ItemType, Count(itemType) AS Totalitems" _    & " From tblAssign" _
        & " GROUP BY tblAssign.Customer, tblAssign.Year, tblAssign.Month, tblAssign.Source, tblAssign.ItemType" _
        & " HAVING (((tblAssign.Customer) Like ""*"" & ""Dave"" & ""*"") And ((tblAssign.Source) = ""WGM"") And ((tblAssign.ItemType) = ""June7""))" _
        & " ORDER BY tblAssign.[Year] DESC , tblAssign.[Month] DESC;"

  13. #13
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    You concatenate the variables with the sql statement.
    I am sure you have asked this and done this in the past?

    Does that code even work? Paste the result from a debug.print into a Query SQL window and run it.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

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

Similar Threads

  1. Sum of reserved material - day-by-day
    By TheMaramatanga in forum Queries
    Replies: 9
    Last Post: 05-15-2021, 02:27 AM
  2. String to Reserved
    By monvani in forum Programming
    Replies: 9
    Last Post: 08-21-2015, 01:12 PM
  3. Replies: 1
    Last Post: 08-06-2013, 07:52 PM
  4. Reserved Error (|)
    By RGRAVLIN_BOS in forum Access
    Replies: 4
    Last Post: 06-14-2012, 07:09 AM
  5. Reserved word
    By squirrly in forum Access
    Replies: 6
    Last Post: 09-20-2011, 02:43 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