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
> Cater For Null Values, Access 2016    
 
   
mike60smart
post Aug 16 2019, 12:07 PM
Post#1


UtterAccess VIP
Posts: 13,369
Joined: 6-June 05
From: Dunbar,Scotland


HI everyone

I have the following code that works just fine. However one of the Controls sometimes does not have any data.

The Control is named "CMSV"

How would I modify the code to cater for this?

Any help appreciated.

CODE
strSQL = " UPDATE tblPayrollVar SET OTFine = " & lngOTFine & ", Arrears = " & lngArrears _
& ", OTAppreciation = " & lngOTAppreciation & ",[CMSVRemarks] = '" & Me.CMSV _
& "' WHERE EmployeeID = " & Me!EmployeeID & ";"
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
DanielPineault
post Aug 16 2019, 12:13 PM
Post#2


UtterAccess VIP
Posts: 6,853
Joined: 30-June 11



What about something along the lines of (untested aircode)

CODE
[CMSVRemarks] = " & IIF(IsNull(Me.CMSV), NULL, "'" & Me.CMSV &"'")

--------------------
Daniel Pineault (2010-2019 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
mike60smart
post Aug 16 2019, 12:24 PM
Post#3


UtterAccess VIP
Posts: 13,369
Joined: 6-June 05
From: Dunbar,Scotland


Hi Danielle

I changed the code to this:-

CODE
10            On Error GoTo cmdInsertVar_Click_Error
      Dim strSQL As String
      Dim lngEmployeeID As Long
      Dim lngOTFine As Long
      Dim lngArrears As Long
      Dim lngOTAppreciation As Long
      Dim strCMSVRemarks As String

20    lngEmployeeID = Me.EmployeeID
30    lngOTFine = Me.OTF
40    lngArrears = Me.AA
50    lngOTAppreciation = Me.O
60    strCMSVRemarks = Me.CMSV




70       strSQL = "INSERT INTO tblPayRollVar (EmployeeID, PayrollMonth, PayrollYear) " & vbCrLf _
                 & "Values (" & Me.EmployeeID & ", '" & Me.txtPaymentMonth & "', '" & Me.txtPayrollYear & "');"
80        Debug.Print strSQL
90        CurrentDb.Execute strSQL, dbFailOnError

100   strSQL = " UPDATE tblPayrollVar SET OTFine = " & lngOTFine & ", Arrears = " & lngArrears _
      & ", OTAppreciation = " & lngOTAppreciation & ",[CMSVRemarks] = " & IIf(IsNull(Me.CMSV), Null, "'" & Me.CMSV & "'") _
      & " WHERE EmployeeID = " & Me!EmployeeID & ";"
110   Debug.Print strSQL
120   CurrentDb.Execute strSQL, dbFailOnError

130   MsgBox "All Salaries Inserted for Month Selected", vbInformation


Now get the following error:-

Attached File  error.JPG ( 13.43K )Number of downloads: 0

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
DanielPineault
post Aug 16 2019, 12:31 PM
Post#4


UtterAccess VIP
Posts: 6,853
Joined: 30-June 11



Instead try

CODE
[CMSVRemarks] = " & IIF(IsNull(Me.CMSV), "NULL", "'" & Me.CMSV &"'")

--------------------
Daniel Pineault (2010-2019 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
DanielPineault
post Aug 16 2019, 12:34 PM
Post#5


UtterAccess VIP
Posts: 6,853
Joined: 30-June 11



From what I can quickly see you're not even using that variable so just comment it out. If you truly which to assign variables, then since it will have to handle Null values you'd need to Dim it as a Variant.

--------------------
Daniel Pineault (2010-2019 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
RJD
post Aug 16 2019, 12:35 PM
Post#6


UtterAccess VIP
Posts: 10,028
Joined: 25-October 10
From: Gulf South USA


Hi Mike: I would have done something like ...

& ", OTAppreciation = " & lngOTAppreciation & ",[CMSVRemarks] = '" & NZ(Me.CMSV,'No Remarks') _

Might be worth a try.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
mike60smart
post Aug 16 2019, 12:48 PM
Post#7


UtterAccess VIP
Posts: 13,369
Joined: 6-June 05
From: Dunbar,Scotland


Hi Joe

Changed the code to this:-

CODE
100   strSQL = " UPDATE tblPayrollVar SET OTFine = " & lngOTFine & ", Arrears = " & lngArrears _
      & ", OTAppreciation = " & lngOTAppreciation & ",[CMSVRemarks] = '" & Nz(Me.CMSV, "No Remarks") _
      & " WHERE EmployeeID = " & Me!EmployeeID & ";"
110   Debug.Print strSQL
120   CurrentDb.Execute strSQL, dbFailOnError


Get the same error referencine Line 60

The Immediate window shows this:-

UPDATE tblPayrollVar SET OTFine = 0, Arrears = 0, OTAppreciation = 200,[CMSVRemarks] = '') WHERE EmployeeID = 1;

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
DanielPineault
post Aug 16 2019, 12:50 PM
Post#8


UtterAccess VIP
Posts: 6,853
Joined: 30-June 11



QUOTE
Get the same error referencine Line 60

See my earlier comment https://www.UtterAccess.com/forum/index.php...t&p=2726864

--------------------
Daniel Pineault (2010-2019 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
dale.fye
post Aug 16 2019, 12:59 PM
Post#9



Posts: 160
Joined: 28-March 18
From: Virginia


How about:
CODE
100   strSQL = "UPDATE tblPayrollVar " _
                    & "SET OTFine = " & lngOTFine & ", Arrears = " & lngArrears _
                    & ", OTAppreciation = " & lngOTAppreciation
         if NOT ISNull(me.CMSV) Then
             strSQL = strSQL & ",[CMSVRemarks] = '" & me.CMSV & "'"
         end if
         strSQL = strSQL & " WHERE EmployeeID = " & Me!EmployeeID & ";"

--------------------
Dale Fye
Microsoft Access MVP 2013-2016
Developing Solutions, LLC
Go to the top of the page
 
mike60smart
post Aug 18 2019, 09:35 AM
Post#10


UtterAccess VIP
Posts: 13,369
Joined: 6-June 05
From: Dunbar,Scotland


Hi Dale

That updates ALL records in the underlying table

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
DanielPineault
post Aug 18 2019, 10:01 AM
Post#11


UtterAccess VIP
Posts: 6,853
Joined: 30-June 11



QUOTE
That updates ALL records in the underlying table

The WHERE statement still applies, so it will only update the specific EmployeeID. Did you Debug.Print the strSQL? What do you get?


Did you ever try commenting out the line or changing the Dim as mentioned earlier?
CODE
    On Error GoTo cmdInsertVar_Click_Error
    Dim strSQL                As String
'    Dim lngEmployeeID         As Long 'Not used
    Dim lngOTFine             As Long
    Dim lngArrears            As Long
    Dim lngOTAppreciation     As Long
'    Dim strCMSVRemarks        As String 'Not used, but should be Variant as it can be NULL

'    lngEmployeeID = Me.EmployeeID 'Not used
    lngOTFine = Me.OTF
    lngArrears = Me.AA
    lngOTAppreciation = Me.O
'    strCMSVRemarks = Me.CMSV 'Not used


    strSQL = "INSERT INTO tblPayRollVar (EmployeeID, PayrollMonth, PayrollYear) " & vbCrLf _
             & "Values (" & Me.EmployeeID & ", '" & Me.txtPaymentMonth & "', '" & Me.txtPayrollYear & "');"
    Debug.Print strSQL
    CurrentDb.Execute strSQL, dbFailOnError

    strSQL = " UPDATE tblPayrollVar SET OTFine = " & lngOTFine & ", Arrears = " & lngArrears _
             & ", OTAppreciation = " & lngOTAppreciation & ",[CMSVRemarks] = " & IIf(IsNull(Me.CMSV), "NULL", "'" & Me.CMSV & "'") _
             & " WHERE EmployeeID = " & Me!EmployeeID & ";"
    Debug.Print strSQL
    CurrentDb.Execute strSQL, dbFailOnError

    MsgBox "All Salaries Inserted for Month Selected", vbInformation


Also, instead of calling CurrentDb multiple times, you'd be better served defining a variable and reusing it, or switching over to a self-healing variable. For instance:
CODE
    On Error GoTo cmdInsertVar_Click_Error
    Dim db                    As DAO.Database
    Dim strSQL                As String
    '    Dim lngEmployeeID         As Long
    Dim lngOTFine             As Long
    Dim lngArrears            As Long
    Dim lngOTAppreciation     As Long
    '    Dim strCMSVRemarks        As String

    '    lngEmployeeID = Me.EmployeeID
    lngOTFine = Me.OTF
    lngArrears = Me.AA
    lngOTAppreciation = Me.O
    '    strCMSVRemarks = Me.CMSV

    Set db = CurrentDb
    strSQL = "INSERT INTO tblPayRollVar (EmployeeID, PayrollMonth, PayrollYear) " & vbCrLf _
             & "Values (" & Me.EmployeeID & ", '" & Me.txtPaymentMonth & "', '" & Me.txtPayrollYear & "');"
    Debug.Print strSQL
    db.Execute strSQL, dbFailOnError

    strSQL = " UPDATE tblPayrollVar SET OTFine = " & lngOTFine & ", Arrears = " & lngArrears _
             & ", OTAppreciation = " & lngOTAppreciation & ",[CMSVRemarks] = " & IIf(IsNull(Me.CMSV), "NULL", "'" & Me.CMSV & "'") _
             & " WHERE EmployeeID = " & Me!EmployeeID & ";"
    Debug.Print strSQL
    db.Execute strSQL, dbFailOnError

    MsgBox "All Salaries Inserted for Month Selected", vbInformation
    Set db = Nothing

--------------------
Daniel Pineault (2010-2019 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
mike60smart
post Aug 18 2019, 10:18 AM
Post#12


UtterAccess VIP
Posts: 13,369
Joined: 6-June 05
From: Dunbar,Scotland


Hi Danielle

Many thanks for the additional help.

I am going to go with inserting the occasional Comments as a separate Event.

Many thanks yet again

cheers.gif

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
ADezii
post Aug 18 2019, 10:31 AM
Post#13



Posts: 2,641
Joined: 4-February 07
From: USA, Florida, Delray Beach


There were a couple of mistakes in the Coding, most notable of which would be the assigning of a possible NULL Value to a String Variable. Using Literals, I duplicated the same functionality, and to the best of my knowledge, achieved the desired Results. The key point was testing for both String and NULL Values in the [CMSV] Field. Your SQL also indicated that the Month and Year Fields are defined as String which is why I used them as such.The Code in it's current context appears to work quite well.
CODE
Dim strSQL As String
Dim lngEmployeeID As Long
Dim lngOTFine As Long
Dim lngArrears As Long
Dim lngOTAppreciation As Long
Dim varCMSVRemarks As Variant
Dim strPayrollMonth As String
Dim strPayrollYear As String

lngEmployeeID = 1234
lngOTFine = 667
lngArrears = 444
lngOTAppreciation = 123
strPayrollMonth = 9
strPayrollYear = 2019

'Test for both valid String and NULL Arguments
varCMSVRemarks = "REMARKS placed here"          'String
'varCMSVRemarks = Null                          'NULL

strSQL = "INSERT INTO tblPayRollVar (EmployeeID, PayrollMonth, PayrollYear) " & vbCrLf _
          & "Values (" & lngEmployeeID & ", '" & strPayrollMonth & "', '" & strPayrollYear & "');"
  
CurrentDb.Execute strSQL, dbFailOnError

strSQL = "UPDATE tblPayrollVar SET [OTFine] = " & lngOTFine & ", [Arrears] = " & lngArrears _
          & ", OTAppreciation = " & lngOTAppreciation & ", [CMSVRemarks] = '" & _
          IIf(IsNull(varCMSVRemarks), "", varCMSVRemarks) & "'" & _
          " WHERE EmployeeID = " & lngEmployeeID & ";"

CurrentDb.Execute strSQL, dbFailOnError

MsgBox "All Salaries Inserted for Month Selected", vbInformation

P.S. - If you would like to have the Demo, just let me know.
This post has been edited by ADezii: Aug 18 2019, 10:34 AM
Go to the top of the page
 
isladogs
post Aug 18 2019, 03:26 PM
Post#14


UtterAccess VIP
Posts: 1,607
Joined: 4-June 18
From: Somerset, UK


OK others have given you more complete answers but your code based on RJD's suggestion had a missing single quote before WHERE
I believe this is correct

CODE
strSQL = " UPDATE tblPayrollVar SET OTFine = " & lngOTFine & ", Arrears = " & lngArrears _
      & ", OTAppreciation = " & lngOTAppreciation & ",[CMSVRemarks] = '" & Nz(Me.CMSV, "No Remarks") _
      & "' WHERE EmployeeID = " & Me!EmployeeID & ";"


though I find this equivalent version easier to read and so avoid errors

CODE
strSQL = " UPDATE tblPayrollVar SET OTFine = " & lngOTFine & ", Arrears = " & lngArrears & ", _
     OTAppreciation = " & lngOTAppreciation & ",[CMSVRemarks] = '" & Nz(Me.CMSV, "No Remarks") & "' _
     WHERE EmployeeID = " & Me!EmployeeID & ";"

--------------------
Go to the top of the page
 
mike60smart
post Aug 18 2019, 04:10 PM
Post#15


UtterAccess VIP
Posts: 13,369
Joined: 6-June 05
From: Dunbar,Scotland


Hi Adezii

Yes please


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
ADezii
post Aug 18 2019, 05:18 PM
Post#16



Posts: 2,641
Joined: 4-February 07
From: USA, Florida, Delray Beach


Mike, simply substitute Field References for Literal Values in the Demo.
This post has been edited by ADezii: Aug 18 2019, 05:18 PM
Attached File(s)
Attached File  Cater_For_NULLs_Demo.zip ( 24.51K )Number of downloads: 2
 
Go to the top of the page
 
mike60smart
post Aug 19 2019, 07:27 AM
Post#17


UtterAccess VIP
Posts: 13,369
Joined: 6-June 05
From: Dunbar,Scotland


Hi Adezii

Many thanks that does just what is needed

Many thanks again
cheers.gif

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
ADezii
post Aug 19 2019, 07:27 AM
Post#18



Posts: 2,641
Joined: 4-February 07
From: USA, Florida, Delray Beach


yw.gif , Mike. In case you're interested, I revised the Demo to now reference Fields instead of Literals. Sounds like you have it already figured out, but I'll post it anyway. Good Luck with your Project.
Attached File(s)
Attached File  Cater_For_NULLs_Demo_2.zip ( 26.3K )Number of downloads: 2
 
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    21st September 2019 - 06:07 AM