423,350 Members | 2,519 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,350 IT Pros & Developers. It's quick & easy.

Need Help With the Instr Function

P: 1
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!
2 Weeks Ago #1
Share this Question
Share on Google+
2 Replies


Seth Schrock
Expert 2.5K+
P: 2,895
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?
2 Weeks Ago #2

NeoPa
Expert Mod 15k+
P: 31,007
@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]));
2 Weeks Ago #3

Post your reply

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