Linking two tables but adding a left command

[TEOA]

I have several tables that I am linking for a query, one of them however has a date and time field and one is just a date. unfortunatly when I try to link them I dont get any results, So what I want to know is can I put a left command in to the link so that the link only looks at the fist 10 characters (the date)
I have created another feild in the query that creates just the date but I cant link the other table to this part of the query as it happens after the query starts.

here is an SQL extract of my query :
SELECT dbo_wireact1.crtd_date, dbo_wireact1.wcnt_nbr, dbo_PRODUCT1.PROD_DESC, dbo_SEMIFINISHED_COST1.COST_TYPE, [dbo_wireact1]![scrp_setup]+[dbo_wireact1]![scrp_ip]+[dbo_wireact1]![scrp_cut] AS scrap, [dbo_SEMIFINISHED_COST1]![CSET_CORP_MATL_COST]+[dbo_SEMIFINISHED_COST1]![CSET_LABR_COST]+[dbo_SEMIFINISHED_COST1]![CSET_VOH_COST]+[dbo_SEMIFINISHED_COST1]![CSET_FOH_COST]+[dbo_SEMIFINISHED_COST1]![CRUN_CORP_MATL_COST]+[dbo_SEMIFINISHED_COST1]![CRUN_LABR_COST]+[dbo_SEMIFINISHED_COST1]![CRUN_VOH_COST]+[dbo_SEMIFINISHED_COST1]![CRUN_FOH_COST] AS [COST PER KM], ([COST PER KM]/1000)*[scrap] AS [COST OF SCRAP], Left([crtd_date],10) AS [JUST DATE], [financial week no].[WEEK NO]
FROM [financial week no] INNER JOIN (dbo_PRODUCT1 INNER JOIN ((dbo_wireact1 INNER JOIN dbo_users ON dbo_wireact1.oper_id = dbo_users.user_id) INNER JOIN dbo_SEMIFINISHED_COST1 ON (dbo_wireact1.oper_nbr =cstr( dbo_SEMIFINISHED_COST1.OPER_NBR)) AND (dbo_wireact1.wcnt_nbr = dbo_SEMIFINISHED_COST1.COST_CENT)) ON (dbo_PRODUCT1.PROD_DESC = dbo_wireact1.prod_desc) AND (dbo_PRODUCT1.PROD_ID = dbo_SEMIFINISHED_COST1.PROD_ID)) ON [financial week no].DATE = dbo_wireact1.crtd_date
GROUP BY dbo_wireact1.crtd_date, dbo_wireact1.wcnt_nbr, dbo_PRODUCT1.PROD_DESC, dbo_SEMIFINISHED_COST1.COST_TYPE, [dbo_wireact1]![scrp_setup]+[dbo_wireact1]![scrp_ip]+[dbo_wireact1]![scrp_cut], [dbo_SEMIFINISHED_COST1]![CSET_CORP_MATL_COST]+[dbo_SEMIFINISHED_COST1]![CSET_LABR_COST]+[dbo_SEMIFINISHED_COST1]![CSET_VOH_COST]+[dbo_SEMIFINISHED_COST1]![CSET_FOH_COST]+[dbo_SEMIFINISHED_COST1]![CRUN_CORP_MATL_COST]+[dbo_SEMIFINISHED_COST1]![CRUN_LABR_COST]+[dbo_SEMIFINISHED_COST1]![CRUN_VOH_COST]+[dbo_SEMIFINISHED_COST1]![CRUN_FOH_COST], Left([crtd_date],10), [financial week no].[WEEK NO]
HAVING (((dbo_wireact1.crtd_date) Between #9/21/2010# And Now()) AND ((dbo_SEMIFINISHED_COST1.COST_TYPE)="c") AND (([dbo_wireact1]![scrp_setup]+[dbo_wireact1]![scrp_ip]+[dbo_wireact1]![scrp_cut])>0));



The reason I want to be able to do this is I want to run a query that I can link to a spreadsheet that updates charts whenever refreshed.
The charts will show cost of scrap produced by week number.
To do this have created a local table that has a list of dates in one field and the corosponding FY week number in the other.

using this I want to get the query to look at the date the job was run (CRTD_DATE) and compare it with the table I have created [financial week no] then show the week number in the query.

Hope everyone can follow my ramblings ??

Anyone able to help??

 

[pbaldy]

See if this untested bit works (warning, you will only be able to work with the query in SQL view after this):

ON DateValue([financial week no].DATE) = DateValue(dbo_wireact1.crtd_date)

You could also create a query that used DateValue() to convert the field to date only, then use that query instead of the table in succeeding queries and join on that converted field.