471,874 Members | 1,869 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,874 software developers and data experts.

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

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
reply views Thread by YellowAndGreen | last post: by
reply views Thread by zermasroor | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.