Results 1 to 7 of 7
  1. #1
    oksmat is offline Novice
    Windows 10 Access 2007
    Join Date
    Nov 2020
    Posts
    1

    SQL correctness problem

    Hello Team, am having a challenge with my SQL statement. I will appreciate any help towards correcting it

    SQL = "SELECT * FROM Room"
    WHERE
    Room.FirstName Like '*" & [Forms]![RoomCustomerSearch]![txtFirstname] & "*'"_ & "


    AND Room.LastName Like '*" & [Forms]![RoomCustomersearch]![txtLastname] & "*'
    AND Room.PhoneNo"_ & " Like '*" & [Forms]![RoomCustomersearch]![txtPhoneNo] & "*'"

  2. #2
    Welshgasman is online now Expert
    Windows 10 Access 2007
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    817
    Do yourself a favour.
    Build each part on a new line by adding each element

    strSQL = "jjdjdjjdjdjdjdjd"
    strSQL = strSQL & " AND ......"
    strSQL = strSQL & " AND ......"

    then
    Debug.Print strSQL to see if you have the syntax correct.

  3. #3
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,814
    Your string concatenation is all over the place, try


    Code:
    strSQL = "SELECT * FROM Room "
    strSQL = strSQL & "Where Room.FirstName Like '*" & [Forms]![RoomCustomerSearch]![txtFirstname] & "*' "
    strSQL = strSQL & "AND Room.LastName Like '*" & [Forms]![RoomCustomerSearch]![txtLastName] & "*' "
    strSQL = strSQL & "AND Room.PhoneNo Like '*" & [Forms]![RoomCustomersearch]![txtPhoneNo] & "*'"
    
    Debug,Print strSQL
    Note the spaces at the end of all the strings
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  4. #4
    Welshgasman is online now Expert
    Windows 10 Access 2007
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    817
    @Minty,
    I like to put mine at the start of a new line, easier to see for me?

  5. #5
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,814
    Quote Originally Posted by Welshgasman View Post
    @Minty,
    I like to put mine at the start of a new line, easier to see for me?
    I actually agree with you - it's just a (bad) habit of mine...
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  6. #6
    Welshgasman is online now Expert
    Windows 10 Access 2007
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    817
    Quote Originally Posted by Minty View Post
    I actually agree with you - it's just a (bad) habit of mine...
    Having it both ways cannot hurt anyway?

  7. #7
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    9,023
    is the problem with the fact you have vba syntax errors (such as WHERE is not in your sql string) or this has been inaccurately freetyped but in your app it 'works' but does not return the expected results.

    Also, be aware that SQL is a reserved word and should not be used for a variable name (or field name for that matter)

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

Similar Threads

  1. Replies: 9
    Last Post: 07-06-2015, 01:47 AM
  2. Replies: 2
    Last Post: 10-31-2012, 11:52 AM
  3. Replies: 2
    Last Post: 06-14-2010, 03:25 PM
  4. query problem i have a problem wi
    By maxx3 in forum Queries
    Replies: 0
    Last Post: 06-29-2009, 02:29 AM

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 - Senior Forums