UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Table Name Has Space, Access 2007    
 
   
access21
post Dec 28 2017, 03:06 PM
Post#1



Posts: 3
Joined: 19-December 17



this query gives me problem because table p & p has space in it. the query work if table name has no space . can you please tell me how i use table has space int . i think line on patient.firstname = [p & p]
thanks


mystring = "select First_name, Last_name , sum(costs)-sum(payments) as debts from patients inner join [p & p] " & _
" on patients.First_name = [p & p].firstname" & _
" group by First_name , Last_name ,id " & " having sum(costs)-sum(payments) > 0 "
Go to the top of the page
 
theDBguy
post Dec 28 2017, 03:25 PM
Post#2


Access Wiki and Forums Moderator
Posts: 71,430
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Welcome to UtterAccess!
welcome2UA.gif

What kinds of problems is it giving you? Were you getting an error message? If so, what did it say?

Have you tried printing what is in "mystring" and then pasting it in the Query Design Grid to see where the problem is coming from?

Just curious...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
access21
post Dec 28 2017, 03:43 PM
Post#3



Posts: 3
Joined: 19-December 17



the error i get
runtime error 3079
the specified field 'first_name' could refer to more than on table listed in the form clause of your SQL statement


however this query works if changed the table name with no space in it.
Go to the top of the page
 
theDBguy
post Dec 28 2017, 03:50 PM
Post#4


Access Wiki and Forums Moderator
Posts: 71,430
Joined: 19-June 07
From: SunnySandyEggo


Hmm, based on the error message you're getting, it may not have anything to do with the space in the table name. For example, do you still get an error with this?

CODE
mystring = "select patients.First_name, patients.Last_name , sum(costs)-sum(payments) as debts from patients inner join [p & p] " & _
    " on patients.First_name = [p & p].firstname" & _
    " group by patients.First_name , patients.Last_name ,id " & " having sum(costs)-sum(payments) > 0 "

Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
projecttoday
post Dec 28 2017, 06:03 PM
Post#5


UtterAccess VIP
Posts: 8,823
Joined: 10-February 04
From: South Charleston, WV


When you tried it without the spaces, was that with "p&p" or something else? Paste the code that works, please.

--------------------
Robert Crouser

Go to the top of the page
 
GroverParkGeorge
post Dec 29 2017, 09:19 AM
Post#6


UA Admin
Posts: 31,654
Joined: 20-June 02
From: Newcastle, WA


I don't think the space had anything to do with this. I think the problem is actually the non-standard character, &, separated by spaces. SQL probably interpreted that as an attempt to concatenate two different things (tables probably).

Removing the extra spaces allowed the SQL to more accurately parse the table name.

You should, as a best practice, avoid spaces in object names.

You should, as a best practice, avoid non-standard, characters like this in object names.

The reason delimiting the bad name works is that it tells the SQL parser explicitly "Treat this as a single table name, not as a concatenation of odds and ends."

--------------------
Go to the top of the page
 
JonSmith
post Dec 29 2017, 11:39 AM
Post#7



Posts: 3,237
Joined: 19-October 10



I would say rename the tables rather than trying to solve this, don't put spaces in object names and certainly avoid special characters like &
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    23rd January 2018 - 07:07 PM