Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing number fields as text (2003)

    Hi folks, I'm trying to import a spreadsheet into Access as a table, but need one of the fields [Emp No] which is currently defined as a number field to come in as text in the following statement:
    CurrentDb.Execute "INSERT INTO COUEmployeeMaster ( [Emp No], Name, Name2, StartDate, Title, Forename, Surname, Location, [Job Title] )SELECT Starters.[Pay No], [Forename] & ' ' & [Surname] AS 1, [Surname] & ', ' & [Forename] AS 2, Starters.[Start Date], Starters.Title, Starters.Forename, Starters.Surname, Starters.Depot, Starters.[Job Title] FROM Starters;"

    is there an easy way to do this???? I see something about the transfer text method in the help files, but not sure if this applies where you only want one field changed. Thanks a lot as always. Ina

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing number fields as text (2003)

    INSERT INTO appends records to an already existing table. So if Emp No is defined as a text field in COUEmployeeMaster, there shouldn't be a problem. Or am I missing something?

  3. #3
    Star Lounger
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing number fields as text (2003)

    No I'm making a meal of explaining this - I'm transferring a spreadsheet in as below, however, despite defining the data as text in the originating spreadsheet, it imports as a number field, when I need it to be text. Therefore when I try and append it to the main table, which has a text field for Ids, I get a data type mismatch. I guess what I'm asking is, before I append it, is there any way I can change the field in question from a number field to a text field behind the same click button I'm using to run the append? Is that clearer? Soz

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Starters", "Hstukgl1fil024.6.8 logical access review$4.6.8.4COU master - pending disablementStarters", True

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing number fields as text (2003)

    Try

    ... SELECT CStr(Starters.[Pay No]), ...

    or

    ... SELECT Format(Starters.[Pay No]), ...

    in the SQL statement

  5. #5
    Star Lounger
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing number fields as text (2003)

    Cheers Hans - coming from a non-programming background, sometimes its a bit difficult finding things out via the helpfiles and your help is invaluable. Thanks muchly.

    Ina

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •