Run time Error

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?
 
M

Marshall Barton

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)
 
J

Johh Thomas

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...
 
M

Marshall Barton

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...
 
J

John Thomas

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

NZ Function continued... 5
Nz function still a problem 3
Copying problem!!! 3
Copying Problem 1
Shell command produces a "Run-time error '5': Invalid procedure call or argumen 0
Run-Time Error 438 Outlook 5
Run-time error when copying entire row! 5
Run-time error '3134' 0

Top