use like with variable string

User avatar
sal21
PlatinumLounger
Posts: 4334
Joined: 26 Apr 2010, 17:36

use like with variable string

Post by sal21 »

I need to use a like based the DATO variable... first three char in NOMINATIVO

is this correct:

for example for DATO="ALL"

return

ALLEANZA
ALLEGRO
ALLEGORIA
....

DATO = Trim(Me.TCERCA.Text)
SQL = "SELECT * FROM DATI WHERE NOMINATIVO LIKE 'DATO*'"

i'm on Access and ADO, and vb6

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: use like with variable string

Post by HansV »

No, you must place DATO outside the quotes and concatenate:

Code: Select all

SQL = "SELECT * FROM DATI WHERE NOMINATIVO LIKE '" & DATO & "*'"
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4334
Joined: 26 Apr 2010, 17:36

Re: use like with variable string

Post by sal21 »

HansV wrote:
29 Jan 2022, 12:14
No, you must place DATO outside the quotes and concatenate:

Code: Select all

SQL = "SELECT * FROM DATI WHERE NOMINATIVO LIKE '" & DATO & "*'"
OK.
but via code return 0!
in access ide return 107 records!!!
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: use like with variable string

Post by HansV »

Sorry, ADO uses % instead of *.

Code: Select all

SQL = "SELECT * FROM DATI WHERE NOMINATIVO LIKE '" & DATO & "%"
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4334
Joined: 26 Apr 2010, 17:36

Re: use like with variable string

Post by sal21 »

HansV wrote:
29 Jan 2022, 12:35
Sorry, ADO uses % instead of *.

Code: Select all

SQL = "SELECT * FROM DATI WHERE NOMINATIVO LIKE '" & DATO & "%"
work!

NOTE:
changed "%" with "%'"

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: use like with variable string

Post by HansV »

Sorry about that.
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4334
Joined: 26 Apr 2010, 17:36

Re: use like with variable string

Post by sal21 »

HansV wrote:
29 Jan 2022, 13:19
Sorry about that.
No prob!

User avatar
sal21
PlatinumLounger
Posts: 4334
Joined: 26 Apr 2010, 17:36

Re: use like with variable string

Post by sal21 »

Ops ...
Based the attached IMG how to add a new recorset for sort?
RS.sort(nominativo, collegio) in asc sort
Is correct?
Last edited by sal21 on 29 Jan 2022, 15:31, edited 1 time in total.

User avatar
sal21
PlatinumLounger
Posts: 4334
Joined: 26 Apr 2010, 17:36

Re: use like with variable string

Post by sal21 »

sal21 wrote:
29 Jan 2022, 15:29
Ops ...
Based the attached IMG how to add a new recorset for sort?
RS.sort(nominativo, collegio) in asc sort
Is correct?

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: use like with variable string

Post by HansV »

You could use

Code: Select all

RS.Sort = "nominativo ASC, collegio ASC"
or you can add the sort order to the SQL instead:

Code: Select all

SQL = "SELECT * FROM DATI WHERE NOMINATIVO LIKE '" & DATO & "%' ORDER BY NOMINATIVO, COLLEGIO"
Best wishes,
Hans