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.
faraoosirisAsked:
Who is Participating?
 
Dale FyeCommented:
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
0
 
Gustav BrockCIOCommented:
Yes. Use Str to convert using dot as decimal separator:

TextValue = Str(DoubleValue)

Open in new window

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

Dale
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
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.
0
 
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.
0
 
faraoosirisAuthor Commented:
Thank you for solving my question on such short notice.
0
 
Dale FyeCommented:
glad to help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.