Using BETWEEN to query data

[ScottK1396]

I have two tables, tblComputers and tblIPSite, where I would find site information for my systems. tblComputers has an IP address formatted as numeric, and tblIPSite has Start and End IPs in the same format.

I tried to use Computers LEFT JOIN tblComputers ON tblComputers.IP BETWEEN tblIPSite.Start AND tblIPSite.End only to find that the join type is not supported.

Using a simple query, I can use WHERE tblComputers.IP BETWEEN tblIPSite.Start AND tblIPSite.End to derive the corresponding tblIPSite.Sitename. However, any tblComputers records with tblComputers.IP values falling outside of those ranges will not appear in my query results. That would be of interest for security and maintenance reasons.

Is there a way to duplicate the results of a LEFT JOIN, that displays all tblComputers records?

 

[PNGBill]

Here is an sql I have played with. Doesn't replicate your situation as I have entered the start and finsh values where as ou have them in your records.
The sql returns all records for TBLLOAN and only thos records in tblBulkLateFees where there is matching data ie there are a number of empty values. - it may assist ?

SELECT TBLLOAN.LDPK AS FirstLoanID, TBLLOAN.LDPK AS LastLoanID, tblBulkLateFees.LateFee
FROM TBLLOAN LEFT JOIN tblBulkLateFees ON TBLLOAN.LDPK = tblBulkLateFees.LoanID
WHERE (((TBLLOAN.LDPK)>4000) AND ((TBLLOAN.LDPK)<5000));

If you only have a Start and End IP Address, then how can you have an empty value when they are not all listed ?

 

[ScottK1396]

I have seen an example like that. I'm using:
SELECT tblComputers.Computer, tblIPSite.IPLocation, tblComputers.IPNumeric
FROM tblIPSite, tblComputers
WHERE (((tblComputers.IPNumeric) BETWEEN [tblIPSite].[StartIP] AND [tblIPSite].[EndIP]));
It's a bit diffferent in that your criteria is hardcoded (4000 and 5000) and mine are variable - values in a field.
The drawback is that records not meeting that criteria (about 5%) do not get listed.

I'm looking for something that works like
SELECT tblComputers.Computer, tblIPSite.IPLocation, tblComputers.IPNumeric
FROM tblComputers
LEFT JOIN tblIPSite
ON tblComputers.IPNumeric BETWEEN [tblIPSite].[StartIP] AND [tblIPSite].[EndIP];
which would get 'all' of tblComputers, and list IPSite info if available.

 

[PNGBill]

This is a solution for Dates. Looks like a subquery
http://stackoverflow.com/questions/9142634/how-to-do-self-join-on-min-max