Using (IN) command in the where clause from values in form 1

[mdav2]

I have an issue with using an IN keyword in a where cluase of a query. It seems to work if the values I look for are hardcoded but not if I try and take them from a textbox on a form.

If I create a query using an IN keyword in the from with hardcoded sites the query will return data (see QUERY1)

QUERY1

SELECT * FROM inputdata WHERE sitename in("site 1","site2");

If I create a form (FORM1) with a textbox (TXTWHERE) with the value: "site 1","site2". Then a query to take the value of the textbox (see QUERY2) it returns no rows.

QUERY2

SELECT * FROM inputdata WHERE sitename in(form1.txtwhere);

Normally when you use a field in a form and use it in a query it works fine. I think the issue is the query is looking for a value called "form1.txtwhere" not the actual values contained in that textbox. Perhaps there is some sort of string replacement I am not aware of.

Any help on this will be appreciated.

Mark Davies
Warwickshire County Council

 

[MajP]

Normally when you use a field in a form and use it in a query it works fine.

Never the way you have it written.
form1.txt
It has to be written using the forms collection and only using Bang notation

Forms!FrmName.ControlName
Still not sure if this will work using IN, I did not try. I think it should.

 

[SkipVought]

hi,

Then a query to take the value of the textbox ...

What is the value in the textbox?

NOT a particularly good idea to use a textbox, IMHO. the IN criteria expects a comma delimited list, in quotes if compared to a text field. Can you guarantee that every time? Better if chosen from a list.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mdav2]

I've not written the actual database so was just making the example up based on what it's doing. I have since knocked up a simple example.

The code wasn't 100% accurate, I meant it as an example of what it should be the proper query is as below:

SELECT inputdata.*
FROM inputdata
WHERE (((inputdata.sitename) In ([Forms]![Form1]![txtwhere])));

This will only work if the value is a single value and has no quotes. So "Manchester" would not work but Manchester would. If you try and extend that by doing a comma separated list that produces no results.

I created a list and could get it to return values for the chosen item in a none multi list. If I turned multi select on to simple and extended then the query returns nothing even if only a single value is selected.

Mark Davies
Warwickshire County Council

 

[PHV]

I'd try something like this:

SELECT *
FROM inputdata
WHERE InStr("," & [Forms]![Form1]![txtwhere] & ",", "," & sitename & ",") > 0

Note: don't use quotes in TXTWHERE.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mdav2]

Thanks, will check that out and see if I can get it too work. I really do hate access sometimes. Makes easy things very hard to achieve.

Mark Davies
Warwickshire County Council

 

[MajP]

If you use it right it will make things that are extremely hard to achieve, easy. Think about what can be done now at the personal desktop level

 

[mdav2]

Tried that PHV and it works a treat.

Mark Davies
Warwickshire County Council