Concatenate in Query with Leading Zero

Posted on 2010-01-12
Medium Priority
Last Modified: 2013-11-28
Happy New Year!

I have a Acces query to Concatenate two number fields and I keep loosing the leading zeros. heres what I have;

Field1             Field2
MaxYr         MaxNum

Value in Field 1..
Value in Field 2..

..I would like the desired concatenated result to be..
..but I keep getting..
10-1    (I keep loosing the leading zeros)

Here is my code in the query;
CONTMAX: "M" & [MaxYr] & "-" & [MaxNum]

Can anyone please help me figure this out!?  Thanks a very bunch!

Question by:naeemahmad
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 75

Accepted Solution

DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1500 total points
ID: 26300265
Try this:

CONTMAX: "M" & Format([MaxYr],"00") & "-" & Format([MaxNum],"000")


Author Closing Comment

ID: 31676485
That worked perfectly!

Thanks as always and Happy coding in 2010!
LVL 75
ID: 26300524
"That worked perfectly!"
You are welcome.


Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question