NZ() function not working?

[EvanRosenlieb]

Hello all, you have helped me in the past, lets see if anyone knows what it going on here.



I have a form that uses a recordset to update a query. Among the fields that need entered are coordinate fields, which are Numeric Double fields. The coordinates must have a Y1 and X1 coordinate, and they may have a Y2 and X2 coordinate, but not necessarily.

This causes a 'data type conversion' error when either the Y2 or X2 fields are left empty, as it does not want to input a null value into the field.

Me understanding is that the Nz() function should be able to fix this, as it returns an empty string instead of Null. However I still get the same 'data type conversion" error

Here is my (selected) code:

Set rsBoxEntry = WorkingDB.OpenRecordset(Me.Year, dbOpenDynaset)
    For i = 1 To Me.HowMany
        rsBoxEntry.AddNew
             
                rsBoxEntry!Y1 = Nz(Me.Y1)
                rsBoxEntry!Y2 = Nz(Me.Y2)
                rsBoxEntry!X1 = Nz(Me.X1)
                rsBoxEntry!X2 = Nz(Me.X2)

For instance, if I have a number entered in the "Y1" field of my form, and not in the "Y2" field, when I try to save the records its pops up with the "data type conversion error" with the "Y2" line of code highlighted in the debugger. Am I using the Nz() function wrong?

Any help is appreciated.

--Evan

[RayMilhon]

NZ(ME.X1) returns an Empty String If x2 and y2 are Doubles then you'll get a data type conversion error. I would use Val(NZ(me.x2,"0")) Note that the Val() may not be necessary as NZ() returns a variant. I prefer to use the Val Or Str Functions that way I know when I look at the code Next Year What Value type I needed.

[EvanRosenlieb]

Dear Ray

Thanks for the help, I believe this may get me going in the right track. However there is a problem. As these are coordinate values, there is a very big difference between leaving a field blank and having a field equal to 0. If I use this Method, then 0's get entered into the Y2 and X2 fields, whereas it needs to be blank. Is there now way to do this?

--Evan

[boblarson]

there is a very big difference between leaving a field blank and having a field equal to 0. If I use this Method, then 0's get entered into the Y2 and X2 fields, whereas it needs to be blank. Is there now way to do this?

Just change the code to this:

 
Set rsBoxEntry = WorkingDB.OpenRecordset(Me.Year, dbOpenDynaset)
 
    For i = 1 To Me.HowMany
        rsBoxEntry.AddNew
 
                If Not IsNull(Me.Y1) Then rsBoxEntry!Y1 = Me.Y1
                If Not IsNull(Me.Y2) Then rsBoxEntry!Y2 = Me.Y2
                If Not IsNull(Me.X1) Then rsBoxEntry!X1 = Me.X1
                If Not IsNull(Me.X2) Then rsBoxEntry!X2 = Me.X2

[EvanRosenlieb]

Dear Bob,
Thanks for you help. I had tried messing around with coding like this before, however I still get the same error message. When I try to save data with a Y1 Coordinate but not a Y2 coordinate with this code, it gives me the "Data Type Conversion" error, with the underlined segment in the following code being highlighted.

If Not IsNull(Me.Y1) Then rsBoxEntry!Y1 = Me.Y1
If Not IsNull(Me.Y1) Then rsBoxEntry!Y2 = Me.Y2
If Not IsNull(Me.X1) Then rsBoxEntry!X1 = Me.X1
If Not IsNull(Me.X2) Then rsBoxEntry!X2 = Me.X2

--Evan

[EvanRosenlieb]

Bob, I figured it out, this is what the code needs to look like

If Not IsNull(Me.Y1) And Me.Y1 <> "" Then rsBoxEntry!Y1 = Me.Y1
If Not IsNull(Me.Y2) And Me.Y2 <> "" Then rsBoxEntry!Y2 = Me.Y2
If Not IsNull(Me.X1) And Me.X1 <> "" Then rsBoxEntry!X1 = Me.X1
If Not IsNull(Me.X2) And Me.X2 <> "" Then rsBoxEntry!X2 = Me.X2

I need to account for both null values and empty strings, as if the data entered tabs through the field without entering anything, then there will be an empty string, but if they use the mouse to click on another field without even clicking on the field, then it will be null. This covers both bases. I know you have helped me before, and you have helped me again. I appreciate it very much.

--Evan

[boblarson]

You could shorten it to this then:

If Len(Me.Y1 & vbNullString) > 0 Then rsBoxEntry!Y1 = Me.Y1
If Len(Me.Y2 & vbNullString) > 0 Then rsBoxEntry!Y2 = Me.Y2
If Len(Me.X1 & vbNullString) > 0 Then rsBoxEntry!X1 = Me.X1
If Len(Me.X2 & vbNullString) > 0 Then rsBoxEntry!X2 = Me.X2

Using Len and appending an empty string will test for both nulls and empty strings.