leading zeros in month and year fields

V

vic

I've imported some tables from csv files and the month and year fields don't
have leading zeros, ie year 2006, is just 6, and month 06 is 6. I'm wanting
both fields to be 2 integers, with year =06 and month 06.
It was a substring command back in dbase days. I'm sure there's a simple
module out there that someone can point me towards? (Have tried the simple
month and year update commands to no avail.)
 
D

Douglas J. Steele

If you're storing the values in numeric fields (Byte,.Integer, Long, etc.),
you won't get leading zeros. Numbers don't have leading zeros. With numeric
fields, 06 is the same as 6.
 
K

KARL DEWEY

Numbers do not have leading zeros but text fields do.
You can add zeros like this --
Right("0"&[YourField],2)
 
V

vic

yes the data was imported from a numeric field and so there were no leading
zeros, but i have since converted to txt fields. I still have the same
problem. The month field is not so important but the year field is. I tried
the query Karl advised but no joy there.
vic
 
D

Douglas J. Steele

What are you trying to do: change them permanently?

You'll want to use an Update query.

The SQL will be something like:

UPDATE MyTable
SET MyMonthField = Right("0" & [MyMonthField], 2),
MyYearField = "20" & Right("0" & [MyYearField], 2)

That assumes that all of the years are supposed to be in the 21st century.
If not, you'll need to introduce logic to know when to put "19" in front,
vs. when to put "20".
 
M

Mikal via AccessMonster.com

Vic,
try:

UPDATE Table1 SET Table1.mymonth = Format([table1].[mymonth],"00");

Same routine for the myyear field.
Hope this Helps.

Mike
 
V

vic

Douglas, yes update query was required. Your queries below worked a treat,
thks.
--
vic


Douglas J. Steele said:
What are you trying to do: change them permanently?

You'll want to use an Update query.

The SQL will be something like:

UPDATE MyTable
SET MyMonthField = Right("0" & [MyMonthField], 2),
MyYearField = "20" & Right("0" & [MyYearField], 2)

That assumes that all of the years are supposed to be in the 21st century.
If not, you'll need to introduce logic to know when to put "19" in front,
vs. when to put "20".

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


vic said:
yes the data was imported from a numeric field and so there were no
leading
zeros, but i have since converted to txt fields. I still have the same
problem. The month field is not so important but the year field is. I
tried
the query Karl advised but no joy there.
vic
Click to expand...
Click to expand...
 
Top