UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How To Assign A Blank To A Textbox Control Having Date Field, Access 2016    
 
   
hemant
post May 18 2020, 01:14 PM
Post#1



Posts: 89
Joined: 19-February 20
From: Mumbai, India


I have a textbox control on a continuous form, which is bound to a Date field.

Based on some calculation either a Date has to be assigned that textbox OR needs to be kept blank. An example code is below. If I assign vbNull to the Date Control, it displays 31-12-1899 - b'cos vbNull assigns zero value to the field.

What is the way:
1. To really assign a Null value and NOT zero. Because this value in turn is used for some Min and Max calculation. And when it is used for Min calculation it ends up selecting the zero value (31-12-1899) and not the real minimum date being compared.
2. To display a blank control on the form. At the moment I am having a workaround by Conditionally Formatting the textbox control. But that does not eliminate the problem in point 1. And it also ends up flashing the conditionally hidden value for a split second when some calculations are going on in the background.

CODE
If Nz(CalculatedDate) = 0 Then
     Me.tbCalDate  = vbNull        ' THIS ASSIGNS 0 value and hence displays 31-12-1899 on the form
Else
     Me.tbCalDate = CalculatedDate
End If
Go to the top of the page
 
June7
post May 18 2020, 01:22 PM
Post#2



Posts: 1,533
Joined: 25-January 16
From: The Great Land


I've never used vbNull, just:

Me.tbCalDate = Null

vbNull appears to be an intrinsic constant that has a value of 1. Check this in VBA immedidate window:

?vbNull
returns 1

Now try
?Null

This post has been edited by June7: May 18 2020, 01:25 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
hemant
post May 18 2020, 11:17 PM
Post#3



Posts: 89
Joined: 19-February 20
From: Mumbai, India


Cool. It worked. Thanks.
Go to the top of the page
 
hemant
post May 31 2020, 06:46 AM
Post#4



Posts: 89
Joined: 19-February 20
From: Mumbai, India


Coming back to this topic..

I was trying to assign Null to a Variable that is declared as Date, and I get error Invalid use of Null in the code below.

CODE
    Dim ActMaxDt As Date, MaxDt As Date
    Dim ProjPlanStDt As Date, ProjPlanEndDt As Date, ProjExpEndDt As Date
    Dim ProjActStDt As Date, PrActEndDt As Date

        '.... code

    If Nz(rst2!MinOfPlanStDt) <> 0 Then
        ProjPlanStDt = rst2!MinOfPlanStDt
    Else
        ProjPlanStDt = Null    
    End If

    ActMaxDt = Nz(rst2!MaxOfActEndDt)
    If Nz(ActMaxDt) <> 0 And Nz(ProjComplete) = 1 Then
        PrActEndDt = rst2!MaxOfActEndDt
    Else
        PrActEndDt = Null    <<<< ERROR 94 - Invalid use of Null
    End If

This post has been edited by hemant: May 31 2020, 06:49 AM
Go to the top of the page
 
hemant
post May 31 2020, 07:08 AM
Post#5



Posts: 89
Joined: 19-February 20
From: Mumbai, India


resolved. Need to set the variable as Variant.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    13th July 2020 - 01:23 AM