Forms parameters in the MS Access URL string

[nector]

I need help to solve the parameters in this URL string below which is working okay as long as the parameters are hard codded , now I want the parameters to be more flexible by providing some kind of input form parameters like below:

Here are the parameters:

Me.txtlatsouth = -15 (where there is -15 in the url must be replaced by Me.txtlatsouth)
Me.txtlongNorth = 28 (where there is 28 in the url must be replaced by Me.txtlongNorth)

MYURL = "https://api.openweathermap.org/data/2.5/weather?lat=-15&lon=28&appid=XXXXXXXXXXXXID"

I tried it several times I cannot get it right

 

[June7]

You need to concatenate. You are not showing any concatenation.

MYURL = "https://api.openweathermap.org/data/2.5/weather?lat=" & Me.txtlatSouth & "&lon=" & Me.txtlongNorth & "&appid=XXXXXXXXXXXXID"

 

[nector]

This what I'm doing :

MYURL = "https://api.openweathermap.org/data/2.5/weather?lat=" & Me.txtlatsouth & "&lon= " & Me.txtlongNorth & "&appid=xxxxxxxxxxxxxxxx"

Its failing it bring type mismatch

 

[June7]

It is just building a string so if MYURL is declared a string type, this should work.

Type mismatch on what?

If you want to provide db for analysis, follow instructions at bottom of my post. Or at least provide complete code. And please use CODE tags.

 

[nector]

its actual leaving spaces on Me.txtlatsouth and Me.txtlongNorth

MYURL = "https://api.openweathermap.org/data/2.5/weather?lat=" & Me.txtlatsouth & "&lon= " & Me.txtlongNorth & "&appid=xxxxxxxxxxxxxxxx"

No space is allowed the URL should like below then it works ok, the issue is how to remove the from above: but Im now doing manually which I do not want or hard coding

MYURL = "https://api.openweathermap.org/data/2.5/weather?lat=-15&lon=28&appid=xxxxxxxxxxxxxxxxxxxxxxxxxxx"

 

[nector]

Okay finally a wrapper has work to remove spaces

Function spaceremove(strs) As String
Dim str As String
Dim nstr As String
Dim sstr As String
Dim x As Integer
str = strs

For x = 1 To VBA.Len(str)
    sstr = Left(Mid(str, x), 1)
    If sstr = " " Or sstr = " " Then
    Else
        nstr = nstr & "" & sstr
    End If

Next x
spaceremove = nstr
End Function

 

[cheekybuddha]

Break it down:

Dim MYURL As String
Dim MyLAT As String
Dim MyLON As String
Dim MyPARAMS As String
Const BASE_URL As String = "MYURL = "https://api.openweathermap.org/data/2.5/weather"
Const APP_IP As String = "appid=xxxxxxxxxxxxxxxx"

If Len(Trim(Me.txtlatsouth & vbNullString)) > 0 Then
  MyLAT = "lat=" & Trim(Me.txtlatsouth)
End If
If Len(Trim(Me.txtlongNorth & vbNullString)) > 0 Then
  MyLON = "lon=" & Trim(Me.txtlatsouth)
End If
MyPARAMS = MyLAT
MyPARAMS = MyParams & IIf(Len(MyPARAMS) > 0 And Len(MyLON)> 0, "&", vbNullString) & IIf(Len(MyLON), MyLON, vbNullString)
MyPARAMS = MyParams & IIf(Len(MyPARAMS), "&", vbNullString) & APP_ID
MYURL = BASE_URL & "?" & MyPARAMS
Debug.Print MYURL

 

[June7]

Don't know why there would be space. In my test, Access drops space from positive number when concatenating to string.
But using Trim() function should take care of regardless.

 

[theDBguy]

I'm curious as well as to why there would be spaces but perhaps also try using CStr() to explicitly convert the number to a string, and you can either deal with the space or perhaps the space would automatically disappear.

Just a thought...

 

[cheekybuddha]

Okay finally a wrapper has work to remove spaces

Easier wrapper if you really need to go that route:

Function spaceremove(strs) As String
  spaceremove = Replace(strs, " ", vbNullString)
End Function

 

[CJ_London]

Don't know why there would be space.

because of the potential for a negative number?

 

[June7]

Yes, but as I said, Access dropped the space in my concatenation test. So no idea why OP still gets spaces in the concatenated string.

 

[CJ_London]

Perhaps OP is storing numbers as text?

 

[nector]

Many thanks to all contributors all your answers are valuable to me

 

[The_Doc_Man]

In post #5, where there can't be a space, there IS one. If it is a typo because of manual entry of the string, the space is understandable - but if that string was entered to the forum via cut/paste then look more carefully.

MYURL = "https://api.openweathermap.org/data/2.5/weather?lat=" & Me.txtlatsouth & "&lon= " & Me.txtlongNorth & "&appid=xxxxxxxxxxxxxxxx"

You show concatenation of & "&lon= " & ... - which puts a space in the string right after the equals sign. I would just edit that concatenation string and then use TRIM() around your call-outs for me.txtlatsouth and me.txtlongNorth.

 

[June7]

Ooops, good catch, Doc. I did not see that errant space in string.

 

[The_Doc_Man]

As a totally separate nit-pick... You can talk about latitudes being N or S (+ or -) but longitudes will be E or W (+ or -). Therefore, txtlongNorth is a little bit confusing with regard to nomenclature. But, it ain't my problem to remember what you REALLY meant, so don't read too much into the comment.