Recordsets

[Rob.Mills]

I have a database that contains a list of properties in several jurisdictions throughout the country with their parcel numbers. I have found one jurisdiction that has 500+ records that the data is messed up on the parcel number (don't ask me how). But here's what I need to do. The parcel number is a 10 digit number. I need to take the last two digits and place them at the beginning of the number. Here's the code I've written out with the error message I'm getting in green.

Public Function Parcel()
Dim db As Database, rst As Recordset, strSQL As String
Dim intLast2 As Integer, intFirst8 As Integer
strSQL = "SELECT [Parcel #], Notes "
strSQL = strSQL & "FROM [Loan table] "
strSQL = strSQL & "WHERE Payee=3105500000 AND Bookmarked=No;"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL) Data type mismatch in criteria expression.
Do Until rst.EOF
intLast2 = Right(rst![Parcel #], 2)
intFirst8 = Left(rst![Parcel #], 8)
[Parcel #] = intLast2 & intFirst8
rst.MoveNext
Loop
End Function


Any ideas?

 

[Jeff Bailey]

Assuming text fields ... try

strSQL = strSQL & "WHERE Payee='3105500000' AND Bookmarked='No';"

note the single quotes (apostrophes)

Jeff

 

[Drevlin]

I've noticed alot of people try to use VBA to fix problems that are far easier to fix using queries. This would be one of those cases.

Why not just create an update query that takes the current value from your table and changes it to the correct value?

UPDATE [Loan table] SET [Loan table].[Parcel #] = Val(Str(Right([Parcel #], 2)) & Str(Left([Parcel #],8)))
WHERE ((([Loan table].[Payee])=3105500000 and [Loan Table].[Bookmarked]="No"));

This assumes that Parcel # is a Number value. If it's a string value then you don't have to worry about the Val().


Peace

 

[Rob.Mills]

Ok, this is a reply to both Jeff Bailey & Drevlin.

Jeff Bailey: I tried what you suggested but got the same error message "Data type mismatch." Not sure where to go from here.

Drevlin: I thought that an update query might be easier but just didn't know how to set it up. I copied your query and updated due to the fact that [Parcel #] is text and [Bookmarked] is Yes/No.

I ran the query and it said it couldn't update 523 of 526 records. And I told it not to continue. I opened up the table to see what happened and it really screwed the parcels up worse than before. I made a backup so it's not a big deal but do you have any idea what would've happened. It rearranged everything and added some spaces.

 

[Drevlin]

Ouch. Dang.... I'm glad you made a backup... I was going to mention to you that you should...

Anyway, I was guessing when I made the update query SQL so it could be that I had something amiss in the statement.

You might try building it yourself in the Query Design mode. Also you might want to add a Trim() around Parcel #. It sounds like you may have leading or trailing spaces. (that would account for it adding spaces in the middle.)

Just to instruct a bit on building an update query. For those who have no idea how it works.

Open up a blank query and add the table you want to update.

Next to the Bang Symbol (the button with the explanation point on it) there is a query type button. If you click the little down arrow attached on its right, you get a list of choices for different Query types. Choose the Update Query button (it has a picture of a pencil). This will add an extra line in the query setup (actually it gets rid of two lines: sort and show and adds Update To).

From the table you only need to add the fields you want to update or need to use as a filter. In this case you would bring down [Parcel #], [Bookmarked], and [Payee].

In the Parcel field's Update to, you would place what you want it to update to. In this case it would be:

Str(Right(Trim([Parcel #]), 2)) & Str(Left(Trim([Parcel #]),8))

The other two fields would have nothing in the Update to field with your criteria in the Criteria field (of course).

Now, once the update query is setup the best way (that I know of) to test what is going to occur is to goto a blank field (in this case it would be the fourth field) create a field name of say Updateto and place your Update To information into the field:

UpdateTo: Str(Right(Trim([Parcel #]), 2)) & Str(Left(Trim([Parcel #]),8))

Then change the query back to a Select Query Type and view the results.

If the information is correct then go back into query design, change back to Update query, remove the extra UpdateTo field and hit the Bang Symbol (the run button/Exclamation point button).

It probably sounds alot more complicated then it really is, and I hope this helps somebody.

Anyway Rob, try the suggestions I made and see if it works. Looking at the UpdateTo in a query view should allow you to make sure the information is correct before running the query.

Give it a shot and let me know if it doesn't work.

Peace

 

[Rob.Mills]

Drevlin,

Actually I fooled around on my own and got it to work before your last reply.

In the update field all I put was
Right([Parcel #]), 2) & Left([Parcel #]),8)
Did the trick. I guess writing out str wasn't necessary. Do you recommend using that anyway?

Also, I've never really used trim() before. Does it just remove spaces?

Thanks for your help.

 

[Drevlin]

No... the only reason I used Str() was because I was thinking you were using a number. It may not be necessary at all, but since putting two parts together with "&" is a string thing I thought it best to convert the numbers to a string and then concatenate them together. But since your information was a string to begin with it made this point mute.

And yeah, Trim() just removes any trailing or leading spaces in a string. There is also RTrim() and LTrim() if you just want to remove spaces from one side or the other.


Glad it works.