How concatenate columns in macro

English
#1

Hi.

How to concatenate 2 columns in a macro. The initial query is :

strSQL = "SELECT ""PER_LASTNAME"", ""PER_ID"" FROM ""T_PERSONS"" WHERE ""LEV_ID"" = '" & intFilter & "' ORDER BY ""PER_LASTNAME"""

I would like :

(PER_LASTNAME || ' ' || PER_FIRSTNAME)

So that the last and first names are displayed in the listbox.

Thanks.

#2

Recent topic: Base form: Filter list box entries based on what is selected in another list box - #4 by Villeroy

#3

Thanks, but no problems filtering. I just need to concatenate 2 columns in a query included in a macro.

#4

Don’t understand why you didn’t try

strSQL = "SELECT ""PER_LASTNAME"" || ' ' || ""PER_FIRSTNAME"", ""PER_ID"" 
FROM ""T_PERSONS"" WHERE ""LEV_ID"" = '" & intFilter & "' 
ORDER BY ""PER_LASTNAME"""

… but might be you aren’t using internal databases or there are other problems like “Per_Firstname” without content, which could be solved by COALESCE.

#5

Okay, I made a formatting mistake with the quotes. :face_with_diagonal_mouth:

""PER_LASTNAME || ' ' || PER_FIRSTNAME""

Thanks.

#6

You try to filter some listbox by means of a parameter query

#7

Hello and Merry Christmas… :partying_face:

This is a cascading filter in 3 listboxes. Macros are ok, I just wanted to display the first and last name in a column.

' Filtering classrooms and pupils per level
Sub FiltrerNiveaux
	
	Dim objForm As Object
	Dim objNiveau As Object
	Dim objClasse As Object
	Dim objEleve As Object
	Dim intFiltreNiveau As Integer
	Dim intFiltreClasse As Integer
	Dim strSQL1 As String
	Dim strSQL2 As String
	Dim intClasse As Integer
	Dim intEleve As Integer
	
	objForm = ThisComponent.DrawPage.Forms.GetByName("MainForm")
	objNiveau = objForm.GetByName("lstNivId")
	objClasse = objForm.GetByName("lstClaId")
	objEleve = objForm.GetByName("lstEleId")
	intFiltreNiveau = objNiveau.SelectedValue
	intFiltreClasse = objClasse.SelectedValue

	strSQL1 = "SELECT DISTINCT ""CLA_LIB"", ""CLA_ID"" FROM ""T_ELEVES"" WHERE ""NIV_ID"" = '" & intFiltreNiveau & "' ORDER BY ""CLA_ID"""
	strSQL2 = "SELECT ""ELE_NOM"" || ' ' || ""ELE_PRENOM"", ""ELE_ID"" FROM ""T_ELEVES"" WHERE ""NIV_ID"" = '" & intFiltreNiveau & "' ORDER BY ""ELE_NOM"" || ' ' || ""ELE_PRENOM"""
	
	objClasse.ListSource = Array(strSQL1)
	objEleve.ListSource = Array(strSQL2)
	intClasse = objForm.Columns.GetByName("CLA_ID").Value
	intEleve = objForm.Columns.GetByName("ELE_ID").Value
	objClasse.SelectedValue = intClasse
	objEleve.SelectedValue = intEleve
	objClasse.Refresh()
	objEleve.Refresh()

End Sub

' Filtering pupils per classroom
Sub FiltrerClasses
	
	Dim objForm As Object
	Dim objClasse As Object
	Dim objEleve As Object
	Dim intFiltreClasse As Integer
	Dim strSQL As String
	Dim intEleve As Integer
	
	objForm = ThisComponent.DrawPage.Forms.GetByName("MainForm")
	objClasse = objForm.GetByName("lstClaId")
	objEleve = objForm.GetByName("lstEleId")
	intFiltreClasse = objClasse.SelectedValue

	strSQL = "SELECT ""ELE_NOM"" || ' ' || ""ELE_PRENOM"", ""ELE_ID"" FROM ""T_ELEVES"" WHERE ""CLA_ID"" = '" & intFiltreClasse & "' ORDER BY ""ELE_NOM"" || ' ' || ""ELE_PRENOM"""

	objEleve.ListSource = Array(strSQL)
	intEleve = objForm.Columns.GetByName("ELE_ID").Value
	objEleve.SelectedValue = intEleve
	objEleve.Refresh()

End Sub

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.