Dato query

try making a VIEW in Enterprise Manager. This gives you a visual representation of your tables. Once this is working you have your query!

I have done that, but it returns an empty recordset...?
If I try to write out the tables independant of each other, it is very obvious that there's a match, and that the recordset shouldn't be empty.

Your date format MUST be either MM-DD-YY or YYYY-MM-DD

If I write out the date format that MS SQL uses it's MM/DD/YYYY HH:NN:SS
If I write out the date format on my variables it's MM/DD/YYYY HH:NN:SS
???

Se also....

http://www.eksperten.dk/spm/264832

can we see the SQL

I have tried.....
------------------
1
SELECT    SUM(log1.log_ip) AS ips
FROM        log1 INNER JOIN
                      log2 ON log1.log1_index = log2.log1_index
WHERE (log1.log1_datetime BETWEEN #30/09/2002# AND #02/10/2002#)
GROUP BY log1.log_ip
------------------
2
SELECT    SUM(log1.log1_count) AS ips
FROM        log1 INNER JOIN
                      log2 ON log1.log1_index = log2.log1_inndex
WHERE (log1.log1_datetime BETWEEN 30/09/2002 AND 02/10/2002)
GROUP BY log1.log1_ip
------------------
3
SELECT    SUM(log1.log1_count) AS ips
FROM        log1 INNER JOIN
                      log2 ON log1.log1_index = log2.log1_inndex
WHERE (log1.log1_datetime BETWEEN #09/30/2002# AND #10/02/2002#)
GROUP BY log1.log1_ip
------------------
4
SELECT    SUM(log1.log1_count) AS ips
FROM        log1 INNER JOIN
                      log2 ON log1.log1_index = log2.log1_inndex
WHERE (log1.log1_datetime BETWEEN 09/30/2002 AND 10/02/2002)
GROUP BY log1.log1_ip
------------------
5
SELECT    SUM(log1.log1_count) AS ips
FROM        log1 INNER JOIN
                      log2 ON log1.log1_index = log2.log1_inndex
WHERE (log1.log1_datetime BETWEEN #09/30/2002 00:00:01# AND #10/02/2002 11:59:59#)
GROUP BY log1.log1_ip
------------------
6
SELECT    SUM(log1.log1_count) AS ips
FROM        log1 INNER JOIN
                      log2 ON log1.log1_index = log2.log1_inndex
WHERE (log1.log1_datetime BETWEEN 09/30/2002 00:00:01 AND 10/02/2002 11:59:59)
GROUP BY log1.log1_ip
------------------
7
SELECT    SUM(log1.log1_count) AS ips
FROM        log1 INNER JOIN
                      log2 ON log1.log1_index = log2.log1_inndex
WHERE (log1.log1_datetime BETWEEN #30/09/2002 00:00:01# AND #02/10/2002 11:59:59#)
GROUP BY log1.log_ip
------------------

Number 2 and 4 returns a empty recordset, and the others fail

SELECT    SUM(log1.log1_count) AS ips
FROM        log1 INNER JOIN
                      log2 ON log1.log1_index = log2.log1_inndex
WHERE (log1.log1_datetime BETWEEN '30/09/2002 00:00:00' AND '02/10/2002 23:59:59')
GROUP BY log1.log1_ip

The conversion of a char data type to a datatime type resulted in a out-of-range datatime value.

but....

This one almost worked....

SELECT    SUM(log1.log1_ip) AS ips
FROM        log1 INNER JOIN
                      log2 ON log1.log1_index = log2.log1_index
WHERE (log1.log1_datetime BETWEEN '09/30/2002 00:00:00' AND '10/02/2002 23:59:59')
GROUP BY log1.log1_ip

Except it returned ips = 8 (and there's only 1 ipaddress in the tables, so the grouping didn't work)?

In ACCESS this would work
BETWEEN #09/30/2002 00:00:00# AND #10/02/2002 23:59:59#
In SQL Server this would work
BETWEEN '09/30/2002 00:00:00' AND '10/02/2002 23:59:59'
I cant see why this deosnt fail!
BETWEEN 09/30/2002 AND 10/02/2002

So try this
BETWEEN 2002-09-30 AND 2002-10-02

Well I actually works now.... Thank to you Terry!

selv tak :o)

Pretty please.... http://www.eksperten.dk/spm/265746
It will properly only take you a second! :)

Doesn't matter a found out! :)