Weird, inconsistent null date error

[striker73]

I have a weird date/null error occuring in my database. I have a form that takes in values and stores them in a table. When I load another form, I want to move certain entries into a temporary table. My code is as follows

Private Sub Form_Load()

'### Find the appropriate entry ###
tempnum = DCount("*", "JobProposal", "[Job_ID] = " & Job_ID)

'### While there are entries to move over to the temp table ###
If (tempnum > 0) Then
Me.RecordSource = "SELECT * FROM JobProposal WHERE JobProposal.Job_ID = " & Job_ID
Me.Recordset.MoveFirst

'### Loop through entries ###
For i = 1 To tempnum
Dim InvoiceType_ID, ProposalStatus_ID, JobProposal_ID As Integer
Dim QuoteAmount As Long
Dim DateRFQ_Received, DateQuoteDue, DateQuoteSubmitted, PlannedTestDate, PlannedAwardDate As String
Dim PO_number As String

'### Store old table values in temporary variables ###
PlannedTestDate = Me.Recordset.Fields("PlannedTestDate&quot
PlannedAwardDate = Me.Recordset.Fields("PlannedAwardDate&quot

'#### Insert values into temp table #####
DoCmd.RunSQL "INSERT INTO TempTable (fieldnames) VALUES (values)

Next i


When I first store the entries into original table, I have the following code that stores "Null" if there wasn't anyhting entered:

Me.Recordset.Fields("PlannedAwardDate&quot = IIf(txtPlannedAwardDate = "", Null, txtPlannedAwardDate)


When I open the second form, and the code loops through fields, PlannedTestDate is null and is stored in the string PlannedTestDate just fine. However, when it moves down to PlannedAwardDate, which is also null, it has issues: "invalid use of null." The two fields are used the exact same throughout the database and both are entered as null. I don't know why access is fine with 4 date fields being null, but the 5th is a problem.

Also, I have my variables declared inside my loop, so theoretically they will have been re-declared once I go back through the loop, however, they retain their values from the previous loop. What's going on with this?

I know this is complicated, but does anyone have any ideas? Thanks!

 

[Steve101]

Striker,

See my thread thread705-252517. I think that this is your problem.

In a nutshell, the line of code:

Dim a,b,c as String

is only declaring the variable c as a string NOT a and b, which are being declared as variants. To declare all as Strings, you would need to say:

Dim a as String, b as String, c as String

or declare them on separate lines as strings (preferable practice.)

At any rate, in your case, because PlannedAwardDate is being declared as a string, and the preceding variables are not (even if you think they are - see above), you're having the problem.

A String variable cannot take on a Null value. If you want to test what I'm saying, create a new function, with the following lines:

Dim a,b as string
a = null
b = null

Run this it will fail on the line b assignment. First assignment succeeds, as its interpreted as a type Variant variable.

Replace the top line with Dim a as String, b as String

will fail at runtime on the line a assignment

Replacing the top line with
Dim a, b

will run without problem, as both variables are variants.

Short fix in your case is to simply remove the "As String" bit from the
Dim DateRFQ_Received, DateQuoteDue, ....., PlannedAwardDate As String
line.

Hope this explains it,
Cheers,
Steve

 

[striker73]

Thank you so much!! That was exactly what it was. It's funny because I usually define my variables all on separate lines, but this time I thought I would try it out, but I didn't think of that when I was trying to figure out what was going on. Thanks again, you're the best!