Querying a Paradox Database with ADO

[Jagata]

Hello,

I have connected sucessfully to a Paradox Database using both a System DSN and a Microsoft Access linked table setup.

I am trying to query table ACTIVITY, with a criteria for a specific date. The query is quite simple;

SELECT *
FROM ACTIVITY
WHERE ACTV_BEGIN_DATE = #27-Aug-2003#

The table has records dating from the 26/08/2003 and onwards. The query returns rows where ACTV_BEGIN_DATE is equal to the 26th or the 27th. If I then change the query to;

WHERE ACTV_BEGIN_DATE = #28-Aug-2003#

It then returns rows for the 26th, 27th, and 28th. This occurs regardless of the access method.

I have simplified the query slightly to try and illustrate my problem. The final query will be specific regarding selection of fields, and will use a similar WHERE clause to the one below;

WHERE ACTV_BEGIN_DATE BETWEEN #1-Aug-2003# AND #31-Aug-2003#

Thanks,

Jason.

 

[Langley]

Have you tried changing the date format to match the one in the date field? Also, I don't know about ADO but in Paradox SQL the date should be between single quotes.

Mac

"There are only 10 kinds of people in this world... those who understand binary and those who don't"

langley_mckelvy@cd4.co.harris.tx.us

 

[dcpking]

SELECT *
FROM ACTIVITY
WHERE ACTV_BEGIN_DATE = #27-Aug-2003#

have you tried something like

SELECT *
FROM ACTIVITY
WHERE ACTV_BEGIN_DATE = "08/27/2003"

which is a more normal date format ?

 

[Jagata]

Hey Guys,

Thanks for the reply, had some servers go down so I have been sooo busy over the last couple of days.

I have tried every date format known to man. It is correct that paradox uses single quotes, but ADO uses #. In ADO a single quotes item is considered literal. If you like take a look here;

http://www.experts-exchange.com/Pro...ges/Visual_Basic/VB_Databases/Q_20786840.html

This shows everything I have tried so far. I'm almost ready to give up, in order to get the project ready by the deadline, I think I am just going to create an SQL Server DTS package to import the data from Paradox.

Jason.

 

[Jagata]

"There are only 10 kinds of people in this world... those who understand binary and those who don't" - Funny