REP: Changing Text to Number

[Lisa]

I have a query that I want to chane one field to a number field. the Field I
want to change is a ship by month fiels. I come from a make table query and
shows up as (June 2007) I and a new field in the query to change the (June
2007) to (6/1/2007)
so on my form I can put date parameters in to create my report. I tried
using datevalue and that did not work. here is the sql of my query.

SELECT tblStores.StoreName, tblStores.StoreNo,
tblPurchMonthlyByStore.[Customer Number], tblPurchMonthlyByStore.[Ship Date
By Month], tblPurchMonthlyByStore.[Sum Of Extended Price],
qrySalesTotalsMonthly.[LastDayOfPeriod By Month], qrySalesTotalsMonthly.[Sum
Of SalesSales], [Sum Of Extended Price]/[Sum Of SalesSales] AS FoodCost,
DateValue([Ship Date By Month]) AS DateValue, tblPurchMonthlyByStore.District
FROM (tblStores INNER JOIN qrySalesTotalsMonthly ON tblStores.StoreNo =
qrySalesTotalsMonthly.SalesStoreNo) INNER JOIN (tblStoresUSCustNos INNER JOIN
tblPurchMonthlyByStore ON tblStoresUSCustNos.USCustNo =
tblPurchMonthlyByStore.[Customer Number]) ON (tblStores.StoreNo =
tblStoresUSCustNos.StoreNo) AND (qrySalesTotalsMonthly.[LastDayOfPeriod By
Month] = tblPurchMonthlyByStore.[Ship Date By Month]);

Help please

 

[John Spencer]

Date Value should be able to change "June 2007" to 6/1/2007.

Although it will error on nulls and things that cannot be interpreted as a
date, so you might want to modify the calculation to

IIF(IsDate([Ship Date By Month]), DateValue([Ship Date By Month]), Null)

SELECT tblStores.StoreName, tblStores.StoreNo,
tblPurchMonthlyByStore.[Customer Number]
, tblPurchMonthlyByStore.[Ship Date By Month]
, tblPurchMonthlyByStore.[Sum Of Extended Price]
, qrySalesTotalsMonthly.[LastDayOfPeriod By Month]
, qrySalesTotalsMonthly.[Sum Of SalesSales]
, [Sum Of Extended Price]/[Sum Of SalesSales] AS FoodCost

, IIF(IsDate([Ship Date By Month]), DateValue([Ship Date By Month]), Null)
AS DateValue

, tblPurchMonthlyByStore.District
FROM (tblStores INNER JOIN qrySalesTotalsMonthly
ON tblStores.StoreNo = qrySalesTotalsMonthly.SalesStoreNo)
INNER JOIN (tblStoresUSCustNos
INNER JOIN tblPurchMonthlyByStore
ON tblStoresUSCustNos.USCustNo = tblPurchMonthlyByStore.[Customer Number])
ON (tblStores.StoreNo = tblStoresUSCustNos.StoreNo)
AND (qrySalesTotalsMonthly.[LastDayOfPeriod By Month] =
tblPurchMonthlyByStore.[Ship Date By Month]);

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..