SQL Query Date Field Problems

[Mighty]

I posted a question a while ao about having a problem pulling data from a table by date. I have two tables which are very alike - the difference being that one contains a lot more data.

If I run the following queries:

SELECT * FROM "Transaction History" WHERE TNXDTE='2001-02-23' and TNXCDE='S'
SELECT * FROM "Lot Tracking History" WHERE TNXDTE='2001-02-23' and TNXCDE='S'

the first query will return data but the second will not. It doesn't flag any errors but it just doesn't return any data. I know for certain that there is data in the table because if I query it on a different field it returns data for that date.

Has anyone got any ideas?? Mise Le Meas,

Mighty

 

[Guest_imported]

It sounds like the second query is not recognizing the date format. Try placing a date format function around your field name "TNXDTE"

 

[Linda84]

It could also be a mismatch between the data file and the dictionary definition. Is TNXDTE defined as a Date or a String in the "Lot Tracking History" table? Is it an index? If so, what index type does it have in the Btrieve file (use Maintenance utility)? Do the index numbers match up?

You might want to start off by running a Database Check from PCC.

 

[Mighty]

Guys,

Thanks for the suggestions. I ran a query from Crystal reports on the table trying to find all the fields that had bad dates as this seemed to be causing the problem. I think that this must have re-indexed the table or something because the query i was trying to run is now working.??? Mise Le Meas,

Mighty