Mid([myField],4) - but with condition. (1 Viewer)

[jamie_b]

Hi all,

I use this and it works as expected:

Mid([myField],4)

The data in myField will almost always start with 'ABC' followed by 'somedata'.

Using Mid does this:

[ABCsomedata] turns to [somedata]

But if myField contains only 'somedata', then the following happens:

[somedata] turns to [edata]

What I am asking is:

How or What do I change to still use Mid([myField],4) - BUT ONLY IF myField's data starts with ABC.

Hoping someone understands what I mean and can help.

Thank you kindly,
Jamie.

 

[jdraw]

If Left(MyField,3) = "ABC" Then
   Mid([myField],4)
Else
'What if it does not start with ABC'
End If

 

[MSAccessRookie]

You should be able to use the IIf() Function to do what you want. The format is:

IIf({Condition to test}, {Value to display if TRUE}, {Value to display if FALSE})

Give it a try and get back if you have any further questions

-- Rookie

Note: It looks like jdraw and I replied at the same time and he beat me to the punch. Now you have two options. Jdraw's option, which is excellent as usual, uses VB to resolve the issue. My option was intended to be used in SQL Queries. You should use whichever option serves you best.

-- Rookie

 

[jamie_b]

@jdraw:
Thank you for your reply. Yes, that code works well in VBA.


@MSAccessRookie:
Yes, I was looking for the QUERY syntax (hence my post in the Query Forum)
You pointed me in the right direction. Thank you.

I now have...
IIf(Left([Field],3)="ABC",Mid([Field],4),[Field])
...and it seems to work. I must test it more to be sure but so far no errors.

I'll post back here if I come across any errors.

Thank you both.

Jamie.

 

[MSAccessRookie]

@jdraw:
Thank you for your reply. Yes, that code works well in VBA.


@MSAccessRookie:
Yes, I was looking for the QUERY syntax (hence my post in the Query Forum)
You pointed me in the right direction. Thank you.

I now have...
IIf(Left([Field],3)="ABC",Mid([Field],4),[Field])
...and it seems to work. I must test it more to be sure but so far no errors.

I'll post back here if I come across any errors.

Thank you both.

Jamie.

Jamie,

Thank you for posting back and describing what worked for you. There are always others with the same or similar questions that can benefit from the information provided by your success.

-- Rookie