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.
Database101.accdb
Is there any way to overcome the regional settings and input the double value correctly.

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.

LVL 52

Dale FyeOwner, Developing Solutions LLCCommented: 2018-02-22

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))

HTH
Dale

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

LVL 56

Gustav BrockCIOCommented: 2018-02-22

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.

Dale

faraoosirisAuthor Commented: 2018-02-22

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: 2018-02-23

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 FyeOwner, Developing Solutions LLCCommented: 2018-02-23

glad to help

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

Who is Participating?