+ Reply to Thread
Results 1 to 11 of 11

Ampersands without string concatenation

  1. #1
    Registered User
    Join Date
    03-07-2019
    Location
    Sydney
    MS-Off Ver
    Office365
    Posts
    22

    Ampersands without string concatenation

    Quick question. I have the following SQL query I'm feeding into a string variable:

    Please Login or Register  to view this content.
    So I've done this (with Amp being set to Chr(38)

    Please Login or Register  to view this content.
    But every time I run the sub, I get:

    Syntax error (missing operator) in query expression 'WHERE StatusID < 255 AND maskDebtorFlags & 256 != 0'

    It's parsing it correctly clearly, as the ampersand is being substituted correctly, and I know the query works, because in SMSS it runs flawlessly. Does anyone have any idea why this would be doing this?

  2. #2
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,329

    Re: Ampersands without string concatenation

    why not easy?
    Please Login or Register  to view this content.
    Is the query exression is valid ? Probably some brackets must be here

    Please Login or Register  to view this content.
    Last edited by BMV; 04-02-2020 at 01:14 AM.

  3. #3
    Registered User
    Join Date
    03-07-2019
    Location
    Sydney
    MS-Off Ver
    Office365
    Posts
    22

    Re: Ampersands without string concatenation

    Quote Originally Posted by BMV View Post
    why not easy?
    Please Login or Register  to view this content.
    Because of the way VBA acts when it sees an ampersand, it strips it from the string, resulting in 'WHERE StatusID < 255 AND maskDebtorFlags 256 != 0'

  4. #4
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,329

    Re: Ampersands without string concatenation


  5. #5
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: Ampersands without string concatenation

    I don't do SQL so this is just a wild guess (I am basing it on something I thought I saw once). With that said, does this work...
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    03-07-2019
    Location
    Sydney
    MS-Off Ver
    Office365
    Posts
    22

    Re: Ampersands without string concatenation

    If I hit Debug when it pauses, it highlights where the string is sent to the connection

    Database.Open Qry, cnn, adOpenStatic, adLockReadOnly

    Where cnn is an ADODB.Connection and Database is an ADODB.Recordset

  7. #7
    Registered User
    Join Date
    03-07-2019
    Location
    Sydney
    MS-Off Ver
    Office365
    Posts
    22

    Re: Ampersands without string concatenation

    Now it's making a liar out of me. && puts && in the string. & alone now works.

    However, it still throws an error saying there is a missing operator when there isn't. The string is complete.

    I'm perplexed.

  8. #8
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,329

    Re: Ampersands without string concatenation

    Could you show your code . What SQL server you use? Bitwise AND synaxis should be checked.

  9. #9
    Registered User
    Join Date
    03-07-2019
    Location
    Sydney
    MS-Off Ver
    Office365
    Posts
    22

    Re: Ampersands without string concatenation

    So the query works fine again in SQL. I actually used it to manually populate the spreadsheet so I could send the report. I want to automate it so I don't have to run it all the time. If the user has access to the report, they can click a button and the VBA will do the rest.
    Attached Files Attached Files

  10. #10
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,329

    Re: Ampersands without string concatenation

    The Microsoft Jet database engine does not support bitwise operations in SQL. This behavior is by design.

  11. #11
    Registered User
    Join Date
    03-07-2019
    Location
    Sydney
    MS-Off Ver
    Office365
    Posts
    22

    Re: Ampersands without string concatenation

    Quote Originally Posted by BMV View Post
    The Microsoft Jet database engine does not support bitwise operations in SQL. This behavior is by design.
    OMG, you're kidding.

    Hopeless.

    Thank you for your help though.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Conditional String Concatenation
    By Solentos in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-20-2019, 12:33 AM
  2. Help with string concatenation logic
    By Nahkranoth in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-16-2013, 06:49 AM
  3. String concatenation of values
    By Chuvawow.com in forum Excel General
    Replies: 9
    Last Post: 03-08-2010, 07:29 AM
  4. string concatenation
    By jaredhawco in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-02-2008, 05:02 PM
  5. string concatenation
    By deepak.kec in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 10-21-2008, 04:34 AM
  6. string concatenation
    By fanfan in forum Excel General
    Replies: 2
    Last Post: 09-14-2007, 03:09 PM
  7. String concatenation
    By shishi in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-08-2005, 10:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1