Results 1 to 7 of 7
  1. #1
    Bri is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Location
    Chicago
    Posts
    2

    Setting a field's character length when the field's data type is long integer?

    Hi,



    Is there a way to set a field’s length to 20 characters when the data type for that field is long Integer?

    I have inherited a database that is used daily to send files to an online application in the form of a text file with pipe delimiters. I was advised that one file is failing to load due to one of the fields within the file has a fixed length of 20 characters that has been changed.

    I might have accidentally changed the set field length but do not know how I might have done this? I might have done this by changing the column (field) width?

    The first failed notice that I received, stated that the set field length went from 20 characters to 41. Since the data type for this field was / is “long Integer”, and not seeing a way to change the length, I changed the width of the column.

    The second failed notice then advised that the set length had changed from 41 to 29, so changing the width of the column I am assuming changed the set character length. Please correct me if my assumption is wrong?

    So basically I have a file that is failing because one field’s set character length has changed and needs to be set back to a length of 20 characters.

    How can I do this?

    I would appreciate any input here.

    Thank you.

  2. #2
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    I have inherited a database that is used daily to send files to an online application in the form of a text file with pipe delimiters.
    Can you post the code? Probably a Docmd.TransferText statement. Note the field with bad length for us.

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Is there a way to set a field’s length to 20 characters when the data type for that field is long Integer?
    Nope.... no way!
    If a field type is selected to be a Long Integer, that field size will take 4 bytes. This cannot be changed for a Long Integer data type.
    There is NO way to store characters in a number field.

    A Text type field can range from 1 to 255 characters.

    See this site


    The first failed notice that I received, stated that the set field length went from 20 characters to 41. Since the data type for this field was / is “long Integer”, and not seeing a way to change the length, I changed the width of the column.

    The second failed notice then advised that the set length had changed from 41 to 29, so changing the width of the column I am assuming changed the set character length. Please correct me if my assumption is wrong?
    Would probably have to see the dB. Hard to understand without also seeing the table structures.

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    if the character length of your number is 20 chars it will have to be treated as text - even a double, will only allow I think 18 characters.

    The only number I can think of which is often used in enterprise scale web based apps is a GUID - it doesn't look like a number (it looks something like {796D0757-C342-45EE-A607-D98D595F4291}) but it is numeric (16 bytes) and typically used as a primary key. And primary keys in access are usually Long, so I'm wondering if something has been lost in translation, or you have changed the field type from replicationID to long.

    Otherwise I think you need to provide the code you are using to create your text file - using something like Transfertext is one thing, creating it using write is another.

    Edit: it would also be helpful to see some sample data from before, when the upload worked and now

  5. #5
    Bri is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Location
    Chicago
    Posts
    2
    I was thinking that it could be an issue with the way the Access file is converted to text. So, changing the width of a field, would not make a change to set length of that field at all? This was the only modification that I had made to try an fix the issue. I also never changed the data type. Thank you, I will see if I can get the code used to convert the file and the name of that program or script.

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    As Steve says, you cannot change the number of characters in a number - it is not even an option. The only way you could do it for the text file is change it to text and use the space function to add spaces.

    myNum & space(20-len(mynum & ""))

    Are you sure you are interpreting the error message correctly?

  7. #7
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    I was advised that one file is failing to load due to one of the fields within the file has a fixed length of 20 characters that has been changed.
    My suggestion would be to ask the recipient of the file what the expected format of the text file is, i.e. what the order of the fields is, and what the expected format of each is.

    There is something strange about this, though. You said that you are sending the file as pipe-delimited, but the receiving application is complaining about a field-width problem. Usually, a text file is delimited or it is fixed-width, but not usually both. The only thing I can think if is that the receiving application uses to first line of the file to determine what a table structure should be, and it compares that with what it had before.

    As I said, get the file structure details from the recipient of what is expected, and once you have that, you can adjust your transfer routine to match.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Calculating GPA with a long integer field
    By JoJoM in forum Queries
    Replies: 3
    Last Post: 01-06-2016, 02:58 PM
  2. how much text in long integer field?
    By accessmatt in forum Database Design
    Replies: 10
    Last Post: 12-22-2014, 03:54 PM
  3. Replies: 10
    Last Post: 04-23-2014, 04:49 PM
  4. Fixed character length of field
    By tylerg11 in forum Access
    Replies: 3
    Last Post: 09-29-2011, 11:58 AM
  5. Replies: 3
    Last Post: 04-12-2009, 05:11 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums