Access Mid query

Hi,

Does anyone have the code to specify the return of values that are "D" or "P" in the third character of a sequence

So, for example

J3PI009C - Return
K4SI008T - Dont return
KKDI007X - Return

I am using the design view.

Thanks
Seamus
Seamus2626Asked:
Who is Participating?
 
RamanhpConnect With a Mentor Commented:
if (Mid (UrCharacter, 3, 1) = 'P') or (Mid (UrCharacter, 3, 1) = 'D') then
'your Return Statement here
end if
0
 
Seamus2626Author Commented:
Thanks Ramanhp, but that didnt do anything.

I uploaded a screen shot of how i interpreted your code

Thanks
Seamus
ss.doc
0
 
MINDSUPERBConnect With a Mentor Commented:
Seamus,

Try this one in a query in design view:

Sample: IIf(InStrRev([YourField],"P")=3 Or InStrRev([YourField],"D")=3,[YourField],"")

Sincerely,
Ed
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Seamus2626Author Commented:
Hi Ed,

Thanks for that.

However, this is not working either. I may be doing something wrong here but i cant see it.

I attached a ss of my query

Thanks
Seamus
ss.doc
0
 
MINDSUPERBCommented:
Seamus,

You need to put it in a new column field not in a criteria.

When properly placed, it will add a column field Sample when you run the query.

Ed
0
 
compTrackConnect With a Mentor Commented:
Hi There,

I think you have misunderstood Ramanhp ... his code would work. However, you'll need to use his IIF Statement to suite your scenario.

After seeing both your attachments, I reconstructred your query on my computer and have suggested the solution in the attachment. The only difference is that your query has a "Total: Group By" and mine does not. Also, dont worry about the "Expr1:" on my suggested solution. These does not matter, you can leave your query the way it is. Only add the bit in the criteria which is:

IIf(Mid([TRANS_REF],3,1)='P' Or Mid([TRANS_REF],3,1)='D',[TRANS_REF])

Best Regards,

compTrack
Mid-Solution-For-Third-Sequence.docx
0
 
hnasrCommented:
Try this for table a with a field adesc

SELECT a.adesc
FROM a
WHERE Eval(adesc Like "??D*" Or adesc like "??P*") <>False
0
 
Seamus2626Author Commented:
Thanks for the help guys, worked out fine.

Sorry for the late response.

Cheers,
Seamus
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.