Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / General 2 / May 2007

Tip: Looking for answers? Try searching our database.

Leading Zeros Lost in Concatenation

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
JimP - 29 May 2007 19:44 GMT
I'm working on creating a database that pulls data from three fields
and concatenates the results into a fourth.

http://s22.quicksharing.com/v/5121074/ScreenShot002.png.html

I'm almost there, I suspect, but the first field from which I pull has
a leading zero that's not carrying over into the concatenated field.

http://s15.quicksharing.com/v/8550436/ScreenShot001.png.html.

What would you advise to fix this?

Thanks,
Jim
Bruce Meneghin - 29 May 2007 20:17 GMT
I'm guessing that the Figure Identifier field is a numeric field, and it has
some custom formatting to give it the leading zeros.  If that is the case,
duplicate the formatting in the concatenation expression.

> I'm working on creating a database that pulls data from three fields
> and concatenates the results into a fourth.
[quoted text clipped - 10 lines]
> Thanks,
> Jim
Jeff Boyce - 29 May 2007 20:18 GMT
Jim

In a query against the three fields, use the CStr() function to coerce the
first field into a text string.  This should preserve the leading zeros.

Another recommendation: DON'T store the concatenated value.  Instead, use a
query to calculate it on the fly.

Regards

Jeff Boyce
Microsoft Office/Access MVP

> I'm working on creating a database that pulls data from three fields
> and concatenates the results into a fourth.
[quoted text clipped - 10 lines]
> Thanks,
> Jim
JimP - 30 May 2007 17:07 GMT
Thanks, Jeff --

I think I'm closer. I confess to being a newbie when it comes to using
functions in Access. If I misunderstood your advice below, I
apologize. I didn't have any luck with the CStr() function, but I
finally got my form to look as desired by putting the following in my
Control Source for the field: =Format([Figure Identifier],"0000000" &
[Description] & ".eps"). So far, so good.

http://s28.quicksharing.com/v/5304419/controlsource.jpg.html

The table tied to the form was imported in Access from Excel, and the
form was then built on the table.  Within the table, however, this
field is not populating when adding new records. Only the initial row,
which was populated at the time of the import, still shows visible
data.

http://s28.quicksharing.com/v/8892670/TableFormView.jpg.html

FWIW, the two fields in the table do not have identical formatting in
Access -- the formatting is the way Access interpreted it upon import.

http://s28.quicksharing.com/v/4717182/Design_View_Figure_Identifier_Field.jpg.html
http://s28.quicksharing.com/v/4842418/DesignView_Figure_Identifier_Description.j
pg.html


I would like the Figure Identifier Description field to populate in
the table, too, though this is perhaps not absolutely essential. What
is your advice for next steps?

> Jim
>
[quoted text clipped - 25 lines]
>
> - Show quoted text -
Jeff Boyce - 30 May 2007 17:27 GMT
Jim

I'll suggest that you post your new questions as new questions.  If you
leave them embedded "down-thread" like this, you won't get as many eyes on
them... nor as many brains!

Regards

Jeff Boyce
Microsoft Office/Access MVP

> Thanks, Jeff --
>
[quoted text clipped - 56 lines]
>>
>> - Show quoted text -
JimP - 30 May 2007 17:33 GMT
> Jim
>
[quoted text clipped - 73 lines]
>
> - Show quoted text -

Good point, Jeff. Will do.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.