Avatar of faraoosiris
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.
Database101.accdb
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
ASKER CERTIFIED SOLUTION
Dale Fye

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
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
GET A PERSONALIZED SOLUTION
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.

Dale
faraoosiris

ASKER
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
faraoosiris

ASKER
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.
faraoosiris

ASKER
Thank you for solving my question on such short notice.
Dale Fye

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