convert function---string to number

[vani65]

Hi All
I need a report based on the criteria(number range)say: "BaleNo(USCNo.) 0302 to BaleNo(USCNo) 0306". The report should display all records between 0302 and 0306. I can't use "Between" criteria as datatype for BaleNo is Text. I used following Union query with parameters and in the where clause Like function with the following syntax(you can see in SQL). With this criteria my report is displaying only 0302 and 0306 records but not picking up in between records say 0303, 0304, 0305.I know something wrong with my syntax, I guess I have to use convert function(string to number) but I am not sure with the syntax. Could anyone suggest me the correct syntax so that all the records between 0302 to 0306 are displayed in my report.


PARAMETERS FORMS!Form1![USCNo1] Text, FORMS!Form1![USCNo2] Text, zz_RepPar.StockStatusId Text;
SELECT tblStock.StockID, tblStock.Description, tblStock.UnitOfMeasureID, tblPattern.BaleNo, tblPattern.Pattern, tblPattern.Dye, tblPattern.QuantityReceived, tblPattern.Quantity, tblPattern.JobID, tblPattern.SBN, tblPattern.Notation, tblPattern.LocationID, tblPattern.ActualCost, [Quantity]*[ActualCost] AS Total, tblPattern.USCCategoryID, tlkpStockStatus.StockStatusDescr, zz_RepPar.TransDate
FROM tblStock RIGHT JOIN ((zz_RepPar LEFT JOIN tlkpStockStatus ON zz_RepPar.StockStatusID = tlkpStockStatus.StockStatusID) LEFT JOIN tblPattern ON zz_RepPar.StockStatusID = tblPattern.StockStatusID) ON tblStock.StockID = tblPattern.StockID
WHERE tblPattern.BaleNo Like Left(FORMS!Form1![USCNo1],4)& &quot;*&quot; AND((tblPattern.Quantity)<>0)
UNION
SELECT tblStock.StockID, tblStock.Description, tblStock.UnitOfMeasureID, tblPattern.BaleNo, tblPattern.Pattern, tblPattern.Dye, tblPattern.QuantityReceived, tblPattern.Quantity, tblPattern.JobID, tblPattern.SBN, tblPattern.Notation, tblPattern.LocationID, tblPattern.ActualCost, [Quantity]*[ActualCost] AS Total, tblPattern.USCCategoryID, tlkpStockStatus.StockStatusDescr, zz_RepPar.TransDate
FROM tblStock RIGHT JOIN ((zz_RepPar LEFT JOIN tlkpStockStatus ON zz_RepPar.StockStatusID = tlkpStockStatus.StockStatusID) LEFT JOIN tblPattern ON zz_RepPar.StockStatusID = tblPattern.StockStatusID) ON tblStock.StockID = tblPattern.StockID
WHERE tblPattern.BaleNo Like Left(FORMS!Form1![USCNo2],4)& &quot;*&quot; AND((tblPattern.Quantity)<>0);

 

[danvlas]

Have you tried:

Where (Val(Right(tblPattern.BaleNo, 4)) Between Val(Right(FORMS!Form1![USCNo1],4)) And Val(Right(FORMS!Form1![USCNo2],4))) AND(tblPattern.Quantity<>0)


HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[vani65]

Hi
Thanks for replying. I tried the code but it didn't work.Could anyone please help me with this

 

[InterGeorge]

Try this one:
CLng([text])
Should give you back the number (IntLong)in a string.

George

 

[vani65]

When i was using following SQLs:
1st SQL no data fetch and when I use 2nd SQL system
brings only the records of related to uscno2 ie the number given in uscno2. Say I enter 0302 in uscno1 and 0304 in uscno2 records of only 0304 are displayed. I want all records between 0302 and 0304. Could anyone tell
me where I was wrong.


1st SQL

PARAMETERS[Date to summerise] date, [Date to summerise1] date; SELECT tblStock.StockID, tblStock.Description, tblStock.UnitOfMeasureID,
Format(CDate(&quot;00&quot; & Mid(tblPattern.BaleNo,3,2) &
Mid(tblPattern.BaleNo,1,2)),'yymm') AS Expr1,
tblPattern.Pattern, tblPattern.Dye, tblPattern.QuantityReceived,
tblPattern.Quantity, tblPattern.JobID, tblPattern.SBN, tblPattern.Notation
tblPattern.LocationID, tblPattern.ActualCost, [Quantity]*[ActualCost] AS
Total, zz_RepPar.StockStatusID, tblPattern.USCCategoryID,
tlkpStockStatus.StockStatusDescr
FROM tblStock RIGHT JOIN ((zz_RepPar LEFT JOIN tlkpStockStatus ON
zz_RepPar.StockStatusID = tlkpStockStatus.StockStatusID) LEFT JOIN
tblPattern ON zz_RepPar.StockStatusID = tblPattern.StockStatusID) ON
tblStock.StockID = tblPattern.StockID
WHERE Format(CDate(&quot;00&quot; & Mid((tblPattern.BaleNo),3,2) &
Mid((tblPattern.BaleNo),1,2)),'yymm') >= [Date to summerise] And
Format(CDate(&quot;00&quot; & Mid((tblPattern.BaleNo),3,2) &
Mid((tblPattern.BaleNo),1,2)),'yymm') <=
[Date to summerise1] AND ((tblPattern.Quantity)<>0);

2nd SQL

SELECT tblStock.StockID, tblStock.Description,
tblStock.UnitOfMeasureID, Format(CDate(&quot;00&quot; & Mid([BaleNo],3,2) &
Mid([BaleNo],1,2)),'yymm') AS Expr1, tblPattern.Pattern, tblPattern.Dye,
tblPattern.QuantityReceived, tblPattern.Quantity, tblPattern.JobID,
tblPattern.SBN, tblPattern.Notation, tblPattern.LocationID,
tblPattern.ActualCost, [Quantity]*[ActualCost] AS Total,
zz_RepPar.StockStatusID, tblPattern.USCCategoryID,
tlkpStockStatus.StockStatusDescr
FROM tblStock RIGHT JOIN ((zz_RepPar LEFT JOIN tlkpStockStatus ON
zz_RepPar.StockStatusID = tlkpStockStatus.StockStatusID) LEFT JOIN
tblPattern ON zz_RepPar.StockStatusID = tblPattern.StockStatusID) ON
tblStock.StockID = tblPattern.StockID
WHERE (((Format(CDate(&quot;00&quot; & Mid([BaleNo],3,2) &
Mid([BaleNo],1,2)),'yymm')) Between [forms]![form1]![uscno1] And
[forms]![form1]![uscno2]) AND ((tblPattern.Quantity)<>0));