update result to whole numbers or 0.5

[monplankton]

Hi, I'm using 2003 and want to update a table of results that fall between 0~0.5, 0.5~1.0 and so on. Example If the result is 0.7 then I want to over write this with 1.0 and repeat this all the way down the column. Any ideas on the best way of doing this?

 

[Isskint]

An update query would be the way to go. use the following formula in the UPDATE TO row

Iif(lngValue-Int(lngValue) >= 0.5,Int(lngValue) + 1,Int(lngValue)) where lngValue is the value you want to convert

 

[monplankton]

Hi, Thanks for the reply. I've tried what you have written and it says there's an invalid .(dot) or ! operator or invalid parentheses. I don't understand the iif statement so I'm not sure what's wrong with it!

 

[Isskint]

hmmm, strange. can you post the actual expression from your query? Or the whole SQL of the query would be better.

 

[monplankton]

UPDATE tbl_Supplier_Inspection INNER JOIN tbl_Supplier_Inspection_Request ON tbl_Supplier_Inspection.NCMAR = tbl_Supplier_Inspection_Request.NCMARNumber SET
WHERE (((tbl_Supplier_Inspection.Date)>Now()-30));

I think this is what your asking?

 

[Isskint]

The SQL is incomplete (Nothing between SET and WHERE) and does not have any Iif() statements in it.

 

[monplankton]

The iif statement won't save due to the error here is the ttable and qry.

 

[Isskint]

hmm, a bit difficult without all the necessary objects. I have added a field (test) to the table on the attached db, and added it to the query and included the If() statement i posted earlier. Have a look, all you need do is add any other tables/fields and change test in the query to the name of the field you wish to update.

 

[monplankton]

Hi, thanks for getting back to me. I figured out what you meant I have updated the querie but it's giving the results that I'm after, see attached database update. I've marked the old querie as old. In the table there's a column called test time and in there I have added what the results should be updated to, rounded up to the next 0.5.

 

[Isskint]

Puzzled

You say "it's giving the results that I'm after" but then imply the results are wrong?
The only value in your table that the formula will not return is KeyID 100291 with an inspection time of 3.120. Rounding that to the specification (<0.5 = 0, =>0.5 = 1) will give 3 but you are saying it should be 3.5?

 

[monplankton]

Sorry I meant NOT giving me the results. I have added my querie to it qry_SupplierInspectionTimingCorrected1

I have writen the required results in the test time column in the table to help you understand what I'm trying to achieve..

Thanks

 

[Isskint]

OK, just to be clear, if the decimal is less than 0.5 (0.45, 1.36 etc) then you would want the Integer (0, 1 etc) +0.5, so 0.5, 1.5. If the decimal is greater or equal to 0.5 then round up to the next whole integer?

This should give you that;
IIf([lnspection_Time]-Int([Inspection_Time])>=0.5,Int([Inspection_Time])+1,Int([Inspection_Time])+0.5)

 

[monplankton]

Briiiiiiiiiiiiiillant works a treat. Took out the = as it was increasing it when it was .5 to the next whole integer but other than that perfect. Thanks a lot for your help and
Perseverance.

 

[monplankton]

Are you on a nightshift or just up late?

 

[Isskint]

Nightshift

Great to have a job where you can do something other than what you are being paid for

 

[monplankton]

Same here, where do you work?

 

[Isskint]

Logistics company, Norbert Dentressangle, Shit supervisor

 

[monplankton]

I work at the Nissan car plant at Sunderland. Parts Quality Supervisor. Thanks again for your help.

 

[Isskint]

Lol, gotta laugh. Swindon Honda:banghead::banghead:

 

[monplankton]

Are you on full production do there? were still running 3 shifts full volumes, very strange.