Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Lexington Park, Maryland, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Getting variables inserted into SQL statement (Access 2000)

    I need the following code to work making this [" & str_1_Value_Low & "] be treated as the variable str_1_Value_Low. Any help will be appreciated.



    Public str_1_Field As String
    Public str_1_Value_Low As Long

    str_1_Value_Up = Forms!Search!txt_Parameter_1_2.Value

    strSQL = "SELECT * From Search_Query where [TOGW] >= [" & str_1_Value_Low & "]"

    With Forms!Search_Results()
    .RecordSource = strSQL
    End With

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Getting variables inserted into SQL statement (Access 2000)

    Does the variable str_1_Value_Low contain a field name? In that case, the code you posted should do what you want.

    Does it contain a field value? If so, you shouldn't have square brackets around it.
    If TOGW is a number or currency field:
    strSQL = "SELECT * From Search_Query where [TOGW] >= " & str_1_Value_Low
    If TOGW is a text field:
    strSQL = "SELECT * From Search_Query where [TOGW] >= " & Chr(34) & str_1_Value_Low & Chr(34)
    If TOGW is a date/time field:
    strSQL = "SELECT * From Search_Query where [TOGW] >= #" & str_1_Value_Low & "#"

  3. #3
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Lexington Park, Maryland, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting variables inserted into SQL statement (Access 2000)

    What does the & Chr(34) portion of the SQL statement for text do?

    Mike

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Getting variables inserted into SQL statement (Access 2000)

    It's equivalent to a double quote ". String (text) values must be enclosed in quotes, but if you want to include a string value within another string, you have a problem. The following will NOT work correctly:
    <code>
    strWhere = "LastName="Johnson""
    </code>
    VBA will interpret the " after the = as the end of the string, and then choke on Johnson as an anknown variable.
    There are several workarounds, one of them is to concatenate with Chr(34):
    <code>
    strWhere = "LastName=" & Chr(34) & "Johnson" & Chr(34)
    </code>
    See <post#=230516>post 230516</post#> for more details.

  5. #5
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Lexington Park, Maryland, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting variables inserted into SQL statement (Access 2000)

    When I run the following code I get this error:

    Run-Time Error '2001':
    You canceled the previous operation.



    Public str_1_Field As String
    Public str_1_Value_Low As Long

    str_1_Value_Up = Forms!Search!txt_Parameter_1_2.Value

    strSQL = "SELECT * From Search_Query where [TOGW] >= " & Chr(34) & str_1_Value_Low & Chr(34)

    With Forms!Search_Results()
    .RecordSource = strSQL
    End With

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Getting variables inserted into SQL statement (Access 2000)

    Could you post a stripped down and zipped copy of the database? See <post#=401925>post 401925</post#> for instructions.

  7. #7
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Lexington Park, Maryland, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting variables inserted into SQL statement (Access 2000)

    Here it is.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Getting variables inserted into SQL statement (Access 2000)

    I can't help. The query is so complicated that I can't open it in design view or datasheet view, so I cannot do anything with it. Also see <post#=472330>post 472330</post#>

  9. #9
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Lexington Park, Maryland, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting variables inserted into SQL statement (Access 2000)

    Hans,

    I realize this is a very complicated query I am building. I realize 99% of the time this is unnecesary. However, this instance is the rare 1%. The entire purpose of this Database is this complicated search. That function is a large part of the job performed in my office and it has been done by hand for over 70 years.

    To manage the complicated manner of this search, I have decided to do the querying in stages. The first stage uses the top drop down menus from the Search Form only. That query is called "Search_Query". My current problem does not involve this query. However, I did simplify it so that you could view it.

    My current problem involves code written in the Search_Data module. This code is SQL used as the second stage of the query. The code has been easily marked by comments so you can find it. The line is as follows:

    strSQL = "SELECT * From Search_Query where [TOGW] >= " & Chr(34) & str_1_Value_Low & Chr(34) & ""

    What I am ultimately attemting to do is use the lower drop down menus and text boxes to allow the user to select from any of the fields to narrow the query. I also need the user to be able to set a lower and/or upper bounds for the field to be queried by. The above line of code is only a very small part of the second stage. The code will be expanded to the full functionality required. What I need in this line of code was expressed in my pressious postings.

    I hope this will help.
    If you need any more information, let me know.

    Mike

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Getting variables inserted into SQL statement (Access 2000)

    TOGW is a number field. As indicated in <post#=476464>post 476464</post#> and <post#=476473>post 476473</post#>, you only need to use Chr(34) for text fields. Try

    strSQL = "SELECT * From Search_Query where [TOGW] >= " & str_1_Value_Low

    BTW, your code refers to a non-existing text box Forms!Search!Text242, but that might be due to your having stripped down the database for posting.

  11. #11
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Lexington Park, Maryland, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting variables inserted into SQL statement (Access 2000)

    Sorry, I forgot to change the code to reflect my real problem. I need something like the following. I need the criteria of the selected field to include all values in the field including null values when the upper and lower bound text boxes have the default 0 and 999999999 values. If the user has changed the upper and lower bound text box values then the criteria must search based on the user defined upper and lower bound values, excluding null values. The following code is my first try at this. You may need to look at the code.

    If str_1_Value_Low = 0 And str_1_Value_Low = 999999999 Then
    str_1_Value_Low = "> 0 or Is Null"
    End If


    strSQL = "SELECT * From Search_Query where [TOGW] >= " & Chr(34) & str_1_Value_Low & Chr(34) & ""

    With Forms!Search_Results()
    .RecordSource = strSQL
    End With

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Getting variables inserted into SQL statement (Access 2000)

    This doesn't make sense:
    If str_1_Value_Low = 0 And str_1_Value_Low = 999999999 Then
    str_1_Value_Low cannot be 0 and 999999999 at the same time. Do you mean Or instead of And? Or did you mean
    If str_1_Value_Low = 0 And str_1_Value_up = 999999999 Then

    If you look at the resulting SQL, you will see that it doesn't make sense. Try

    If str_1_Value_Low = 0 And str_1_Value_Up = 999999999 Then ' correct as needed!
    strSQL = "SELECT * From Search_Query where [TOGW] > 0 Or [TOGW] Is Null"
    Else
    strSQL = "SELECT * From Search_Query where [TOGW] >= " & str_1_Value_Low
    End If

  13. #13
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Lexington Park, Maryland, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting variables inserted into SQL statement (Access 2000)

    We are sooo close. I appreciate all the help. This problem will make me never open my big mouth again when someone asks if anybody knows a piece of software or language.

    How do I get the following to work. I guess what I need to do is figure out how to get the strSQL query to run in the module. Is that correct? If so, how do I do that?

    If str_1_Value_Low = 0 And str_1_Value_Up = 999999999 Then
    strSQL_1 = "SELECT * From Search_Query where [TOGW] > 0 Or [TOGW] Is Null"
    Else
    strSQL_1 = "SELECT * From Search_Query where [TOGW] >= " & str_1_Value_Low & ""
    End If

    If str_2_Value_Low = 0 And str_2_Value_Up = 999999999 Then
    strSQL = "SELECT * From strSQL_1 where [Empty_Weight] > 0 Or [TOGW] Is Null"
    Else
    strSQL = "SELECT * From strSQL_1 where [Empty_Weight] >= " & str_1_Value_Low & ""
    End If


    Mike

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Getting variables inserted into SQL statement (Access 2000)

    No, what you'd do is concatenate the various WHERE parts into one long string.

    Dim strWhere As String

    If str_1_Value_Low = 0 And str_1_Value_Up = 999999999 Then
    strWhere = "([TOGW] > 0 Or [TOGW] Is Null)"
    Else
    strWhere = "([TOGW] >= " & str_1_Value_Low & ")"
    End If

    If str_2_Value_Low = 0 And str_2_Value_Up = 999999999 Then
    strWhere = strWhere & " AND ([Empty_Weight] > 0 Or [TOGW] Is Null)"
    Else
    strWhere = strWhere & "AND ([Empty_Weight] >= " & str_1_Value_Low & ")"
    End If

    ...

    strSQL = "SELECT * FROM Search_Query WHERE " & strWhere

    Note the use of parentheses ( ). Without them, AND has precedence above OR.

  15. #15
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Lexington Park, Maryland, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting variables inserted into SQL statement (Access 2000)

    Thank you sooo much. It is completely working now. I have attached the stripped down working version.

    Let me know what you think and if you find any bugs.

    Once again, Thank You.

    Mike

Page 1 of 2 12 LastLast

Posting Permissions

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