Another "Invalid use of Null"

K

KellyS

I read somewhere that you cannot use any data type other than Variant to have
a null value, not String, Date, or anything. So, how do you have a "Null" in
a date field? My particular date is not necessary for all records and I do
not want anything to display in the textbox. Do you define the variable as a
"Date" or "Variant"?

I'm stumped. I have looked through the other posts and did not find anything
similar to my situation.

Thanks, in advance, for any help.
 
A

Allen Browne

You can store a Null value in a Date/Time field in Access.
You cannot store a Null value in a Date variable in VBA code.
 
P

peregenem

Allen said:
You cannot store a Null value in a Date variable in VBA code.
Click to expand...

It depends what you mean by 'Null'. A VBA variable of type Date can be
'empty':

Sub test()
Dim dDate As Date
MsgBox CStr(CBool(dDate = vbEmpty))

dDate = Now
MsgBox CStr(CBool(dDate = vbEmpty))

dDate = vbEmpty
MsgBox CStr(CBool(dDate = vbEmpty))
End Sub
 
D

Dirk Goldgar

It depends what you mean by 'Null'. A VBA variable of type Date can be
'empty':

Sub test()
Dim dDate As Date
MsgBox CStr(CBool(dDate = vbEmpty))

dDate = Now
MsgBox CStr(CBool(dDate = vbEmpty))

dDate = vbEmpty
MsgBox CStr(CBool(dDate = vbEmpty))
End Sub
Click to expand...

Not really; that's just a confusing type conversion:

dDate = vbEmpty
MsgBox Format(dDate, "long date")

dDate = CDate("30-Dec-1899 12:00:00 AM")
MsgBox Format(dDate, "long date") & " " & _
CLng(dDate) & " " & _
vbEmpty
 
P

peregenem

Dirk said:
Not really; that's just a confusing type conversion:

dDate = vbEmpty
MsgBox Format(dDate, "long date")

dDate = CDate("30-Dec-1899 12:00:00 AM")
MsgBox Format(dDate, "long date") & " " & _
CLng(dDate) & " " & _
vbEmpty
Click to expand...

I think I mean Empty - no matter.

An alternative is to use an ADO object:

Sub NullableDates()

' Single value
Dim oDate As ADODB.Parameter

Set oDate = New ADODB.Parameter
oDate.Type = adDate

MsgBox CStr(CBool(oDate.Value = Empty))

oDate.Value = Now
MsgBox CStr(CBool(oDate.Value = Empty))

oDate.Value = Empty
MsgBox CStr(CBool(oDate.Value = Empty))

' Array values
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
With rs
.Fields.Append "my_date", adDate, , adFldIsNullable
.Open
.AddNew "my_date", Empty
.AddNew "my_date", CDate("2005-10-01")

.MoveFirst
MsgBox CStr(CBool(.Fields("my_date").Value = Empty))

.MoveNext
MsgBox CStr(CBool(.Fields("my_date").Value = Empty))

.Fields("my_date").Value = Empty
MsgBox CStr(CBool(.Fields("my_date").Value = Empty))
End With

End Sub

Probably the best way is to use a date with an actual value but one
that is implausible in the data model, preferably an unambiguous one
(i.e. for format dd/mm/ccyy, where the dd element is greater than 12
and the yy element is greater than 31).
 
R

RoyVidar

[email protected] wrote in message
I think I mean Empty - no matter.

An alternative is to use an ADO object:

Sub NullableDates()

' Single value
Dim oDate As ADODB.Parameter

Set oDate = New ADODB.Parameter
oDate.Type = adDate

MsgBox CStr(CBool(oDate.Value = Empty))

oDate.Value = Now
MsgBox CStr(CBool(oDate.Value = Empty))

oDate.Value = Empty
MsgBox CStr(CBool(oDate.Value = Empty))

' Array values
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
With rs
.Fields.Append "my_date", adDate, , adFldIsNullable
.Open
.AddNew "my_date", Empty
.AddNew "my_date", CDate("2005-10-01")

.MoveFirst
MsgBox CStr(CBool(.Fields("my_date").Value = Empty))

.MoveNext
MsgBox CStr(CBool(.Fields("my_date").Value = Empty))

.Fields("my_date").Value = Empty
MsgBox CStr(CBool(.Fields("my_date").Value = Empty))
End With

End Sub

Probably the best way is to use a date with an actual value but one
that is implausible in the data model, preferably an unambiguous one
(i.e. for format dd/mm/ccyy, where the dd element is greater than 12
and the yy element is greater than 31).
Click to expand...

The help file states the following on empty:
"Indicates that no beginning value has been assigned to a Variant
variable. An Empty variable is represented as 0 in a numeric context
or a zero length string ("") in a string context"

Why should one then be surprised when comparing 0 (a numeric)
with empty and get True?

Using a magic date is not something I'd do. I'd prefer Null for
unknown or input actual dates.
 
B

Brendan Reynolds

While you can not assign a Null value to a Date/Time *variable*, you can
store a Null value in a Date/Time *field* provided it is not a required
field. You should define your field as a Date/Time field. When you need to
assign the value of that field to a variable, you can ...

1) Use a query to select only records that do not contain Null values in the
field.

2) Use the NZ or IIF functions to return a default value when the field is
Null.

3) Use the IsNull function to check for Null values before attempting to
assign values to a Date/Time variable.

4) Use a Variant variable.

All four techniques have appropriate uses.
 
S

Student Databaser

I am getting this same error, "invalid use of Null" when importing data using
an import form. I know nothing about VBA and only a little about SQL, but
our programmer is not here so i have to figure it out. So i thought i would
give this a shot. From what i've read i understand that this error is a
result of a problem with the format of dates so I think the error lies
somewhere within this VBA code:

If Len(Trim(imprecSet.Fields("date_of_registration"))) = 0 Then
stcoRecSet.Fields("date_of_registration") = " "
Else
stcoRecSet.Fields("date_of_registration") =
Trim(imprecSet.Fields("date_of_registration"))
End If

This is all Greek to me so any help would be appreciated.

Thanks!

Lindsay
 
S

Stefan Hoffmann

hi,

Student said:
If Len(Trim(imprecSet.Fields("date_of_registration"))) = 0 Then
stcoRecSet.Fields("date_of_registration") = " "
Else
stcoRecSet.Fields("date_of_registration") =
Trim(imprecSet.Fields("date_of_registration"))
End If

This is all Greek to me so any help would be appreciated.
Click to expand...
Try this:

If Len(Trim(imprecSet.Fields("date_of_registration") & " ")) = 0 Then
stcoRecSet.Fields("date_of_registration") = " "
Else
stcoRecSet.Fields("date_of_registration") = _
Trim(imprecSet.Fields("date_of_registration") & " ")
End If


mfG
--> stefan <--
 
A

amdrit

I think you may want to make use of IsNull. This will allow you to test if
the data is null before attempting to assign it to your destination.

Now, your invalid use of null issue might becomming from the first time you
attempt to call trim() or len(). Since you can do niether to a null value,
that might explain where the actuall errors is coming from.

So if you were to test for a null value prior to any other operation, you
should be fine. So I would propose you add one more level of nesting to
your if statement.

'''' Test for null values
If IsNull(imprecSet.Fields("date_of_registration"))) then
stcoRecSet.Fields("date_of_registration") = " "

'''' Test for empty values
ElseIf Len(Trim(imprecSet.Fields("date_of_registration"))) = 0 Then
stcoRecSet.Fields("date_of_registration") = " "

'''' Use the trimmed value
Else
stcoRecSet.Fields("date_of_registration") =
Trim(imprecSet.Fields("date_of_registration"))
End If
 
D

Douglas J. Steele

Couple of issues.

It sounds as though the imprecSet.Fields("date_of_registration") is Null.
Len(Trim(Null)) returns Null, which isn't equal to 0, so you're always going
to the Else side of the If statement (and hence assigning a value of Null to
stcoRecSet.Fields("date_of_registration"))

Second, if stcoRecSet.Fields("date_of_registration") is a Date field, you
cannot assign " " to it: dates are numbers, not strings.

To get around the first problem, try

If IsNull(imprecSet.Fields("date_of_registration")) Then

or

If Len(Trim(imprecSet.Fields("date_of_registration") & vbNullString)) = 0
Then

To get around the second problem, you'll need to pick a date that will never
occur naturally and use it (using 0, which equates to 30 Dec, 1899, is often
done). On the other hand, what's wrong with having a Null date in
stcoRecSet?
 
S

Student Databaser

I tried this and got the same error. Yesterday i updated a table that is
related to the one that this import form is for. Could the new records in
the related table be doing something? When i try to enter data that is
related to older records in the related table it works just fine.
 
S

Student Databaser

I changed a couple things as suggested. Now i don't get an error, but it
doesn't work either. I think amdrit might be right and the error is coming
from trim() or len(). So i am going to post the whole thing and then see if
someone can fix it because i don't know what else to do.


On Error GoTo Err_imp_Registration_Click

Dim con1 As ADODB.Connection

Dim imprecSet As ADODB.Recordset
Dim stuRecSet As ADODB.Recordset
Dim coRecSet As ADODB.Recordset
Dim stcoRecSet As ADODB.Recordset

Dim impstrSql As String
Dim stuStrSql As String
Dim coStrSql As String
Dim stcoStrSql As String
Dim reccount As Integer
Dim updreccount As Integer
Dim newreccount As Integer
Dim noProcRecCount As Integer
Dim courseCost As Integer

Dim strOutPut As String

Dim impStatus As Boolean
Dim stuStatus As Boolean
Dim stuCoStatus As Boolean
Dim coStatus As Boolean

Set con1 = CurrentProject.Connection

reccount = 0
updreccount = 0
newreccount = 0
noProcRecCount = 0

Me.Form.Results = " "
Me.Form.Results = "Begin Importing " + vbCrLf + vbCrLf

impstrSql = "SELECT * FROM import_registration"

Set imprecSet = New ADODB.Recordset
imprecSet.CursorType = adOpenKeyset
imprecSet.LockType = adLockOptimistic
imprecSet.CursorLocation = adUseClient

imprecSet.Open impstrSql, con1

'***** Process Data from Import_Registration

Do Until imprecSet.EOF

stuStatus = False
stuCoStatus = False
coStatus = False
impStatus = False

If imprecSet.Fields("student_id") = " " Then
impStatus = False
nullRecCount = nullRecCount + 1
Me.Form.Results = Me.Form.Results + _
" Null Student Id " + _
Trim(imprecSet.Fields("firstname")) + " , " + _
Trim(imprecSet.Fields("lastname")) + vbCrLf + vbCrLf

ElseIf Not IsNull(Trim(imprecSet.Fields("student_id"))) Then

Me.Form.Results = Me.Form.Results + _
Left(Trim(imprecSet.Fields("student_id")), 8) + "
" + _
imprecSet.Fields("lastname") + " " + _
imprecSet.Fields("firstname")
'******* Check the student

stuStrSql = "SELECT * FROM STUDENT WHERE " & _
"LCase(student_id) = " & "'" &
LCase(Trim(imprecSet.Fields("student_id"))) + "'" '& _
'" AND UCase(Trim(firstname)) = " & "'" &
UCase(Trim(imprecSet.Fields("firstname"))) & "'" & _
'" AND UCase(Trim(lastname)) = " & "'" &
UCase(Trim(imprecSet.Fields("lastname"))) & "'"


Set stuRecSet = New ADODB.Recordset
stuRecSet.CursorType = adOpenKeyset
stuRecSet.LockType = adLockOptimistic
stuRecSet.CursorLocation = adUseClient

stuRecSet.Open stuStrSql, con1

If stuRecSet.RecordCount = 0 Then
'******* Student Processing
Me.Form.Results = Me.Form.Results + "Not In STUDENT table"
stuStatus = False
Else
stuStatus = True
End If

stuRecSet.Close

'****** Check course_offerings

coStrSql = "SELECT * FROM COURSE_OFFERINGS WHERE
UCase(Trim(offering_id)) = " & _
"'" & UCase(Trim(imprecSet.Fields("offering_id"))) & "'"

Set coRecSet = New ADODB.Recordset
coRecSet.CursorType = adOpenKeyset
coRecSet.LockType = adLockOptimistic
coRecSet.CursorLocation = adUseClient

coRecSet.Open coStrSql, con1

Me.Form.Results = Me.Form.Results +
Trim(imprecSet.Fields("offering_id"))

If coRecSet.RecordCount = 0 Then
coStatus = False
Me.Form.Results = Me.Form.Results + " Course not in
course_offering "
Else
coStatus = True
Do Until coRecSet.EOF
courseCost = coRecSet.Fields("course_cost")
coRecSet.MoveNext
Loop
End If

coRecSet.Close

If stuStatus And coStatus Then
stcoStrSql = "SELECT * FROM STUDENT_COURSE WHERE " & _
" LCase(student_id) = " & "'" &
LCase(Trim(imprecSet.Fields("student_id"))) & "'" & _
" AND UCase(offering_id) = " & "'" &
UCase(Trim(imprecSet.Fields("offering_id"))) & "'"
Set stcoRecSet = New ADODB.Recordset
stcoRecSet.CursorType = adOpenKeyset
stcoRecSet.LockType = adLockOptimistic
stcoRecSet.CursorLocation = adUseClient
stcoRecSet.Open stcoStrSql, con1

If stcoRecSet.RecordCount = 0 Then
Me.Form.Results = Me.Form.Results + " New Processed
" + vbCrLf + vbCrLf
stcoRecSet.AddNew

stcoRecSet.Fields("student_id") =
LCase(Left(Trim(imprecSet.Fields("student_id")), 8))
stcoRecSet.Fields("mphtc_id") =
Trim(imprecSet.Fields("mphtc_id"))
stcoRecSet.Fields("firstname") =
Left(Trim(imprecSet.Fields("firstname")), 50)
stcoRecSet.Fields("lastname") =
Left(Trim(imprecSet.Fields("lastname")), 50)
stcoRecSet.Fields("offering_id") =
UCase(Left(Trim(imprecSet.Fields("offering_id")), 50))

If Len(Trim(imprecSet.Fields("date_of_registration"))) = 0 Then
stcoRecSet.Fields("date_of_registration") = " "
Else
stcoRecSet.Fields("date_of_registration") =
Trim(imprecSet.Fields("date_of_registration"))
End If

If Len(Trim(imprecSet.Fields("payment_method"))) = 0 Then
stcoRecSet.Fields("payment_method") = " "
Else
stcoRecSet.Fields("payment_method") =
Left(Trim(imprecSet.Fields("payment_method")), 50)
End If

If Len(Trim(imprecSet.Fields("registration_status"))) = 0 Then
stcoRecSet.Fields("registration_status") = " "
Else
stcoRecSet.Fields("registration_status") =
Left(Trim(imprecSet.Fields("registration_status")), 50)
End If

If Len(Trim(imprecSet.Fields("amt_paid"))) = 0 Then
stcoRecSet.Fields("amt_paid") = 0
Else
stcoRecSet.Fields("amt_paid") =
Trim(imprecSet.Fields("amt_paid"))
End If

stcoRecSet.Fields("amt_due") = courseCost -
stcoRecSet.Fields("amt_paid")

If Len(Trim(imprecSet.Fields("scholarship_aprd"))) > 0 And _
UCase(Trim(imprecSet.Fields("scholarship_aprd"))) = "T" Then
stcoRecSet.Fields("scholarship_aprd") = 1
Else
stcoRecSet.Fields("scholarship_aprd") = 0
End If

stcoRecSet.Update
newreccount = newreccount + 1
impStatus = True
Else
Me.Form.Results = Me.Form.Results + " Update Processed
" + vbCrLf + vbCrLf
Do Until stcoRecSet.EOF
stcoRecSet.Fields("mphtc_id") =
Trim(imprecSet.Fields("mphtc_id"))
If Len(Trim(imprecSet.Fields("date_of_registration") & " "))
= 0 Then
stcoRecSet.Fields("date_of_registration") = " "
Else
stcoRecSet.Fields("date_of_registration") =
Trim(imprecSet.Fields("date_of_registration") & " ")
End If

If Len(Trim(imprecSet.Fields("payment_method"))) = 0 Then
stcoRecSet.Fields("payment_method") = " "
Else
stcoRecSet.Fields("payment_method") =
Left(Trim(imprecSet.Fields("payment_method")), 50)
End If

If Len(Trim(imprecSet.Fields("registration_status"))) = 0 Then
stcoRecSet.Fields("registration_status") = " "
Else
stcoRecSet.Fields("registration_status") =
Left(Trim(imprecSet.Fields("registration_status")), 50)
End If

If Len(Trim(imprecSet.Fields("amt_paid"))) = 0 Then
stcoRecSet.Fields("amt_paid") = 0
Else
stcoRecSet.Fields("amt_paid") =
Trim(imprecSet.Fields("amt_paid"))
End If

stcoRecSet.Fields("amt_due") = courseCost -
stcoRecSet.Fields("amt_paid")

If Len(Trim(imprecSet.Fields("scholarship_aprd"))) > 0 And _
UCase(Trim(imprecSet.Fields("scholarship_aprd"))) = "T" Then
stcoRecSet.Fields("scholarship_aprd") = 1
Else
stcoRecSet.Fields("scholarship_aprd") = 0
End If

stcoRecSet.Update
impStatus = True
updreccount = updreccount + 1
stcoRecSet.MoveNext
Loop
End If

stcoRecSet.Close

Else
Me.Form.Results = Me.Form.Results + " NOT PROCESSED " + vbCrLf +
vbCrLf
noProcRecCount = noProcRecCount + 1
End If


Else
impStatus = False
nullRecCount = nullRecCount + 1
Me.Form.Results = Me.Form.Results + _
" Null Student Id " + _
Trim(imprecSet.Fields("firstname")) + " , " + _
Trim(imprecSet.Fields("lastname")) + vbCrLf + vbCrLf
End If

reccount = reccount + 1

If impStatus Then
imprecSet.Delete
End If

imprecSet.MoveNext
Loop

Me.Form.Results = Me.Form.Results + vbCrLf

Me.Form.Results = Me.Form.Results + "Total Number of Null Student Id " & _
" Records Not Processed : " + Str(nullRecCount) + vbCrLf

Me.Form.Results = Me.Form.Results + "Total Number of Records Not
Processed Due to " & _
" Invalid Student or Offering : " +
Str(noProcRecCount) + vbCrLf

Me.Form.Results = Me.Form.Results + "Total Number of Student_Course
Records Inserted " & _
"Completely : " + Str(newreccount) + vbCrLf

Me.Form.Results = Me.Form.Results + "Total Number of Student_Course
Records Updated " & _
"Completely : " + Str(updreccount) + vbCrLf

Me.Form.Results = Me.Form.Results + "Total Number of Import_Registration
Records Processed : " & _
Str(reccount) + vbCrLf + vbCrLf

Me.Form.Results = Me.Form.Results + "End Importing " + vbCrLf
imprecSet.Close
con1.Close
Exit_imp_Registration_Click:
Exit Sub

Err_imp_Registration_Click:
MsgBox Err.Description
Resume Exit_imp_Registration_Click
End Sub
 
L

Larry Linson

Student Databaser said:
It doesn't specify.
Click to expand...

Open any module, and in the module window, on the menu, Tools | Options,
specify Break on all Errors. Then it will take you to the line of code
where the error occurred and highlight it.

Once you solve your development problems, Break on Unhandled Errors is the
usual option for production. If you run a production app which includes
some error handling for "expected errors", but forget and leave Break on all
Errors, you can get some surprises.

Larry Linson
Microsoft Office Access MVP
 
A

amdrit

You cannot do anything with imprecSet.Fields("student_id") if it is null,
even testing for if imprecSet.Fields("student_id") = "" then would be an
issue until you test for null first.

Since null is your culprit issue here. start your logic with:

if isnull(imprecSet.Fields("student_id") ) then
elseif .... then
else
end if

This way no processing is attempted on a null value.
 
Top