update to parse out part of record

[Ken Bush]

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

[Fredg]

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.4086 57$o%2.186111@s ccrnsc02...

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

[Ken Bush]

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.ne t (Fredg) wrote in
<t7************ **********@bgtn sc04-news.ops.worldn et.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:Forma t([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.408 657$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

[Fredg]

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.Field 2 = 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.Field 2 =
Left([Field1],InStrRev([Field1],"_")-1)
WHERE YourTable.Field 1) 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.Field 2 = 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.3093 40$cF.94367@rwc rnsc53...

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.ne t (Fredg) wrote in
<t7************ **********@bgtn sc04-news.ops.worldn et.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:Forma t([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.408 657$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