Where... IN statement not returning records

[Dinger_80]

I have a query that I set up to return multiple records. The number of the records is in that text box. I have tried to set the where clause to the text box but for some reason I don't get any of the records I want returned. I am using a Where... IN statement. I have tried to put the numbers in single quotes and no quotes and I have had no luck at all. If I do individual records they will return, just not when I try and do more then one at a time. This query is a part of something more, but since it doesn't return anything, there isn't any sense worrying about the bigger picture. Here is my sql statement

SELECT CalibratedEquipmentListTable.ID, CalibratedEquipmentListTable.Manufacturer, CalibratedEquipmentListTable.ModelNo, CalibratedEquipmentListTable.Description, CalibratedEquipmentListTable.SerNo, CalibratedEquipmentListTable.LastCal, CalibratedEquipmentListTable.CalDue
FROM CalibratedEquipmentListTable
WHERE CalibratedEquipmentListTable.ID In ([Forms]![ReportForm]![Text41]);

 

[spikepl]

Because you do not have an IN clause there. An IN clause contains a list of values separated by commas, but you cannot pass a list of values using a parameter as you are attempting to do - you are passing a string, and that is a different animal.

Namliam has shown you how to do it in your last post on this subject so do what he wrote or ask about what you do not understand. Don't just ignore the help you are given or else you will not be getting any.

 

[Dinger_80]

spikepl,

I did try what was suggested in multiple fashions and had no success. I don't ignore what people suggest, I give things an honest go. When I did what he suggested I got the the SQL string that I have. I imagine this has something to do with the recordsource not being opened to obtain the values. Either way at this point the result is that no records are returned regardless of the fact.

The text box does have values in it like 4, 6, 7. I can return the values that I would like via different methods. None of them seem to be retrieved via the query so the records aren't returned.

 

[JHB]

Why not post your database with some sample data?

 

[namliam]

I thought this read as familiar even before reading Spike's response...
Continuation from here: http://www.access-programmers.co.uk/forums/showthread.php?t=274914 ???!!!

The reason it does work for one and not for multiple is this... well let me not repeat myself
http://www.access-programmers.co.uk/forums/showpost.php?p=1412518&postcount=3

The solution is the same, simply use a querydefinition to "fix" the sql for you.

 

[Dinger_80]

Well after spending sometime trying to create a sample db so I could show what was working and not working I got things working. Ran into a bit of problems making sure all of the tables, reports and queries were copied over. In doing so I had a problem with the :

CurrentDb.QueryDefs("SubReportCalEquipmentUsedQuery").SQL = mySQL

I kept getting a error '3265' Item not found in collection. I wasn't getting that error in my system, why on y db mockup. Once I figured out why it was causing that problem everything else suddenly fell into place. I don't know why I couldn't get things working before and then saved things before we had a power outage. For some reason it didn't but now it works just like you said it should. Now I just have to go back and make some changes to how another form interacts with this report and I will be all set. Sorry if I seem like I wasn't attempting things because I was, I just had no luck with things working and I don't know what I did differently this time to get things working but it did. Thank you for your patience.

 

[namliam]

Glad you were able to get it working

As for patience... try my patience here
http://www.access-programmers.co.uk/forums/showthread.php?p=1414237#post1414237