Problem with date joins -- Access tables and linked SQLServer tables

[KHerm]

I've run into a problem joining with dates. I'm linking an Access table with a linked SQLServer table in Access. I have a date in each table that looks like: 10/10/2000 12:15:25PM.

The join works if I put the following in the criteria field:
format([table1].[startdate],"mm/dd/yyyy&quot=format([table2].[startdate],"mm/dd/yyyy&quot

I've even tried creating an Access table from the SQLServer table, but the join still has been working only with the format date criteria above.

Any other suggestions? The query seems to be taking forever and I suspect it's because of the format criteria.

Thanks for any ideas.

Ken

 

[cmmrfrds]

The slowness is probably not due to the format function as much as the fact of using a linked table in the join operation. The solution will be in trying to avoid the join between the local and linked table.

You are lucky there are a number of options available with SQL Server. Some of these options depend on the versions of Access and sql server. What versions? There is an additional option if the Access table is relatively few rows. How many rows in each table is typical?

Have you considered an Access Project instead on an Access mdb file. Do you use ADO?

 

[cmmrfrds]

Check this thread705-411835

 

[KHerm]

I'm stuck with Access97 at this location. I'm dealing with over 500,000 rows in the SQLServer (version 7) table and I'm trying to create a working Access table from it to run some reports.

My other queries are not super fast, but they definately aren't as slow as the way I've joined them.

I'm interested in knowing about the other options know that you know the Access and SQLServer versions.

Thanks for taking the time to reply!

Ken
(Also I'm not enough of an Access user to know what ADO is.)

 

[cmmrfrds]

First, ADO is a library to interface with data objects. The default in Access 97 is the DAO library, but you can use ADO also. You would need to add a reference to the ADO library.

Check the other thread since I went over the options in that thread.

What is the reason you can't use the sql server tables directly in your reports. A pass-through query is reasonably fast.

How many rows of the 500K do you actually need on the report?

Show an example of the query.