By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,569 Members | 1,358 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,569 IT Pros & Developers. It's quick & easy.

update to parse out part of record

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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

Nov 12 '05 #2

P: n/a
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



Nov 12 '05 #3

P: n/a
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


Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.