Volunteer needs help with select statement

Volunteer

Registered User.
Local time
Today, 09:42
Joined
Nov 10, 2009
Messages
12
Hi all,

I am creating a database for a charity in the UK and having a problem with the following Select Statement. As you can see I am only selecting the fist and last names.

I am kind of new to this so any help would be appreciated

As I run the code an error message appears as follows.

Run Time Error 3078
"The Microsoft Database Engine cannot find the Table or Query 'Members_Contact_Details'. Make sure it exist

The Table does exist and is spelt the same way -- Can anyone HELP!!!




Option Explicit
Function BuryPostCode()

Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT First_Name, " _
& "Last_Name FROM Members_Contact_Details;")
dbs.Close

End Function

Private Sub Command4_Click()
End Sub
 
Are there spaces or underscores in the actual name? If spaces, it would require bracketing the name:

"...FROM [Members Contact Details]"
 
Code:
Option Explicit
Function BuryPostCode()
[COLOR=black][FONT=Verdana]Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT First_Name, " [COLOR=red]& [/COLOR]_
                                        "Last_Name FROM Members_Contact_Details;")
dbs.Close[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End Function[/FONT][/COLOR]
 
Scooter, the placement of the ampersand should be irrelevant. Mine are virtually always on the following line.
 
No biggie; I think it's just personal preference as to where they look "right" to any given person. Access doesn't care.
 
No biggie; I think it's just personal preference as to where they look "right" to any given person. Access doesn't care.

Being a long time 'c' programmer, I understand that Access does not care if they are on the same line, since blank lines are ignored, but in the example, the "&" and "_" were out of traditional order. Are you saying that Access accepts both "& _" and "_ &"?

NOTE: I just tried using "_ &" in an existing sample application, and I cannot get it to work. Only "& _" works for me.
 
Last edited:
Well, Scooter put it on the first line rather than the second, and that's what I mean by Access doesn't care (the OP had it on the second line). I wouldn't have thought it could be after the line continuation characters. In other words, this is perfectly valid:

Code:
  strSQL = "SELECT ResNum, CitiFareNum, ResDate, Source " _
         & "FROM vueAllReservations " _
         & "WHERE " & strCrit
 
since blank lines are ignored

By the way, as a slight quibble with the above, this would cause an error:

Code:
  strSQL = "SELECT ResNum, CitiFareNum, ResDate, Source " _
         & "FROM vueAllReservations " _

         & "WHERE " & strCrit

Blank lines are ignored to a point.
 
Well, Scooter put it on the first line rather than the second, and that's what I mean by Access doesn't care (the OP had it on the second line). I wouldn't have thought it could be after the line continuation characters. In other words, this is perfectly valid:

Code:
  strSQL = "SELECT ResNum, CitiFareNum, ResDate, Source " _
         & "FROM vueAllReservations " _
         & "WHERE " & strCrit

I just now tried several different combinations, and only the following worked:

Code:
    strSQL = "SELECT ResNum, CitiFareNum, ResDate, Source " & _
    "FROM vueAllReservations " & _
    "WHERE " & strCrit

Every other combination that I tried received an error. I do not perceive that I am doing anything incorrectly.
 
Not sure what's going on. The code I posted is straight out of a working db, and like I said earlier, I usually put the ampersand on the following line like that. I work in everything from 2000 to 2007, and it works for me in all of them.
 
Not sure what's going on. The code I posted is straight out of a working db, and like I said earlier, I usually put the ampersand on the following line like that. I work in everything from 2000 to 2007, and it works for me in all of them.

My tests were for Access 2003 only. I thought it might be indicative of a change for 2007 and beyond.
 
Not sure what's going on. The code I posted is straight out of a working db, and like I said earlier, I usually put the ampersand on the following line like that. I work in everything from 2000 to 2007, and it works for me in all of them.

I don't do it this way but it works fine for me if I do. Just thought I would add my info in so you have further verification from another source that what Paul has given is valid.
 
I have tried both your examples but unfortunatly non of them did run.
I am still getting the same error message.

Can you help further
 
I have tried both your examples but unfortunatly non of them did run.
I am still getting the same error message.

Can you help further
You never answered Paul's original question:

pbaldy said:
Are there spaces or underscores in the actual name? If spaces, it would require bracketing the name:

"...FROM [Members Contact Details]"
 
No, I just double checked, and I do the same thing in 2007. I can't imagine why 2003 would be different, but I don't have a computer with it here.
 
By the way there are spaces between the words, i have tried [] but still does not run
 
By the way there are spaces between the words, i have tried [] but still does not run

So, go ahead and repost what you currently have and doesn't work so we can see exactly what you have.
 
By the way there are spaces between the words, i have tried [] but still does not run


Would it be possible for you to post all of the VB Code that is related to the query (exactly what is not working) here for us to see?

NOTE: I see that Bob beat me to this request, so I must be on to something, and await the code.
 
Last edited:

Users who are viewing this thread

Back
Top Bottom