Update Time Field (1 Viewer)

[Geoff Codd]

Hi there,

I have a table of data which contains a time field which I need to update, I have created a select query to find the records that need updating

SELECT Format([Time],'nn') AS [Minute]
FROM tblFlexima_Profile_Data
WHERE (((Format([Time],'nn')) Between '16' And '45' And Not (Format([Time],'nn'))='30'));

The problem I am having is that when I change it to an update query and tell it to update the selection to 30 it doesn't work.

Any ideas appreciated, thanks in advance

Geoff

 

[Brianwarnock]

Hi

I don't know what your update code was, did you use TimeSerial?

Brian

 

[Geoff Codd]

When I change it to an update query and try and run I get the error

'Format([Time],'nn')' is not a valid name, make sure it does not include invalid characters or punctuation and that it is not too long.

Any ideas

Thanks
Geoff

 

[Brianwarnock]

When I change it to an update query and try and run I get the error

'Format([Time],'nn')' is not a valid name, make sure it does not include invalid characters or punctuation and that it is not too long.

Any ideas

Thanks
Geoff

The code in your update to should be TimeSerial(Hour([Time]),30,0) it will not accept nor does it need the Format function.

Brian

 

[Brianwarnock]

Hi there,

I have a table of data which contains a time field which I need to update, I have created a select query to find the records that need updating

SELECT Format([Time],'nn') AS [Minute]
FROM tblFlexima_Profile_Data
WHERE (((Format([Time],'nn')) Between '16' And '45' And Not (Format([Time],'nn'))='30'));

The problem I am having is that when I change it to an update query and tell it to update the selection to 30 it doesn't work.

Any ideas appreciated, thanks in advance

Geoff

If you prefer to see the SQL it should be

UPDATE tblFlexima_Profile_Data SET tblFlexima_Profile_Data.Time = TimeSerial(Hour([Time]),30,0)
WHERE (((Format([Time],"nn")) Between 20 And 45 And (Format([Time],"nn"))<>30));

Brian

 

[Geoff Codd]

thanks brian, you are a star

thanks
geoff