Results 1 to 7 of 7
  1. 11-09-2020, 06:48 AM #1
    diegomarino
    diegomarino is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    409

    handle null values in recordset


    hi, i use a recordset based on this query that i use to fill some textboxes. The problem is when the field of the recordset is null

    Code:
       Set rs = db.OpenRecordset("SELECT [ConBanGraf].TrattativaID, [ConBanGraf].CandidatoID, [ConBanGraf].[Taglio cliente], Count([ConBanGraf].[Taglio cliente]) AS [ConteggioDiTaglio cliente], Switch([taglio cliente]=""<100k"",1,[taglio cliente]=""100k-300k"",2,[taglio cliente]=""300k-500k"",3,[taglio cliente]=""500k-700k"",4,[taglio cliente]=""700k-1mln"",5,[taglio cliente]=""1mln-2mln"",6,[taglio cliente]=""2mln-5mln"",7,[taglio cliente]=""5mln-10mln"",8,[taglio cliente]="">=10mln"",9) AS Ordinamento " & vbCrLf & _      "FROM [ConBanGraf] " & vbCrLf & _
      "GROUP BY [ConBanGraf].TrattativaID, [ConBanGraf].CandidatoID, [ConBanGraf].[Taglio cliente], Switch([taglio cliente]=""<100k"",1,[taglio cliente]=""100k-300k"",2,[taglio cliente]=""300k-500k"",3,[taglio cliente]=""500k-700k"",4,[taglio cliente]=""700k-1mln"",5,[taglio cliente]=""1mln-2mln"",6,[taglio cliente]=""2mln-5mln"",7,[taglio cliente]=""5mln-10mln"",8,[taglio cliente]="">=10mln"",9) " & vbCrLf & _
      "HAVING ((([ConBanGraf].TrattativaID)= " & Me.OpenArgs & ")) " & vbCrLf & _
      "ORDER BY Switch([taglio cliente]=""<100k"",1,[taglio cliente]=""100k-300k"",2,[taglio cliente]=""300k-500k"",3,[taglio cliente]=""500k-700k"",4,[taglio cliente]=""700k-1mln"",5,[taglio cliente]=""1mln-2mln"",6,[taglio cliente]=""2mln-5mln"",7,[taglio cliente]=""5mln-10mln"",8,[taglio cliente]="">=10mln"",9);")
   rs.MoveFirst
      Me.Taglio1 = rs![conteggioditaglio cliente]
   rs.MoveNext
      Me.Taglio2 = rs![conteggioditaglio cliente]
   rs.MoveNext
      Me.Taglio3 = rs![conteggioditaglio cliente]
   rs.MoveNext
      Me.Taglio4 = rs![conteggioditaglio cliente]
   rs.MoveNext
      Me.Taglio5 = rs![conteggioditaglio cliente]
   rs.MoveNext
     Me.Taglio6 = rs![conteggioditaglio cliente]
     rs.MoveNext
     Me.Taglio7 = rs![conteggioditaglio cliente]
   rs.Close
   Set db = Nothing
   Set rs = Nothing
    i tried to use me.taglio1.text but i have the error "impossible to refer to a property of a control not active"

    i tried too to check if the field is null with "if isnull(rs![conteggioditaglio cliente]) then" but nothing
    Reply With Quote Reply With Quote
  2. 11-09-2020, 06:57 AM #2
    Welshgasman
    Welshgasman is online now VIP
    Windows 10 Access 2007
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    5,007
    Use the NZ() function to put a default value in the field?
    Reply With Quote Reply With Quote
  3. 11-09-2020, 07:20 AM #3
    diegomarino
    diegomarino is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    409
    Code:
       Set rs = db.OpenRecordset("SELECT [ConBanGraf].TrattativaID, [ConBanGraf].CandidatoID, [ConBanGraf].[Taglio cliente], Count([ConBanGraf].[Taglio cliente]) AS [ConteggioDiTaglio cliente], Switch([taglio cliente]=""<100k"",1,[taglio cliente]=""100k-300k"",2,[taglio cliente]=""300k-500k"",3,[taglio cliente]=""500k-700k"",4,[taglio cliente]=""700k-1mln"",5,[taglio cliente]=""1mln-2mln"",6,[taglio cliente]=""2mln-5mln"",7,[taglio cliente]=""5mln-10mln"",8,[taglio cliente]="">=10mln"",9) AS Ordinamento " & vbCrLf & _      "FROM [ConBanGraf] " & vbCrLf & _
      "GROUP BY [ConBanGraf].TrattativaID, [ConBanGraf].CandidatoID, [ConBanGraf].[Taglio cliente], Switch([taglio cliente]=""<100k"",1,[taglio cliente]=""100k-300k"",2,[taglio cliente]=""300k-500k"",3,[taglio cliente]=""500k-700k"",4,[taglio cliente]=""700k-1mln"",5,[taglio cliente]=""1mln-2mln"",6,[taglio cliente]=""2mln-5mln"",7,[taglio cliente]=""5mln-10mln"",8,[taglio cliente]="">=10mln"",9) " & vbCrLf & _
      "HAVING ((([ConBanGraf].TrattativaID)= " & Me.OpenArgs & ")) " & vbCrLf & _
      "ORDER BY Switch([taglio cliente]=""<100k"",1,[taglio cliente]=""100k-300k"",2,[taglio cliente]=""300k-500k"",3,[taglio cliente]=""500k-700k"",4,[taglio cliente]=""700k-1mln"",5,[taglio cliente]=""1mln-2mln"",6,[taglio cliente]=""2mln-5mln"",7,[taglio cliente]=""5mln-10mln"",8,[taglio cliente]="">=10mln"",9);")
   rs.MoveFirst
      Me.Taglio1 = Nz(rs![conteggioditaglio cliente], "")
   rs.MoveNext
      Me.Taglio2 = Nz(rs![conteggioditaglio cliente], "")
   rs.MoveNext
      Me.Taglio3 = Nz(rs![conteggioditaglio cliente], "")
   rs.MoveNext
      Me.Taglio4 = Nz(rs![conteggioditaglio cliente], "")
   rs.MoveNext
      Me.Taglio5 = Nz(rs![conteggioditaglio cliente], "")
   rs.MoveNext
     Me.Taglio6 = Nz(rs![conteggioditaglio cliente], "")
     rs.MoveNext
     Me.Taglio7 = Nz(rs![conteggioditaglio cliente], "")
   rs.Close
   Set db = Nothing
   Set rs = Nothing
    it gives me "no current record"
    Reply With Quote Reply With Quote
  4. 11-09-2020, 08:17 AM #4
    ArviLaanemets
    ArviLaanemets is offline VIP
    Windows 8 Access 2016
    Join Date
    Apr 2017
    Posts
    1,687
    Obviously recordset returns less than 7 records. Try to run same query from query editor window and look at returned data.
    Reply With Quote Reply With Quote
  5. 11-09-2020, 08:34 AM #5
    Welshgasman
    Welshgasman is online now VIP
    Windows 10 Access 2007
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    5,007
    Your structure appears to be wrong?
    Might want to take note of rs.EOF as well ?
    Reply With Quote Reply With Quote
  6. 11-09-2020, 08:59 AM #6
    diegomarino
    diegomarino is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    409
    of course that was the problem.
    i'll do this way: since i have to fill some textboxes that have fixed values (like 100k-300k) i check if the field of recordset match with my textbox, if so i give that value and move next, if not i stick in the row of the recordset till i find the matching textbox
    Reply With Quote Reply With Quote
  7. 11-09-2020, 10:48 AM #7
    diegomarino
    diegomarino is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    409
    i soved in this way, a bit longer but it gives me to solve every circunstances.

    Code:
       Set rs = db.OpenRecordset("SELECT [ConBanGraf].TrattativaID, [ConBanGraf].CandidatoID, [ConBanGraf].[Taglio cliente], Count([ConBanGraf].[Taglio cliente]) AS [ConteggioDiTaglio cliente], Switch([taglio cliente]=""<100k"",1,[taglio cliente]=""100k-300k"",2,[taglio cliente]=""300k-500k"",3,[taglio cliente]=""500k-700k"",4,[taglio cliente]=""700k-1mln"",5,[taglio cliente]=""1mln-2mln"",6,[taglio cliente]=""2mln-5mln"",7,[taglio cliente]=""5mln-10mln"",8,[taglio cliente]="">=10mln"",9) AS Ordinamento " & vbCrLf & _      "FROM [ConBanGraf] " & vbCrLf & _
      "GROUP BY [ConBanGraf].TrattativaID, [ConBanGraf].CandidatoID, [ConBanGraf].[Taglio cliente], Switch([taglio cliente]=""<100k"",1,[taglio cliente]=""100k-300k"",2,[taglio cliente]=""300k-500k"",3,[taglio cliente]=""500k-700k"",4,[taglio cliente]=""700k-1mln"",5,[taglio cliente]=""1mln-2mln"",6,[taglio cliente]=""2mln-5mln"",7,[taglio cliente]=""5mln-10mln"",8,[taglio cliente]="">=10mln"",9) " & vbCrLf & _
      "HAVING ((([ConBanGraf].TrattativaID)= " & Me.OpenArgs & ")) " & vbCrLf & _
      "ORDER BY Switch([taglio cliente]=""<100k"",1,[taglio cliente]=""100k-300k"",2,[taglio cliente]=""300k-500k"",3,[taglio cliente]=""500k-700k"",4,[taglio cliente]=""700k-1mln"",5,[taglio cliente]=""1mln-2mln"",6,[taglio cliente]=""2mln-5mln"",7,[taglio cliente]=""5mln-10mln"",8,[taglio cliente]="">=10mln"",9);")
   rs.MoveFirst
   If rs.EOF Then
      GoTo chiudi
   Else
      If rs![taglio cliente] = "<100k" Then
         Me.Taglio0 = rs![conteggioditaglio cliente]
            rs.MoveNext
      Else
         Me.Taglio0 = 0
      End If
   End If
   If rs.EOF Then
      GoTo chiudi
   Else
      If rs![taglio cliente] = "100k-300k" Then
         Me.Taglio1 = rs![conteggioditaglio cliente]
            rs.MoveNext
      Else
         Me.Taglio1 = 0
      End If
   End If
   If rs.EOF Then
      GoTo chiudi
   Else
      If rs![taglio cliente] = "300k-500k" Then
         Me.Taglio2 = rs![conteggioditaglio cliente]
            rs.MoveNext
      Else
         Me.Taglio2 = 0
      End If
   End If
   If rs.EOF Then
      Me.Taglio3 = 0
      Me.Taglio4 = 0
      Me.Taglio5 = 0
      Me.Taglio6 = 0
      Me.Taglio7 = 0
      Me.Taglio8 = 0
      GoTo chiudi
   Else
      If rs![taglio cliente] = "500k-700k" Then
         Me.Taglio3 = rs![conteggioditaglio cliente]
            rs.MoveNext
      Else
         Me.Taglio3 = 0
      End If
   End If
   If rs.EOF Then
      Me.Taglio4 = 0
      Me.Taglio5 = 0
      Me.Taglio6 = 0
      Me.Taglio7 = 0
      Me.Taglio8 = 0
      GoTo chiudi
   Else
      If rs![taglio cliente] = "700k-1mln" Then
         Me.Taglio4 = rs![conteggioditaglio cliente]
            rs.MoveNext
      Else
         Me.Taglio4 = 0
      End If
   End If
   If rs.EOF Then
      Me.Taglio5 = 0
      Me.Taglio6 = 0
      Me.Taglio7 = 0
      Me.Taglio8 = 0
     GoTo chiudi
   Else
      If rs![taglio cliente] = "1mln-2mln" Then
         Me.Taglio5 = rs![conteggioditaglio cliente]
            rs.MoveNext
      Else
         Me.Taglio5 = 0
      End If
   End If
   If rs.EOF Then
      Me.Taglio6 = 0
      Me.Taglio7 = 0
      Me.Taglio8 = 0
      GoTo chiudi
   Else
      If rs![taglio cliente] = "2mln-5mln" Then
         Me.Taglio6 = rs![conteggioditaglio cliente]
            rs.MoveNext
      Else
         Me.Taglio6 = 0
      End If
   End If
   If rs.EOF Then
      Me.Taglio7 = 0
      Me.Taglio8 = 0
      GoTo chiudi
   Else
      If rs![taglio cliente] = "5mln-10mln" Then
         Me.Taglio7 = rs![conteggioditaglio cliente]
            rs.MoveNext
      Else
         Me.Taglio7 = 0
      End If
   End If
   If rs.EOF Then
      Me.Taglio8 = 0
      GoTo chiudi
   Else
      If rs![taglio cliente] = ">=10mln" Then
         Me.Taglio8 = rs![conteggioditaglio cliente]
            rs.MoveNext
      Else
         Me.Taglio8 = 0
      End If
   End If
chiudi:
   rs.Close
   Set db = Nothing
   Set rs = Nothing
    Reply With Quote Reply With Quote
Please reply to this thread with any new information or opinions.
« Previous Thread | Next Thread »

Similar Threads

  1. how to handle a null field
    By fishhead in forum Programming
    Replies: 5
    Last Post: 08-27-2020, 08:39 AM
  2. Best way to handle decimal values?
    By smaier in forum Access
    Replies: 6
    Last Post: 07-22-2020, 12:16 PM
  3. How to handle missing values when cleaning data?
    By TerraEarth in forum Programming
    Replies: 1
    Last Post: 04-10-2018, 05:25 PM
  4. How to handle null in SQL statement
    By ultimateguy in forum Programming
    Replies: 21
    Last Post: 08-15-2014, 10:48 AM
  5. How to handle multiple null values using form-based parameter queries?
    By tim_tims33 in forum Access
    Replies: 2
    Last Post: 11-19-2012, 05:42 PM

Posting Permissions

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

Forum Rules

Other Forums: Microsoft Office Forums