• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 503
  • Last Modified:

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
0
Seamus2626
Asked:
Seamus2626
3 Solutions
 
RamanhpCommented:
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
 
MINDSUPERBCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
compTrackCommented:
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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now