asked on

Update MS Access table through SQL

Hi,

Database : MS Access
Tablename : ChangeDate

Fieldname Status Date
New 23-12-2004
New 04-03-2004
New 13-11-2004
New 11-06-2004

I need to update one field (Status) in the dateset whenever the date in that record is in the past.

Result:
Fieldname Status Date
New 23-12-2004
Old 04-03-2004
New 13-11-2004
Old 11-06-2004

Can anybody give me the correct statement?

Thanks, Stef

esoftbg

procedure TForm1.btnUpdateClick(Sender: TObject);
var
S: string;
begin
S := FormatDateTime('yyyymmdd', Date);
AdoQuery1.Connection := AdoConnection;
AdoQuery1.Sql.Text := ''
+ ' update CHANGEDATE '
+ ' set STATUS='
+ '''' + 'Old' + ''''
+ ' where DATES<=:TODAY'
+ '';
AdoQuery1.Parameters.ParamByName('TODAY').Value := Date;
AdoQuery1.ExecSQL;
end;

procedure TForm1.ADOQueryCHANGESNewRecord(DataSet: TDataSet);
begin
ADOQueryCHANGES.FieldByName('STATUS').AsString := 'New';
end;

emil

ASKER CERTIFIED SOLUTION

esoftbg

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

esoftbg

The above example expected the database is in the same directory with .exe

Stef Merlijn

ASKER

Thanks.
Why do you this?

var
S: string;
begin
S := FormatDateTime('yyyymmdd', Date);

I don't see you use it futheron.

Stef

esoftbg

this is an unsuccessful attemt to assign TODAY as string, please ignore it....

download working example from :
page: http://www.geocities.com/esoftbg/
link: Q_21090692.zip

esoftbg

> this is an unsuccessful attemt to assign TODAY as string, please ignore it....

mean that:

var
S: string;

and

S := FormatDateTime('yyyymmdd', Date);

to be ignored....

Stef Merlijn

ASKER

Thanks Emil for your help and for the example on your site it works great.

Stef

esoftbg

Hi Delphiwizard,
You are welcome !
I did improve the example on my site. If you are interested, download it again ....
Best Regards,
Emil