dcsimg
Results 1 to 7 of 7

Thread: handling NuLLS

  1. #1

    Thread Starter
    Member
    Join Date
    Feb 2018
    Posts
    54

    handling NuLLS

    i have this code that sucessfully works takes a record from one table and moves it to another, but when doing this is handling the null dates not as desired. it is putting default 12:00:00 am in in all feilds with date and time i would like it not to show anything
    Code:
    Private Sub Combo970_AfterUpdate()
    'On Error GoTo Err_Run
        Dim rs As Object
        Dim sql As String
        Dim Answer As Integer
        Dim MyDb As Database
        Dim ArchiveSet As Recordset
        Dim CurrentSet As Recordset
        Dim strSQL As String
        Dim Save2 As String
        Dim Save4 As String
        Dim Save5 As Date
        Dim Save6 As Date
        Dim Save7 As String
        Dim Save8 As String
        Dim Save9 As Date
        Dim Save10 As Integer
        Dim Save11 As Integer
        Dim Save12 As Long
        Dim Save13 As Integer
        Dim Save14 As Boolean
        Dim Save15 As String
        Dim Save16 As String
        Dim Save17 As String
        Dim Save18 As String
        Dim Save19 As String
        Dim Save20 As Date
        Dim Save21 As Date
        Dim Save22 As Integer
        Dim Save23 As Date
        Dim Save24 As Date
        Dim Save25 As Date
        Dim Save26 As Date
        Dim Save27 As Date
        Dim Save28 As Date
        Dim Save29 As String
        Dim Save30 As Date
        Dim Save31 As Boolean
        Dim Save32 As String
        Dim Save33 As Integer
        Dim Save34 As String
        Dim Save35 As Integer
        Dim Save36 As Integer
        Dim Save37 As Integer
        Dim Save38 As Integer
        Dim Save39 As Integer
        Dim Save40 As Integer
        Dim Save41 As Integer
        Dim Save42 As Integer
        Dim Save43 As Integer
        Dim Save44 As String
        Dim Save45 As String
        Dim Save46 As Integer
        Dim Save47 As Integer
        Dim Save48 As Integer
        Dim Save49 As Integer
        Dim Save50 As String
        Dim Save51 As Integer
        Dim Save52 As Integer
        Dim Save53 As String
        Dim Save54 As Boolean
        Dim Save55 As Integer
        Dim Save56 As Integer
        Dim Save57 As Integer
        Dim Save58 As Integer
        Dim Save59 As String
        Dim Save60 As Integer
        Dim Save61 As Integer
        Dim Save62 As Integer
        Dim Save63 As String
        Dim Save64 As Boolean
        Dim Save65 As Date
        Dim Save66 As Date
        Dim Save67 As Date
        Dim Save68 As Date
        Dim Save69 As Date
        Dim Save70 As Date
        Dim Save71 As Boolean
        Dim Save72 As Date
        Dim Save73 As Boolean
        Dim Save74 As Integer
        Dim Save75 As Date
        Dim Save76 As Integer
        Dim Save77 As Integer
        Dim Save78 As Integer
        Dim Save79 As Integer
        Dim Save80 As Date
        Dim Save81 As Integer
        Dim Save82 As Integer
        Dim Save83 As Integer
        Dim Save84 As Integer
        Dim Save85 As Integer
        Dim Save86 As Integer
        Dim Save87 As Integer
        Dim Save88 As String
        Dim Save89 As String
        Dim Save90 As String
        Dim Save91 As String
        Dim Save92 As String
        Dim Save93 As String
        Dim Save94 As Date
        Dim Save95 As Date
        Dim Save96 As String
        Dim Save97 As String
        Dim Save98 As String
        
     strSQL = "SELECT Archive.[Order Number], Archive.[Customer],Archive.[Received],Archive.[Need By], Archive.[Sales Rep], Archive.[Parts]," & _
        "[Archive].[Scheduled Ship],[Archive].[Width],[Archive].[Length],[Archive].[Quantity],[Archive].[Delivery Method],[Archive].[Midax],[Archive].[WCSS]," & _
        "Archive.[Job Type],Archive.[Why],Archive.[Service Rep], Archive.[Why Reop], Archive.[Plate Date],Archive.[Press Date], Archive.[Press Sequence]," & _
        "Archive.[Collator Date],Archive.[Offline Date], Archive.[Paper Due], Archive.[Carbon Due],Archive.[Die Due],Archive.[Ink Due]," & _
        "Archive.[Repeat Order Numbers],Archive.[Misc Due],Archive.[Companion Order], Archive.[Companion Order Number], Archive.[Press],Archive.[No Parts]," & _
        "Archive.[No Wide],Archive.[Calc Length],Archive.[Speed],Archive.[Calc Hrs], Archive.[Completed],Archive.[Remaining],Archive.[Additional Hrs]," & _
        "Archive.[No of Inks Face],Archive.[No of Inks Back] , Archive.[Die Number(s) Face], Archive.[Die Number(s) Liner], Archive.[Collator No:]," & _
        "Archive.[Calc Collator Hours] , Archive.[CompletedC],Archive.[RemainingC],Archive.[Collator Delivery], Archive.[Offline Machine No]," & _
        "Archive.[Calc roto Hrs],Archive.[Feet Per Core roto], Archive.[Order Complete],Archive.[Collator Speed],Archive.[Offline Speed]," & _
        "Archive.[No Wide Thru Collator],Archive.[Calc Length Thru Collator],Archive.[No Wide Thru Roto],Archive.[Calc Length Thru roto]," & _
        "Archive.[Completed roto Hrs],Archive.[Remaining roto Hrs],Archive.[Offline Delivery roto],Archive.[Exact Repeat] , Archive.[Cores Due]," & _
        "Archive.[Cartons Due], Archive.[Ribbons Due], Archive.[Laminate Due], Archive.[Cleaning Cards Due],Archive.[Chip Due] , Archive.[Tamarack]," & _
        "Archive.[Backer Die Due] , Archive.[Quadrel], Archive.[Quadrel No], Archive.[Quadrel Date], Archive.[completed quad hrs]," & _
        "Archive.[Remaining Hrs- Quadrel], Archive.[Calc Length Thru quardel], Archive.[Tamarack Machine No], Archive.[Tamarack date]," & _
        "Archive.[Remaining Hr-Tamarack],Archive.[Calc Length Thru tamarack],Archive.[completed tam hrs], Archive.[Roto Machine No],Archive.[Quad speed]," & _
        "Archive.[tam speed], Archive.[roto speed],Archive.[Feet Per Core Tam],Archive.[Feet Per Core quad], Archive.[Offline Delivery tam],Archive.[Offline Delivery quad]," & _
        "Archive.[No Wide Thru quad],Archive.[No Wide Thru Tam],Archive.[Special Pallets Due],Archive.[Security Tape Due],Archive.[Content Number]," & _
        "Archive.[Plate Length],Archive.[Number Wide Plates] FROM Archive WHERE [Order Number] = '" & Me![Combo970] & "' " 'Me![Combo970]"
        Set MyDb = CurrentDb
     
    ' This opens the single "Template Record"
        Set ArchiveSet = MyDb.OpenRecordset(strSQL)
     
        With ArchiveSet
            Save2 = Nz(![order number])
            Save4 = Nz(![Customer])
            Save5 = Nz(![Received])
            Save6 = Nz(![Need By])
            Save7 = Nz(![Sales Rep])
            Save8 = Nz(![Parts])
            Save9 = Nz(![Scheduled Ship])
            Save10 = Nz(![Width])
            Save11 = Nz(![Length])
            Save12 = Nz(![Quantity])
            Save13 = Nz(![Delivery Method])
            Save14 = Nz(![Midax])
            Save15 = Nz(![WCSS])
            Save16 = Nz(![Job Type])
            Save17 = Nz(![Why])
            Save18 = Nz(![Service Rep])
            Save19 = Nz(![Why Reop])
            Save20 = Nz(![Plate Date])
            Save21 = Nz(![Press Date])
            Save22 = Nz(![Press Sequence])
            Save23 = Nz(![Collator Date])
            Save24 = Nz(![Offline Date])
            Save25 = Nz(![Paper Due])
            Save26 = Nz(![Carbon Due])
            Save27 = Nz(![Die Due])
            Save28 = Nz(![Ink Due])
            Save29 = Nz(![Repeat Order Numbers])
            Save30 = Nz(![Misc Due])
            Save31 = Nz(![Companion Order])
            Save32 = Nz(![Companion Order Number])
            Save33 = Nz(![Press])
            Save34 = Nz(![No Parts])
            Save35 = Nz(![No Wide])
            Save36 = Nz(![Calc Length])
            Save37 = Nz(![Speed])
            Save38 = Nz(![Calc Hrs])
            Save39 = Nz(![Completed])
            Save40 = Nz(![Remaining])
            Save41 = Nz(![Additional Hrs])
            Save42 = Nz(![No of Inks Face])
            Save43 = Nz(![No of Inks Back])
            Save44 = Nz(![Die Number(s) Face])
            Save45 = Nz(![Die Number(s) Liner])
            Save46 = Nz(![Collator No:])
            Save47 = Nz(![Calc Collator Hours])
            Save48 = Nz(![CompletedC])
            Save49 = Nz(![RemainingC])
            Save50 = Nz(![Collator Delivery])
            Save51 = Nz(![Offline Machine No])
            Save52 = Nz(![Calc roto Hrs])
            Save53 = Nz(![Feet Per Core roto])
            Save54 = Nz(![Order Complete])
            Save55 = Nz(![Collator Speed])
            Save56 = Nz(![Offline Speed])
            Save57 = Nz(![No Wide Thru Collator])
            Save58 = Nz(![Calc Length Thru Collator])
            Save59 = Nz(![No Wide Thru Roto])
            Save60 = Nz(![Calc Length Thru roto])
            Save61 = Nz(![Completed roto Hrs])
            Save62 = Nz(![Remaining roto Hrs])
            Save63 = Nz(![Offline Delivery roto])
            Save64 = Nz(![Exact Repeat])
            Save65 = Nz(![Cores Due])
            Save66 = Nz(![Cartons Due])
            Save67 = Nz(![Ribbons Due])
            Save68 = Nz(![Laminate Due])
            Save69 = Nz(![Cleaning Cards Due])
            Save70 = Nz(![Chip Due])
            Save71 = Nz(![Tamarack])
            Save72 = Nz(![Backer Die Due])
            Save73 = Nz(![Quadrel])
            Save74 = Nz(![Quadrel No])
            Save75 = Nz(![Quadrel Date])
            Save76 = Nz(![completed quad hrs])
            Save77 = Nz(![Remaining Hrs- Quadrel])
            Save78 = Nz(![Calc Length Thru quardel])
            Save79 = Nz(![Tamarack Machine No])
            Save80 = Nz(![Tamarack date])
            Save81 = Nz(![Remaining Hr-Tamarack])
            Save82 = Nz(![Calc Length Thru tamarack])
            Save83 = Nz(![completed tam hrs])
            Save84 = Nz(![Roto Machine No])
            Save85 = Nz(![Quad speed])
            Save86 = Nz(![tam speed])
            Save87 = Nz(![roto speed])
            Save88 = Nz(![Feet Per Core tam])
            Save89 = Nz(![Feet Per Core quad])
            Save90 = Nz(![Offline Delivery tam])
            Save91 = Nz(![Offline Delivery quad])
            Save92 = Nz(![No Wide Thru quad])
            Save93 = Nz(![No Wide Thru Tam])
            Save94 = Nz(![Special Pallets Due])
            Save95 = Nz(![Security Tape Due])
            Save96 = Nz(![Content Number])
            Save97 = Nz(![Plate Length])
            Save98 = Nz(![Number Wide Plates])
        .Close
        Set ArchiveSet = Nothing
        End With
     
     strSQL = "SELECT [Order Master].[Order Number],[Order Master].[Customer],[Order Master].[Received],[Order Master].[Need By],[Order Master].[Sales Rep], [Order Master].[Parts]," & _
        "[Order Master].[Scheduled Ship],[Order Master].[Width],[Order Master].[Length],[Order Master].[Quantity],[Order Master].[Delivery Method],[Order Master].[Midax],[Order Master].[WCSS]," & _
        "[Order Master].[Job Type],[Order Master].[Why],[Order Master].[Service Rep], [Order Master].[Why Reop], [Order Master].[Plate Date],[Order Master].[Press Date], [Order Master].[Press Sequence]," & _
        "[Order Master].[Collator Date],[Order Master].[Offline Date], [Order Master].[Paper Due], [Order Master].[Carbon Due],[Order Master].[Die Due],[Order Master].[Ink Due]," & _
        "[Order Master].[Repeat Order Numbers],[Order Master].[Misc Due],[Order Master].[Companion Order], [Order Master].[Companion Order Number], [Order Master].[Press],[Order Master].[No Parts]," & _
        "[Order Master].[No Wide],[Order Master].[Calc Length],[Order Master].[Speed],[Order Master].[Calc Hrs], [Order Master].[Completed],[Order Master].[Remaining],[Order Master].[Additional Hrs]," & _
        "[Order Master].[No of Inks Face],[Order Master].[No of Inks Back] ,[Order Master].[Die Number(s) Face], [Order Master].[Die Number(s) Liner], [Order Master].[Collator No:]," & _
        "[Order Master].[Calc Collator Hours] , [Order Master].[CompletedC],[Order Master].[RemainingC],[Order Master].[Collator Delivery],[Order Master].[Offline Machine No]," & _
        "[Order Master].[Calc roto Hrs],[Order Master].[Feet Per Core roto], [Order Master].[Order Complete],[Order Master].[Collator Speed],[Order Master].[Offline Speed]," & _
        "[Order Master].[No Wide Thru Collator],[Order Master].[Calc Length Thru Collator],[Order Master].[No Wide Thru Roto],[Order Master].[Calc Length Thru roto]," & _
        "[Order Master].[Completed roto Hrs],[Order Master].[Remaining roto Hrs],[Order Master].[Offline Delivery roto],[Order Master].[Exact Repeat],[Order Master].[Cores Due]," & _
        "[Order Master].[Cartons Due], [Order Master].[Ribbons Due], [Order Master].[Laminate Due],[Order Master].[Cleaning Cards Due],[Order Master].[Chip Due],[Order Master].[Tamarack]," & _
        "[Order Master].[Backer Die Due] , [Order Master].[Quadrel], [Order Master].[Quadrel No], [Order Master].[Quadrel Date],[Order Master].[completed quad hrs]," & _
        "[Order Master].[Remaining Hrs- Quadrel], [Order Master].[Calc Length Thru quardel], [Order Master].[Tamarack Machine No], [Order Master].[Tamarack date]," & _
        "[Order Master].[Remaining Hr-Tamarack],[Order Master].[Calc Length Thru tamarack],[Order Master].[completed tam hrs], [Order Master].[Roto Machine No],[Order Master].[Quad speed]," & _
        "[Order Master].[tam speed], [Order Master].[roto speed],[Order Master].[Feet Per Core tam],[Order Master].[Feet Per Core quad], [Order Master].[Offline Delivery tam],[Order Master].[Offline Delivery quad]," & _
        "[Order Master].[No Wide Thru quad],[Order Master].[No Wide Thru Tam],[Order Master].[Special Pallets Due],[Order Master].[Security Tape Due],[Order Master].[Content Number]," & _
        "[Order Master].[Plate Length],[Order Master].[Number Wide Plates] FROM [Order Master];"
        Set CurrentSet = MyDb.OpenRecordset(strSQL)
     
        With CurrentSet
        .AddNew
            ![order number] = Save2
            ![Customer] = Save4
            ![Received] = Save5
            ![Need By] = Save6
            ![Sales Rep] = Save7
            ![Parts] = Save8
            ![Scheduled Ship] = Save9
            ![Width] = Save10
            ![Length] = Save11
            ![Quantity] = Save12
            ![Delivery Method] = Save13
            ![Midax] = Save14
            ![WCSS] = Save15
            ![Job Type] = Save16
            ![Why] = Save17
            ![Service Rep] = Save18
            ![Why Reop] = Save19
            ![Plate Date] = Save20
            ![Press Date] = Save21
            ![Press Sequence] = Save22
            ![Collator Date] = Save23
            ![Offline Date] = Save24
            ![Paper Due] = Save25
            ![Carbon Due] = Save26
            ![Die Due] = Save27
            ![Ink Due] = Save28
            ![Repeat Order Numbers] = Save29
            ![Misc Due] = Save30
            ![Companion Order] = Save31
            ![Companion Order Number] = Save32
            ![Press] = Save33
            ![No Parts] = Save34
            ![No Wide] = Save35
            ![Calc Length] = Save36
            ![Speed] = Save37
            ![Calc Hrs] = Save38
            ![Completed] = Save39
            ![Remaining] = Save40
            ![Additional Hrs] = Save41
            ![No of Inks Face] = Save42
            ![No of Inks Back] = Save43
            ![Die Number(s) Face] = Save44
            ![Die Number(s) Liner] = Save45
            ![Collator No:] = Save46
            ![Calc Collator Hours] = Save47
            ![CompletedC] = Save48
            ![RemainingC] = Save49
            ![Collator Delivery] = Save50
            ![Offline Machine No] = Save51
            ![Calc roto Hrs] = Save52
            ![Feet Per Core roto] = Save53
            ![Order Complete] = Save54
            ![Collator Speed] = Save55
            ![Offline Speed] = Save56
            ![No Wide Thru Collator] = Save57
            ![Calc Length Thru Collator] = Save58
            ![No Wide Thru Roto] = Save59
            ![Calc Length Thru roto] = Save60
            ![Completed roto Hrs] = Save61
            ![Remaining roto Hrs] = Save62
            ![Offline Delivery roto] = Save63
            ![Exact Repeat] = Save64
            ![Cores Due] = Save65
            ![Cartons Due] = Save66
            ![Ribbons Due] = Save67
            ![Laminate Due] = Save68
            ![Cleaning Cards Due] = Save69
            ![Chip Due] = Save70
            ![Tamarack] = Save71
            ![Backer Die Due] = Save72
            ![Quadrel] = Save73
            ![Quadrel No] = Save74
            ![Quadrel Date] = Save75
            ![completed quad hrs] = Save76
            ![Remaining Hrs- Quadrel] = Save77
            ![Calc Length Thru quardel] = Save78
            ![Tamarack Machine No] = Save79
            ![Tamarack date] = Save80
            ![Remaining Hr-Tamarack] = Save81
            ![Calc Length Thru tamarack] = Save82
            ![completed tam hrs] = Save83
            ![Roto Machine No] = Save84
            ![Quad speed] = Save85
            ![tam speed] = Save86
            ![roto speed] = Save87
            ![Feet Per Core tam] = Save88
            ![Feet Per Core quad] = Save89
            ![Offline Delivery tam] = Save90
            ![Offline Delivery quad] = Save91
            ![No Wide Thru quad] = Save92
            ![No Wide Thru Tam] = Save93
            ![Special Pallets Due] = Save94
            ![Security Tape Due] = Save95
            ![Content Number] = Save96
            ![Plate Length] = Save97
            ![Number Wide Plates] = Save98
        .Update
        .Close
        Set CurrentSet = Nothing
        End With
        
        'Me.Combo960 = Me.Combo970
        
        
        Answer = MsgBox("record added with order number " + Combo970.Value + " Do you wish to delete this record from Archive?", vbYesNo + vbExclamation + vbDefaultButton2, "Delete Confirmation")
            If Answer = vbYes Then
                DoCmd.SetWarnings False
                sql = "DELETE * FROM Archive WHERE [Order Number] = '" & Me![Combo970] & "'"
                CurrentDb.Execute sql
    'DoCmd.SetWarnings True
            Else
            End If
            DoCmd.OpenForm "Order Master", acNormal, , "[Order Number] = '" & Me![Combo970] & "'"
            Me.Refresh
    Exit_Combo970_AfterUpdate:
    Exit Sub
    Err_Run:
    MsgBox "Order Number not in archive "
    End Sub

  2. #2
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,041

    Re: handling NuLLS

    just do as people suggest you to do, and your problems will all disappear
    do not put off till tomorrow what you can put off forever

  3. #3
    PowerPoster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,094

    Re: handling NuLLS

    Just for grins try selecting the dates using isnull. MSSQL

    isnull(Save5,null)
    Please remember next time...elections matter!

  4. #4
    Frenzied Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    1,063

    Re: handling NuLLS

    how to handle NULL

    Code:
    Public Function CDateSQL(ByVal Datumswert As Variant) As String
     
       'Quelle: www.dbwiki.net oder www.dbwiki.de
     
       Const AccessDateFmt As String = "\#mm\/dd\/yyyy\#"
     
       If IsDate(Datumswert) Then
          CDateSQL = Format$(CDate(Datumswert), AccessDateFmt)
       Else
          Err.Raise 13, , "Ungültiger Datumswert: " & Nz(Datumswert, "Null")
       End If
    End Function
    usage..
    Code:
    a)... WHERE Bestelldatum = " & CDateSQL(MeinDatum) & " AND ...
    b) Dim strFilter As String
     
       strFilter = "Bestelldatum = " & CDateSQL(MeinDatum)
       Me.Filter = strFilter
       Me.FilterOn = True
    don't know what Ikke told you to do, but you should have tried it

    regards
    Chris
    Last edited by ChrisE; Mar 5th, 2018 at 10:01 AM.
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  5. #5
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,638

    Re: handling NuLLS

    Quote Originally Posted by IkkeEnGij View Post
    just do as people suggest you to do, and your problems will all disappear
    I gave up. There's only so much you can do for someone that doesn't want your help. You can lead a horse to water, but you can't make him drink. All you can do is stand to the side, shake your head, and watch the train wreck happen.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6
    Frenzied Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    1,063

    Re: handling NuLLS

    Quote Originally Posted by techgnome View Post
    I gave up. There's only so much you can do for someone that doesn't want your help. You can lead a horse to water, but you can't make him drink. All you can do is stand to the side, shake your head, and watch the train wreck happen.

    -tg
    well I'm sure you and Ikke gave him good advice, perhaps the OP will reply

    ...thanks Resolved..

    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  7. #7

    Thread Starter
    Member
    Join Date
    Feb 2018
    Posts
    54

    Re: handling NuLLS

    Quote Originally Posted by IkkeEnGij View Post
    just do as people suggest you to do, and your problems will all disappear
    i have missed your post suggesting what i can do to handle nulls

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width


×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.