Digit selection for a new code

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

Guest

Hi, I'm new here please be patient.
In a query, I have a field called MEMBERSHIPDATE with the format MM/DD
/YYYY. From it I want to obtain, in another column, a number MMDDY. To
clarify, please see the next examples:

12/9/2003 I want to obtain 12093
1/1/2004 I want to obtain 01014
8/10/2005 I want to obtain 08105

What expresion do I need to retreive such a number. Thanks
 
Put the following in an empty field in your query:
MyNumber:Format(Month([MembershipDate]),"00") & _
Format(Day([MembershipDate]),"00") & _
Right([MembershipDate],1)
 
VIDAL said:
In a query, I have a field called MEMBERSHIPDATE with the format MM/DD
/YYYY. From it I want to obtain, in another column, a number MMDDY. To
clarify, please see the next examples:

12/9/2003 I want to obtain 12093
1/1/2004 I want to obtain 01014
8/10/2005 I want to obtain 08105

What expresion do I need to retreive such a number. Thanks
Click to expand...


Format(MEMBERSHIPDATE, "mmdd") &
Right(Year(MEMBERSHIPDATE),1)

But this a poor numbering scheme. What are you going to do
when the year is 2010?
 
Thanks for your help, but this expression ask me for a value, then to be
converted. I already have a column with the dates, I just want the other
column to convert it to the format I described before, WITHOUT MANUAL ENTRY.
 
What value is it prompting for? The only value the
expression need is the value of the field MEMBERSHIPDATE,
which I assume is the name of a field.

Perhaps the field name is spelled differently??
 
Dear Barton,
I don't want any EXPRESSION asking me for a MEMBERSHIPDATE. There is a
column called MEMBERSHIPDATE, I just want another one that convert the data
from one format to another. See examples

COLUMN1 COLUMN2
4/1/2004 | 04014
6/1/2003 | 06013
3/1/2000 | 03010
8/22/2009| 30229

Thanks again


Marshall Barton said:
What value is it prompting for? The only value the
expression need is the value of the field MEMBERSHIPDATE,
which I assume is the name of a field.

Perhaps the field name is spelled differently??
--
Marsh
MVP [MS Access]


Thanks for your help, but this expression ask me for a value, then to be
converted. I already have a column with the dates, I just want the other
column to convert it to the format I described before, WITHOUT MANUAL ENTRY.
Click to expand...
Click to expand...
 
Both the expressions that PC and I posted will do that, so
there must be something else causing you trouble.

What, exactly. are you being prompted for?

What, exactly, is the expression in the query?
 
Back
Top