New query in front-end: " is not a valid name

[PattiB]

I have just started using Access 2007, and I am converting from an old, but
very reliable db. I am trying to re-create a query based on two tables. In
design view, I select the first table, but when I select the second table, I
get the following message:

" is not a valid name. Make sure that it does not include invalid
characters or punctuatiion and that it is not too long.

I am using a front-end, and a back-end db. In the back-end, I can create
the query just fine. But if I try to create the same query in the front-end,
I get the above message.

Help, please!

Patti

 

[PattiB]

By the way, I just installed SP1 for Office, and it did not help my
situation. :-(

 

[Jeff Boyce]

Consider posting the SQL statement of your query. Perhaps there's a
"character or punctuation" that Access 2007 is choking on...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[PattiB]

Here is the query:

SELECT [tblProposal].[ProposalID],
[tblProposal].[ClientID],
[tblProposal].[JobID],
[tblProposal].[JobName],
[tblProposal].[JobAddress],
[tblProposal].[JobCity],
[tblProposal].[JobState],
[tblProposal].[JobZip],
[tblProposal].[Phone] AS JobPhone,
[tblProposal].[Fax] AS JobFax,
[tblProposal].[Date],
[tblProposal].[FUDate],
[tblProposal].[AttnContactID],
[tblProposal].[SubmittedBy] AS SubmitByID,
[tblUser].[FullName] AS SubmitByName,
[tblUser].[Title] AS SubmitByTitle,
[tblUser]. AS SubmitByEMail,
[tblProposal].[BidNum],
[tblProposal].[TotalValue],
[tblProposal].[BaseBid],
[tblProposal].[Status],
[tblProposal].[Note],
[tblProposal].[ResultSent],
[tblProposal].[DateResultSent],
[tblProposal].[FileLoc],
[tblProposal].[Estimator],
[tblProposal].[BidDate],
[tblProposal].[ProposalDetail],
[tblProposal].[DOP],
[tblProposal].[DOPAddm],
[tblProposal].[Scope],
[tblProposal].[Delta],
[tblProposal].[Terms],
[tblProposal].[PropAttach]
FROM tblProposal
LEFT JOIN tblUser ON [tblProposal].[SubmittedBy]=[tblUser].[UserID]
WHERE ((([tblProposal].[ProposalID])=GetProposalID()))
ORDER BY [tblProposal].[JobName];

 

[Jeff Boyce]

Patti

I didn't spot anything that made me wonder.

Sometimes it seems like Access can get a chicken bone stuck in its throat.

Have you tried starting over and building a new query from the ground up?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Here is the query:

SELECT [tblProposal].[ProposalID],
[tblProposal].[ClientID],
[tblProposal].[JobID],
[tblProposal].[JobName],
[tblProposal].[JobAddress],
[tblProposal].[JobCity],
[tblProposal].[JobState],
[tblProposal].[JobZip],
[tblProposal].[Phone] AS JobPhone,
[tblProposal].[Fax] AS JobFax,
[tblProposal].[Date],
[tblProposal].[FUDate],
[tblProposal].[AttnContactID],
[tblProposal].[SubmittedBy] AS SubmitByID,
[tblUser].[FullName] AS SubmitByName,
[tblUser].[Title] AS SubmitByTitle,
[tblUser]. AS SubmitByEMail,
[tblProposal].[BidNum],
[tblProposal].[TotalValue],
[tblProposal].[BaseBid],
[tblProposal].[Status],
[tblProposal].[Note],
[tblProposal].[ResultSent],
[tblProposal].[DateResultSent],
[tblProposal].[FileLoc],
[tblProposal].[Estimator],
[tblProposal].[BidDate],
[tblProposal].[ProposalDetail],
[tblProposal].[DOP],
[tblProposal].[DOPAddm],
[tblProposal].[Scope],
[tblProposal].[Delta],
[tblProposal].[Terms],
[tblProposal].[PropAttach]
FROM tblProposal
LEFT JOIN tblUser ON [tblProposal].[SubmittedBy]=[tblUser].[UserID]
WHERE ((([tblProposal].[ProposalID])=GetProposalID()))
ORDER BY [tblProposal].[JobName];


[QUOTE="Jeff Boyce"]
Consider posting the SQL statement of your query. Perhaps there's a
"character or punctuation" that Access 2007 is choking on...

Regards

Jeff Boyce
Microsoft Office/Access MVP
[/QUOTE][/QUOTE]

 

[Bob Barrows [MVP]]

Here is the query:

WHERE ((([tblProposal].[ProposalID])=GetProposalID()))

Is that function in the front end or the back end?

 

[PattiB]

Yes, I did try to build a new query. From the design view, I can add
tblProposal. However, as soon as I add tblUser, that "not a valid name"
message pops up. Interestingly, if I do the exact same thing in the backend,
it works just fine. It is only when I build the query in the front-end that
I have the problem.

The query runs fine, but I cannot view it in design view. Nor can I create
any other queries in design view if they involve two or more tables.

For more info that may or may not help, I have also converted the back-end
DB to Access 2007, mostly because I want to use the handy-dandy attachments
field and control. That also required using the linked-table manager to
re-link the tables to the newly converted back-end.

I have, in the past, run into those chicken bones. I have run into problems
that just didn't make any sense. On at least one of those occasions, I have
opened a blank DB and imported all tables, forms, queries, etc. For some
reason that I never figured out, that alone fixed the problem. Hmmm...

Many thanks for your time.

Patti

Patti

I didn't spot anything that made me wonder.

Sometimes it seems like Access can get a chicken bone stuck in its throat.

Have you tried starting over and building a new query from the ground up?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Here is the query:

SELECT [tblProposal].[ProposalID],
[tblProposal].[ClientID],
[tblProposal].[JobID],
[tblProposal].[JobName],
[tblProposal].[JobAddress],
[tblProposal].[JobCity],
[tblProposal].[JobState],
[tblProposal].[JobZip],
[tblProposal].[Phone] AS JobPhone,
[tblProposal].[Fax] AS JobFax,
[tblProposal].[Date],
[tblProposal].[FUDate],
[tblProposal].[AttnContactID],
[tblProposal].[SubmittedBy] AS SubmitByID,
[tblUser].[FullName] AS SubmitByName,
[tblUser].[Title] AS SubmitByTitle,
[tblUser]. AS SubmitByEMail,
[tblProposal].[BidNum],
[tblProposal].[TotalValue],
[tblProposal].[BaseBid],
[tblProposal].[Status],
[tblProposal].[Note],
[tblProposal].[ResultSent],
[tblProposal].[DateResultSent],
[tblProposal].[FileLoc],
[tblProposal].[Estimator],
[tblProposal].[BidDate],
[tblProposal].[ProposalDetail],
[tblProposal].[DOP],
[tblProposal].[DOPAddm],
[tblProposal].[Scope],
[tblProposal].[Delta],
[tblProposal].[Terms],
[tblProposal].[PropAttach]
FROM tblProposal
LEFT JOIN tblUser ON [tblProposal].[SubmittedBy]=[tblUser].[UserID]
WHERE ((([tblProposal].[ProposalID])=GetProposalID()))
ORDER BY [tblProposal].[JobName];


[QUOTE="Jeff Boyce"]
Consider posting the SQL statement of your query. Perhaps there's a
"character or punctuation" that Access 2007 is choking on...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have just started using Access 2007, and I am converting from an old,
but
very reliable db. I am trying to re-create a query based on two
tables.
In
design view, I select the first table, but when I select the second
table,
I
get the following message:

" is not a valid name. Make sure that it does not include invalid
characters or punctuatiion and that it is not too long.

I am using a front-end, and a back-end db. In the back-end, I can
create
the query just fine. But if I try to create the same query in the
front-end,
I get the above message.

Help, please!

Patti
[/QUOTE][/QUOTE]
[/QUOTE]

 

[PattiB]

The function is in the front-end. Please see my reply to Jeff for more info.
I still have not figured this out.

Many thanks for your time.

Patti

Here is the query:

WHERE ((([tblProposal].[ProposalID])=GetProposalID()))

Is that function in the front end or the back end?
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

 

[PattiB]

OK... now this is getting really weird. To take out any of the variables of
a converted DB or user-defined functions or anything else, this is what I did:

1. Created a brand new blank DB in Access 2007 called "Test Back End.accdb."
2. Created the following 2 tables in Test Back End:
tblClients, fields ClientID (autonum), ClientName, ClientAddress
tblJobs, fields JobID, ClientID (type long int), JobName, JobAddress
3. Created Join: tblClients.ClientID to tblJobs.ClientID, one to many,
enforce referential integrity
4. Added some data.
5. Created the following query:
SELECT tblClients.ClientID,
tblClients.ClientName,
tblClients.ClientAddress,
tblJobs.ClientID,
tblJobs.JobName,
tblJobs.JobAddress
FROM tblClients
LEFT JOIN tblJobs ON tblClients.ClientID = tblJobs.ClientID;

Note that I created this in Design View with no problems.

6. Created a brand new blank front end in Access 2007 called "Test Front
End.accdb"
7. Linked the tables from Test Back End.accdb.
8. Created a new query called qryTest in Design View:
From the NavPane, I clicked 'Create'
Clicked Query Design (the Show Tabel dialog box came up)
I added tblClients
Still in the Show Table dialog box, I selected tblJobs
I clicked the Add button, and tblJobs was added to the design window,
but also the '" is not a valid name" message came up immediately
9. Added all fields from both tables to the query, and ran it. Works fine.
10. Closed the query.
11. Tried to open the query in Design View and got the "not a valid name"
message, and the query opened in Datasheet View.
12. Ok, done with that, let's try the Simple Query Wizard:
13. Selected all fields in tblClients.
14. Selected all fields in tblJobs.
15. Clicked 'Next' and got this message: Application-defined or
object-defined error

This is a trial version of Access 2007, which I am running until I can
purchase the full version. I have downloaded and installed SP1.

Like I said in my first post, I am new to Access 2007. However, I am not
new to Access. I designed and built this application in 1998, and it has
been running very smoothly since then. Until, of course, my client upgraded
to Access 2007 without consulting with me first. Even then, it may have
continued to run smoothly if it were not for a third-party control that does
not like Access 2007. So I am a bit under the gun to get them back up and
running quickly. <grin>

I am now completely at a loss as to what to do. If anyone can give me any
clues I would really appreciate it!

Respectfully,

Patti

Here is the query:

SELECT [tblProposal].[ProposalID],
[tblProposal].[ClientID],
[tblProposal].[JobID],
[tblProposal].[JobName],
[tblProposal].[JobAddress],
[tblProposal].[JobCity],
[tblProposal].[JobState],
[tblProposal].[JobZip],
[tblProposal].[Phone] AS JobPhone,
[tblProposal].[Fax] AS JobFax,
[tblProposal].[Date],
[tblProposal].[FUDate],
[tblProposal].[AttnContactID],
[tblProposal].[SubmittedBy] AS SubmitByID,
[tblUser].[FullName] AS SubmitByName,
[tblUser].[Title] AS SubmitByTitle,
[tblUser]. AS SubmitByEMail,
[tblProposal].[BidNum],
[tblProposal].[TotalValue],
[tblProposal].[BaseBid],
[tblProposal].[Status],
[tblProposal].[Note],
[tblProposal].[ResultSent],
[tblProposal].[DateResultSent],
[tblProposal].[FileLoc],
[tblProposal].[Estimator],
[tblProposal].[BidDate],
[tblProposal].[ProposalDetail],
[tblProposal].[DOP],
[tblProposal].[DOPAddm],
[tblProposal].[Scope],
[tblProposal].[Delta],
[tblProposal].[Terms],
[tblProposal].[PropAttach]
FROM tblProposal
LEFT JOIN tblUser ON [tblProposal].[SubmittedBy]=[tblUser].[UserID]
WHERE ((([tblProposal].[ProposalID])=GetProposalID()))
ORDER BY [tblProposal].[JobName];


[QUOTE="Jeff Boyce"]
Consider posting the SQL statement of your query. Perhaps there's a
"character or punctuation" that Access 2007 is choking on...

Regards

Jeff Boyce
Microsoft Office/Access MVP
[/QUOTE][/QUOTE]

 

[bcap]

It sounds like Access is very broken on that computer. If I were you I
think I'd install Access 2007 again on another computer and try it there.

When you say you are using a "trial" version, I take it that you mean a
trial version of the production product, not a beta test version? Because
if you're using a beta test version, you're pretty much on your own...

OK... now this is getting really weird. To take out any of the variables
of
a converted DB or user-defined functions or anything else, this is what I
did:

1. Created a brand new blank DB in Access 2007 called "Test Back
End.accdb."
2. Created the following 2 tables in Test Back End:
tblClients, fields ClientID (autonum), ClientName, ClientAddress
tblJobs, fields JobID, ClientID (type long int), JobName, JobAddress
3. Created Join: tblClients.ClientID to tblJobs.ClientID, one to many,
enforce referential integrity
4. Added some data.
5. Created the following query:
SELECT tblClients.ClientID,
tblClients.ClientName,
tblClients.ClientAddress,
tblJobs.ClientID,
tblJobs.JobName,
tblJobs.JobAddress
FROM tblClients
LEFT JOIN tblJobs ON tblClients.ClientID = tblJobs.ClientID;

Note that I created this in Design View with no problems.

6. Created a brand new blank front end in Access 2007 called "Test Front
End.accdb"
7. Linked the tables from Test Back End.accdb.
8. Created a new query called qryTest in Design View:
From the NavPane, I clicked 'Create'
Clicked Query Design (the Show Tabel dialog box came up)
I added tblClients
Still in the Show Table dialog box, I selected tblJobs
I clicked the Add button, and tblJobs was added to the design window,
but also the '" is not a valid name" message came up immediately
9. Added all fields from both tables to the query, and ran it. Works
fine.
10. Closed the query.
11. Tried to open the query in Design View and got the "not a valid name"
message, and the query opened in Datasheet View.
12. Ok, done with that, let's try the Simple Query Wizard:
13. Selected all fields in tblClients.
14. Selected all fields in tblJobs.
15. Clicked 'Next' and got this message: Application-defined or
object-defined error

This is a trial version of Access 2007, which I am running until I can
purchase the full version. I have downloaded and installed SP1.

Like I said in my first post, I am new to Access 2007. However, I am not
new to Access. I designed and built this application in 1998, and it has
been running very smoothly since then. Until, of course, my client
upgraded
to Access 2007 without consulting with me first. Even then, it may have
continued to run smoothly if it were not for a third-party control that
does
not like Access 2007. So I am a bit under the gun to get them back up and
running quickly. <grin>

I am now completely at a loss as to what to do. If anyone can give me any
clues I would really appreciate it!

Respectfully,

Patti

Here is the query:

SELECT [tblProposal].[ProposalID],
[tblProposal].[ClientID],
[tblProposal].[JobID],
[tblProposal].[JobName],
[tblProposal].[JobAddress],
[tblProposal].[JobCity],
[tblProposal].[JobState],
[tblProposal].[JobZip],
[tblProposal].[Phone] AS JobPhone,
[tblProposal].[Fax] AS JobFax,
[tblProposal].[Date],
[tblProposal].[FUDate],
[tblProposal].[AttnContactID],
[tblProposal].[SubmittedBy] AS SubmitByID,
[tblUser].[FullName] AS SubmitByName,
[tblUser].[Title] AS SubmitByTitle,
[tblUser]. AS SubmitByEMail,
[tblProposal].[BidNum],
[tblProposal].[TotalValue],
[tblProposal].[BaseBid],
[tblProposal].[Status],
[tblProposal].[Note],
[tblProposal].[ResultSent],
[tblProposal].[DateResultSent],
[tblProposal].[FileLoc],
[tblProposal].[Estimator],
[tblProposal].[BidDate],
[tblProposal].[ProposalDetail],
[tblProposal].[DOP],
[tblProposal].[DOPAddm],
[tblProposal].[Scope],
[tblProposal].[Delta],
[tblProposal].[Terms],
[tblProposal].[PropAttach]
FROM tblProposal
LEFT JOIN tblUser ON [tblProposal].[SubmittedBy]=[tblUser].[UserID]
WHERE ((([tblProposal].[ProposalID])=GetProposalID()))
ORDER BY [tblProposal].[JobName];


[QUOTE="Jeff Boyce"]
Consider posting the SQL statement of your query. Perhaps there's a
"character or punctuation" that Access 2007 is choking on...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have just started using Access 2007, and I am converting from an
old, but
very reliable db. I am trying to re-create a query based on two
tables.
In
design view, I select the first table, but when I select the second
table,
I
get the following message:

" is not a valid name. Make sure that it does not include invalid
characters or punctuatiion and that it is not too long.

I am using a front-end, and a back-end db. In the back-end, I can
create
the query just fine. But if I try to create the same query in the
front-end,
I get the above message.

Help, please!

Patti
[/QUOTE][/QUOTE][/QUOTE]

 

[PattiB]

Yes, this is the trial version (good for 60 days) of the full pruduction
product, downloaded from Microsoft.com 3 days ago, plus SP1. This is not the
beta.

Thanks, I will try installing on another machine. I did install Access 2007
on a machine that still has Access 2003 on it. That could also a problem. I
had read that it could co-reside with previous versions quite comfortably,
but maybe that isn't always the case.

Patti

 

[bcap]

Installing on a machine which has Access 2003 already installed has worked
for me, but there are so many variables!

I can assure you that creating such simple queries in an accdb front-end
using tables in an accdb back-end is not a general problem with Access 2007,
which is why I suggest that your installation is broken.

Good luck!