Problem with NULL values to Date AND Currency fields 1

[Syerston]

Would anyone have any idea why I can not save a null value to a date or currency field in MS Access. The required property of these fields have been set to no.

Below is a snippet of code to explain what I am doing.

'Current NAV
	If Len(Request.Form("Current NAV")) > 0 Then
		cCurrentNAV = ConvertString(Request.Form("Current NAV"))
	Else
	        cCurrentNAV = Empty			
	End If
	sSQL = sSQL & ", CurrentNAV = '" & cCurrentNAV & "'"

Now in place of empty I have tried Null and "" but to no avail. The variables are the placed into an Update SQL string and executed via a command object.

 

[DreXor]

Allow Null property in your table design view, right next to Allow zero length and required

 

[JamesLean]

You don't need the single quotes around the value for a start. Also, you should set the value = NULL, try this:

If Len(Request.Form("Current NAV")) > 0 Then
  cCurrentNAV = ConvertString(Request.Form("Current NAV"))
Else
  cCurrentNAV = "NULL"
End If

sSQL = sSQL & ", CurrentNAV = " & cCurrentNAV

--James

 

[Syerston]

I cannot find that option.

In fact as far as I can see date and currency fields do not even have Allow zero length.

 

[Syerston]

James you are a star and because of that you get one.