Update to complete even without a value in a textbox

Fordraiders
Fordraiders used Ask the Experts™
on
trying to update a record in sql server
getting error message when a field is blank  ( = null)


These are unbound  textboxes on a form.

R.Edit
' 13 FIELDS SKU INFORMATION
      R![SKU] = Nz(Me("SKU_FRM"), "") ' SKU
      R![QTY] = Nz(Me("QTY_FRM"), "") ' QTY
      R![TARGET_PRICE] = Nz(Me("TARGET_PRICE_frm"), "") ' TARGET_PRICE
      R![TARGET_GP] = Nz(Me("TARGET_GP_FRM"), "") ' TARGET_GP
      R![CURRENT_PRICE] = Nz(Me("CURRENT_PRICE_FRM"), "") ' CURRENT PRICE
      R![CURRENT_GP] = Nz(Me("CURRENT_GP_FRM"), "") 'CURRENT_GP
      R![VENDOR_GUIDELINE_GP] = Nz(Me("VENDOR_GUIDELINE_GP_FRM"), "") ' FLOOR_GP
      R![PRODUCT_DESCRIPTION] = Nz(Me("PRODUCT_DESCRIPTION_FRM"), "") 'PRODUCT_DESCRIPTION
      R![TARIFF_ITEM] = Nz(Me("TARIFF_ITEM_FRM"), "") '  TARIFF ITEM
      R![RENEWAL_SKU] = Nz(Me("RENEWAL_SKU_FRM"), "")  '  RENEWAL SKU
      R![RENEWAL_OLVP_QUOTENUMBER] = Nz(Me("RENEWAL_OLVP_QUOTENUMBER_FRM"), "") '  RENEWAL OLVP ITEM
      R![APPROVED_PRICE] = Nz(Me("APPROVED_PRICE_FRM"), "") ' APPROVED PRICE                                                                             error
      R![APPROVED_GP] = Nz(Me("APPROVED_GP_FRM"), "") ' APROVED GP                                                                                               error

R.Update

Open in new window


i thought  NZ would take care of a blank value ?


These 2 fields may not have data in the textbox,
but i still need the code to finish the .update

Thanks
fordraiders
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

Commented:
These 2 fields may not have data in the textbox,

which 2 fields are you referring to?

in the case needed, you can do a IF to verify the values then to determine whether to update that particular field or not, like:

if isnull( Me("RENEWAL_OLVP_QUOTENUMBER_FRM") ) = false then
    R![RENEWAL_OLVP_QUOTENUMBER] = Nz(Me("RENEWAL_OLVP_QUOTENUMBER_FRM"), "")
end if
Distinguished Expert 2017
Commented:
Just FYI - "" is a ZLS (Zero Length STRING)  Strings CANNOT be placed into numeric fields.  Either your table allows null, in which case, you just store what came in on the form OR you change the NZ() to return a 0 when the field is null rather than a "".
John TsioumprisSoftware & Systems Engineer

Commented:
It would be a good idea to try pure SQL to see exactly what values are "expected"
INSERT INTO YourTable (Field1,Field2....Fieldn) VALUES ('Val1','Val2',...'Valn')

Open in new window

You can try it first on SSMS and then Action Passthrough query on Access
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Don't attempt to insert all those zero-length strings. Such are very uncommon.
Use Null or - if a numeric value is required - 0. Thus, for example:

      R![CURRENT_GP] = Nz(Me("CURRENT_GP_FRM"), 0) 'CURRENT_GP
      R![VENDOR_GUIDELINE_GP] = Me("VENDOR_GUIDELINE_GP_FRM") ' FLOOR_GP

Open in new window

Author

Commented:
approved price  =  currency
approved gp =  numeric


@all   would this work ???


If Len(Trim(Me.APPROVED_PRICE_FRM) & vbNullString) = 0 Then ' APPROVED PRICE
           R![APPROVED_PRICE] = 0
         Else
           R![APPROVED_PRICE] = Nz(Me("APPROVED_PRICE_FRM"), "")
      End If
     
      If Len(Trim(Me.APPROVED_GP_FRM) & vbNullString) = 0 Then ' ' APROVED GP
           R![APPROVED_GP] = 0
         Else
           R![APPROVED_GP] = Nz(Me("APPROVED_GP_FRM"), "")
      End If
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
No, Don't assign text ("") to numeric/currency fields:

      R![CURRENT_GP].Value = Nz(Me("CURRENT_GP_FRM"), 0) 'CURRENT_GP
      R![VENDOR_GUIDELINE_GP].Value = Nz(Me("VENDOR_GUIDELINE_GP_FRM"), 0) ' FLOOR_GP

Open in new window

Author

Commented:
thanks all,
Just did a recheck on the datatype fields and adjusted code as:
 
numeric   R![CURRENT_GP] = Nz(Me("CURRENT_GP_FRM"), 0) 'CURRENT_GP
currency :  R![approved_price] = Nz(Me("approved_price"), 0) 'approved price

text =    R![description] = Nz(Me("description"), "")

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial