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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
I'm on my iPad, so I cannot open your database, but my first thought is that the columns( ) property of the listbox will return a string value, not a number, so you might want to try something like:

gLat = val(me.cbo_Latitude.Column(1))


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
Yes. Use Str to convert using dot as decimal separator:

TextValue = Str(DoubleValue)

Open in new window

Dale FyeOwner, Developing Solutions LLCCommented:
Actually, Gustav, I was talking about using Val, to convert the string which is stored in the listbox control to a number.

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

faraoosirisAuthor Commented:
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.
faraoosirisAuthor Commented:
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.
faraoosirisAuthor Commented:
Thank you for solving my question on such short notice.
Dale FyeOwner, Developing Solutions LLCCommented:
glad to help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.