Syntax help requested for update query

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

Donna

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
 
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 doubt that this query will function properly, because it assumes, for
example, that the Section field already contains data whose length can be
determined. But this data is not in the Section field when the query is
performed.
One approach would be to separate this into 3 queries and run them
sequentially.
Another would be to correct the UpdateTo entries so that they all refer
exclusively to the Parcel field.
A third approach (which I've often found useful) is to export the table to
Excel and use Data - Text to Columns to do the separation. Then import the
data back into Access.

One important factor influencing which option you choose to implement will
be whether this is a one-time operation, or one which will be repeated in
the future. In the latter case, you may want to go to the trouble to
develop a query like the second option, because that will be the easiest to
use (fewest steps) in the future.

HTH
- Turtle

PC Datasheet said:
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)

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


Donna said:
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
Click to expand...
Click to expand...
 
Turtle,

Just tried it and the formulas work fine this way! However the original formulas
need tweaked to:
Left([Parcel],InStr([Parcel],"-")-1)
Mid([Parcel],Len([Section])+2,InStr(Len([Section])+2,[Parcel],"-")-Len([Section]
)-2)
Right([Parcel],Len([Parcel])-Len([Section] & [Block])-2)


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com



MacDermott said:
I doubt that this query will function properly, because it assumes, for
example, that the Section field already contains data whose length can be
determined. But this data is not in the Section field when the query is
performed.
One approach would be to separate this into 3 queries and run them
sequentially.
Another would be to correct the UpdateTo entries so that they all refer
exclusively to the Parcel field.
A third approach (which I've often found useful) is to export the table to
Excel and use Data - Text to Columns to do the separation. Then import the
data back into Access.

One important factor influencing which option you choose to implement will
be whether this is a one-time operation, or one which will be repeated in
the future. In the latter case, you may want to go to the trouble to
develop a query like the second option, because that will be the easiest to
use (fewest steps) in the future.

HTH
- Turtle

PC Datasheet said:
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)

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


Donna said:
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
Click to expand...
Click to expand...
Click to expand...
 
Interesting -
because I can't get it to work as a single query in Access 2000.
Are you perhaps using a different version?
Or running these updates as separate queries?

I have 3 text fields, A, B, and C
If I use SQL like this:
UPDATE MyTable Set B= Left(A,Instr(A,"-")-1), C=Len(B)
my C column remains empty after the query is executed.
If I use SQL like this:
UPDATE MyTable Set B= Left(A,Instr(A,"-")-1), C=Mid(A,Len(B)+1)
I get a type conversion error on every row.

What do you think?
- Turtle

PC Datasheet said:
Turtle,

Just tried it and the formulas work fine this way! However the original formulas
need tweaked to:
Left([Parcel],InStr([Parcel],"-")-1)
Mid([Parcel],Len([Section])+2,InStr(Len([Section])+2,[Parcel],"-")-Len([Sect
ion]
)-2)
Right([Parcel],Len([Parcel])-Len([Section] & [Block])-2)


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com



MacDermott said:
I doubt that this query will function properly, because it assumes, for
example, that the Section field already contains data whose length can be
determined. But this data is not in the Section field when the query is
performed.
One approach would be to separate this into 3 queries and run them
sequentially.
Another would be to correct the UpdateTo entries so that they all refer
exclusively to the Parcel field.
A third approach (which I've often found useful) is to export the table to
Excel and use Data - Text to Columns to do the separation. Then import the
data back into Access.

One important factor influencing which option you choose to implement will
be whether this is a one-time operation, or one which will be repeated in
the future. In the latter case, you may want to go to the trouble to
develop a query like the second option, because that will be the easiest to
use (fewest steps) in the future.

HTH
- Turtle

PC Datasheet said:
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)

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


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
Click to expand...
Click to expand...
Click to expand...
 
MacDermott said:
I doubt that this query will function properly, because it assumes, for
example, that the Section field already contains data whose length can be
determined. But this data is not in the Section field when the query is
performed.
One approach would be to separate this into 3 queries and run them
sequentially.
Another would be to correct the UpdateTo entries so that they all refer
exclusively to the Parcel field.
A third approach (which I've often found useful) is to export the table to
Excel and use Data - Text to Columns to do the separation. Then import the
data back into Access.

One important factor influencing which option you choose to implement will
be whether this is a one-time operation, or one which will be repeated in
the future. In the latter case, you may want to go to the trouble to
develop a query like the second option, because that will be the easiest to
use (fewest steps) in the future.

HTH
- Turtle

PC Datasheet said:
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)

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


Donna said:
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
Click to expand...
Click to expand...
Click to expand...

Another approach would be to put a button on one of the forms and paste the
following code in the OnClick event.

(watch for line wrap)
'-----------------------------------
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("Table1")

If rs.BOF And rs.EOF Then
rs.Close
Set rs = Nothing
Exit Sub
End If
rs.MoveFirst

With rs
Do
If IsNull(Trim(!Section)) And IsNull(Trim(!Block)) And
IsNull(Trim(!Lot)) Then
.Edit
!Section = Left(!Parcel, InStr(!Parcel, "-") - 1)
!Block = Mid(!Parcel, InStr(1, !Parcel, "-") + 1,
InStr(InStr(1, !Parcel, "-") + 1, !Parcel, "-") - InStr(1, !Parcel, "-") - 1)
!Lot = Right(!Parcel, Len(!Parcel) - InStr(InStr(1, !Parcel,
"-") + 1, !Parcel, "-"))
.Update
End If
.MoveNext
Loop Until .EOF
End With

rs.Close
Set rs = Nothing
'-----------------------------------


Using the subroutine would allow future conversions.


HTH

Steve
 
Same results when I tested in A2K2. This is consistent
with Null Value for B when C is updated. If instead of B,
use B & "" in your 2 expressions to update C, the results
also show that the Null value was used for B.

Cheer
Van T. Dinh
 
Back
Top