Solved

How to set a numeric field and date field as Null

Posted on 2004-04-22
20
549 Views
Last Modified: 2008-03-10
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
Comment
Question by:LakshmanaRavula
  • 9
  • 7
  • 4
20 Comments
 

Expert Comment

by:ppaldus
ID: 10893175
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
 

Author Comment

by:LakshmanaRavula
ID: 10893202
when I use
Test = null

I get an error:  Invalid use of null.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10893362
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

Expert Comment

by:ppaldus
ID: 10893382
Is test a field or variable? How/where are you setting Test to null?
0
 

Author Comment

by:LakshmanaRavula
ID: 10893503
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
 

Expert Comment

by:ppaldus
ID: 10893699
if WorkDays is a variable, it should be declared as a variant data type to accept a null value.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10893782
Make for a numeric field sure that the "Required" property is set to "No" to allow Null values in the table.

Nic;o)
0
 

Author Comment

by:LakshmanaRavula
ID: 10893791
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
 

Author Comment

by:LakshmanaRavula
ID: 10893804
NICO-
The "Required" property is set to "No"
Error remains
0
 

Accepted Solution

by:
ppaldus earned 125 total points
ID: 10893854
You need to change the return data type to variant.

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

Author Comment

by:LakshmanaRavula
ID: 10893917
Thanks Ppaldus
I changed it
But  it is giving me an compile error in query expression
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10893934
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
 
LVL 54

Expert Comment

by:nico5038
ID: 10893955
Make sure field2 has the required set to No

Nic;o)
0
 

Author Comment

by:LakshmanaRavula
ID: 10894059
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
 
LVL 54

Expert Comment

by:nico5038
ID: 10894312
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
 

Author Comment

by:LakshmanaRavula
ID: 10962790
I could not find out the exact problem some times it is giveing me error and some times it is working
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10962898
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
 

Author Comment

by:LakshmanaRavula
ID: 10962938
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
 
LVL 54

Expert Comment

by:nico5038
ID: 10962984
Here's all there is to know:

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

Nic;o)

0
 

Author Comment

by:LakshmanaRavula
ID: 10963048
Thank You
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question