SELECT question

  • Thread starter Thread starter TC
  • Start date Start date
T

TC

Replace this whole part:

(Left$([Name],1)<>"~") AND
(Left$([Name],4) <> "Msys")

with this:

([Name] Like "tbl*")

HTH,
TC
 
Not if you are entering the SQL directly into an SQL window or the row
source of a listbox.

The quotes do need to be doubled-up if you are saving the SQL in a string
variable, but there was no indication in your post, that you were doing
that.

And, your other quotes weren't doubled-up either!

Cheers,
TC


JohnE said:
TC, that worked. But there needed to be double quotes
around ""tbl"".

Thanks.
*** John

-----Original Message-----
Replace this whole part:

(Left$([Name],1)<>"~") AND
(Left$([Name],4) <> "Msys")

with this:

([Name] Like "tbl*")

HTH,
TC


I have a listbox on a form that I am populating with the
table names. It populates okay but I have 2 table naming
methods; tblTableName and usrtblTableName. I am using the
following SELECT statement to populate the list box. What
I would really like to do is only populate with the list
box with the tables beginning with tbl.
Can this be done? What needs changing in the following
statement?

SELECT MSysObjects.Name FROM MsysObjects WHERE
(Left$([Name],1)<>"~") AND
(Left$([Name],4) <> "Msys") AND (MSysObjects.Type)=1 ORDER
BY MSysObjects.Name;

Any assistance would be appreciated.
*** John
Click to expand...
Click to expand...
Click to expand...
 
I have a listbox on a form that I am populating with the
table names. It populates okay but I have 2 table naming
methods; tblTableName and usrtblTableName. I am using the
following SELECT statement to populate the list box. What
I would really like to do is only populate with the list
box with the tables beginning with tbl.
Can this be done? What needs changing in the following
statement?

SELECT MSysObjects.Name FROM MsysObjects WHERE
(Left$([Name],1)<>"~") AND
(Left$([Name],4) <> "Msys") AND (MSysObjects.Type)=1 ORDER
BY MSysObjects.Name;

Any assistance would be appreciated.
*** John
 
"JohnE" said:
I have a listbox on a form that I am populating with the
table names. It populates okay but I have 2 table naming
methods; tblTableName and usrtblTableName. I am using the
following SELECT statement to populate the list box. What
I would really like to do is only populate with the list
box with the tables beginning with tbl.
Can this be done? What needs changing in the following
statement?

SELECT MSysObjects.Name FROM MsysObjects WHERE
(Left$([Name],1)<>"~") AND
(Left$([Name],4) <> "Msys") AND (MSysObjects.Type)=1 ORDER
BY MSysObjects.Name;

Any assistance would be appreciated.
*** John
Click to expand...

John

You just need to change it to:

SELECT MSysObjects.Name FROM MSysObjects
WHERE (Left(MSysObjects.Name,3)="tbl") AND (MSysObjects.Type=1)
ORDER BY MSysObjects.Name;
 
Jon, I added what you have to the button to populate the
list box but an end of statement error occurs at
the "tbl". If I double quote (""tbl""), nothing occurs
when I click the button.

Me.lstAddEdit.RowSource = _
"SELECT " & _
"Name " & _
"FROM MSysObjects " & _
"WHERE (Left(MSysObjects.Name, 3)="tbl"), " & _
"And (MSysObjects.Type = 1) " & _
"ORDER BY Name; "


Did I miss something?
*** John
-----Original Message-----
I have a listbox on a form that I am populating with the
table names. It populates okay but I have 2 table naming
methods; tblTableName and usrtblTableName. I am using the
following SELECT statement to populate the list box. What
I would really like to do is only populate with the list
box with the tables beginning with tbl.
Can this be done? What needs changing in the following
statement?

SELECT MSysObjects.Name FROM MsysObjects WHERE
(Left$([Name],1)<>"~") AND
(Left$([Name],4) <> "Msys") AND (MSysObjects.Type)=1 ORDER
BY MSysObjects.Name;

Any assistance would be appreciated.
*** John
Click to expand...

John

You just need to change it to:

SELECT MSysObjects.Name FROM MSysObjects
WHERE (Left(MSysObjects.Name,3)="tbl") AND (MSysObjects.Type=1)
ORDER BY MSysObjects.Name;


--

Jon

www.applecore99.com - Access Tips and Tricks

.
Click to expand...
Click to expand...
 
TC, that worked. But there needed to be double quotes
around ""tbl"".

Thanks.
*** John

-----Original Message-----
Replace this whole part:

(Left$([Name],1)<>"~") AND
(Left$([Name],4) <> "Msys")

with this:

([Name] Like "tbl*")

HTH,
TC


I have a listbox on a form that I am populating with the
table names. It populates okay but I have 2 table naming
methods; tblTableName and usrtblTableName. I am using the
following SELECT statement to populate the list box. What
I would really like to do is only populate with the list
box with the tables beginning with tbl.
Can this be done? What needs changing in the following
statement?

SELECT MSysObjects.Name FROM MsysObjects WHERE
(Left$([Name],1)<>"~") AND
(Left$([Name],4) <> "Msys") AND (MSysObjects.Type)=1 ORDER
BY MSysObjects.Name;

Any assistance would be appreciated.
*** John
Click to expand...


.
Click to expand...
 
"JohnE" said:
Jon, I added what you have to the button to populate the
list box but an end of statement error occurs at
the "tbl". If I double quote (""tbl""), nothing occurs
when I click the button.

Me.lstAddEdit.RowSource = _
"SELECT " & _
"Name " & _
"FROM MSysObjects " & _
"WHERE (Left(MSysObjects.Name, 3)="tbl"), " & _
"And (MSysObjects.Type = 1) " & _
"ORDER BY Name; "


Did I miss something?
*** John
Click to expand...

John

As it is being used in VBA code, you need to replace the double quotes around
"tbl" with single quotes, otherwise Access comes to the first double quote, and
assumes that it is the end of the statement:

Me.lstAddEdit.RowSource="SELECT Name FROM MSysObjects " _
& " WHERE (Left(MSysObjects.Name,3)='tbl') AND (MSysObjects.Type=1) " _
& " ORDER BY Name;"

You also appear to have gained a comma in the middle of the statement.
 
Back
Top