Thanks for that, after i posted i started looking around and found a better, later version in 2008.

I downloaded this: http://www.microsoft.com/downloads/details.aspx?FamilyId=B5D1B8C3-FDA5-4508-B0D0-1311D670E336&displaylang=en]

Seems to have done the trick, i have lots of...

Hey

Well i finally got it working and as suggested above it was alot easier to do it via the query.

All i ended up doing was left joining the table into...

Ahh cool, its been so long since i did anything like this i seem to have forgotten even the basics 🙂

Going to have a go getting it all working on...

Hi

Thanks for the Input, it sounds logical what you are saying. However, the Do you have there does it need not to be linked to a Rst? I know at...

Never thought about the NULL fields, good call!

As per the code above, excellent it works!!!! Thank you, did just over 811k rows in around 20 seconds 🙂

Thanks to you both...

The value in the table in the field is 08:10 for instance, however as its a NVARCHAR we cant subtract or use DateDiff on the values in To and From...

Then the problem is somewhere else, using the line you typed above i still get the same error.

Need to try and find out what's causing it, it obviously is something...

Hmmm, not sure i understand what you mean. I thought this:

CONVERT(nvarchar(5),HC_IH_TD_ACTUAL_TIME_TO, 108)

Converted the value from NVARCHAR to style 108, which is datetime.

The table is made up of an imported file, and for a reason we cant figure out (and we cant change it) the times are imported into a NVARCHAR(5) field...

If i use this:

SELECT DATEDIFF(N,Data.TimeFrom,DATEADD(mi,1,Data.TimeTo)) TimeDifference

FROM

(

SELECTCONVERT(nvarchar(5),HC_IH_TD_ACTUAL_TIME_TO, 108) TimeTo,

CONVERT(nvarchar(5),HC_IH_TD_ACTUAL_TIME_FROM, 108) TimeFrom

FROM ...

You make a fair point, at the moment my test data is only around 50 records or so all in. When the database goes live it will be open to...

I can see what it's doing wrong, the code is right but the thinking is wrong it seems.

It's getting the record from rst, so the first record. It then opens...

Hey

Thanks for that, you are alot closer than i am that's for sure!

I ran it and it tells me it skips 5 records, which is correct because those 5 are...

Hey

Thanks for the information, the function GetSeq() works fine. Is declared in a module and ive no problems with that side as when it adds the record (wrongly) it adds...

I was hoping to be able to do it in code rather than use more queries, to get to the final query you see in the uses 8 seperate ones...