Access 97: Select Case VBA to return values to query (1 Viewer)

[Velocipedus]

Depending on the value of "imagecount", this function should return a string of 1, 2, or 3 image URLs for the same book record, separated by a space.
I think the Select Case logic is OK,

but I think I need one more line so that the function actually returns a value. What is the magic word?

The function will be called in a query in this manner:
... (fImgURLs([image_count])) AS URL

Is the manner in which the [books_id] field is embedded in the statement the correct way to output the URL ?

Public Function fImgURLs(image_count As Integer) As String

Dim ImgURL As String

Select Case image_count

Case Is = "3"
ImgURL = "LINKREMOVED.com/images/books/" & "[books_id]" & ".jpg" & " " & "LINKREMOVED.com/images/books/" & "[books_id]" & "_2" & ".jpg" & " " & "LINKREMOVED.com/images/books/" & "[books_id]" & "_3" & ".jpg"

Case Is = "2"
ImgURL = "LINKREMOVED.com/images/books/" & "[books_id]" & ".jpg" & " " & "LINKREMOVED.com/images/books/" & "[books_id]" & "_2" & ".jpg"


Case Is = "1"
ImgURL = "LINKREMOVED.com/images/books/" & "[books_id]" & ".jpg"


Case Else
ImgURL = ""

End Select

End Function

 

[arnelgp]

...
...
...
Case Else
ImgURL = ""

End Select
fImgURL = ImgUrl
End Function

 

[Velocipedus]

Thanks a bunch, that seems to do it.

But there is an issue about the [books_id] field. The query represents the resulting URL as
URL..com/images/books/[books_id].jpg URL..com/images/books/[books_id]_2.jpg

the spacing is right, but rather than the name of the variable, I need to see the variable itself here. How do I have to write it in the Module to achieve this?

 

[Galaxiom]

The "[books_id]" is a literal string.

If you want the value from the field then you would need to pass it to the function as another parameter.

 

[arnelgp]

yes, as galaxiom has stated, modify your query to pass another parameter (book_id)

... (fImgURLs([image_count], [book_id])) AS URL

then modify your function:

Public Function fImgURLs(image_count As Integer, books_id as variant) As String

Dim ImgURL As String

books_id = replace(books_id & "", ".jpg", "")

Select Case image_count

Case Is = "3"
ImgURL = "LINKREMOVED.com/images/books/" & books_id & ".jpg" & " " & "LINKREMOVED.com/images/books/" & "[books_id]" & "_2" & ".jpg" & " " & "LINKREMOVED.com/images/books/" & "[books_id]" & "_3" & ".jpg"

Case Is = "2"
ImgURL = "LINKREMOVED.com/images/books/" & books_id & ".jpg" & " " & "LINKREMOVED.com/images/books/" & "[books_id]" & "_2" & ".jpg"


Case Is = "1"
ImgURL = "LINKREMOVED.com/images/books/" & books_id & ".jpg"


Case Else
ImgURL = ""

End Select
fImgURLs = ImgURL
End Function

 

[Velocipedus]

perfect, the baby is running nicely now and the image URLs come out well-formed and fast! Your support is much appreciated