• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 556
  • Last Modified:

How to set a numeric field and date field as Null

Hi,
I have a problem

If I set a text field null like this:

Test = ""

How can I set a numeric field to null?  An empty string doesn't work because field types aren't compatible.

Thank You
0
LakshmanaRavula
Asked:
LakshmanaRavula
  • 9
  • 7
  • 4
1 Solution
 
ppaldusCommented:
I suggest you use vbNullString instead of double quotes"".
eg. set a text field to null use
Test = vbNullstring

To set a numeric or date field to null use the keyword Null.
Test=Null
0
 
LakshmanaRavulaAuthor Commented:
when I use
Test = null

I get an error:  Invalid use of null.
0
 
nico5038Commented:
Using:
test = null
Will only work when the tablefield it's connected to allows a zero length.
Just check the fielddefinition in the table.
To have the field just empty you can use the
test = ""

Looks strange, but Null means "not there" and "" means "empty" and they are different for a database !

Nic;o)
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
ppaldusCommented:
Is test a field or variable? How/where are you setting Test to null?
0
 
LakshmanaRavulaAuthor Commented:
Greetings Nico:

I'm using this in a function and when I use NULL to assign to a numeric field it is giving me an error

either Vbnullstring is also giving me an error


    If IsNull(BegDate) Then
        WorkDays = NULL
        Exit Function
        End If
   
0
 
ppaldusCommented:
if WorkDays is a variable, it should be declared as a variant data type to accept a null value.
0
 
nico5038Commented:
Make for a numeric field sure that the "Required" property is set to "No" to allow Null values in the table.

Nic;o)
0
 
LakshmanaRavulaAuthor Commented:
Ihave written a function to calculate workdays and a append Query to insert the workdays in to a field.

Here is my total function


Public Function WorkDays(BegDate As Variant, EndDate As Variant) As Integer

    If IsNull(BegDate) Then
        WorkDays = Null
        Exit Function
        End If
    If IsNull(EndDate) Then
        WorkDays = Null
        Exit Function
        End If
    If BegDate > EndDate Then
        WorkDays = Null
        Exit Function
        End If
    If BegDate < #1/1/1980# Then
        WorkDays = Null
        Exit Function
        End If
    If BegDate = EndDate Then
        WorkDays = 0
        Exit Function
        End If

      Dim DateCnt As Variant
      DateCnt = BegDate
     
      Do While DateCnt < EndDate
         If Format(DateCnt, "ddd") <> "Sun" And Format(DateCnt, "ddd") <> "Sat" And _
            IsNull(DLookup("HoliDate", "Holidays", "[HoliDate]=#" & DateCnt & "#")) Then
         WorkDays = WorkDays + 1
         End If
         DateCnt = DateCnt + 1
      Loop
     
      If Format(EndDate, "ddd") = "Sun" Or Format(EndDate, "ddd") = "Sat" Or _
        Not IsNull(DLookup("HoliDate", "Holidays", "[HoliDate]=#" & EndDate & "#")) Then
        WorkDays = WorkDays - 1
        End If
       
   End Function

And this is my append Query

UPDATE TestNull SET TestNull.Field2 = WorkDays(BegDate, EndDate)
;


0
 
LakshmanaRavulaAuthor Commented:
NICO-
The "Required" property is set to "No"
Error remains
0
 
ppaldusCommented:
You need to change the return data type to variant.

Public Function WorkDays(BegDate As Variant, EndDate As Variant) As Variant
0
 
LakshmanaRavulaAuthor Commented:
Thanks Ppaldus
I changed it
But  it is giving me an compile error in query expression
0
 
nico5038Commented:
Yep, thought you were referring to a form field, but for a function that's indeed the way to get a Null value returned.
But just why activate a function when the data isn't OK ?
And why have both Null and 0 workdays ?

Nic;o)
0
 
nico5038Commented:
Make sure field2 has the required set to No

Nic;o)
0
 
LakshmanaRavulaAuthor Commented:
I only set 0 where Begdate = Enddate

The data is Ok some times there won't be enddate
Then I need Null in workdays.

Later I have to process for the field2 like Where Field2 = Null

I ve already set Field2 required = NO
0
 
nico5038Commented:
Looks to me you have the Workdays initially with a null value.
Just ignoring a Null begin and enddate in the queries WHERE clause will have the same effect as setting the Null workdays again to Null.

Nic;o)
0
 
LakshmanaRavulaAuthor Commented:
I could not find out the exact problem some times it is giveing me error and some times it is working
0
 
nico5038Commented:
Bit odd to see an answer follwed by "But  it is giving me an compile error in query expression"

Would have been better when you requested a moderator to delete/refund the question.

Nic;o)
0
 
LakshmanaRavulaAuthor Commented:
I don't this option Nico
if I don't get asatisfactory answer can i ask the moderator to delete my question?
If so How to ask to delete a question

Thank you for the suggestion
0
 
nico5038Commented:
Here's all there is to know:

http://www.experts-exchange.com/help.jsp#hs5

Nic;o)

0
 
LakshmanaRavulaAuthor Commented:
Thank You
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 9
  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now