Avatar of faraoosiris
 asked on

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

I downloaded twilight.zip 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.
Microsoft AccessVBAMicrosoft SQL Server

Avatar of undefined
Last Comment
Dale Fye

8/22/2022 - Mon
Dale Fye

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Gustav Brock

Yes. Use Str to convert using dot as decimal separator:

TextValue = Str(DoubleValue)

Open in new window

Dale Fye

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


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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy

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.
Dale Fye

glad to help
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.