SQL Statement in Access - PLEASE HELP!! URGENT!!

[Jill Morris]

I have 3 tables in an Access Database (Access 2000) and I want data from all
3 tables. I have put in the following select statement but the Access query
keeps reporting a Syntax error - PLEASE HELP - I need this solution within
the next couple of hours!!

SELECT Guest.Firstnames+' '+Guest.Surname+','+' '+Spouse.SPFirstName+' '+Spouse.SPLastName
AS Name, Contact.CoName, Contact.ConNotes, Guest.HAttend, Spouse.SPAttend
FROM Functions INNER JOIN Guest ON Functions.GuestID=Guest.GuestID
JOIN Spouse ON Functions.SpouseID=Spouse.SpouseID
Join Contact ON Guest.GuestID=Contact.GuestID
Where Functions.Description= '" & strFnName & "' and Functions.DOF=#" & strFnDate
& "#"

The table names are Guest, Functions and Contacts
The conditions are strings that are passed to the database from the visual
basic form, using the variables strFnName and strFnDate

This has driven me up the wall - am at breaking point now!!!!!!

[JimmyZ]

Hi,

Instead of writing SQL Statements in VB, which to me is a pain in the you
know where, try creating the Query in Access and then use the ADODB.Command
and the ADODB.Parameter objects to return the recordset.

You code will look something like this:

Dim objCon As ADODB.Connection
Dim objCom As ADODB.Command
Dim objPara As ADODB.Parameter
Dim objpara2 As ADODB.Parameter
Dim objRS As ADODB.Recordset
Dim Value as string
Dim value1 as string
Dim k As Integer

Set objCon = New ADODB.Connection
Set objCom = New ADODB.Command

'Creating the DB connection string
'Please change the below connection string as per your server and

database being used.
objCon.ConnectionString = "PROVIDER=SQLOLEDB.1;PASSWORD=;PERSIST SECURITY
INFO=TRUE;USER ID=sa;INITIAL CATALOG=TestSQL;DATA SOURCE=Rockets"

'Opening the connection
objCon.Open objCon.ConnectionString

MsgBox "Connection opened"

'assigning the command object parameters
With objCom
.CommandText = "GetRecords" 'Name of the access query
.CommandType = adCmdTable 'Type : Table/Query
.ActiveConnection = objCon.ConnectionString 'Connection String
End With

'Create 2 parameters
Set objPara = objCom.CreateParameter("rows", adInteger, adParamInput,4,Value)
Set objpara2 = objCom.CreateParameter("Status", adVarChar,
adParamInput, 50,Value1)

'Append the output parameters to command object
objCom.Parameters.Append objPara
objCom.Parameters.Append objpara2

'Store the result in a recordset
Set objRS = objCom.Execute


'Open the recordset
Do While Not objRS.EOF
For k = 0 To objRS.Fields.Count - 1
Debug.Print objRS(k).Name & ": " & objRS(k).Value
Next
Debug.Print "_____"
objRS.MoveNext
Loop

'Close the recordset
objRS.Close

'retrieve the output parameters values
MsgBox "Total records returned: " & objPara.Value
MsgBox objpara2.Value

'close connection
objCon.Close

'cleaning up
Set objCom = Nothing
Set objCon = Nothing
Set objPara = Nothing
Set objpara2 = Nothing
Set objRS = Nothing

End Sub

"Jill Morris" <genem@omantel.net.om> wrote:
>
>I have 3 tables in an Access Database (Access 2000) and I want data from
all
>3 tables. I have put in the following select statement but the Access query
>keeps reporting a Syntax error - PLEASE HELP - I need this solution within
>the next couple of hours!!
>
>SELECT Guest.Firstnames+' '+Guest.Surname+','+' '+Spouse.SPFirstName+' '+Spouse.SPLastName
>AS Name, Contact.CoName, Contact.ConNotes, Guest.HAttend, Spouse.SPAttend
>FROM Functions INNER JOIN Guest ON Functions.GuestID=Guest.GuestID
>JOIN Spouse ON Functions.SpouseID=Spouse.SpouseID
>Join Contact ON Guest.GuestID=Contact.GuestID
>Where Functions.Description= '" & strFnName & "' and Functions.DOF=#" &
strFnDate
>& "#"
>
>The table names are Guest, Functions and Contacts
>The conditions are strings that are passed to the database from the visual
>basic form, using the variables strFnName and strFnDate
>
>This has driven me up the wall - am at breaking point now!!!!!!

[MarkN]

Which is fine - until you move to a different DB.