instr & recordsets

A

Abbey Normal

Hi all.
Do I have to always refer to a field that I am using in a recordset with the
..Field Preface? I saw it once in an example, and it worked for me but not
with this new code.
I am trying to use the inStr example as shown:
'Left([YourField],InStr([YourField],"#")-1)

'strip out anything before the paren
LPlastic = Left([.Fields("Plastic")], InStr([.Fields("Plastic")], "(") - 1)
.Fields("Plastic") = LPlastic

I tried it this way too but I keep getting a type mismatch:
LPlastic = Left([Plastic], InStr([Plastic], "(") - 1)

I don't have a clue as to what is wrong. Can someone Help!
I am retrieving the data with a DAO recordset.
 
J

Jerry Porter

Try
LPlastic = Left(!Plastic, InStr(!Plastic, "(") - 1)

If you were referencing your recordset explicitly, instead of with a
With clause, the following would all work:
rs.Fields("Plastic")
rs("Plastic")
rs!Plastic

Jerry
 
V

veyisarslan

haber iletisinde said:
Hi all.
Do I have to always refer to a field that I am using in a recordset with the
.Field Preface? I saw it once in an example, and it worked for me but not
with this new code.
I am trying to use the inStr example as shown:
'Left([YourField],InStr([YourField],"#")-1)

'strip out anything before the paren
LPlastic = Left([.Fields("Plastic")], InStr([.Fields("Plastic")], "(") - 1)
.Fields("Plastic") = LPlastic

I tried it this way too but I keep getting a type mismatch:
LPlastic = Left([Plastic], InStr([Plastic], "(") - 1)

I don't have a clue as to what is wrong. Can someone Help!
I am retrieving the data with a DAO recordset.
Click to expand...
 
A

Abbey Normal

didn't like it. I keep getting Runtime error 5 Invalid procedure call or
argument.
I tried it a number of ways. Can you take a look at this and see if you see
something? Thanks,


Option Compare Database
Option Explicit
Public Sub LoopThru()
'Uses a DAO recordset
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim Count As Integer
Dim intPos As Integer
Dim i As Integer
Dim delSQL As String
Dim NewStyleSave As String
Dim LPlastic As String
Dim LMetal As String
Dim LLens As String

strSQL = "SELECT Plastic,Metal,Lens,CatalogRef from TrayLabels"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)

With rst
Do Until .EOF
.Edit

Count = Count + 1
'strip out anything before the paren
'Left([YourField],InStr([YourField],"#")-1)

LPlastic = Left(rst!Plastic, InStr(rst!Plastic, "(") - 1)

.Fields("Plastic") = LPlastic

'If CatalogRef already has a value, we'll use that
If (IsNull(.Fields("CatalogRef"))) Then

'If the metals field is filled in, we'll use that
If Not (IsNull(.Fields("Metal"))) Then .Fields("CatalogRef") =
..Fields("Metal") & .Fields("Lens")

'otherwise use the plastic
.Fields("CatalogRef") = .Fields("Plastic") & .Fields("Lens")
End If

.Update

.MoveNext
Loop
.Close
End With


delSQL = "DELETE FROM TrayLabels WHERE [TrayLabels.Plastic] is Null AND
[TrayLabels.Metal] is Null AND [Traylabels.Lens] is Null "

CurrentDb.Execute delSQL, dbFailOnError

MsgBox ("Change me to use a method that is actual number of recs in the
table = " & Count)
End Sub
 
D

Douglas J. Steele

Are any of the values Null? You'll get that error if they are.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Abbey Normal said:
didn't like it. I keep getting Runtime error 5 Invalid procedure call or
argument.
I tried it a number of ways. Can you take a look at this and see if you
see
something? Thanks,


Option Compare Database
Option Explicit
Public Sub LoopThru()
'Uses a DAO recordset
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim Count As Integer
Dim intPos As Integer
Dim i As Integer
Dim delSQL As String
Dim NewStyleSave As String
Dim LPlastic As String
Dim LMetal As String
Dim LLens As String

strSQL = "SELECT Plastic,Metal,Lens,CatalogRef from TrayLabels"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)

With rst
Do Until .EOF
.Edit

Count = Count + 1
'strip out anything before the paren
'Left([YourField],InStr([YourField],"#")-1)

LPlastic = Left(rst!Plastic, InStr(rst!Plastic, "(") - 1)

.Fields("Plastic") = LPlastic

'If CatalogRef already has a value, we'll use that
If (IsNull(.Fields("CatalogRef"))) Then

'If the metals field is filled in, we'll use that
If Not (IsNull(.Fields("Metal"))) Then .Fields("CatalogRef") =
.Fields("Metal") & .Fields("Lens")

'otherwise use the plastic
.Fields("CatalogRef") = .Fields("Plastic") & .Fields("Lens")
End If

.Update

.MoveNext
Loop
.Close
End With


delSQL = "DELETE FROM TrayLabels WHERE [TrayLabels.Plastic] is Null AND
[TrayLabels.Metal] is Null AND [Traylabels.Lens] is Null "

CurrentDb.Execute delSQL, dbFailOnError

MsgBox ("Change me to use a method that is actual number of recs in the
table = " & Count)
End Sub


Jerry Porter said:
Try
LPlastic = Left(!Plastic, InStr(!Plastic, "(") - 1)

If you were referencing your recordset explicitly, instead of with a
With clause, the following would all work:
rs.Fields("Plastic")
rs("Plastic")
rs!Plastic

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

Jerry Porter

Hi Abbey, sorry for the delay.

Your key line is: LPlastic = Left(rst!Plastic, InStr(rst!Plastic, "(")
- 1)

The error is probably because you've got and entry in rst!Plastic with
no "(" in it. In this case, Instr returns 0. Then you've got
essentially Left(rst!Plastic,-1).

Try something like:

ParenPosition = InStr(rst!Plastic, "(")
If ParenPosition = 0 Then
LPlastic = ""
Else
LPlastic = Left(rst!Plastic,ParenPosition)
Endif

Jerry
 
Top