UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Skip Line Of Code If Value Is Null Recordset Update, Access 2016    
 
   
daveyedgar
post Feb 6 2018, 06:04 PM
Post#1



Posts: 16
Joined: 16-March 15



Hi,

In reference to the code below, I want to skip the line if the field being looked up is null. Not sure what is the most efficient way to do that. Any suggestions?
CODE
rs!LETT = DLookup("[TTSN]", "qryEngine", "[EPOSITION] = 'LEFT' And [REGISTRATION] = '" & Forms![frmAIRCRAFT].Form.[txtREGISTRATION] & "'")
rs!LSMOH = DLookup("[SMOH]", "qryEngine", "[EPOSITION] = 'LEFT' And [REGISTRATION] = '" & Forms![frmAIRCRAFT].Form.[txtREGISTRATION] & "'")
rs!LSFOH = DLookup("[SFOH]", "qryEngine", "[EPOSITION] = 'LEFT' And [REGISTRATION] = '" & Forms![frmAIRCRAFT].Form.[txtREGISTRATION] & "'")
rs!LSFRM = DLookup("[SFRM]", "qryEngine", "[EPOSITION] = 'LEFT' And [REGISTRATION] = '" & Forms![frmAIRCRAFT].Form.[txtREGISTRATION] & "'")
Go to the top of the page
 
MadPiet
post Feb 6 2018, 06:11 PM
Post#2



Posts: 2,492
Joined: 27-February 09



Dim varValue As Variant
varValue = DLookup("[TTSN]", "qryEngine", "[EPOSITION] = 'LEFT' And [REGISTRATION] = '" & Forms![frmAIRCRAFT].Form.[txtREGISTRATION] & "'")
If IsNull(varValue) Then
' do one thing
ELSE
'do something else
END IF

?
Go to the top of the page
 
daveyedgar
post Feb 6 2018, 07:48 PM
Post#3



Posts: 16
Joined: 16-March 15



I'm close I can feel it. I also want to check if the field is empty as well, so 3 things...

1.) I would like know how, if possible, to check if the string is not null and not empty since the code below works for not null, but not for empty strings.
2.) The code below also enters zeros in the strings that are null, but I don't want it to do that, I just want it to skip that line.
3.) The code below also will enter the data from the string to the record set, but only the first one it encounters.
For example, if there is a value in TTSN, then it will enter it in strLETT, then will put zeros in the remaining strings regardless of the contents of the remaining strings.

CODE
Dim strLETT As Variant
Dim strLSMOH As Variant
Dim strSFOH As Variant
Dim strSFRM As Variant

strLETT = DLookup("[TTSN]", "qryEngine", "[EPOSITION] = 'LEFT' And [REGISTRATION] = " & Forms![frmAIRCRAFT].Form.[txtREGISTRATION] & "")
strLSMOH = DLookup("[SMOH]", "qryEngine", "[EPOSITION] = 'LEFT' And [REGISTRATION] = " & Forms![frmAIRCRAFT].Form.[txtREGISTRATION] & "")
strSFOH = DLookup("[SFOH]", "qryEngine", "[EPOSITION] = 'LEFT' And [REGISTRATION] = " & Forms![frmAIRCRAFT].Form.[txtREGISTRATION] & "")
strSFRM = DLookup("[SFRM]", "qryEngine", "[EPOSITION] = 'LEFT' And [REGISTRATION] = " & Forms![frmAIRCRAFT].Form.[txtREGISTRATION] & "")

If Not IsNull(strLETT) Then
    rs!LETT = strLETT
ElseIf Not IsNull(strLSMOH) Then
    rs!LETT = strLSMOH
ElseIf Not IsNull(strLSFOH) Then
    rs!LSFOH = strLSFOH
ElseIf Not IsNull(strSFRM) Then
    rs!LSFRM = strSFRM
Else
End If

This post has been edited by daveyedgar: Feb 6 2018, 08:11 PM
Go to the top of the page
 
daveyedgar
post Feb 6 2018, 08:32 PM
Post#4



Posts: 16
Joined: 16-March 15



O.k. this seems to be working. If anyone knows a way to make this more efficient please post.

CODE
If Not IsNull(strLETT) Or Len(strLETT) <> 0 Then
    rs!LETT = strLETT
    Else
    rs!LETT = Null
    End If
If Not IsNull(strLSMOH) Or Len(strLETT) <> 0 Then
    rs!LSMOH = strLSMOH
    Else: rs!LSMOH = Null
    End If
If Not IsNull(strSFOH) Or Len(strLETT) <> 0 Then
    rs!LSFOH = strSFOH
    Else: rs!LSFOH = Null
    End If
If Not IsNull(strSFRM) Or Len(strLETT) <> 0 Then
    rs!LSFRM = strSFRM
    Else: rs!LSFRM = Null
    End If
Go to the top of the page
 
moke123
post Feb 7 2018, 04:42 AM
Post#5



Posts: 1,318
Joined: 26-December 12
From: Western Ma.,L.I.,N.Y.,Jupiter,Fl.



you can test for null and zls in one shot with
CODE
If  nz(strLETT,"")  <> "" Then
Go to the top of the page
 
daveyedgar
post Feb 7 2018, 06:06 AM
Post#6



Posts: 16
Joined: 16-March 15



so this would test for "not" null?
Go to the top of the page
 
BruceM
post Feb 7 2018, 07:37 AM
Post#7


UtterAccess VIP
Posts: 7,638
Joined: 24-May 10
From: Downeast Maine


Yes. If strLETT (a Variant) is null, Nz will return "" (ZLS). If strLETT is a ZLS, Nz will not affect strLett. With either a null or a ZLS, strLETT is "".

You can get to the same place with:

If Len(strLETT & "") > 0 Then...

If [TISN], etc. are table fields, and if you configure the fields (table design view) not to accept a ZLS you should be able to avoid the need for the test.
Go to the top of the page
 
cheekybuddha
post Feb 7 2018, 09:04 AM
Post#8


UtterAccess VIP
Posts: 10,347
Joined: 6-December 03
From: Telegraph Hill


Please also tell us what table name / query name / SQL that recordset rs is based on.


--------------------


Regards,

David Marten
Go to the top of the page
 
daveyedgar
post Feb 7 2018, 02:25 PM
Post#9



Posts: 16
Joined: 16-March 15



Good info thanks,

qryEngine which has calculated fields (the ones in the code above), is a query based on the tables tblAircraft one-to-many with tblEngine.
For example TTSN: (([ATTAF]+[ACTACH]-[TTT])+[ETT]-[ENEWACTTAF]) [It calculates the total time since new for the engine]
I retrieve the info in these fields to put into a new record in tblLog [the recordset opened], for the aircraft maintenance log entry.
I need the null values since I use it as a condition for hiding certain controls and labels on the log entry report.
Go to the top of the page
 
moke123
post Feb 8 2018, 04:56 AM
Post#10



Posts: 1,318
Joined: 26-December 12
From: Western Ma.,L.I.,N.Y.,Jupiter,Fl.



Its early in the AM and the coffee hasn't kicked in yet but are any of these values null? - (([ATTAF]+[ACTACH]-[TTT])+[ETT]-[ENEWACTTAF])
If so, bear in mind that a null + anything is null.

heres a good explanation - Nulls and their behavior
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    25th September 2018 - 06:01 AM