If I understand you correctly you want to use Field1's data (10_51_ER)
to parse into Field2 (as 10_51).
If the final underscore is ALWAYS followed by 2 letters:
Update YourTable Set YourTable.Field2 = Left([Field1],len([Field1]-3))
Where [Field1] is not null;
However, if the characters after the final underscore can vary, (10_51_A or
10_51_ABCD),
AND you are using Access 2002 (or some versions of Access 2000) you can use:
UPDATE YourTable SET YourTable.Field2 =
Left([Field1],InStrRev([Field1],"_")-1)
WHERE YourTable.Field1) Is Not Null;
If you are using a previous version of Access (97, 95, 2) that does not have
the InStrRev() function, you'll need to write a user defined function to
find the final underscore position and return the wanted string.
Add the following function to a module:
Public Function Parse(strIn As String) As String
Dim intX As Integer
Dim intY As Integer
intX = InStr(strIn, "_")
If intX = 0 Then
Parse = strIn
Exit Function
Else
Do While intX <> 0
intY = intX + 1
intX = InStr(intY, strIn, "_")
Loop
Parse = Left(strIn, intY - 2)
End If
End Function
===
Then make an Update query:
Update YourTable Set YourTable.Field2 = Parse([Field1])
Where Field1 is not null.
--
Fred
Please reply only to this newsgroup.
I do not reply to personal e-mail.
"Ken Bush" <KB****@csu.edu> wrote in message
news:WiL7b.309340$cF.94367@rwcrnsc53...
Er.. that was just kind of an example, not to be taken literally as such.
I guess that's my fault.
Let me try again.
Field1 has values such as
10_51_ER
I need to update Field2 to everything but the last _ and whatever follows
it, for example in this case it would be 10_51. Note that the values of
field1 may have less or more underscore seperators, but in any case I need
the last one and what follow to be removed.
fg******@att.net (Fredg) wrote in
<t7**********************@bgtnsc04-news.ops.worldnet.att.net>:
Ken,
Regardless of how the date is displayed, if the field is a Date/Time
datatype, the actual value is stored as a number.
Today's date, 9/10/2003, is stored as 37874.
Any valid date must include the month, day, and year values.
8/3 is NOT a valid date datetype.
There is no need for an update query.
To 'display' a date as 8/3 in a date field all you need do is set the
field's format property to
mm/dd
To do this in a query, you would use:
NewField:Format([DateField],"mm/dd")
In a form or report control:
=Format([DateField],"mm/dd")
make sure the name of the control is not the same as [DateField].
Just show it as I have indicated above, in a form, report, or query.
--
Fred
Please reply only to this newsgroup.
I do not reply to personal e-mail.
"Ken Bush" <KB****@csu.edu> wrote in message
news:hxK7b.408657$o%2.186111@sccrnsc02... How can I write an update query that removes part of a field? Like if
I have a field with values such as 8/3/68 (a birthday obviously) and I
need to put values in a new column but I need everything after and
including
the final / removed to end up with simply 8/3
