Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Feb 2007
    Posts
    13

    Unanswered: Passing SQL to a query/report issue

    OK, I am trying to build sql strings from a form buy the user selecting criteria from boxes on a form.

    I was given some code which I have adapted very slighty

    Code:
    Dim strCrit As String
      
      If IsNull(Me.server) Then
         ' proceed
      Else
         strCrit = strCrit & " And [Server type] = '" & Me.server & "'"
      End If
      If IsNull(Me.RAM) Then
         ' proceed
      Else
         strCrit = strCrit & " And [Ram] = " & Me.RAM
      End If
    
    
                DoCmd.OpenReport "Your Results", , acPreview, strCrit
    
    End Sub
    however when I run this I get the following error

    http://i145.photobucket.com/albums/r...rrorsyntax.jpg

    or trying to edit the code this error

    http://i145.photobucket.com/albums/r...eith/error.jpg

    how do I pass the built SQL string directly into the query defs?

    the strcrit when displayed looks like this
    http://i145.photobucket.com/albums/r...h/stricrit.jpg

    Thanks

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Before appending anything to strCrit, you need to test whether or not it already contains a value. If it's empty ("") to begin with, you don't want
    to start it with "And..."

    If Len(strCrit)=0 Then
    strCrit= "[Server type] = '" & Me.server & "'"
    Else
    strCrit=strCrit & " AND [Server type] = '" & Me.server & "'"
    End if

    edit: inserted AND Thx golferguy
    Last edited by RedNeckGeek; 02-07-07 at 11:51.
    Inspiration Through Fermentation

  3. #3
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    I believe RedNeckGeek meant this:
    Code:
    If Len(strCrit)=0 Then
    strCrit= "[Server type] = '" & Me.server & "'"
    Else
    strCrit=strCrit & " AND [Server type] = '" & Me.server & "'"
    End if
    You will need a space after the first double quote, and you will need a space, AND, and another space before your second, third, etc. field in the criteria.
    HTH,

  4. #4
    Join Date
    Feb 2007
    Posts
    13
    its getting me there! :-)

  5. #5
    Join Date
    May 2005
    Posts
    1,191
    Just a quickie by looking at your error messages...

    For your RAM criteria, you're passing the criteria a string (e.g.: 512mb). But you're not bothering to put quotes around it, which is what you would do if it was a number. But since you're also passing the 'mb', you'll need to use the single quotes around it like '512mb'. Else drop the mb and just pass the 512. Depending on what the data looks like in the table you're searching.
    Me.Geek = True

  6. #6
    Join Date
    Feb 2007
    Posts
    13
    ok now using this

    Code:
    Dim db As DAO.Database
       Dim qdf As DAO.QueryDef
       Dim strSQL As String
       Dim serv1 As String
       Dim ram1 As String
       Dim hard1 As String
       Dim project1 As String
       Dim ipaddress1 As String
       Dim location1 As String
       Dim os1 As String
       Dim network1 As String
       Dim ports1 As String
       Dim Status As String
       
       
       
       Set db = CurrentDb
       Set qdf = db.QueryDefs("qbf")
       
       'strSQL = "SELECT tlb_machines.* " & _
                     "FROM tlb_machines where"
       
       'set server
       If IsNull(Me.server) Then
        serv1 = " Like '*'"
      Else
      serv1 = "='" & Me.server.Value & "' "
      End If
       
       'set ram info
       If IsNull(Me.RAM) Then
       ram1 = " Like '*'"
       Else
       ram1 = "='" & Me.RAM.Value & "' "
       End If
       
       'set hdd
       If IsNull(Me.hard) Then
       hard1 = " Like '*'"
       Else
       hard1 = "=" & Me.hard.Value & " "
       End If
       
         
       'set project
       If IsNull(Me.Project) Then
        project1 = " Like '*'"
      Else
      project1 = "='" & Me.Project.Value & "' "
      End If
       
       'set network ports info
       If IsNull(Me.networkports) Then
       ports1 = " Like '*'"
       Else
       ports1 = "=" & Me.networkports.Value & " "
       End If
       
       'set os type
       If IsNull(Me.OS) Then
       os1 = " Like '*'"
       Else
       os1 = "='" & Me.OS.Value & "' "
       End If
       
       'set status
       If IsNull(Me.Status) Then
        status1 = " Like '*'"
      Else
      status1 = "='" & Me.Status.Value & "' "
      End If
       
       'set network ports info
       If IsNull(Me.networkports) Then
       ports1 = " Like '*'"
       Else
       ports1 = "=" & Me.networkports.Value & " "
       End If
       
       'set ipadress type
       If IsNull(Me.ip) Then
       ipaddress1 = " Like '*'"
       Else
       ipaddress1 = "='" & Me.ip.Value & "' "
       End If
       
         ' create sql string
          strSQL = "SELECT tlb_machines.* " & _
                 "FROM tlb_machines " & _
                 "WHERE tlb_machines.servertype" & serv1
                 '"AND tlb_machines.ram" & ram1
                 '"AND tlb_machines.hddsize" & hard1
                 '"AND tlb_machines.status" & status1
                 '"AND tlb_machines.project" & project1
                 '"AND tlb_machines.os" & os1
                 '"AND tlb_machines.network" & network1 & _
                ' "AND tlb_machines.networkports" & ports1 & _
                 '"AND tlb_machines.IpAddress" & ipaddress1
                 '"ORDER BY tlb_machines.servertype;"
    
       MsgBox strSQL
         qdf.SQL = strSQL
          DoCmd.OpenQuery "qbf"
       'DoCmd.OpenReport "your results", acPreview, strSQL
        'DoCmd.Close acForm, Me.Name
        Set qdf = Nothing
        Set db = Nothing
    This passes it fine, however there are 9 servers of the dell 1850 kind. if i comment out all the rest of the sqlstr (as shown) all 9 are shown.

    However, if I then start to add extra lines of code it removes some, eg if I add the ram line it will only then show 6 items, even if the RAM entry is left blank.

    If the user dose not enter the criteria then ram should be

    Code:
     like "*"
    any ideas? I thought like"*" would show all records from that field?

    EDIT: It seems to be missing out the results that have missing entries from that field. eg
    I have 9 1850 servers in the list, 3 of them have no entries in the ram box (so are blank) when I had the ram option, it returns all servers as long as the boxes are not empty.

    what do i need to change for it to display all results even if nothing is in that field at all?
    Last edited by stuk; 02-07-07 at 12:06.

  7. #7
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by stuk
    EDIT: It seems to be missing out the results that have missing entries from that field. eg
    I have 9 1850 servers in the list, 3 of them have no entries in the ram box (so are blank) when I had the ram option, it returns all servers as long as the boxes are not empty.

    what do i need to change for it to display all results even if nothing is in that field at all?
    Are these fields Null or just zero-length? If they are zero-length they should be included in your results if you use the * method (return all records via wildcard). If they are null, Access will throw these records out as the * does not include null entries. Check that out, hope it helps.
    Me.Geek = True

  8. #8
    Join Date
    Feb 2007
    Posts
    13
    Quote Originally Posted by nckdryr
    Are these fields Null or just zero-length? If they are zero-length they should be included in your results if you use the * method (return all records via wildcard). If they are null, Access will throw these records out as the * does not include null entries. Check that out, hope it helps.
    They must be null then!

    What can I do to display the null entries? is there a way to do this? or do I need to make sure users enter data into all searchable fields?

  9. #9
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by stuk
    What can I do to display the null entries? is there a way to do this? or do I need to make sure users enter data into all searchable fields?
    I try to avoid entering Null values into tables, they tend to cause problems as you have witnessed first hand. You could always run an update query to get rid of all existing nulls, but you will have to address how future records are entered to avoid propogating more nulls. As far as I know, there is no way to include the null records in your search. It's just the way a null value is, but maybe someone can enlighten us both.
    Me.Geek = True

  10. #10
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Seem to me that if the user leaves that field empty, he doesn't want to search on that field, therefore, instead of using

    Code:
     If IsNull(Me.server) Then
        serv1 = " Like '*'"
    Else
      serv1 = "='" & Me.server.Value & "' "
    End If
    try using

    Code:
    If  NOT IsNull(Me.server) Then
      serv1 = "='" & Me.server.Value & "' "
    End If
    That way if the user doesn't enter anything in the Server field
    serv1 maintains it's default value of ""
    Inspiration Through Fermentation

  11. #11
    Join Date
    Feb 2007
    Posts
    13
    Quote Originally Posted by RedNeckGeek
    Seem to me that if the user leaves that field empty, he doesn't want to search on that field, therefore, instead of using

    Code:
     If IsNull(Me.server) Then
        serv1 = " Like '*'"
    Else
      serv1 = "='" & Me.server.Value & "' "
    End If
    try using

    Code:
    If  NOT IsNull(Me.server) Then
      serv1 = "='" & Me.server.Value & "' "
    End If
    That way if the user doesn't enter anything in the Server field
    serv1 maintains it's default value of ""
    Im talking about the actual data in the table itself, not the entry on the search form!

    If the entry is left blank on the form thats ok, but sometimes (when entering the data for a server) the information is not available so they leave it blank.

    When they goto the search function and enter the info they want to search on, it will only return items that do not have any information missing.

  12. #12
    Join Date
    Sep 2006
    Posts
    265
    Perhaps another way of looking at is test if there is an input on your each feild and only then include it, if there is no input the user is not needing to ask that question and it can be excluded from the criteria. In VB you simply concatenatiion the fields with an input value. I use modules.

    Private Function Criteria_Something() as String

    Dim User_Input as Byte

    With codecontextobject
    User_Input = False

    if Not IsNull(.[Server]) then
    Criteria_Something = "[Serv1] = '" & .[Server] & "'"
    User_Input = True
    end if
    if Not Isnull(.[Ram]) then
    If User_Input = True then
    Criteria_Something = Criteria_Something & " and [Ram1] = " & .[Ram] & "
    else
    Criteria_Something = "[Ram1] = '" & .[Ram] & "'"
    User_Input = True
    end if

    and so forth

    On the Docmd.Open ... Put the Criteria_Something into the Where

    The form is a great concept, database design is about ideas and then getting the damn code to work for you!

    If the user does ask for something they don't get it! You could use wildcards on every input Field however this narrows down the criteria to only what is being asked for.

  13. #13
    Join Date
    Feb 2007
    Posts
    13
    Quote Originally Posted by SimonMT
    Perhaps another way of looking at is test if there is an input on your each feild and only then include it, if there is no input the user is not needing to ask that question and it can be excluded from the criteria. In VB you simply concatenatiion the fields with an input value. I use modules.

    Private Function Criteria_Something() as String

    Dim User_Input as Byte

    With codecontextobject
    User_Input = False

    if Not IsNull(.[Server]) then
    Criteria_Something = "[Serv1] = '" & .[Server] & "'"
    User_Input = True
    end if
    if Not Isnull(.[Ram]) then
    If User_Input = True then
    Criteria_Something = Criteria_Something & " and [Ram1] = " & .[Ram] & "
    else
    Criteria_Something = "[Ram1] = '" & .[Ram] & "'"
    User_Input = True
    end if

    and so forth

    On the Docmd.Open ... Put the Criteria_Something into the Where

    The form is a great concept, database design is about ideas and then getting the damn code to work for you!

    If the user does ask for something they don't get it! You could use wildcards on every input Field however this narrows down the criteria to only what is being asked for.

    THe searching part is fine, let me explain again

    in the table tlb_machines all of the information is stored. lets say I have 4 records like this

    server Ram HDD Size Location
    ------------------------------------------------
    1850 512mb 73gig 2525
    1850 512mb 73gig 2525
    1850 256mb 73gig 2525
    1850 73gig 2525

    Now all bar one server in the table is fully populated.

    If in the search form I enter servertype as 1850 I should see all for records returned.

    However only the 3 records that are completly populated are returned. the forth with the "ram" entry missing is not returned as it is zero lenght null so the like"*" option dose not work.

    I was asking if there is a way to include empty fields in the results, or to set default item for that field.

  14. #14
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    You have tried using "Like '*' " but that does not work because the field is Null. So you have asked if there is a way to include those records that have a Null in them. The answer is Yes. As I have read down this thread, I have wished I could point to the answer for you, and I know the feeling because I have done it too many times.
    Here is the answer: Forget about the Nulls in your table. Look for something that you can put into your criteria that will allow all records to be shown that should be shown. The way to put that "something" into your criteria is to LEAVE OUT the criteria that the user is not asking for.
    For example:
    The user asks to see everything with 2525 in the Location column. So that is all that is put into the criteria. It would look like this: "Location='2525' " That is the ONLY statement needed in the criteria. And with this being the only thing in the criteria, you will get your 4th record in your example, even though the Ram is NULL. It will be in the output because we did not even address the Ram column. The user was not interested in filtering by the Ram column, so the criteria can just ignore it.
    Hope this helps!

  15. #15
    Join Date
    Feb 2007
    Posts
    13
    Found a way with this code cheers. I know its cheating but it works!

    Code:
    'checks the searcable fields on the form and if empty, enters some default values
    'This will allow the searches to return all fields.
    
    'setup error routine
    On Error Resume Next
    
    'hard disc
    If IsNull(Me.HDDSize.Value) Then
    Me.HDDSize.SetFocus
    Me.HDDSize.Text = "Unknown"
    Me.cmd_next.SetFocus
    End If
    
    'IP Address
    If IsNull(Me.txt_IP.Value) Then
    Me.txt_IP.SetFocus
    Me.txt_IP.Text = "Unknown"
    Me.cmd_next.SetFocus
    End If
    
    'OS Selection
    If IsNull(Me.OS.Value) Then
    Me.OS.SetFocus
    Me.OS.ListIndex = 4
    Me.cmd_next.SetFocus
    End If
    
    'Processor type
    If IsNull(Me.com_processor.Value) Then
    Me.com_processor.SetFocus
    Me.com_processor.ListIndex = 6
    Me.cmd_next.SetFocus
    End If
    
    'User now
    If IsNull(Me.txt_user_now.Value) Then
    Me.txt_user_now.SetFocus
    Me.txt_user_now.Text = "No User"
    Me.cmd_next.SetFocus
    End If
    
    'User Next
    If IsNull(Me.txt_user_next.Value) Then
    Me.txt_user_next.SetFocus
    Me.txt_user_next.Text = "No User"
    Me.cmd_next.SetFocus
    End If
    
    'RAM
    If IsNull(Me.RAM.Value) Then
    Me.RAM.SetFocus
    Me.RAM.ListIndex = 9
    Me.cmd_next.SetFocus
    End If
    
    
    'LI Version
    If IsNull(Me.txt_version_sw.Value) Then
    Me.txt_version_sw.SetFocus
    Me.txt_version_sw.Text = "Version Unknown"
    Me.cmd_next.SetFocus
    End If

Posting Permissions

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