NZ Function continued...

E

Enderjit Singh

So far the help of the Bruce and Ron to name just two. I
have got this far in my query. I am trying convert text
records in a feild from a format of DDMMYY to YYYYMMDD,
ignoring records that are blank.

This is the complete code that I currently have in place:

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
'This assumes that the recordset variable is named "rbk"
If Len(rbk![LastDNAdate] & "") <> 6 Then
'Do nothing - does not meet specified format, or is
Null
Else
'Modify the value
LastDNAdate = Format(DateSerial(Right(rbk!
[LastDNAdate], 2), Mid(rbk![LastDNAdate], 3, 2), Left(rbk!
[LastDNAdate], 2)), "yyyymmdd")
End If
rbk.Edit
rbk![LastDNAdate] = LastDNAdate
rbk.Update
rbk.MoveNext

Wend



End Function


The problem is that when I run this code it does not
always ignore the blank fields, instead it inserts a
YYYYMMYY figure in the field. What am I doing wrong?
 
G

Graham R Seach

Enderjit,

This should work for you.

Function Change_date16()
Dim wait As Database
Dim sSQL As String

On Error Resume Next

Set wait = CurrentDb
sSQL = "UPDATE [OP WL CMDS (RBK02)] " & _
"SET LastDNADate = Format(DateSerial(Right(rbk.LastDNAdate,
2), " & _
"Mid(rbk.LastDNAdate] 3, 2), Left(rbk.LastDNAdate., 2)),
'yyyymmdd') " & _
"WHERE Len(LastDNADate) = 6"

db.Execute sSQL, dbFailOnError
If Err <> 0 Then MsgBox Err.Number & vbCrLf & Err.Description

db.Close
Set db = Nothing
End Function


Graham R Seach
Microsoft Access MCP, MVP
Sydney, Australia
 
E

Enderjit Singh

I've tried this as suggested but I get a error message of
object required. I must mention that I am totally new to
VB so am learning all the time.

-----Original Message-----
Enderjit,

This should work for you.

Function Change_date16()
Dim wait As Database
Dim sSQL As String

On Error Resume Next

Set wait = CurrentDb
sSQL = "UPDATE [OP WL CMDS (RBK02)] " & _
"SET LastDNADate = Format(DateSerial (Right(rbk.LastDNAdate,
2), " & _
"Mid(rbk.LastDNAdate] 3, 2), Left (rbk.LastDNAdate., 2)),
'yyyymmdd') " & _
"WHERE Len(LastDNADate) = 6"

db.Execute sSQL, dbFailOnError
If Err <> 0 Then MsgBox Err.Number & vbCrLf & Err.Description

db.Close
Set db = Nothing
End Function


Graham R Seach
Microsoft Access MCP, MVP
Sydney, Australia
---------------------------------------------

So far the help of the Bruce and Ron to name just two. I
have got this far in my query. I am trying convert text
records in a feild from a format of DDMMYY to YYYYMMDD,
ignoring records that are blank.

This is the complete code that I currently have in place:

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
'This assumes that the recordset variable is named "rbk"
If Len(rbk![LastDNAdate] & "") <> 6 Then
'Do nothing - does not meet specified format, or is
Null
Else
'Modify the value
LastDNAdate = Format(DateSerial(Right(rbk!
[LastDNAdate], 2), Mid(rbk![LastDNAdate], 3, 2), Left (rbk!
[LastDNAdate], 2)), "yyyymmdd")
End If
rbk.Edit
rbk![LastDNAdate] = LastDNAdate
rbk.Update
rbk.MoveNext

Wend



End Function


The problem is that when I run this code it does not
always ignore the blank fields, instead it inserts a
YYYYMMYY figure in the field. What am I doing wrong?
Click to expand...


.
Click to expand...
 
G

Graham R Seach

Enderjit,

Sorry - force of habit! I usually use "db" for my database object. You're
using "wait". Therefore, change this line:
db.Execute sSQL, dbFailOnError
to this:
wait.Execute sSQL, dbFailOnError

....and then change the last lines to:
wait.Close
Set wait = Nothing

Graham R Seach
Microsoft Access MCP, MVP
Sydney, Australia
---------------------------------------------

Enderjit Singh said:
I've tried this as suggested but I get a error message of
object required. I must mention that I am totally new to
VB so am learning all the time.

-----Original Message-----
Enderjit,

This should work for you.

Function Change_date16()
Dim wait As Database
Dim sSQL As String

On Error Resume Next

Set wait = CurrentDb
sSQL = "UPDATE [OP WL CMDS (RBK02)] " & _
"SET LastDNADate = Format(DateSerial (Right(rbk.LastDNAdate,
2), " & _
"Mid(rbk.LastDNAdate] 3, 2), Left (rbk.LastDNAdate., 2)),
'yyyymmdd') " & _
"WHERE Len(LastDNADate) = 6"

db.Execute sSQL, dbFailOnError
If Err <> 0 Then MsgBox Err.Number & vbCrLf & Err.Description

db.Close
Set db = Nothing
End Function


Graham R Seach
Microsoft Access MCP, MVP
Sydney, Australia
---------------------------------------------

So far the help of the Bruce and Ron to name just two. I
have got this far in my query. I am trying convert text
records in a feild from a format of DDMMYY to YYYYMMDD,
ignoring records that are blank.

This is the complete code that I currently have in place:

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
'This assumes that the recordset variable is named "rbk"
If Len(rbk![LastDNAdate] & "") <> 6 Then
'Do nothing - does not meet specified format, or is
Null
Else
'Modify the value
LastDNAdate = Format(DateSerial(Right(rbk!
[LastDNAdate], 2), Mid(rbk![LastDNAdate], 3, 2), Left (rbk!
[LastDNAdate], 2)), "yyyymmdd")
End If
rbk.Edit
rbk![LastDNAdate] = LastDNAdate
rbk.Update
rbk.MoveNext

Wend



End Function


The problem is that when I run this code it does not
always ignore the blank fields, instead it inserts a
YYYYMMYY figure in the field. What am I doing wrong?
Click to expand...


.
Click to expand...
Click to expand...
 
J

John Mishefske

Graham said:
Enderjit,

Sorry - force of habit! I usually use "db" for my database object. You're
using "wait". Therefore, change this line:
db.Execute sSQL, dbFailOnError
to this:
wait.Execute sSQL, dbFailOnError

...and then change the last lines to:
wait.Close
Set wait = Nothing
Click to expand...

Hi Graham. you wouldn't actually do a .Close on "wait" would you?
I don't think it would harm anything but its not actually doing
anything is it? My understanding is that even setting the database
reference to Nothing isn't necessary. Minor point, just curious...
 
G

Graham R Seach

John,

There is debate about it, but it is something I've always done as a matter
of course, without problem.

Graham R Seach
Microsoft Access MCP, MVP
Sydney, Australia
---------------------------------------------
 

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

Run time Error 4
Nz function still a problem 3
Copying Problem 1
Copying problem!!! 3
Understanding Codes 1
Query- missing values- Nz function 1
CSE function with #VALUE errors 2
#Error using function as controlsource 4

Top