combine code with strSQL

[sven2]

Hello,

how can I bring the following code into a strSQL?

the code is:

Me.txtachternaam = StrConv(LCase(Me.txtachternaam), 3)

the strSQL:

strSQL2 = " SELECT Wachtlijstdeelnemers.ID, Wachtlijstdeelnemers.Personeelsnummer, Wachtlijstdeelnemers.Achternaam "

Is this possible?

Thanks in advance,
Sven.

 

[Uncle Gizmo]

I think it would be like this:

Dim strSQL as String
Dim strSQL1 as String
Dim strSQL2 as String

strSQL1 = "SELECT Wachtlijstdeelnemers.ID, Wachtlijstdeelnemers.Personeelsnummer, " 
strSQL2 = " FROM Wachtlijstdeelnemers"

strSQL = strSQL1 & Me.txtachternaam & strSQL2

 

[sven2]

Hello,

I am a little bit confused ...

in a table "werknemers" there is a field achternaam where I have some names that are imported with capital letters.

In a field (me.txtachternaam) i have changed this with the code:

Me.txtachternaam = StrConv(LCase(Me.txtachternaam), 3)

and this is working just fine.

Now I want to do the same in a listbox with the code:

strSQL = " Select werknemers.achternaam From Werknemers "

How can I change the werknemers.achternaam in this listbox so that the display is not with capital letters except the first letter?

Sven.

 

[Uncle Gizmo]

.
... sorry I don't understand.... I think I've misunderstood your question.

 

[sven2]

Hello,

my question is how to change the display of data in a listbox.

In my table employees I have several names like GEERT, DANNY

When I put these names in a listbox with the code:

strSQL = " Select werknemers.achternaam From Werknemers "
me.listbox.rowsource = strSQL
me.listbox.requery

I See

GEERT
DANNY

But I want to see

Geert
Danny

How can I change this without changing the data in the table?

Thanks in advance,
Sven.

 

[pbaldy]

Does this not work?

strSQL = "Select StrConv(LCase(werknemers.achternaam), 3) From Werknemers "

 

[DKO]

Any reason that you need to use code instead of just manipulating the properties of the controls themselves?

Is the user able to choose which name format is displayed or does it change depending on a trigger?

If not, then you could just enter "=StrConv(werknemers.achternaam, 3)" as the data source for your text box (assuming it's in your field list) - and:

"SELECT Wachtlijstdeelnemers.ID, Wachtlijstdeelnemers.Personeelsnummer, StrConv(werknemers.achternaam, 3) From Werknemers..." as the row source for your list box.

Of course, at the end where the dots are you would have to define the join between the two tables...or you can put your query name there instead of "Werknemers"

It may be easier for you to build a query in design view with the fields you want to show in your list box, then switch to SQL view and copy the code.

In any case - you can use the StrConv Function in SQL statements. And you don't need to use both the LCase Function and StrConv Function together. That's basically just formatting the text twice and the one that's on the inside of the parentheses will have no affect.