Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Topspeed ODBC and SQL query

Topspeed ODBC and SQL query

Topspeed ODBC and SQL query

I'm new to Topspeed ODBC and am wirting the query: "select date, register   from cshthist where date = 6/19/01" using Access 97.  I get the error "invalid operand".  I have tried different variations trying to get the date but no go.  Date is set up in the ODBC manager to convert it to Access style date.  I can select specific other fields but always fails on Date.  I've tried "date = "#6/19/01#". "date = convert(datetime,'06/19/2001') and others.  Any ideas?  Thanks much.

RE: Topspeed ODBC and SQL query

The Topspeed ODBC has a problem with dates especially if you have set up the data source to identify date fields. It is better to query using the integer value of the dates and have a function to convert a Clarion Date to an Access date.
Clarion stores the date as the no of days since Dec 28, 1800 e.g. today's date Jun 26, 2003 is 73959.

P.S.: Although it is not going to work, the proper syntax for the query is to enclose the date in single quotes in the yyyy-mm-dd format e.g. '2003-06-26'

RE: Topspeed ODBC and SQL query

Thanks JShankar,
I had a feeling this was the case.

RE: Topspeed ODBC and SQL query


This should do the trick...
Added a bit more just to show whats possible.

Select  i.invdate, {fn dayofyear(i.Invdate)} AS B, {fn CurDate()} as Date  , OrdH.OrdNo
FROM Invh  i,{OJ OrdH LEFT OUTER JOIN Invh ON Invh.OrdNo = OrdH.OrdNo}
Where  i.Invdate = {d'2003-06-05'}

RE: Topspeed ODBC and SQL query

Thanks a lot, lvdm. You have given me the solution to a problem which has limited me from using Topspeed ODBC effectively for a long time. You do deserve a Star.

I did experiment and found out the {fn dayofyear(date)} returns the no of days from the beginning of the year, {fn dayofmonth(date)} returns the Day part of the date, {fn month(date)} returns the Month part of the date, {fn year(date)} returns the Year part of the date and can be used effectively in queries like select no,date,{fn dayofyear(date)}as dy,{fn dayofmonth(date)}as d,{fn month(date)} as m,{fn year(date)} as y from tranhdr where {fn month(date)} = 2 and {fn year(date)} = 2002.

Are these functions standard ODBC functions? Is there any way to find out the full list of supported functions by the Topspeed ODBC driver?

RE: Topspeed ODBC and SQL query

No problem.

I saw that this is a general problem with Topspeed/Clarion, they never provide any help or example when it comes to things like this. You pay a lot of money for the TPS ODBC drivers, but with NO HELP... go figure.

I used Clarion for the last 5 years successfully, but with LOTS OF LIMITATIONS like this.. so I have vowed to never use it again.
There are a lot better development tools and platforms available.

Here is another site with a bit more info ODBC.


RE: Topspeed ODBC and SQL query

Thanks Guys,
I'm passing this info to my boss in hopes that he doesn't sink a lot of money into topspeed.  There is a potential of over 500 sites that he wants to buy driver licenses for.
One more:
I offered to learn and write my own ODBC drivers for our use on our Clarion databases.  Any ideas on the reality of such an undertaking?

RE: Topspeed ODBC and SQL query

I have looked at the Scalar Functions for Access.

Althought it claims the return types should always be SQL_FLOAT it does not seem the case.

I For example I do something like this:

Select 22/3
from mytable

The result is just 3
There the result should be something like 3.14285714

I am just getting the interger value back, not the entire value.

This becomes and issue when I am trying to manipulate some dates.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close