Check Syntax

R

Richard

Hi

When I open a recordset using the syntax below, it returns nothing, but when
I copy the strSql in the immediate window and paste in the SQL view in the
queries window and runs the query it returns with records.

Why is it so and how do I overcome this?

Thanks in advance

Richard

strSql = "SELECT DISTINCT Company.CompanyId, Company.Building,
Company.Tower, Company.BeatUpdate " & _
"FROM Company " & _
"WHERE (((Company.Tower) Like ""Tower*"") AND
((Company.BeatUpdate)=False));"
 
K

Ken Snell [MVP]

How are you opening the recordset? Show us all the code that you're using to
do this.
 
T

tina

try

strSql = "SELECT DISTINCT Company.CompanyId, " _
& "Company.Building, Company.Tower, " _
& "Company.BeatUpdate FROM Company " _
& "WHERE Company.Tower Like 'Tower*' AND " _
& "Company.BeatUpdate=False"

hth
 
R

Richard

HI Ken

Part of the code goes like this:

Set Cnn = CurrentProject.Connection
Set Rs = New ADODB.Recordset

strSql = "SELECT Company.CompanyId, Company.Building, Company.Tower,
Company.BeatUpdate " & _
"FROM Company " & _
"WHERE (((Company.Tower) Like ""tower*"") AND
((Company.BeatUpdate)=False));"

Rs.Open strSql, Cnn, adOpenKeyset, adLockPessimistic

With Rs
If Not (.EOF And .BOF) Then

.MoveFirst

Richard
 
R

Richard

Hi Tina

Thanks for the code, it still won't work, its the wildcard that is giving
the trouble.

Richard
 
D

Dirk Goldgar

Richard said:
HI Ken

Part of the code goes like this:

Set Cnn = CurrentProject.Connection
Set Rs = New ADODB.Recordset

strSql = "SELECT Company.CompanyId, Company.Building,
Company.Tower, Company.BeatUpdate " & _
"FROM Company " & _
"WHERE (((Company.Tower) Like ""tower*"") AND
((Company.BeatUpdate)=False));"

Rs.Open strSql, Cnn, adOpenKeyset, adLockPessimistic

With Rs
If Not (.EOF And .BOF) Then

.MoveFirst
Click to expand...

ADO uses different wildcard characters than DAO and the Access query
window do. Use '%' instead of '*' as the wildcard character.
 
B

Brendan Reynolds

It should work, Richard. For example, the following test works for me ...

Public Sub TestSQL()

Dim strSQL As String
Dim rst As ADODB.Recordset

strSQL = "SELECT TestNum, TestText FROM tblTest " & _
"WHERE TestText LIKE ""Tower%"" ORDER BY TestNum"
Set rst = New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.Open strSQL
Debug.Print .Fields("TestNum"), .Fields("TestText")
.Close
End With

End Sub

If you still can't get it to work, can you think of anything else about your
scenario that might be relevant? What does the data in the Tower field that
you expect to meet this criteria look like? Is the BeatUpdate field a
Boolean (Yes/No) field?
 
P

peregenem

Richard said:
When I open a recordset using the syntax below, it returns nothing

Why is it so and how do I overcome this?

strSql = "SELECT DISTINCT Company.CompanyId, Company.Building,
Company.Tower, Company.BeatUpdate " & _
"FROM Company " & _
"WHERE (((Company.Tower) Like ""Tower*"") AND
((Company.BeatUpdate)=False));"
Click to expand...

The below works for me in ADO

CREATE TABLE Company (
CompanyId INTEGER NOT NULL,
Building INTEGER NOT NULL,
Tower VARCHAR(255) NOT NULL,
BeatUpdate LOGICAL);

INSERT INTO Company
VALUES (1, 1, 'Tower Hamlets', 0);

SELECT CompanyId, Building,
Tower, BeatUpdate
FROM Company
WHERE Tower LIKE 'tower%'
AND BeatUpdate = FALSE;
 
R

Richard

Hi Brendan

I changed the sql to:

strSql = "SELECT DISTINCT Company.CompanyId, Company.Building,
Company.Tower, Company.BeatUpdate " & _
"FROM Company " & _
"WHERE (((Company.Tower)=""Tower%"") AND
((Company.BeatUpdate)=False));"

but it didn't return and values. The Beatupdate field is a yes/no field and
the "Tower" field should consist of text like "Tower 1", Tower 2", etc

I have other syntax that I use with "*" wildcard with ADO and works. Can't
pinpoint the point of trouble.

Thanks again
Richard
 
B

Brendan Reynolds

Unless that's just a typo in the newsgroup post, you've now got "=" where
you should have "LIKE" ...

<quote>
"WHERE (((Company.Tower)=""Tower%"") AND
</quote>

If it *is* just a typo, try this ...

"WHERE (((Left$(Company.Tower & """", 5))=""Tower"") AND

If Company.Tower is a required field and can not contain Null values, you
can simplify that a bit ...

"WHERE (((Left$(Company.Tower, 5))=""Tower"") AND

I wouldn't normally recommend using a function call like this in preference
to LIKE, as I suspect it may be less efficient, but finding out whether it
works with Left$ may help us narrow down the cause of the problem.
 
R

Richard

Hi Brendan

I changed the typo and it worked. Thanks a million.

I am wondering how come the "*" wildcard works in another form and doesn't
in this form.

Thanks again
Richard
 
Top