Simcfc73 Posted April 2, 2008 Posted April 2, 2008 Hi, I've got a couple of issues with Sims (well 5 in total) and a couple of them they have said that they can't replicate the issue with my data at their place so it must be something wrong locally. They both seem to be related to UDF that I have created. The latest on is for a date field. When I run the report in Excel this fields data is wrong. The date seems to flip round when its allowed to do so. So when a date is entered as 11/01/2008 it flips it round to 01/11/2008 If the date can't be flipped round say 17/01/08 it leaves it alone. When I export it to RTF the date seems to be fine. They said I should check the regional settings and then came back and said its a local system problem as they can't get it to fail on their system. The other issue is running a report I get 'Subquery returned more than 1 value.' Anyone else had any problems with UDF's.
enjay Posted April 2, 2008 Posted April 2, 2008 On the face of it, that does sound like a date format / regional setting question. If this is so and Capita can't replicate it, ask them how they have their test environment set up and then match your settings to theirs and try again.
PhilNeal Posted April 2, 2008 Posted April 2, 2008 As a guess the machine you are using has an American date setting.
Simcfc73 Posted April 2, 2008 Author Posted April 2, 2008 So the machine has selective American settings??? I need to look in the registry and work out the setting that selects column B to change some of the settings to US if it can but leaves the ones it can't and leaves column C alone. Also it only switches on the US settings via Excel but not word. Need to speak to Microsoft cos this must be a OS error. Thanks Sims people for pointing out M$ core problems. CAN YOU SMELL THE SARCASM??????????????????
jcollings Posted April 2, 2008 Posted April 2, 2008 I have a vague recollection of this issue a while back. I'm not in til Monday but I'll dig through my stuff and see if I can find anything.
enjay Posted April 3, 2008 Posted April 3, 2008 I did detect your sarcasm, but I have seen SIMS do very funny things with exporting dates, and presenting them in a format different to what Excel seems to think they are. Also it only switches on the US settings via Excel but not word. In which case, can you export your report to Word (complete with UK settings) and then paste that into Excel?
Ric_ Posted April 3, 2008 Posted April 3, 2008 The reason that world might not be exhibiting the issue is because it purely treats the dates as text. Excel will try to be clever and format the cells as 'date' and if your regional settings are wrong it will mess this up. It does sound more like a Windows or Excel issue than SIMS. You could try opening your export in a different spreadsheet app - e.g. OpenOffice Calc.
Simcfc73 Posted April 3, 2008 Author Posted April 3, 2008 The reason that world might not be exhibiting the issue is because it purely treats the dates as text. Excel will try to be clever and format the cells as 'date' and if your regional settings are wrong it will mess this up. It does sound more like a Windows or Excel issue than SIMS. You could try opening your export in a different spreadsheet app - e.g. OpenOffice Calc. But why would it only do selective changes and why would other exported data display correctly? It only messes up the UDF field that I created, the other date fields that are exported are all correct. This is the data exported to Word Mr, **** 11 March 2008 17/03/2008 11/03/2008 08 March 2008 The same data in excel Mr, **** 11-Mar-08 17/03/2008 03/11/2008 08-Mar-08 Must be an issue with UDFs.
PhilNeal Posted April 3, 2008 Posted April 3, 2008 I will ask someone to try this out for you again. I don't suppose you have a case reference?
Domino Posted April 3, 2008 Posted April 3, 2008 But why would it only do selective changes and why would other exported data display correctly? Cause word just treats it as text, whereas excel formats it as a date. It's not an OS error, its a a misconfiguration. look in your date/time and regional settings hope this helps
Simcfc73 Posted April 3, 2008 Author Posted April 3, 2008 Nope, every setting on the computers I have tried is set to UK, there's no settings either in the regional settings or in Excel that are not set to UK. Logically it would change the other dates to US if it was set wrong wouldn't it?
enjay Posted April 3, 2008 Posted April 3, 2008 As I said earlier, can you export the report to Word and then paste it into Excel? A bit cumbersome, but if it works...
Domino Posted April 3, 2008 Posted April 3, 2008 Wouldn't necessarily change all the dates, as I said, just those which are processed as a "date" and not as Text. Possibly on the server? just need to check all possibilities.
PhilNeal Posted April 5, 2008 Posted April 5, 2008 Just to confirm what the SIMS Service Desk have done. We have taken your data onto our machine and run the report without exhibiting the problem that you describe. They were able to replicate it by doing the following: • Go to Control Panel • Select Regional Settings • Select Regional Options tab • Top drop down menu to English (United States) • Customize button – select date tab • Changed short date format to MM/dd/yyyyy So this would indicate that it is an environmental setting at the school in Regional Settings. Hopefully you will be able to resolve this now.
Oops_my_bad Posted April 7, 2008 Posted April 7, 2008 Just to confirm what the SIMS Service Desk have done. We have taken your data onto our machine and run the report without exhibiting the problem that you describe. They were able to replicate it by doing the following: • Go to Control Panel • Select Regional Settings • Select Regional Options tab • Top drop down menu to English (United States) • Customize button – select date tab • Changed short date format to MM/dd/yyyyy So this would indicate that it is an environmental setting at the school in Regional Settings. Hopefully you will be able to resolve this now. Lmao.. pwnt
Simcfc73 Posted April 7, 2008 Author Posted April 7, 2008 Lmao.. pwnt New acronym to me but thanks Every setting on the server is Uk English, every setting on the workstation is Uk English. Have tried Excel 2003 and 2007 both with the same results. I changed the regional settings to US and every date changed to US not just selective ones as my original problem described.
PhilNeal Posted April 7, 2008 Posted April 7, 2008 This is bizarre – the service desk has booked a LAN desk into your system to see what is happening on your site.
PhilNeal Posted April 16, 2008 Posted April 16, 2008 Something very odd indeed was happening here. A re-install of SQL Server has, I understand, solved the issue. We just could not replicate on our set-ups.
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now