469,073 Members | 1,791 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,073 developers. It's quick & easy.

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 771
Seth Schrock
2,957 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,154 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

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Icaro | last post: by
4 posts views Thread by Andy_Khosravi | last post: by
1 post views Thread by Srinivasa Ra via .NET 247 | last post: by
7 posts views Thread by lovecreatesbea... | last post: by
4 posts views Thread by =?Utf-8?B?UGF1bA==?= | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.