Help! SQL syntax problem

[Polariz666]

Hey guys,

I'm currently writing a program that pulls data out of a database for statistical purposes. The following SQL command is wrong somehow and I can't figure out why.

rs.Open "SELECT Name, S, Time, Tid FROM TstArchive WHERE Time LIKE '" & passDateToQueryField.Text & "*' ", db, adLockOptimistic

It is designed with the following data in mind:

Time: 20000623121426
Tid: 2 or 3 (All data in the database is in String format)

In the text box 'passDateToQueryField.Text' there will be a value of something like '2000062312', and I obviously want to find all records that are like this, ignoring the '1426' part of the example. However at the moment the SQL statement only ever looks for records equal to 2000062312, and hence never finds any records.

Any help really appreciated,

Mike.

 

[MakeItSo]

Hi Mike.

Since you are not running the SQL in Access, you must use the Standard SQL wildcard "%" instead of the asterisk:

rs.Open "SELECT [Name], S, Time, Tid FROM TstArchive WHERE Time LIKE '" & passDateToQueryField.Text & "%' ", db, adLockOptimistic

Plus: try to avoid using reserved words such as "Name" or "Date" as column names - will save you loads of trouble. ;-)

Cheers,
Andy

[blue]The last voice we will hear before the world explodes will be that of an expert saying:
"This is technically impossible!" - Sir Peter Ustinov[/blue]
HP:

http://andygalambos.de

 

[Polariz666]

That's brilliant mate thankyou.

I would try to stay away fro mreserved names but unfortunately another piece of software not written by me makes the database and populates it with data. Also you may have noticed that the time data is backwards too hehe, the date that is 23/06/2000 gets stored as 20000623, which I have no idea why someone chose to write their software in that way and is causing a right headache!

Thanks again,

Mike.

 

[johnwm]

In the olden days (before the common use of Date datatypes) dates were commonly stored that way so you don't have any problem with sorting them, or doing Greater Than etc.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[Polariz666]

Ah nice, thanks for that John.