Bizzare Date problem when accessing SQL Server...

[steverbs]

Hi all.

Got a strange one here for you. I'm trying to query an SQL server database from an Access 97 front end, via a linked table. The query uses a date field in the select query and I am trying to get all records where the date = 01/01/04. So I have the following query:

SELECT dbo_BOOKED.URN, dbo_BOOKED.BKDATE
FROM dbo_BOOKED
WHERE (((dbo_BOOKED.BKDATE)=#1/1/2004#));

The strange thing is that it returns 214 rows of records that have the date 09/08/02. What is even weirder is that, in the table being queried, there are 214 records with the date 01/01/04, while there are 336 records with the date 09/08/02! So the query result is pulling the wrong records but is returning the correct number of rows! I have tried creating views in SQL server and linking to these instead and they work fine. It is just when I try to query the inked table that it all goes wrong.

What on earth could be causing this???

Regards.

Stephen.

 

[Terpsfan]

When accessing SQL Server, you usually use the date format inside quotes... '01/01/2004'. I'm not sure if that is the issue for you.

 

[PHV]

Replace this:
WHERE (((dbo_BOOKED.BKDATE)=#1/1/2004#));
By this:
WHERE dbo_BOOKED.BKDATE='2004-01-01';

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[steverbs]

Thanks for the help. I won't be able to try your suggestions until Friday now, but I'll let you know how I get on.

Cheers.

Stephen.

 

[KenReay]

Hi

I think you said it is a linked table not a pass thru query, in which case it should be in Access SQL dialect, so it should be

SELECT dbo_BOOKED.URN, dbo_BOOKED.BKDATE
FROM dbo_BOOKED
WHERE (((dbo_BOOKED.BKDATE)=#2004/01/01#));

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[steverbs]

OK. It wasn't anything to do with the date or query format, but it is sorted. There was no key column in the booked table. I added a uniqueidentifier column and set it to rowgiud so that it would populate the column with new ids and re-ran the query and it worked as it should. Wierd eh?

Regards.

Stephen.