Access 97 DAO

[M]

I am editing a fields values based on two other fields. I
have code set up to use a Select Case statement to look
for a specified value in two fields.

Here's an example:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim varRespDept As Variant
Dim varXZone As Variant

Set db = CurrentDb
Set rs = db.OpenRecordset("RESTRICTED BALANCE")

With rs

varXZone = .Fields("XZONE").Value
varRespDept = .Fields("RESPONSIBLE DEPARTMENT").Value

Do Until .EOF

.Edit

Select Case varRespDept

Case "" And varXZone = "BB"
varRespDept = "BANKRUPTCY"

End Select
.update
.movenext

loop

end with

etc...

Both fields have text as the datatype. I have tried "",
Empty, Null and everything else I can think of but the
code stops every time with a RunTime error 13 - Type
Mismatch.

And I set the table to accept zero-length strings

Any Ideas?

Thanks

M

 

[Guest]

AFAIK, you can't throw additional stuff into a "Case" statement. It could be that your Case "" And varXZone... is what's throwing it. Try thi

Do Until .EO

.Edi

If IsNull(.Fields("RESPONSIBLE DEPARTMENT").Value) Or .Fields("RESPONSIBLE DEPARTMENT").Value = "" The

varRespDept = "

End I

Select Case varRespDep

Case ""

If varXZone = "BB" Then
varRespDept = "BANKRUPTCY
End I

End Selec
.updat
.movenex

loo


----- M wrote: ----

I am editing a fields values based on two other fields. I
have code set up to use a Select Case statement to look
for a specified value in two fields

Here's an example

Dim db As DAO.Databas
Dim rs As DAO.Recordse
Dim varRespDept As Varian
Dim varXZone As Varian

Set db = CurrentD
Set rs = db.OpenRecordset("RESTRICTED BALANCE"

With r

varXZone = .Fields("XZONE").Valu
varRespDept = .Fields("RESPONSIBLE DEPARTMENT").Valu

Do Until .EO

.Edi

Select Case varRespDep

Case "" And varXZone = "BB
varRespDept = "BANKRUPTCY

End Selec
.updat
.movenex

loo

end wit

etc..

Both fields have text as the datatype. I have tried "",
Empty, Null and everything else I can think of but the
code stops every time with a RunTime error 13 - Type
Mismatch

And I set the table to accept zero-length string

Any Ideas

Thank

 

[M]

Thanks for your help. Neither way has worked, so I devised
another scheme that makes a temporary field, looks for
the "" in the first field and puts a 1 in the temp field.
It then compares that 1 to the other field and then
changes the data if needed. Lastly it deletes the temp
field. A little more work, but it seems to work just as
well.

Thanks again for your help.

M

-----Original Message-----
AFAIK, you can't throw additional stuff into a "Case"

statement. It could be that your Case "" And varXZone...
is what's throwing it. Try this

Do Until .EOF

.Edit

If IsNull(.Fields("RESPONSIBLE

DEPARTMENT").Value) Or .Fields("RESPONSIBLE
DEPARTMENT").Value = "" Then

 

[david epsom dot com dot au]

Case "" And varXZone = "BB"

Your Case value has to be a valid expression or list of expressions
that match the SELECT variable or expression:

select case vName
Case "fred", sName, sFirst & " " & sLast

You can't select on a different variable. Perhaps you mean something like:

Select Case varRespDept
Case ""
if VarXZone= "BB" then varRespDept = "BANKRUPTCY"

or perhaps:

Select Case VarXZone & varRespDept

Case "BB": varRespDept = "BANKRUPTCY"


Your error message actually means that your Case expression was an invalid
expression:
("" And (varXZone = "BB"))
You can't do boolean operations (AND) on a string expression ("")

(david)