Query Criteria Format Access Front SQL Backend 1

[ssatech]

I have a query I run from Access 97 frontend. Query opens based on Delivery Time, criteria #00:00:00#. Runs fine with Access back end, but not with SQL Server 2000 backend. Could someone please tell why this is so?

SQL Server backend :

Column Name Data Type Length Allow Nulls
DeliveryTime datatime 8 checked

Default Value = Blank

Access 97 Frontend:

Field Name Data Type
DeliverTime Date/Time

Input Mask: 00:00;0;_

Any help would be appreciated.....Thanks!

 

[vongrunt]

SQL Server doesn't support date literals (##).
Use single quotes instead.

 

[ssatech]

I tried using single quotes. But a now get an error that reads:

"This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variable"

Any ideas would be appreciated..Thanks again...

 

[vongrunt]

Er... This is Access runtime error 3071.

Dunno about Access frontent but you'll probably have to write query specifically for SQL Server.

 

[SQLBill]

You probably have some Access code in the script that is unrecognized in SQL Server. Can you post your code?

-SQLBill

 

[ssatech]

Hi Bill,

I have no code. I am running the query from linked tables in the front with a criteria. I have tried using criteria with the following for deliverytime:

#00:00:00#
#13:00:00#

'00:00:00'
'13:00:00'

But still no avail. I have even tried placing the default value in SQL Server with: (0) or '00:00:00' and still nothing. The query works fine with criteria #00:00'00# or #13:00:00# and Access 97 backend. My Access 97 frontend seems to have problem reading data with 00:00:00 or 13:00:00.

 

[karthik555]

In SQL Server the column type is DateTime and not just time

Try something as follows

insert into tablename values ('21 mar 2004 13:00:00')

 

[SQLBill]

You have to have code (it's your query). How is SQL Server supposed to know what data you want if you don't tell it?

-SQLBill

 

[SQLSister]

You cannot store just time in a datetime field. So you need to adjust your query to account for the date part of the field.

 

[ssatech]

Thanks All,
I figured out the problem If I write the criteria as follows: TimeValue(#00:00:00#), instead of #00:00:00#. The query works fine for SQl Server 2000.