Results 1 to 6 of 6
  1. #1
    megatronixs is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    91

    pass criteria using LIKE and % in VBA query

    Hi all,



    I have a connection to Teradata with VBA. First part of the query is to get all records that starts with the first letter of a customer name:
    Code:
    WHERE FRNAME like 'M%'
    this works great when hard coded.
    I have a text field where I fill in the first letter, and there is a variable called "FirstNameLetter" I tried things like:
    Code:
    Where ((FORENAME)= '" & FirstNameLetter & " %')"
    and I only get errors.

    Any clue where I go wrong? I will also need for the address field that has:
    Code:
    AND (ADDR_LINE_2 like '%WEXFORD%' OR ADDR_LINE_3 like '%WEXFORD%' OR ADDR_LINE_4 like '%WEXFORD%')
    Greetings.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I use star, *.
    It works just fine.
    Where ((FORENAME)= '" & FirstNameLetter & "*')"

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    after 88 posts, you should know by now to provide sufficient information so responders do not have to guess (are you building this directly as a query or using vba to build the query?). Looks like you are mixing up building a string in vba and using the query builder.

    Assuming you are using the latter then enter the following in your forename criteria
    Code:
    Like [FirstNameLetter] & "%"
    otherwise If the former then

    Code:
    strSQL=strSQL & "Where FORENAME Like '" & FirstNameLetter & "%'"
    Last edited by CJ_London; 05-09-2016 at 09:30 AM. Reason: missed out the likes

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I use star, *.
    He hasn't said, but I'm assuming the OP is using ANSI 92 standard since the first criteria worked OK

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    It seems to me, based on
    a connection to Teradata with VBA
    the OP is trying to set up a pass through query to Teradata.
    However, since I don't know Teradata, I'm asking the OP to tell us more.
    Do you use a Teradata database? Are you trying to create a Pass-through query that will be executed directly on the Teradata hardware?

    From wikipedia:
    Teradata sells applications and software to process different types of data. In 2010, Teradata added text analytics to track unstructured data, such as word processor documents, and semi-structured data, such as spreadsheets.[8]

  6. #6
    megatronixs is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    91
    Hi all,

    My IP address got blocked and I could not anwer earlier as I needed to connect from different pc.

    I managed to solve it. The code from Ajax did the trick. Silly me I forgot about it that I was passing text and not numbers.

    @Orange, no, I don't use that to connect to Teradata, there is an Excel version included in the tools that has the Teradata drivers and I can use VBA to do the automation etc.

    Greetings and thank you all.

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

Similar Threads

  1. Replies: 10
    Last Post: 11-24-2015, 03:30 PM
  2. Replies: 7
    Last Post: 04-18-2015, 08:23 AM
  3. Replies: 7
    Last Post: 03-11-2015, 12:48 PM
  4. Replies: 14
    Last Post: 02-19-2013, 03:16 PM
  5. Replies: 4
    Last Post: 02-14-2013, 09:33 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