CODEWITHSUNDEEP
sqlms-accessms-access-2007
Sifu
Sifu

Reputation: 1082

How can I keep leading zeros when concatenating two numbers together in a query?

Question

How can I keep leading zeros when concatenating two numbers together in an Access query?

Table

FileYear | FileIteration
------------------------ 
14       | 0001
14       | 0002
14       | 0003
14       | 0004

(FileIteration has a "0000" format, but is still stored as integer)

Query

SELECT MAX(FileYear & FileIteration)
FROM FileNumber
WHERE FileYear=Format(Date(),"yy");

Current (wrong) output

144

It doesn't keep leading zeros.

Desired output

140004

I want it to keep leading zeros.

Upvotes: 0

Views: 2188

Answers (4)

ashareef
ashareef

Reputation: 1846

Using the Format function is my opinion the "correct" way to do it although there are many ways to achieve what you want in this case.

The output of Format() will be a string

SELECT MAX(FileYear & Format(FileIteration,"0000"))
FROM FileNumber
WHERE FileYear=Format(Date(),"yy");

Upvotes: 2

Marty
Marty

Reputation: 2016

One possible Access SQL statement for your query, assuming for a 4-digit FileIteration is:

SELECT Max(([FileYear]*1000)+[FileIteration]) AS Answer
FROM FileNumber
WHERE ((([FileYear])=Format(Date(),"yy")));

Upvotes: 1

ron tornambe
ron tornambe

Reputation: 10780

Convert them to strings before concatenating:

CStr(Fileyear) & CStr(FileIteration)

Upvotes: -1

wvdz
wvdz

Reputation: 16651

If it's always a 4-digit number, you can simply do this:

Fileyear * 10000 + FileIteration

If the number of digits is dynamic, let's assume it's in the column NoOfDigits:

Fileyear * 10 ^ NoOfDigits + FileIteration

Upvotes: 1

Related Questions