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

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

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

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.

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

Who is Participating?

Already a member? Login.