Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a text file emailed to me every day from our other branch.
    Once received, I run a make table query:

    SELECT EST.RECORD_NUMBER, EST.EST_NO, EST.SUP_NO, EST.REG, EST.STA, EST.STA_DTE, EST.U_DTE, EST.A_DTE, EST.X_DTE, EST.C_DTE, EST.I_DTE, EST.F_DTE, EST.PRT_CST, EST.PNT_CST, EST.TAK_PRT_CST, EST.TAK_PNT_CST, EST.TOT_HRS, EST.LAB_CST, EST.AMD_LAB_CST, EST.AMD_PNT_CST, EST.AMD_PRT_CST, EST.NEW_HRS, EST.RR_HRS, EST.REP_HRS, EST.PTC_HRS, EST.PTF_HRS, EST.OWN_NME_CDE, EST.OWN_NME, EST.OWN_ADD_1, EST.OWN_ADD_2, EST.OWN_ADD_3, EST.OWN_ADD_4, EST.OWN_PCD, EST.OWN_TEL_H, EST.OWN_TEL_W, EST.OWN_VAT_IND, EST.PST_NME_CDE, EST.PST_NME, EST.INV_NO, EST.INV_DTE, EST.PST_REF, EST.PST_ADD_1, EST.PST_ADD_2, EST.PST_ADD_3, EST.PST_ADD_4, EST.PST_PCD, EST.PST_CNT, EST.PST_TEL, EST.INS_NME_CDE, EST.INS_NME, EST.CLM_NO, EST.POL_NO, EST.EXS, EST.BET, EST.LAB_TIM, EST.LAB_DSC_PCT, EST.OTH_DSC_PCT, EST.TCH_DSC_PCT, EST.PRT_DSC_PCT, EST.PNT_DSC_PCT, EST.PNT_MRK_PCT, EST.ASS, EST.INS_CNT, EST.INS_TEL, EST.VEH_CDE, EST.VEH_BOD_CDE, EST.VEH_MAK, EST.VEH_MOD, EST.VEH_BOD, EST.VEH_SIZ, EST.VEH_YR, EST.VEH_MIL, EST.VEH_CHA_NO, EST.VEH_ENG_NO, EST.EST_NME, EST.VEH_TRM_NO, EST.VEH_PNT_TYP, EST.VEH_PNT_CDE, EST.VEH_PNT_COL, EST.CMT_IND, EST.LST_TXN_DTE, EST.LST_SUP_NO, EST.ALL_HRS_IND, EST.BKI_DTE, EST.BKO_DTE, EST.LET_FLG1, EST.LET_FLG2, EST.LET_DTE1, EST.LET_DTE2, EST.JOB_IND, EST.LAB_RTE, EST.TCH_RTE, EST.TCH_HRS, EST.PNL_TOT, EST.FDE_TOT, EST.LST_UNL_NO, EST.JOB_CAT, EST.ENQ_SRC, EST.CAN_DES, EST.LAB_VAT_RTE, EST.OTH_VAT_RTE, EST.TCH_VAT_RTE, EST.PRT_VAT_RTE, EST.PNT_VAT_RTE, EST.WTY_DTE_SLD, EST.WTY_VEH_SRC, EST.WTY_CLM_TYP, EST.WTY_EXT_CLM, EST.WTY_PREV_CLM, EST.WTY_TOURIST, EST.MAX_INDIV_CDE, EST.MAX_INDIV_HRS, EST.CUS_VAT_AMT, EST.USR_DTE, EST.USR_FLG, EST.USR_NUM, EST.JLIB_REC, EST.WA_FLG, EST.PRT_BULK_DSC, EST.LAB_BULK_DSC, EST.VEH_STYLE_CDE INTO tblEST
    FROM EST;


    Some of the dates look like: 24/12/2009 which is fine for me to work with.
    Some of the dates look like: 24/12/2009 00:00:00 which I cannot work with.

    How can I get around this within the make table quiery?

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    If you have the field with the dates with time already in an Access table, then you should be able to use a format statement in your query to only display the date portion. However I'm puzzled by the fact that you cannot use the date with time - that is the way dates are stored in Access - a date with no time is presumed to be at midnight on that date. So what am I missing here? Is EST a text file that you are linking to? If so, what is the delimiter?
    Wendell

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Wendell.
    Yes I didn't mention the table is linked to a text document and the delimiter is a comma.

    Appologies

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    So what happens when you try to use dates formatted with the time?
    Wendell

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts
    A couple of things really.
    One is I can't set any format, ie [BKI_DTE] is a book in date which I would like to set as =Format$([BKI_DTE],"ddd-dd-mmm-yyyy",0,0) in a report.
    So really the conversion needs to take place in the make table query.
    I get circular reference error if I use "BKI_DTE: DateValue(Left(BKI_DTE,10))"

    Thanks for replying.

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Right - you can't use the same field name for the expression and the field name you use for the "text" table. Is the make table query creating a "date" field type for BKI_DTE field? If so, you should be able to format the field on the report control and not worry about the time value. If you really want to do it in the data source for the report, you could use a format that specifies the format as you specified in your post.
    Wendell

Posting Permissions

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