Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2002
    Posts
    61

    Unanswered: WHats wrong with this SQL Code

    I have a procedure that creates a Record Set of data from a table. I am attempting to set one of the criteria by using a text box on a form but when I run the following code I get a data mismatch error:

    ssql = "SELECT * FROM CNumbers WHERE [Type] = 'C' and [Yr] =" & Me!YearDate

    CNumbers is a query that I created, Me!YearDate is a text box on the form that I use to run the proc

    If I run the code without the text box:

    ssql = "SELECT * FROM CNumbers WHERE [Type] = 'C' and [Yr] ='03';"

    It runs fine. I want to be able to change the criteria without having to adjust the code each time.

    Thanks

  2. #2
    Join Date
    May 2003
    Posts
    144
    '03' is a string, so your YearDate is a string. You might want to try to add quatation on the YearDate, just like what you have on 'C'.

    SELECT * FROM CNumbers WHERE [Type] = 'C' and [Yr] =" & Me!YearDate

  3. #3
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820

    Re: WHats wrong with this SQL Code

    Originally posted by Chumpie999typla
    I have a procedure that creates a Record Set of data from a table. I am attempting to set one of the criteria by using a text box on a form but when I run the following code I get a data mismatch error:

    ssql = "SELECT * FROM CNumbers WHERE [Type] = 'C' and [Yr] =" & Me!YearDate

    CNumbers is a query that I created, Me!YearDate is a text box on the form that I use to run the proc

    If I run the code without the text box:

    ssql = "SELECT * FROM CNumbers WHERE [Type] = 'C' and [Yr] ='03';"

    It runs fine. I want to be able to change the criteria without having to adjust the code each time.

    Thanks
    put the txtbox you want to use in single quotes as well as
    ie [Type] like 'C' and [Yr] like '" & Me!YearDate & "';"

    also you might try changing = into like as = is meant for numeric comparision and can give very odd results on a string
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  4. #4
    Join Date
    Oct 2002
    Posts
    61

    Talking Re: WHats wrong with this SQL Code

    Thank you for your suggestions, this code works great:

    ssql = "SELECT * FROM CNumbers WHERE [Type] Like '" & B & "' and [Yr] Like '" & Me!YearDate & "';"

    I never thought to include the text box in quotations.


    Thank you

Posting Permissions

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