Date Format Between Query And Table

[Mike Hughes]

I have set the Left([DSERDBA_RECEIPTS].[RCTH_RECEIPT_NO],8) AS [DISTRIBUTION DT] field property to Short Date, 99/99/0000;0;_ . When I run the query I get that field formated as a short date, but when I try to create a table from the same query the data returned is no longer in the date format. Is there something I can do to have the make table results look like what I get when I just run the query without creating a table?

SELECT
DSERDBA_RECEIPTS.RCTH_ID_CASE AS [CASE],
DSERDBA_RECEIPTS.RCTH_DT_RECEIPT AS [DT RCTH],
Left([DSERDBA_RECEIPTS].[RCTH_RECEIPT_NO],8) AS [DISTRIBUTION DT], DSERDBA_DISBURSEMENTS.DSB_DT_DISBURSE INTO [TABLE 1]

FROM [UNIVERSE OF CASES] INNER JOIN (DSERDBA_RECEIPTS INNER JOIN DSERDBA_DISBURSEMENTS ON DSERDBA_RECEIPTS.RCTH_RECEIPT_NO = DSERDBA_DISBURSEMENTS.DSB_RECEIPT_NO) ON [UNIVERSE OF CASES].STAT_CASE_ID = DSERDBA_RECEIPTS.RCTH_ID_CASE

WHERE (((DSERDBA_RECEIPTS.RCTH_ID_CASE)="20028570P"));

 

[EMP]

The Left() function returns a text string.

To remove the time from [RCTH_RECEIPT_NO], you can use the DateValue() function:

DateValue([DSERDBA_RECEIPTS].[RCTH_RECEIPT_NO]) AS [DISTRIBUTION DT]

^

 

[Mike Hughes]

The left function was used to strip out the first 8 numbers (which is a date) from a larger number - Like a record is 12222006p12312345 and I the query to return only the first 8 numbers (which is actualy the date) so I don't think your suggestion would work.

 

[neileg]

You're going to have to chip out the date and then convert it. Something like
Cdate(Left([DSERDBA_RECEIPTS].[RCTH_RECEIPT_NO],)&"/"&Mid([DSERDBA_RECEIPTS].[RCTH_RECEIPT_NO],3,2)&"/"&Mid([DSERDBA_RECEIPTS].[RCTH_RECEIPT_NO],5,4))

 

[Mike Hughes]

This is what I tried:

SELECT
DSERDBA_RECEIPTS.RCTH_ID_CASE AS [CASE],
DSERDBA_RECEIPTS.RCTH_DT_RECEIPT AS [DT RCTH], Cdate(Left([DSERDBA_RECEIPTS].[RCTH_RECEIPT_NO],)&"/"&Mid([DSERDBA_RECEIPTS].[RCTH_RECEIPT_NO],3,2)&"/"&Mid([DSERDBA_RECEIPTS].[RCTH_RECEIPT_NO],5,4))AS [DISTRIBUTION DT],
DSERDBA_DISBURSEMENTS.DSB_DT_DISBURSE

FROM [UNIVERSE OF CASES] INNER JOIN (DSERDBA_RECEIPTS INNER JOIN DSERDBA_DISBURSEMENTS ON DSERDBA_RECEIPTS.RCTH_RECEIPT_NO = DSERDBA_DISBURSEMENTS.DSB_RECEIPT_NO) ON [UNIVERSE OF CASES].STAT_CASE_ID = DSERDBA_RECEIPTS.RCTH_ID_CASE

WHERE (((DSERDBA_RECEIPTS.RCTH_ID_CASE)="20028570P"));

got an error message:
WRONG NUMBER OF AUGUMENTS USED WITH FUNCTION IN QUERY EXPRESSION Cdate(Left([DSERDBA_RECEIPTS].[RCTH_RECEIPT_NO],)&"/"&Mid([DSERDBA_RECEIPTS].[RCTH_RECEIPT_NO],3,2)&"/"&Mid([DSERDBA_RECEIPTS].[RCTH_RECEIPT_NO],5,4))

 

[EMP]

CDate(Left([DSERDBA_RECEIPTS].[RCTH_RECEIPT_NO],2) & "/" & Mid([DSERDBA_RECEIPTS].[RCTH_RECEIPT_NO],3,2) & "/" & Mid([DSERDBA_RECEIPTS].[RCTH_RECEIPT_NO],5,4))

^

 

[Mike Hughes]

You did it EMP ! Thanks for all your help! Mike

 

[neileg]

Oops. I missed the 2 out of my example!