Link to home
Start Free TrialLog in
Avatar of faraoosiris

asked on

Problem with the DOUBLE value caused by my regional settings being comma seperated value

I downloaded from the internet. It is an excel sheet able to calculate sunrise and sunset created by Keith Burnett. I took the VBA code and incorporated it in Ms Access. In MS access I created a world clock and now I want to be able to display the sunrise sunset times for the relevant time zone as well.

I tested the VBA code via the Immediate window and this gave the correct values for sunrise and sunset. I ‘am able to type a value for glat in the argument. For example 51.5.

Function Sunrise(ByVal day As Double, glat As Double, glong As Double, index As Integer, _
 Optional altitude) As Double

When I debug the function to check the value of variable glat it gives a value of 51,5. This is with my regional setting with the comma being decimal separator. If I repeat the same procedure for the English setting with the dot separator it states 51.5 in de debug window. In both cases if gives the correct value for sunrise.

I live in the Netherlands and the DOUBLE value gives me problems. I want to transfer the latitude and longitude to the function. When I type the value with the keyboard directly in the arguments of the sub it displays to correct values. However I want to transfer the value of a combobox’s column to the sub. If I change the column of the combobox to double it display the comma  where ms access obvious expects a dot.

I searched the internet and EE. Tried replace,  changing string to value and concatenated the coordinates to a dot value and change it to a value. All to no avail.
Is there any way to overcome the regional settings and input the double value correctly.
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Yes. Use Str to convert using dot as decimal separator:

TextValue = Str(DoubleValue)

Open in new window

Actually, Gustav, I was talking about using Val, to convert the string which is stored in the listbox control to a number.

Avatar of faraoosiris


I called the sub from the form and used val(me.cbo_Latitude.Column(1)) instead of glat. So far it looks promising. I get back to you with feedback.
Your suggestion Dale solved my problem. I used the dot separated value for the coordinates in the me.cbo_Latitude.Column(1) string

Thank you both for the prompt reaction to my question.

Now I get on with the next seemingly problem of not correcty calculating the sunrise for the longitudes beyond  -90.00.
Thank you for solving my question on such short notice.
glad to help