Null Date (1 Viewer)

[saross]

Hi all.

I'm copying the record from one table (shown in a form) over to another table. This is the code I've done (no doubt you'll all be disgusted at it... I've only learned off these forums really so I know I don't code correctly!)

lngTenderID = Me.TenderID.Value
strJobNo = vbNullString & Me.JobNo.Value
intOrgID = Me.OrgID.Value
lngContactID = vbNullString & Me.ContactID.Value
intStaffID = vbNullString & Me.StaffID.Value
strTitle = Me.TenderTitle.Value
strSummary = vbNullString & Me.TenderSummary.Value
strMethodology = vbNullString & Me.TenderMethodology.Value
dtDateSubmitted = Me.DateSubmitted.Value

Set db = CurrentDb
Set rsNewContract = db.OpenRecordset("TBLContractInfo", dbOpenTable, dbAppendOnly)

With rsNewContract
    .AddNew
    [TenderID] = lngTenderID
    [ContractCode] = strJobNo
    [OrgID] = intOrgID
    [ContactID] = lngContactID
    [SDPContractManagerID] = intStaffID
    [ContractName] = strTitle
    [SummaryofWork] = strSummary
    [Methodology] = strMethodology
    [TenderSubmittedDate] = dtDateSubmitted
    [ContractConfirmedDate] = Date
    .Update
    lngContractID = [ContractID]
    .Close
End With

I put the vbNullStrings in because not all fields on the original form may be filled in and so I thought that would handle the null values - my problem is that it won't work for the Date type.

So my question is, how do I manage a potential Null value on the Date field/type?

Of course, any other observations and recommendations would be most gratefully received!

 

[ghudson]

You are breaking the rule against normalization by having the same record info in more than one table. There are examples on how to duplicate a record posted in other threads. Your method will depend on why you are trying to copy a record from one table to another. Sounds like a design flaw with your tables.

 

[KenHigg]

Can't you do this with a simple sql statement?

 

[saross]

Errr.. thanks ghudson. The information won't remain the same, it will be updated. It's converting a tender that's been submitted and won into a contract...

Can anyone explain how I work with the null value assigned to a date type?

 

[KenHigg]

Can you just if/then around it if the date is null?

 

[Jibbadiah]

Dim dbs As Database
Dim rst As Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM YOUR_TABLE;")

With rst
.MoveFirst

Do While Not rst.EOF

If IsNull(rst("Date")) Then
DO THIS
Else
'Date populated
DO THIS