Access 97 DAO

  • Thread starter Thread starter M
  • Start date Start date
M

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
 
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
 
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"
Click to expand...
statement. It could be that your Case "" And varXZone...
is what's throwing it. Try this
Do Until .EOF

.Edit

If IsNull(.Fields("RESPONSIBLE
Click to expand...
DEPARTMENT").Value) Or .Fields("RESPONSIBLE
DEPARTMENT").Value = "" Then
 
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)
 
Back
Top