Run time Error

  • Thread starter Thread starter John Thomas
  • Start date Start date
J

John Thomas

I have got the following module to convert dates within a
field:

Function Change_date16()

Dim wait As Database, rbk As Recordset

Set wait = DBEngine.Workspaces(0).Databases(0)
Set rbk = wait.OpenRecordset("OP WL CMDS (RBK02)")

rbk.MoveFirst
While Not rbk.EOF
LastDNAdate = Right$(rbk![LastDNAdate], 4) & Mid$(rbk!
[LastDNAdate], 3, 2) & Left$(rbk![LastDNAdate], 2)
rbk.Edit
rbk![LastDNAdate] = LastDNAdate
rbk.Update
rbk.MoveNext
Wend



End Function



The only problem is when I run this I get the following
run time error message:

Run Time Error '94':
Invalid Use of Null

I know the problem relates to there being no data in the
field but how do I get around this and run the module
successfully?
 
John said:
I have got the following module to convert dates within a
field:

Function Change_date16()
Dim wait As Database, rbk As Recordset

Set wait = DBEngine.Workspaces(0).Databases(0)
Set rbk = wait.OpenRecordset("OP WL CMDS (RBK02)")

rbk.MoveFirst
While Not rbk.EOF
LastDNAdate = Right$(rbk![LastDNAdate], 4) & Mid$(rbk!
[LastDNAdate], 3, 2) & Left$(rbk![LastDNAdate], 2)
rbk.Edit
rbk![LastDNAdate] = LastDNAdate
rbk.Update
rbk.MoveNext
Wend

End Function

The only problem is when I run this I get the following
run time error message:

Run Time Error '94':
Invalid Use of Null

I know the problem relates to there being no data in the
field but how do I get around this and run the module
successfully?
Click to expand...

If the LastDNAdate field might be Null, then don't use the
string form of the Left, Mid and Right functions:

Dim varLastDNAdate As Variant

varLastDNAdate = Right(rbk![LastDNAdate], 4) & _
Mid(rbk![LastDNAdate], 3, 2) & Left(rbk![LastDNAdate], 2)
rbk.Edit
rbk![LastDNAdate] = varLastDNAdate

BTW, you don't really need code to so this, you could just
use an UPDATE query instead:

UPDATE [OP WL CMDS (RBK02)]
SET [LastDNAdate] = Right(rbk![LastDNAdate], 4) &
Mid(rbk![LastDNAdate], 3, 2) & Left(rbk![LastDNAdate], 2)
 
I did not think of doing it in a UPDATE query. But your
right this is a lot easier.

Can I also do the following in an UPDATE query:
Function Change_date15()

Dim wait As Database, rbk As Recordset

Set wait = DBEngine.Workspaces(0).Databases(0)
Set rbk = wait.OpenRecordset("OP WL CMDS (RBK02)")

rbk.MoveFirst
While Not rbk.EOF
CensusDate = Format(DateSerial(Right$(rbk![CensusDate],
2), Mid$(rbk![CensusDate], 3, 2), Left$(rbk![CensusDate],
2)), "yyyymmdd")
rbk.Edit
rbk![CensusDate] = CensusDate
rbk.Update
rbk.MoveNext


Wend


End Function

Here I am changing the the date from DDMMYY to YYYYMMDD,
it is worth noting that the fields are text fields and not
Date/Time fields. How would I do this in an UPDATE query,
with the problem of 'blank' fields included.
-----Original Message-----
John said:
I have got the following module to convert dates within a
field:

Function Change_date16()
Dim wait As Database, rbk As Recordset

Set wait = DBEngine.Workspaces(0).Databases(0)
Set rbk = wait.OpenRecordset("OP WL CMDS (RBK02)")

rbk.MoveFirst
While Not rbk.EOF
LastDNAdate = Right$(rbk![LastDNAdate], 4) & Mid$(rbk!
[LastDNAdate], 3, 2) & Left$(rbk![LastDNAdate], 2)
rbk.Edit
rbk![LastDNAdate] = LastDNAdate
rbk.Update
rbk.MoveNext
Wend

End Function

The only problem is when I run this I get the following
run time error message:

Run Time Error '94':
Invalid Use of Null

I know the problem relates to there being no data in the
field but how do I get around this and run the module
successfully?
Click to expand...

If the LastDNAdate field might be Null, then don't use the
string form of the Left, Mid and Right functions:

Dim varLastDNAdate As Variant

varLastDNAdate = Right(rbk![LastDNAdate], 4) & _
Mid(rbk![LastDNAdate], 3, 2) & Left(rbk! [LastDNAdate], 2)
rbk.Edit
rbk![LastDNAdate] = varLastDNAdate

BTW, you don't really need code to so this, you could just
use an UPDATE query instead:

UPDATE [OP WL CMDS (RBK02)]
SET [LastDNAdate] = Right(rbk![LastDNAdate], 4) &
Mid(rbk![LastDNAdate], 3, 2) & Left(rbk! [LastDNAdate], 2)
Click to expand...
 
Johh said:
I did not think of doing it in a UPDATE query. But your
right this is a lot easier.

Can I also do the following in an UPDATE query:
Function Change_date15()
Click to expand...

Sure, it's essentially the same situation as your other
question. Drop the $ from the function names and use your
expression in the SET clause of the Update query. One of
the really cool things about Access queries is that you can
use all of the Access functions in SQL statements.
--
Marsh
MVP [MS Access]


Dim wait As Database, rbk As Recordset

Set wait = DBEngine.Workspaces(0).Databases(0)
Set rbk = wait.OpenRecordset("OP WL CMDS (RBK02)")

rbk.MoveFirst
While Not rbk.EOF
CensusDate = Format(DateSerial(Right$(rbk![CensusDate],
2), Mid$(rbk![CensusDate], 3, 2), Left$(rbk![CensusDate],
2)), "yyyymmdd")
rbk.Edit
rbk![CensusDate] = CensusDate
rbk.Update
rbk.MoveNext


Wend


End Function

Here I am changing the the date from DDMMYY to YYYYMMDD,
it is worth noting that the fields are text fields and not
Date/Time fields. How would I do this in an UPDATE query,
with the problem of 'blank' fields included.
-----Original Message-----
John said:
I have got the following module to convert dates within a
field:

Function Change_date16()
Dim wait As Database, rbk As Recordset

Set wait = DBEngine.Workspaces(0).Databases(0)
Set rbk = wait.OpenRecordset("OP WL CMDS (RBK02)")

rbk.MoveFirst
While Not rbk.EOF
LastDNAdate = Right$(rbk![LastDNAdate], 4) & Mid$(rbk!
[LastDNAdate], 3, 2) & Left$(rbk![LastDNAdate], 2)
rbk.Edit
rbk![LastDNAdate] = LastDNAdate
rbk.Update
rbk.MoveNext
Wend

End Function

The only problem is when I run this I get the following
run time error message:

Run Time Error '94':
Invalid Use of Null

I know the problem relates to there being no data in the
field but how do I get around this and run the module
successfully?
Click to expand...

If the LastDNAdate field might be Null, then don't use the
string form of the Left, Mid and Right functions:

Dim varLastDNAdate As Variant

varLastDNAdate = Right(rbk![LastDNAdate], 4) & _
Mid(rbk![LastDNAdate], 3, 2) & Left(rbk! [LastDNAdate], 2)
rbk.Edit
rbk![LastDNAdate] = varLastDNAdate

BTW, you don't really need code to so this, you could just
use an UPDATE query instead:

UPDATE [OP WL CMDS (RBK02)]
SET [LastDNAdate] = Right(rbk![LastDNAdate], 4) &
Mid(rbk![LastDNAdate], 3, 2) & Left(rbk!
Click to expand...
[LastDNAdate], 2)
Click to expand...
 
Thanks for the help in this, it worked a treat and will
make my life a lot easier. Thanks again.

-----Original Message-----
Johh said:
I did not think of doing it in a UPDATE query. But your
right this is a lot easier.

Can I also do the following in an UPDATE query:
Function Change_date15()
Click to expand...

Sure, it's essentially the same situation as your other
question. Drop the $ from the function names and use your
expression in the SET clause of the Update query. One of
the really cool things about Access queries is that you can
use all of the Access functions in SQL statements.
--
Marsh
MVP [MS Access]


Dim wait As Database, rbk As Recordset

Set wait = DBEngine.Workspaces(0).Databases(0)
Set rbk = wait.OpenRecordset("OP WL CMDS (RBK02)")

rbk.MoveFirst
While Not rbk.EOF
CensusDate = Format(DateSerial(Right$(rbk! [CensusDate],
2), Mid$(rbk![CensusDate], 3, 2), Left$(rbk! [CensusDate],
2)), "yyyymmdd")
rbk.Edit
rbk![CensusDate] = CensusDate
rbk.Update
rbk.MoveNext


Wend


End Function

Here I am changing the the date from DDMMYY to YYYYMMDD,
it is worth noting that the fields are text fields and not
Date/Time fields. How would I do this in an UPDATE query,
with the problem of 'blank' fields included.
-----Original Message-----
John Thomas wrote:

I have got the following module to convert dates
Click to expand...
within
a
field:

Function Change_date16()
Dim wait As Database, rbk As Recordset

Set wait = DBEngine.Workspaces(0).Databases(0)
Set rbk = wait.OpenRecordset("OP WL CMDS (RBK02)")

rbk.MoveFirst
While Not rbk.EOF
LastDNAdate = Right$(rbk![LastDNAdate], 4) & Mid$(rbk!
[LastDNAdate], 3, 2) & Left$(rbk![LastDNAdate], 2)
rbk.Edit
rbk![LastDNAdate] = LastDNAdate
rbk.Update
rbk.MoveNext
Wend

End Function

The only problem is when I run this I get the following
run time error message:

Run Time Error '94':
Invalid Use of Null

I know the problem relates to there being no data in the
field but how do I get around this and run the module
successfully?

If the LastDNAdate field might be Null, then don't use the
string form of the Left, Mid and Right functions:

Dim varLastDNAdate As Variant

varLastDNAdate = Right(rbk![LastDNAdate], 4) & _
Mid(rbk![LastDNAdate], 3, 2) & Left(rbk! [LastDNAdate], 2)
rbk.Edit
rbk![LastDNAdate] = varLastDNAdate

BTW, you don't really need code to so this, you could just
use an UPDATE query instead:

UPDATE [OP WL CMDS (RBK02)]
SET [LastDNAdate] = Right(rbk![LastDNAdate], 4) &
Mid(rbk![LastDNAdate], 3, 2) & Left(rbk!
Click to expand...
[LastDNAdate], 2)
Click to expand...

.
Click to expand...
 
Back
Top