Is this possible 2

[NVSbe]

Hello again.

I was wondering if I could create a Query (in an mdb file) with this definition

SELECT resk0szk.NAAM_WERKN, resk0szk.UREN_ZIEK, [FORMS]![FORMULIER1].strAfdelingen
FROM resk0szk
WHERE resk0szk.AFDELING in ( [FORMS]![FORMULIER1].strAfdelingen );

and in which strAfdelingen is filled in in the program depending on a few values selected. (it'd look for instance like:

1,2,3,4,10

It seems to me hen when I run the query with DoCmd.OpenQuery, it should give me all employees from the selected departments. This works if there's only 1 item selcted, gives no data when there's 2 items selected and gives error 2001 "Previous command has been cancelled" when selecting 3 or more items. I have no clue what to do with this. Hints, please?

Niki

 

[WayneRyan]

NVSbe,

I got the right query with one entry. More than that
and I got nothing; but no error message.

I tried populating a combo box with the following:

Me.Combo.RowSource = "Select title from projects " & _
"where uid in (" & Me.strTest & &quot"

This works fine.

As for queries, I don't know.
I'm pretty sure that you could parse your string,
enter them in a table, and join the two tables.
That may be a temporary work-around. I'll let you
know if I get any answers.

HTH,
Wayne

 

[jjob]

Hi Niki,

I'm no Access Guru, but I think the problem is that for anything other than a single selection, you would not be building the SQL you expect.

I think that the value of [FORMS]![FORMULIER1].strAfdelingen would be used to build the SQL string and I think it would look like this to the JET engine:-

SELECT resk0szk.NAAM_WERKN, resk0szk.UREN_ZIEK, "1,2,3,4"
FROM resk0szk
WHERE resk0szk.AFDELING in ( "1,2,3,4"

I may be wrong, but my tests (using 5 records with id's 1 to 5) suggest that the WHERE is the problem and it is because the form control is seen as a single string value in the IN.

Missing the WHERE gives all my test entries as records with "1,2,3,4" as the strAfdelingen value in each record.

Including the WHERE with a "1" gives the id=1 record, interestingly enough using "1," also gives the id=1 record, which I would not expect, but I assume that maybe MS's parser is being a bit clever (I never use IE for testing HTML as it also is clever and ignores/assumes missing closing tags that cause havoc in Netscape!)

When I used "1,2" in the WHERE it gives no records, so I think the problem is the SQL treating (correctly I beleive) the "1,2" as a single string value which, obviously, gives no matching records.

regards

John

 

[jjob]

OOps forgot to say that if I hard code the values

1,2,3 as the values of the WHERE IN, I get records ids, 1 2 and 3 which points to the "1,2" single string value being the problem - which then suggests that it may be possible to code and edit the querydef to fill in the numbers from your control.

regards

John

 

[NVSbe]

Thanks. Both your sugestions hav really helped and I now have the following:

Dim x As Integer
Dim strAfdelingen As String
Dim cmd As String

strAfdelingen = ""
For x = 0 To ddlAfdelingen.ItemsSelected.Count - 2
strAfdelingen = strAfdelingen & ddlAfdelingen.ItemData(ddlAfdelingen.ItemsSelected.Item(x)) * 100 + 5 & ", "
Next x
strAfdelingen = strAfdelingen & ddlAfdelingen.ItemData(ddlAfdelingen.ItemsSelected.Item(x)) * 100 + 5

cmd = "SELECT resk0szk.NAAM_WERKN, resk0szk.UREN_ZIEK FROM resk0szk"
cmd = cmd & " WHERE resk0szk.AFDELING in ( " & strAfdelingen & ""

Dim db As database
Dim qry As QueryDef

Set db = Application.CurrentDb
Set qry = db.CreateQueryDef("tmpQry", cmd)

DoCmd.OpenQuery "tmpQry", , acReadOnly

db.QueryDefs.Delete "tmpQry"
Set qry = Nothing
Set db = Nothing


You were probably right bout the string being watched as one whole, and creating the QueryDef during runtime worked like a charm. Thanks a lot.