Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ms access 2000 table default value  truncating leading zeroes

Posted on 2015-01-23
4
Medium Priority
?
117 Views
Last Modified: 2015-01-26
I need to setup a table in ms  access  2000 that will be used to send data to a text file.  Some of the fields(columns) need to have default values like  '01' .  but when I put 01 in the default value for a field, it display just 1. it needs to display  01( is truncating the leading zeroes.)  I need to export this table to a text file with the value not truncated( i.e. 01). I tried with single quote and I get error.  I also tried formats like 999999 or 000000, but did work either.
0
Comment
Question by:Shen
[X]
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
  • 2
4 Comments
 
LVL 18

Expert Comment

by:Simon
ID: 40567655
in the query you use to generate the export, replace that field value with
right("00" & yourfieldname,2)
or
right("0000000" & yourfieldname,6) if you need to cater for larger values without truncating them.
or
convert the field to a text field so you can add the zeros... but check first if that will break anything in your database design.

Hopefully clear, but come back with any questions.
0
 

Author Comment

by:Shen
ID: 40567736
I have been trying to do this as default value in the table .  I put  "01" as a default value, and  it is working now, however, I have another case, I can't create a field with say two blanks . So a field text length 2 if I put in default value " ", it creates a null. is there a way to create blanks  or  spaces through the table designer
0
 
LVL 18

Accepted Solution

by:
Simon earned 2000 total points
ID: 40568130
Again, I'd do it in the export query by defining the column as
Exprx: "  "
I'm not sure that you can do it in the table designer because of the way that Access auto-trims trailing spaces. I don't have Access 2000 any more, but you could try disallowing nulls and zero length in the field (if those options are available).

You could also try using non-breaking spaces as the default, but that may not be suitable for the output?
This wikipedia shows some methods of entering non-breaking spaces http://en.wikipedia.org/wiki/Non-breaking_space
or you could create them in Word (CTRL+SHIFT+SPACEBAR) and then copy-n-paste into the default value in the table design.

Really, I think the best answer is to store data in tables, and format output in queries, but I can understand that you may have a requirement to get the model right in the table because some export methods require a table rather than a query. Is this the case for you?
0
 

Author Closing Comment

by:Shen
ID: 40571365
thanks,  i am now doing a lot of the work through queries now. I have been using the format function in the queries. i have not completed the project yet.  I will propbably have addtional question as i move into the next steps in the project.

thanks again, very helpfull.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Problem: You created a new custom form in Outlook for your contacts (added fields, deleted fields, changed the layout of fields, whatever) and made it the default form for contacts. The good news is that all new contacts will utilize the new form. T…
As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

660 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