Syntax help requested or correct existing advice

  • Thread starter Thread starter Donna
  • Start date Start date
D

Donna

I posted the below question and received the posted below
advice which doesn't work. Advice as to correct syntax or
correction of existing advice would be appreciated.
______________________________________________________
I need to separate data now in field "parcel" into
fields "section", "block" and "lot", which is prently
separated by dashes. Help as to the proper syntax would
be appreciated.

The parcel field has information as follows:
43-093-018
34-251-201
65-32-1018

I want it to update into something like this
section block lot
43 0932 018
34 251 201
65 32 1018

RESPONSE

Assuming Section, Block and Lot are new fields in the same
table as Parcel,
create an update query that includes all four fields.
Put the following expression under section where it says
Update To:
Left([Parcel],Instr([Parcel],"-")-1)
Put the following expression under Block where it says
Update To:
Mid([Parcel],Len([Section])+2,Instr(Len([Section])+2,
[Parcel],"-")-1)
Put the following expression under Lot where it says
Update To:
Right([Parcel],Len([Parcel])-Len([Section]&[Block]+2))
 
I posted the below question and received the posted below
advice which doesn't work. Advice as to correct syntax or
correction of existing advice would be appreciated.
______________________________________________________
I need to separate data now in field "parcel" into
fields "section", "block" and "lot", which is prently
separated by dashes. Help as to the proper syntax would
be appreciated.

The parcel field has information as follows:
43-093-018
34-251-201
65-32-1018

I want it to update into something like this
section block lot
43 0932 018
34 251 201
65 32 1018

RESPONSE

Assuming Section, Block and Lot are new fields in the same
table as Parcel,
create an update query that includes all four fields.
Put the following expression under section where it says
Update To:
Left([Parcel],Instr([Parcel],"-")-1)
Put the following expression under Block where it says
Update To:
Mid([Parcel],Len([Section])+2,Instr(Len([Section])+2,
[Parcel],"-")-1)
Put the following expression under Lot where it says
Update To:
Right([Parcel],Len([Parcel])-Len([Section]&[Block]+2))
Click to expand...

If you have Access 2000 or newer:
Copy and Paste the following into a Module:

Public Function ParseText(TextIn As String, X) As Variant
On Error Resume Next
Dim var As Variant
var = Split(TextIn, "-", -1)
ParseText = var(X)

End Function
===========

Then paste this SQL into a new Query.
Change YourTable to what ever the actual table name is...

Update YourTable Set YourTable.Section = ParseText([Parcel],0),
YourTable.Block = ParseText([Parcel],1),YourTable.Lot =
ParseText([Parcel],2);
 
Back
Top