Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-13-2018, 02:13 PM   #1
SteveE
Newly Registered User
 
Join Date: Dec 2002
Location: Southport UK
Posts: 221
Thanks: 7
Thanked 0 Times in 0 Posts
SteveE
instr AND

I have a string field called WCodes which can consist of 1 to 10 Codes ie "W01 , W02 , W03 , W04" ETC

Trying to run an update quert through the dataset to find any that have W01 AND W04 in the string (the rules apply to a few different code mixes but just for this example) as in the case of having both only the W04 can be used, I thought I may use instr but struggling to format it to use an AND (if it can)

so basically looking at field and if there is a W01 and a W04 only use W04

iif(instr([WCodes],"W01" And instr([WCodes],"W04","W04","")))

Can instr be used in this way?, is there a better way?
amy help appriciated

SteveE is offline   Reply With Quote
Old 07-13-2018, 02:18 PM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 31,929
Thanks: 9
Thanked 3,844 Times in 3,787 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: instr AND

Shot in the dark:

iif(instr(1,[WCodes],"W01") > 0 And instr(1, [WCodes],"W04") > 0,"W04","")
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
Old 07-13-2018, 02:56 PM   #3
SteveE
Newly Registered User
 
Join Date: Dec 2002
Location: Southport UK
Posts: 221
Thanks: 7
Thanked 0 Times in 0 Posts
SteveE
Re: instr AND

Thanks Paul certainly works for me , and has given me the basis to tweak it for my use.
steve

SteveE is offline   Reply With Quote
Old 07-13-2018, 03:14 PM   #4
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 31,929
Thanks: 9
Thanked 3,844 Times in 3,787 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: instr AND

No problem Steve.
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
Old 07-13-2018, 09:09 PM   #5
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,394
Thanks: 13
Thanked 1,416 Times in 1,350 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: instr AND

Of course properly normalizing the schema is ultimately a better solution. Rather than having multiple values stored in a single column, you need a separate table which will be a 1-m relationship with the original table.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Instr From URL abbaddon223 Modules & VBA 1 09-25-2013 04:08 AM
Instr() ? SteveE Modules & VBA 2 02-24-2009 06:22 AM
LIke vs InStr() JaedenRuiner Modules & VBA 1 11-01-2006 06:50 AM
[SOLVED] Instr Asm Queries 2 12-17-2004 12:53 AM
InStr, Right, etc. . . kidzmom3 Queries 6 10-11-2003 12:08 AM




All times are GMT -8. The time now is 04:52 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World