Append Table without second half of text

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi All,
I am appending a table with data from another table. The problem I have is
that when I append, I only want to bring in the first half of text in a
field. For example, if the table I am taking info out of has the following
string: lm6NAX1765PK63 APDANDPANELASSY in a field, but I only want to append
to the new table VP6NAX1765AAPK63 without the second half. I can not seem to
find the answer on MSFT website, in help or from other coworkers. Another
issue is that the first half of data in the text box is variable in length.
The string could be anywhere from 9 to 18 characters long; which is why the
following code does not work: Left([MASTER FILE]![Current SAP
Description],16) in the query design window. Any ideas on what code I could
put in that top line that would give me the desired results? Thank you for
your time. If you don't know, could there be any other place I could go to
get the answer?
 
If the string always has a space to separate the 2 halves then you can use
Left([MASTER FILE]![Current SAP Description],Instr(1,[MASTER FILE]![Current
SAP Description]," ",1)-1)
 
Not tested but can you try this, it might give a problem for records which
don't have a space inbetween or ending with a blank so best to add a blank as
in example 2


INSERT INTO NewTable ( ID, [Value] )
SELECT OldTable.ID, Left([Value],InStr(1,[Value]," ")-1)
FROM OldTable;


INSERT INTO NewTable ( ID, [Value] )
SELECT OldTable.ID, Left([Value] & " ",InStr(1,[Value]," ")-1)
FROM OldTable;

- Raoul
 
Thank you Dennis!. That worked perfectly. Thank you for your time.

Dennis said:
If the string always has a space to separate the 2 halves then you can use
Left([MASTER FILE]![Current SAP Description],Instr(1,[MASTER FILE]![Current
SAP Description]," ",1)-1)

Mike said:
Hi All,
I am appending a table with data from another table. The problem I have is
that when I append, I only want to bring in the first half of text in a
field. For example, if the table I am taking info out of has the following
string: lm6NAX1765PK63 APDANDPANELASSY in a field, but I only want to append
to the new table VP6NAX1765AAPK63 without the second half. I can not seem to
find the answer on MSFT website, in help or from other coworkers. Another
issue is that the first half of data in the text box is variable in length.
The string could be anywhere from 9 to 18 characters long; which is why the
following code does not work: Left([MASTER FILE]![Current SAP
Description],16) in the query design window. Any ideas on what code I could
put in that top line that would give me the desired results? Thank you for
your time. If you don't know, could there be any other place I could go to
get the answer?
Click to expand...
Click to expand...
 
Back
Top