Code Problem?

[James]

Hi I have modified the code someone has given me to the
following:

-----------------------------------------------------------
Select Title,"CD" as MediaType,"tblCD's" as BaseTable from
tblCD's
UNION
Select Title,"DVD Title" as MediaType,"tblDVD's" as
BaseTable from
tblDVD's
UNION
Select Title,"VCD" as MediaType,"tblVCD's" as BaseTable
from
tblVCD's
UNION
Select Title,"Software" as MediaType,"tblSoftware" as
BaseTable from tblSoftware
UNION
Select Title,"MP3Title" as MediaType,"tblMP3Title" as
BaseTable from tblMP3Title
UNION
Select Title,"MP3's on CD" as MediaType,"tblMP3List" as
BaseTable from tblMP3List

-----------------------------------------------------------

I am getting an Error on the line of:

Select Title,"DVD Title" as MediaType,"tblDVD's" as
BaseTable from
tblDVD's

It says "Invalid Brackiting of Name "s
Select Title,"DVD Title" as MediaType,"tblDVD"

What does this mean?

Many Thanks

James

 

[Bill Taylor]

,"[DVD Title]" . Any Names of objects in Access that have a space in it must
be surrounded with brackets when referencing in VBA code.

 

[James]

It is still doing the same thing plus its in SQL did I
post this in the wrong forum thing?

Many Thanks

James

-----Original Message-----
,"[DVD Title]" . Any Names of objects in Access that have a space in it must
be surrounded with brackets when referencing in VBA code.

Hi I have modified the code someone has given me to the
following:

-------------------------------------------------------- ---
Select Title,"CD" as MediaType,"tblCD's" as BaseTable from
tblCD's
UNION
Select Title,"DVD Title" as MediaType,"tblDVD's" as
BaseTable from
tblDVD's
UNION
Select Title,"VCD" as MediaType,"tblVCD's" as BaseTable
from
tblVCD's
UNION
Select Title,"Software" as MediaType,"tblSoftware" as
BaseTable from tblSoftware
UNION
Select Title,"MP3Title" as MediaType,"tblMP3Title" as
BaseTable from tblMP3Title
UNION
Select Title,"MP3's on CD" as MediaType,"tblMP3List" as
BaseTable from tblMP3List

-------------------------------------------------------- ---

I am getting an Error on the line of:

Select Title,"DVD Title" as MediaType,"tblDVD's" as
BaseTable from
tblDVD's

It says "Invalid Brackiting of Name "s
Select Title,"DVD Title" as MediaType,"tblDVD"

What does this mean?

Many Thanks

James

.

 

[SteveS]

James

I found a great MDB (A97 but it converted to A2K) called "SQL wizard" by Anita Smith at
http://www.colbyconsulting.com/UsefulFiles.ht
Using it, I tested your SQL code

As you have experienced, using spaces and apostrophe's in field names (or objects in Access for that matter) causes massive headaches - at least in my experience.....

The best thing to do would be rename the fields (and the tables) without the spaces and apostrophe's.
Since no one sees the names, they don't have to conform to "proper english"
Examples: MP3's on CD => MP3s_on_CD or MP3sOnC
tblDVD's => tblDVD
tblCan_You_Read_This or tblCanYouReadThis <= easy to rea

If that is impractical, here is your code that *should* work - tested by the SQL Wizard (identifers with spaces or apostrophe's are enclosed in [brackets] - don't use double quotes)

'---------------------------------
Select Title, CD as MediaType, tblCDs as BaseTable from [tblCD's
UNION
Select Title, [DVD Title] as MediaType, [tblDVD's] as BaseTable from [tblDVD's
UNIO
Select Title, VCD as MediaType, tblVCDs as BaseTable from [tblVCD's
UNION
Select Title, Software as MediaType, tblSoftware as BaseTable from tblSoftwar
UNIO
Select Title, MP3Title as MediaType, tblMP3Title as BaseTable from tblMP3Titl
UNIO
Select Title, [MP3's on CD] as MediaType, tblMP3List as BaseTable from tblMP3Lis
'---------------------------------

HT

Stev