Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

SQL string

jlawren7jlawren7 Member Posts: 19
Much thanks to GideonOmega for his help. for sure i would be bald by now if he didn't help me with my project.

next quest.... based on the values inputed into the labels on my form, i want to use a between instead of = . how in the world do you give a range in a SQL statement when you don't know the value that is going to be used for the search.

i'll post a statement soon to let you know how



Comments

  • GideonOmegaGideonOmega Member Posts: 617
    : Much thanks to GideonOmega for his help. for sure i would be bald by now if he didn't help me with my project.
    :
    : next quest.... based on the values inputed into the labels on my form, i want to use a between instead of = . how in the world do you give a range in a SQL statement when you don't know the value that is going to be used for the search.
    :
    : i'll post a statement soon to let you know how
    :
    :
    :
    :
    you could try LIKE [0-9][0-9]

    but I have no idea if you can even use ranges for numbers in access, I know for sure that LIKE b[a-z]ll can find any values that start with b have any character in the alphabet, and end with ll, but I'm not sure if you can check ranges with them or not.
    [blue]
    C:Dos
    C:Dos Run
    Run Dos Run
    [/blue]

  • jlawren7jlawren7 Member Posts: 19
    [b][red]This message was edited by jlawren7 at 2004-4-11 6:18:52[/red][/b][hr]
    : : Much thanks to GideonOmega for his help. for sure i would be bald by now if he didn't help me with my project.
    : :
    : : next quest.... based on the values inputed into the labels on my form, i want to use a between instead of = . how in the world do you give a range in a SQL statement when you don't know the value that is going to be used for the search.
    : :
    : : i'll post a statement soon to let you know how
    : :
    : :
    : :
    : :
    : you could try LIKE [0-9][0-9]
    :
    : but I have no idea if you can even use ranges for numbers in access, I know for sure that LIKE b[a-z]ll can find any values that start with b have any character in the alphabet, and end with ll, but I'm not sure if you can check ranges with them or not.
    : [blue]
    : C:Dos
    : C:Dos Run
    : Run Dos Run
    : [/blue]
    :
    : >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

    here is the search string i came up with. it takes the values the user puts into the text boxes and thats their search criteria. right? well yes it is, but what if there is no match? well i used a between statement. created 6 more varibles and took the value that the user inputs, multiply it by .9 and by 1.1 there is the range to search against cool huh! well here is the code to make this work...if you actually want to see the range it uses to search for, misspell the word "BETWEEN" in the SQL string and run the program. a message will inform you of a syntax error'missing operator'and post the range for each search criteria.


    Private Sub cmdSearch_Click()

    Dim intAlt As Integer, intAltA As Integer, intAltB As Integer
    Dim dblHumid As Double, dblhumidA As Double, dblhumidB As Double
    Dim intTemp As Integer, intTempA As Integer, intTempB As Integer
    Dim intWindS As Integer
    Dim strWindD As String


    intAlt = Val(lblAlt.Caption)
    dblHumid = Val(lblHumid.Caption)
    intTemp = Val(lblTemp.Caption)
    intWindS = Val(lblWindS.Caption)
    strWindD = lblWindD.Caption

    ' creating the range to search for
    ' Range for Altitude
    intAltA = Val(intAlt * 0.9)
    intAltB = Val(intAlt * 1.1)
    ' Range For Humidity
    dblhumidA = Val(dblHumid * 0.9)
    dblhumidB = Val(dblHumid * 1.1)
    'Range for Temperature
    intTempA = Val(intTemp * 0.9)
    intTempB = Val(intTemp * 1.1)


    'the search criteria has to be reduced due to the fact that
    'i'm only allowed to use 2 ANDs in my select statement

    AdoRunsRetrieved.RecordSource = "select * from RaceTrack where [Relative_Humidity] BETWEEN " & dblhumidA & " AND " & dblhumidB & " AND [Altitude] BETWEEN " & intAltA & " AND " & intAltB & " AND [Air_Temp] BETWEEN " & intTempA & " AND " & intTempB


    AdoRunsRetrieved.Refresh
    so the next time you come across a problem where you need to search for a range of numbers, heres the formula.
    good luck all



  • GideonOmegaGideonOmega Member Posts: 617
    : [b][red]This message was edited by jlawren7 at 2004-4-11 6:18:52[/red][/b][hr]
    : : : Much thanks to GideonOmega for his help. for sure i would be bald by now if he didn't help me with my project.
    : : :
    : : : next quest.... based on the values inputed into the labels on my form, i want to use a between instead of = . how in the world do you give a range in a SQL statement when you don't know the value that is going to be used for the search.
    : : :
    : : : i'll post a statement soon to let you know how
    : : :
    : : :
    : : :
    : : :
    : : you could try LIKE [0-9][0-9]
    : :
    : : but I have no idea if you can even use ranges for numbers in access, I know for sure that LIKE b[a-z]ll can find any values that start with b have any character in the alphabet, and end with ll, but I'm not sure if you can check ranges with them or not.
    : : [blue]
    : : C:Dos
    : : C:Dos Run
    : : Run Dos Run
    : : [/blue]
    : :
    : : >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
    :
    : here is the search string i came up with. it takes the values the user puts into the text boxes and thats their search criteria. right? well yes it is, but what if there is no match? well i used a between statement. created 6 more varibles and took the value that the user inputs, multiply it by .9 and by 1.1 there is the range to search against cool huh! well here is the code to make this work...if you actually want to see the range it uses to search for, misspell the word "BETWEEN" in the SQL string and run the program. a message will inform you of a syntax error'missing operator'and post the range for each search criteria.
    :
    :
    : Private Sub cmdSearch_Click()
    :
    : Dim intAlt As Integer, intAltA As Integer, intAltB As Integer
    : Dim dblHumid As Double, dblhumidA As Double, dblhumidB As Double
    : Dim intTemp As Integer, intTempA As Integer, intTempB As Integer
    : Dim intWindS As Integer
    : Dim strWindD As String
    :
    :
    : intAlt = Val(lblAlt.Caption)
    : dblHumid = Val(lblHumid.Caption)
    : intTemp = Val(lblTemp.Caption)
    : intWindS = Val(lblWindS.Caption)
    : strWindD = lblWindD.Caption
    :
    : ' creating the range to search for
    : ' Range for Altitude
    : intAltA = Val(intAlt * 0.9)
    : intAltB = Val(intAlt * 1.1)
    : ' Range For Humidity
    : dblhumidA = Val(dblHumid * 0.9)
    : dblhumidB = Val(dblHumid * 1.1)
    : 'Range for Temperature
    : intTempA = Val(intTemp * 0.9)
    : intTempB = Val(intTemp * 1.1)
    :
    :
    : 'the search criteria has to be reduced due to the fact that
    : 'i'm only allowed to use 2 ANDs in my select statement
    :
    : AdoRunsRetrieved.RecordSource = "select * from RaceTrack where [Relative_Humidity] BETWEEN " & dblhumidA & " AND " & dblhumidB & " AND [Altitude] BETWEEN " & intAltA & " AND " & intAltB & " AND [Air_Temp] BETWEEN " & intTempA & " AND " & intTempB
    :
    :
    : AdoRunsRetrieved.Refresh
    : so the next time you come across a problem where you need to search for a range of numbers, heres the formula.
    : good luck all
    :
    :
    :
    :


    Nice one, I havent actually used the between statement before in sql, I will have to remember that one.
    [blue]
    C:Dos
    C:Dos Run
    Run Dos Run
    [/blue]

Sign In or Register to comment.