Date fields in SQL queries

[Guest]

I am struggeling with date fields in queries to MS SQL200 server tables.
After having defined the recordset, I open the recordset with a query like
this:

SELECT <fields> FROM <table> WHERE <date-field-in-SQL-server-table> <=
<date-field-in-Access>

The system returns an arror because the date field in the SQL Server table
cannot be equated with the date field in Access.

Has anyone a solution for this?

/Leif S.

 

[Nick 'The database Guy']

What is the format of the date in SQL server then?

Nick

 

[Guest]

Nick,

The date field in the SQL server is of type smalldatetime and the server is
set opp to Norwegian date format which is "dd.mm.yyyy-hh.mm.ss"

/Leif S.

 

[Nick 'The database Guy']

Hi Leif,

Try inserting the following code where the criteria is

iif(len(datepart("m", date)) = 1 , 0 & datepart("m",
date),datepart("m", date)) & "/" & iif(len(datepart("d", date)) = 1 , 0
& datepart("d", date),datepart("d", date)) & "/" & datepart ("yyyy",
date)

Hope that this helps,

Good luck,

Nick

 

[Guest]

Nick,

I have finally reached a solution or at least a work-around. I convert the
date field to a string formatted to yyyymmdd. Then I use this field in my
query.

Thanks for your help.

/Leif S.