please HELP me with This NULL !!!

[Corne]

If i want to save my values back into the database i get the following error:invalid use of null if one of my controls are empty.

I've tried everything?
What must i do?

I am using the following querry:

sql = "update Main set " & _
"Job_No = '" & txtJob.Text & "', " & _
"Cust = '" & txtCust.Text & "', " & _
"Site = '" & txtSite.Text & "', " & _
"Order_No = '" & txtOrder.Text & "', " & _
"Model = '" & txtModel.Text & "', " & _
"Prefix = '" & txtPrefix.Text & "', " & _
"Serial = '" & txtSerial.Text & "', " & _
"SMR = '" & txtSMR.Text & "', " & _
"DateOpen = '" & txtDateOpen.Text & "', " & _
"DateClosed = '" & txtDateClosed.Text & "', " & _
"DateStart & = '" & txtDateStart.Text & "', " & _
"DateEnd = '" & txtDateEnd.Text & "' , " & _
"Status = '" & chkStatus.Value & "' , " & _
"Remarks = '" & txtRemarks.Text & "'where idJob = " & _
intCurrJob

'execute the query
dbProgram.Execute sql

End If

[Mark Sreeves]

change AllowZeroLength in the design of your table to "yes"

[Corne]

Thanks that helped allot.

But what must i do if it it a date/time or a number field where the allowzerolenght is not supported.

Any help?

[smalig]

You can check in your values before updating or use decode function.

For sample:

decode(Text1.Text, NULL, '', Text1.Text)

------------------
smalig
smalig@hotmail.com
http://vbcode.webhostme.com/

[chrisjk]

I had this problem once and I found only Text fields caused it because the AllowZeroLength property was set to "No".

You should not have a problem with any other data type, but just to be sure, set fields that are Number, Date/Time etc to the following:

Required = No
Indexed = No

If they are indexed, then I think they have to have a value - this could be your problem.

Regards,

------------------
- Chris
chris.kilhams@btinternet.com
If it ain't broke - don't fix it :)

[netSurfer]

I use a function that I made:

Function nonulls(s As Variant) As String
' - string = NoNulls(string)
' Checks to see if the string passed is null, if so then return "" else return
' the passed string. A wrapper for access stuff.
If IsNull(s) Or Len(s) = 0 Then
nonulls = ""
Else
nonulls = s
End If
End Function

and I call it -- StrField = nonulls(txtTextBox.text)

Basically, it replaces a Null with a blank. It works very well with databases.

[smalig]

Sorry Corne, I have forgotten to specify it - DECODE function is a nonstandard SQL extension that is specific to Oracle. SQL Server's CASE function is a SQL extension that is part of the ANSI standard beyond Entry level and, therefore, not implemented in all database products.

In MS Access it's not working, I think.

What do you use?

------------------
smalig
smalig@hotmail.com
http://vbcode.webhostme.com/

[Corne]

Thanks for all your help!!!!
But i cant seem to win ....
any more help please???

Thanks.

ps. I sorted out strings but struggle with empty date and value controls to write it back into a database.(access database)

[Crazy D]

txt.Text = Iif(IsNUll(rs!MyField), "", rs!MyField)

[Corne]

I used the function netSurfer gave to me.
and it works very well.It converts a empty value to a zero.
Will it work with date values?
Is there no way that i can get empty textboxes.(value) not zero

Please help.

[netSurfer]

It should work with any value at all. You can modify it to look for a certain date if that's what you mean.
I'm not sure what you mean by:
empty textboxes.(value) not zero

If you mean you want the value of an empty text box, which is normally null, to be something other than ZERO, you could always replace it with a space. Is that what you mean? If so, in the code:

If IsNull(s) Or Len(s) = 0 Then
nonulls = ""
Else

replace the "" with " " and that would work. If this isn't what you meant, please explain futher.