InStr Help

[MTSPEER]

Hello,

I'm trying to bull this string from a field that is in between characters. Everything seems to work but except when it comes to the update query it appends 0 records when there is the right criteria there for it. Here is my code beloww:

Public Function ExtractString()
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim strAll As String
Dim strExtract As String


Dim strSQL As String
Dim strLength As Integer
Dim startPos As Integer
Dim lastPos As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM Copy_Producer_World_Final")
strAll = rs.Fields(3).Value

Do Until rs.EOF

startPos = InStr(strAll, "Primary Phone:")

If (startPos > 0) Then
lastPos = InStr(strAll, "Blank")
strLength = (lastPos - startPos)
strExtract = Mid(strAll, startPos, strLength)
strSQL = "UPDATE Table1 SET Table1.Phone = '" & strExtract & "';"
DoCmd.RunSQL (strSQL)
End If
rs.MoveNext
Loop
MsgBox "Done"
Set db = Nothing
Set rs = Nothing
End Function

[CJ_London]

if your record does not contain the word 'blank' then lastpos will be 0, which means strLength will be a negative number

perhaps show some example data and what you want as a result - you code appears to be much more complex than it needs to be

[June7]

UPDATE does not append records, it edits records.

Your UPDATE action will modify every record in Table1 with the same value.

Your string will have the actual word 'Blank'?

[MTSPEER]

What I want is it to pull the string from the one field and then place it in another. it's in the same table though. I got it to work with an update query but it is updating the record to all of them. How can I have it so it only updates that data to that row then moves to the next record, pulls that string and updates it to that record and loop it.

[JoeM]

Why?
The general rule of thumb is that if something can be easily calculated in a query, there usually is no reason to store it in a field.
Why do you think it needs to be in a table field as opposed to just a calculated field in a query?

[MTSPEER]

Because when I'm importing these spreadsheets in my table I need the data to be changed around. Like the one field contains data for the other field as well and I need to put it in the other field.

[JoeM]

It seems to me that this should be able to be done with a simple Update Query, and VBA may not be necessary.
(Note that since Update queries update existing records, it would not add any new records to the table, it simply changes some values in existing records).
Can you post some data examples along with what the final output should look like?

Note if you want to do it the way you are trying to do it, I think you need to add Criteria to your SQL code so it knows exactly which record you are updating.
Though you are looping through your records in your table one-by-one, the SQL code you are creating is being applied to ALL records, and not just the record you are looking at because you left off the WHERE clause.

June made a lot of these points in her reply.

[MTSPEER]

Yea I need to add Criteria to my SQL statement so it knows what record to choose but I just don't know what to use. I have 2 fields. Email and Primary_Phone, the phone number is in the Email field and I'm trying to pull that out and get that into the Primary Phone Field. Okay here's an example:

Email
dkashdas@ymail.com Primary Phone: 84623894 Blank Column 1
doifhsdof@google.com Primary Phone: 43279479 Blank COlumn 1

[JoeM]

Yea I need to add Criteria to my SQL statement so it knows what record to choose but I just don't know what to use.

You would want to use a Unique Identifier. If you don't have one, you can add an Autonumber field to the table, and just use that.

I have 2 fields. Email and Primary_Phone, the phone number is in the Email field and I'm trying to pull that out and get that into the Primary Phone Field. Okay here's an example:

Email
dkashdas@ymail.com Primary Phone: 84623894 Blank Column 1
doifhsdof@google.com Primary Phone: 43279479 Blank COlumn 1

So, let me make sure I have this right.
The Primary Phone field is currently blank, and you want to move it from the Email field to the Primary Phone field.
I assume that you just want the number, and want to drop the phrase "Primary Phone:" from the field value.
Is that correct?

[MTSPEER]

There is just garbage data in the Primary_Phone field now. But yes I just want to keep the email address in the Email field and I just want the number in the Primary_Phone field

[JoeM]

OK, you should be able to do this with a simple Update query, no VBA required.

Here is what that code would look like, based on the example data you provided:

UPDATE Table1 
SET Table1.Email = Left([Email],InStr([Email]," ")-1), 
Table1.[Primary Phone] = Mid([Email],InStrRev([Email]," ")+1)
WHERE ((Not (Table1.Email) Is Null));

Note you may need to change the Table name (I used "Table1") and field names (I used "Email" and "Primary Phone") to match your names.

I just used the logic of when the email field is not blank, take up to the first blank as the email address, and everything after the last blank is the phone number.

[MTSPEER]

That cleaned up the email field but for the Primary_Phone field it deleted everything and didn't add the number

[JoeM]

Do the current entries end with the phone number, or is there something after them.
It sounds like you may have a space at the very end of your string. If so, the Trim function should take care of that.

[MTSPEER]

Here is what a record looks like in the email field:

dahsdhas@<a href="mailto:beverly@con...ulting.com</a> Primary Phone: 1111111111 Blank Column 1:

And yes there is a space after the ":" so that is the problem? The space after the :?

[JoeM]

If you data looks like your examples, this version should work regardless of whether or not anything appears after the phone number:

UPDATE Table1 
SET Table1.Email = Left([Email],InStr([Email]," ")-1), 
Table1.[Primary Phone] = Mid([Email],InStr([Email],"Primary Phone: ")+15,9)
WHERE ((Not (Table1.Email) Is Null));