Insert null values into Date and Integer data type

[drPavlovic]

Hello guys,

Does anybode has any idea, of how to do that ? I can do it very easy in VB.NET, or C#, but in Access I give up.

So, I have to take data from the form , and send it to Sub.

With data type of String , I have no any problem, use Nz funciton, end everything, goes well.

But with Date and Integer, I can not find solution.

So :

Date

Dim PensionerFromDate as Date

PensionerFromDate = IIf(IsNull(Me.txtPDPensionerFromDate) = True, ????, Me.txtPDPensionerFromDate)

(instead of ???? i tried everything .. dbNull, vbEmpty, vbNull,sqldatenull, and somtimes it works but int the table stores "12/301899")

Integer

Dim CompanyID as integer
CompanyID = IIf(Me.cboCompany.Column(0) = 0, ????? , Me.cboCompany.Column(0))

I tried here instead of ????, tu insert "", ",," , " " , Cint("") itd. itd. . but nothing works.

Has anybody any idea ?

100 x thanks in advance

 

[mhartman]

Hello:

Please try

NULL, or CHR$(0) or VBNULLSTRING
Regards
Mark

 

[drPavlovic]

still Nothing

Thank you Mark, but

for first Case (null into Date ) , just vbNull pass code, but, in field save no NULL , but 12/31/1899

for second Case (null into Integer) , VBA treats VBNull as constant, and puts "1" into Integer field, instaed of NULL ..


... can't believe it ..

 

[mhartman]

Hello

Your statement below if it evaluates to TRUE, already has a null entry in it. Why would you want to change it?
'
Or am I misunderstanding your statement?

PensionerFromDate = IIf(IsNull(Me.txtPDPensionerFromDate) = True, ????, Me.txtPDPensionerFromDate)
'
Regards
Mark

 

[drPavlovic]

yes,

Mark, you're right .. I don't have to have " = True" ..

but anyhow, even if I put

PensionerFromDate = IIf(IsNull(Me.txtPDPensionerFromDate) , vbNull, Me.txtPDPensionerFromDate)

it stores some crazy date into the table. Not NULL

Now, I tried also with "IsDate" function ... but results are the same

PensionerFromDate = IIf(IsDate(Me.txtPDPensionerFromDate), Me.txtPDPensionerFromDate, vbNull) ... againg stores 12/30/1899.

But, I'm really intersted, what does access sends to code from the form ?

If i Just put : prmDate = Forms!Person!txtDate .. it works fine, in both case, if field txtDate empty or not .. hmmmmm

 

[drPavlovic]

Problem solved

Mark, Instead of using

Dim PensionerFromDate as Date

I used :

Dim PensionerFromDate as Variant !!!

and now, everything works fine

PensionerFromDate = IIf(IsNull(Me.txtPDPensionerFromDate) ,NULL, Me.txtPDPensionerFromDate)

Thanks a lot to Alan, from Forum :: http://www.utteraccess.com

 

[Pat Hartman]

As someone else already pointed out, there is no point to this statement. If the date is nul, you are storing null, otherwise you are storing a date. Why not just store whatever value is in the field?

 

[ChrisO]

Private Sub cmdTestIt_Click()
    Dim PensionerFromDate As Date [color=green]' <--- This is a Date data type (numeric type double)
                                  '      numeric data types can not accept a Null[/color]
    
    [color=green]' This line is reduntant but does return [b]vbNull[/b] not a Null if Me.txtPDPensionerFromDate is Null
    ' The compiler type casts the returned [b]vbNull[/b] not Null to [b]1[/b] not zero(0) on asignment.[/color]
    PensionerFromDate = IIf(IsNull(Me.txtPDPensionerFromDate), vbNull, Me.txtPDPensionerFromDate)
    
    [color=green]' [b]1[/b] not Zero(0) as a date is displayed as zero offset from Access start date.[/color]
    MsgBox PensionerFromDate [color=green]' <---   31st. of December 1899[/color]
    
    With CurrentDb.OpenRecordset("tblMyTable")
        .AddNew
        !MyDate = PensionerFromDate [color=green]' <--- [b]1[/b] not zero(0) is saved to the table.[/color]
        .Update                     [color=green]' and will be displayed as 31st. of December 1899[/color]
        .Close
    End With

End Sub

Edit for correction
The above assignment is returning 1 to the Date variable.
The 1 is coming from the numerical value of the constant vbNull.
Silly mistake on my part, sorry about that.