Delphi SQL IN Command at Runtime 1

[filipe26]

Hi What's the sintaxe for SQl command IN on a querie at runtime.

I have :

dm.entradasq.sql.Add('Where origem in ("'+s+'&quot');

and s:='Filipe Lourenço','CArla Sofia Alves '

but gives me error.

 

[LucieLastic]

hi

That looks ok, what's the error? and can you paste the rest of the query ?

lou

 

[Zathras]

You want the final SQL string to look like this:

  ... Where origem in ('Filipe Lourenço','CArla Sofia Alves') ....

To get that result, you could use something like this:

 dm.entradasq.sql.Add( ' Where origem in (' );
 dm.entradasq.sql.Add( QuotedStr('Filipe Lourenço') + ',');
 dm.entradasq.sql.Add( QuotedStr('CArla Sofia Alves') );
 dm.entradasq.sql.Add( ') ');

A couple of things to watch out for:
1. Be sure to have "white space" in front of the "Where" keyword and after the last &quot"
2. Watch out for trailing spaces in the name constants.

 

[LucieLastic]

I didn't read the message properly, did I?

bah! half asleep today!
lou

 

[filipe26]

Ok well the trick was Quotedtostr.

Thanks.

 

[filipe26]

Ok I have another problem that is the fact that if i hace a query by date dont give me results,Ex:

select x from x
where origem in (..+','+...)also resolved
And da>=Datetostr(cal.date)
And da<=DAtetostr(cal.enddate)


it seems that if i add the two last lines dont match any records.

da=datetime field
cal.date and cal.enddate=datetime field and =11-04-2003

obvious that 11-04-2003 is in the table.

Thanks a lot.

 

[filipe26]

Sorry the sintaxe of sql i have is

dm.entradasq.SQL.Add('and da='+Datetostr(cal.date)+'');
dm.entradasq.SQL.Add('and da<='+DatetoStr(cal.EndDate)+'');

 

[Zathras]

What DBMS are you using?

If SQL Server, then try this:

dm.entradasq.SQL.Add('and da>='+QuotedStr(Datetostr(cal.date))+'');
dm.entradasq.SQL.Add('and da<='+QuotedStr(DatetoStr(cal.EndDate))+'');

(Note the >= instead of = in the first line.)

Or try this:

dm.entradasq.SQL.Add(' AND da BETWEEN '+QuotedStr(Datetostr(cal.date))+' AND '+QuotedStr(DatetoStr(cal.EndDate))+'');

If you're not using SQL Server, you may not need the QuotedStr( ). The problem may just be the = instead of >=.

 

[filipe26]

thanks but i already did that.The error is :

General Sql Error.Microsoft Access ODBC&quot;Field Type Incorrect in Expression&quot;

 

[filipe26]

thanks but i already did that.The error is :

General Sql Error.Microsoft Access ODBC&quot;Field Type Incorrect in Expression&quot;

And sorry i forgot to post the >

 

[Zathras]

I think Access wants dates expressed with the &quot;#&quot; symbol, as in:

dm.entradasq.SQL.Add(' AND da BETWEEN #'+ Datetostr(cal.date)+'# AND #'+DatetoStr(cal.EndDate)+'# ');

 

[filipe26]

Yes in did

dm.entradasq.SQL.Add('AND Da>=#'+formatdatetime('mm,dd,yyyy',cal.date)+'#');
dm.entradasq.SQL.Add('AND Da<=#'+formatdatetime('mm,dd,yyyy',cal.enddate)+'#');

That's it.

Thanks a lot.