471,874 Members | 1,869 Online

# Need Help With the Instr Function

I am trying to use the Instr function to find all six possible combinations of 123, 213,213,312 and 321 using this sql script:
Expand|Select|Wrap|Line Numbers
1. SELECT WaltLottery.DTE AS TargetDates, WaltLottery.Cash3eve, WaltLottery.Cash3nite
2. FROM WaltLottery
3. WHERE (((WaltLottery.Cash3mid)=InStr([Enter###s: Separated by Commas],[Cash3mid])));
I get no data outputted and no syntax error.
but when I run the near identical script:
Expand|Select|Wrap|Line Numbers
1. SELECT WaltLottery.DTE AS TargetDates, WaltLottery.Cash3eve, WaltLottery.Cash3nite
2. FROM WaltLottery
3. WHERE (((WaltLottery.Cash3mid)=[3###;])) OR (((WaltLottery.Cash3eve)=[3###;])) OR (((WaltLottery.Cash3nite)=[3###;]));
I get the desired output from each field. Both scripts are nearing identical.

Is there something that I am missing in the Instr function, (I have never used the Instr function. Need help please!
Sep 7 '18 #1
2 859
Seth Schrock
2,965 Expert 2GB
The InStr() function returns an integer value for the starting location of the string found, not the string itself.

Now that you have a working query, are you still needing help with the overall solution and just wondering about the InStr() function, or do you still need help with the query?
Sep 7 '18 #2
NeoPa
32,468 Expert Mod 16PB
@Walt.
This is a very strange question. Quite apart from the fact that the function is being misused, as explained by Seth, there are almost countless differences between the `WHERE` clauses of the two versions. How this appears to you as even remotely similar is quite beyond me - and I actually understand what each is doing.

If what you're after is to check each of three fields from your data against a string entered by the operator to ensure there's at least one match, and you want to write it succinctly, then you could try turning the check around and using the `IN()` function :
Expand|Select|Wrap|Line Numbers
1. WHERE ([Enter value here] IN([Cash3mid],[Cash3eve],[Cash3nite]));
Sep 7 '18 #3