Make table queiry

Topic

New Reply

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?

Reply | Quote

Replies

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?

Reply | Quote

Hi Wendell.
Yes I didn’t mention the table is linked to a text document and the delimiter is a comma.

Appologies

Reply | Quote

So what happens when you try to use dates formatted with the time?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote