help with where section of query

[pmcm]

Hi everyone, I developed a query that acts as a datasource for a report but I need some help with finalising the where clause. the two tables in question have a one to many relationship for example the production table has 1 row of data with these 2 fields:
First Serial = 624
Last Serial = 642

and Table A could have multiple rows with serial numbers that fall between the range of 000624 - 000642.

At present the leading zeros are not entered into the First and Last serial fields, but the serial number field in table A contains all serial digits e.g. 000631, how do I compare my serial number from table A to see if its >= First Serial or <= Last Serial from my production table? This is what I have so far:

WHERE ((((tblQuantum.DateEntered) Between [forms]![frmReports]![StartDate] And [forms]![frmReports]![EndDate]) 
AND (tblQuantum.SerialNumber) >= Assembly_tblProduction.FirstSerial AND <= Assembly_tblProduction.LastSerial) 
AND ((tblQuantum.ProductCode) Like "*" & [Forms]![frmReports]![ProductCode] & "*") 
AND ((tblQuantum.BatchNumber) Like "*" & [Forms]![frmReports]![BatchNumber] & "*"));

Thanks for helping!!

 

[plog]

Here's what I heard: You are trying to join two tables where one table has a field who's data looks like this: 642

And another who's data looks like this: 000642

IF that's correct, I'd first advise you to reconsider your structure and make both those fields numeric data types so that you can perform this join without a hack.

Now here's the hack. You can force them both to be numeric in sub-queries and then join those sub-queries together to generate the query you ultimately want. You would bring each table into its own query, bring all the fields down and then create a calculated field like this:

NumValue: CInt(SerialNumber)

 

[pmcm]

after a bit of trial and error to fix syntax issues I now have:

WHERE ( ( (tblQuantum.DateEntered) Between [forms]![frmReports]![StartDate] And [forms]![frmReports]![EndDate] )
AND ( Cint(tblQuantum.SerialNumber) >= CInt(Assembly_tblProduction.FirstSerial) AND Cint(tblQuantum.SerialNumber) <= CInt(Assembly_tblProduction.LastSerial) )
AND ( (tblQuantum.ProductCode) Like "*" & [Forms]![frmReports]![ProductCode] & "*")
AND ( (tblQuantum.BatchNumber) Like "*" & [Forms]![frmReports]![BatchNumber] & "*"));

which seems to work