query multiple field

[vojaka]

Hi,

got a problem with mulltiple fields. When i have one record in contactid(see dlookup), works fine, but when i get more than 1. listbox returns first contact name and shows error "Characters found after end of SQL statement. (Error 3142)".
In multiple fields, records delimiter is ;, which is end of statement in sql.

Anyhelp is very appreciated. Thanks in advance


strSqlContactEvent = "select tblcontact.contactname, tblcontact.contactid from tblcontact " & _
" where tblcontact.contactid = " & DLookup("[contactid]", "[tblEvents]", "[eventid] = " & lstEvent.Value)

With lstContact
.RowSource = "table/query"
.RowSource = strSqlContactEvent
.ColumnCount = 1
.BoundColumn = 2
End With

 

[namliam]

let me give an answer by asking a question...
What is the DLookup returning?
In extension of that
What is lstEvent returning as Value?
As the next extension,
What does your strSqlContactEvent look like? Is it proper SQL?

Find the answers to these questions, solve your problem

Good luck !

 

[SimonB1978]

Hi,

A join like this should work:

strSqlContactEvent = "select tblcontact.contactname, tblcontact.contactid from tblcontact INNER JOIN tblEvents ON tblcontact.contactid = tblEvents.contactid where tblEvents.eventid = " & lstEvent.Value

hope that helps,

Simon B.

 

[vojaka]

What is the DLookup returning?
dlookup returns ContactID as 2;3;4
What is lstEvent returning as Value?
lstEvent returns eventID
What does your strSqlContactEvent look like? Is it proper SQL?
What do you mean hear?

let me give an answer by asking a question...
What is the DLookup returning?
In extension of that
What is lstEvent returning as Value?
As the next extension,
What does your strSqlContactEvent look like? Is it proper SQL?

Find the answers to these questions, solve your problem

Good luck !

 

[vojaka]

Seems good, but
the multivalued field 'tblEvents.contactid' is not valid in the specified Join clause.

Hi,

A join like this should work:

strSqlContactEvent = "select tblcontact.contactname, tblcontact.contactid from tblcontact INNER JOIN tblEvents ON tblcontact.contactid = tblEvents.contactid where tblEvents.eventid = " & lstEvent.Value

hope that helps,

Simon B.

 

[namliam]

What is the DLookup returning?
dlookup returns ContactID as 2;3;4

Exactly... now how does that fit into your SQL?

What is lstEvent returning as Value?
lstEvent returns eventID

I mean in values like above with the DLookup

What does your strSqlContactEvent look like? Is it proper SQL?
What do you mean hear?

I mean what is the content of strSqlContactEvent??

And when you see the content is that content proper SQL...
For example... using = in the SQL leaves you searching for 1 and only 1 value... not multiple...