Correct use of Like 2

[vamoose]

I am having a problem with a MSSql statement containing the condition Like. I am using this statement within Access 2000 VBA. I cannot seem to get the syntax correct. It goes like this:

SQLa = "Select PartNo from parameters where PartNo Like #*"
SQLa = "Select PartNo from parameters where PartNo Like '#*'"
SQLa = "Select PartNo from parameters where PartNo Like (#*)"
SQLa = "Select PartNo from parameters where PartNo Like "#*""

None of which are working. Any Ideas, Thanks.

 

[ESquared]

This is the SQL Server forum. You need to ask your question in the Access or VB forum.

[COLOR=black #d0d0d0]When I walk, I sometimes bump into things. I am closing my eyes so that the room will be empty.[/color]

 

[mrdenny]

E,
I think that he's passing the query up to SQL from Access.

The correct syntax would be like this.

SQLa = "Select PartNo from parameters where PartNo Like '#[red]%[/red]'"

SQL Server uses % as the multi-character wild card not *.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ESquared]

Let's let him or her tell us if it is SQL Server or Access processing the query. I have a suspicion that the # is supposed to match a number, too...

[COLOR=black #d0d0d0]When I walk, I sometimes bump into things. I am closing my eyes so that the room will be empty.[/color]

 

[vamoose]

What I am doing is using Access 2000 VBA to connect to a MSSql server database using ADODB. There are 2 SQL queries returning values to place on an Access form.

The first one is looking for any part number that starts with a letter then whatever (wildcard), so Like "A*" ?

The second one is looking for any part number that starts a number then whatever (wildcard), so Like "#*" ?

I was not sure if this was a VBA or SQL topic, I was thinking it was more SQL related so I posted here. Sorry for the confusion and thanks for the help.

 

[Lamprey13]

LIKE '[a-z]%'
LIKE '[0-9]%'