Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2006

    Unanswered: define data type with make table query

    I'm trying to use a make table query to create a temp table.

    I have an expression

    IIf([BODY_OR_PLATE] Like "body","1" & Mid([JOB_NUMBER],3,5) & [JOB_LINE],IIf([BODY_OR_PLATE] Like "plate","2" & Mid([JOB_NUMBER],3,5) & [JOB_LINE])) AS [Barcode number]

    but when I make the table this field is set to text when I need it to be set to number

    Is there anyway I can force it to do this? I have tried setting the format to # but that doesn't work

    Thanks in advance for any help!

  2. #2
    Join Date
    Oct 2006
    Could you run the make table query once, and keep the table that is produced. Alter the data types in the resulting table, then in future only run the query as an append query each time, in other words ditch the make table query?

    of course this is only possible if the table structure is intended to remain the same.

  3. #3
    Join Date
    Jul 2005
    I assume the concatenated string in your iif clause are all digits. If so, multiply it by 1 i.e., 1*("1" & Mid([JOB_NUMBER],3,5) & [JOB_LINE]) should make it a number.

  4. #4
    Join Date
    Apr 2004
    Derbyshire, UK
    Provided Answers: 2

    An alternative suggestions, assuming the IIF is required to returns an int/long data type

    CLng(IIf([BODY_OR_PLATE] Like "body","1" & Mid([JOB_NUMBER],3,5) & [JOB_LINE],IIf([BODY_OR_PLATE] Like "plate","2" & Mid([JOB_NUMBER],3,5) & [JOB_LINE]))) AS [Barcode number]

    One question

    What is returned if [BODY_OR_PLATE] is neither "body" or "plate". If it can only be "body" or "plate" then the second IIF is redundant !!??

    Sorry two questions (no one expects the Spanish Inquisition !!)

    Do you need to use 'Like' (usualy used with '*').

    My tuppence worth.


  5. #5
    Join Date
    Aug 2002
    Northampton, England
    Have you tried to create a table using SQL. This SQL creates a table with all the data types.

    Dim TD As DAO.TableDef
    Dim db As DAO.Database
    Dim fld As DAO.Field

    Set db = CurrentDb
    'Set TD = DB.TableDefs("MyTestTable")
    Set TD = db.CreateTableDef("MyTestTable")

    With TD
    .Fields.Append .CreateField("Field1", dbText, 255)
    .Fields.Append .CreateField("Field2", dbDouble, 2)
    .Fields.Append .CreateField("Field3", dbInteger, 3)
    .Fields.Append .CreateField("Field4", dbLong, 2)
    .Fields.Append .CreateField("Field5", dbMemo)
    .Fields.Append .CreateField("Field6", dbBoolean)
    .Fields.Append .CreateField("Field7", dbCurrency)
    End With

    Set fld = Nothing
    Set TD = Nothing
    Set db = Nothing

Posting Permissions

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