Iif Statement help!!!

[GOVMATE]

I'm new to writing nested iff statement. Can anyone help me understand the how to write arguments to avoid syntax errors??? I'm need to write multiple iif statements with arguments that will not change/ignore a field that has a current date displayed MM/DD/YYYY but will flip fields that have just numbers and no slashes // between #'s for dates YYYYMMDD. Thanks in advance!

 

[ajetrumpet]

Is this a repost of this??

Syntax

IIF(condition evaluated, "Value if condition = True", "Value if condition = False")

You can nest IIF's in the TRUE or FALSE "if" sections.

 

[GOVMATE]

Is this a repost of this??

Syntax

IIF(condition evaluated, "Value if condition = True", "Value if condition = False")

You can nest IIF's in the TRUE or FALSE "if" sections.

No this isn't a re-post. I really appreciate your reply. I'm still attempting to get the Iif statement logic working in a query.

 

[Brianwarnock]

Don't understand what is going on here.
If you are saying that some fields display dates as DD/MM/YYYY and some as YYYYMMDD and you want the later to display as DDMMYYYY then , assuming they are date fields, remember that all dates are stored as numbers so just format the display Format([fieldname],"ddmmyyyy").

Brian

 

[ajetrumpet]

I'm still attempting to get the Iif statement logic working in a query.

Do you have a draft of what you have attempted that you can post??

 

[GOVMATE]

Don't understand what is going on here.
If you are saying that some fields display dates as DD/MM/YYYY and some as YYYYMMDD and you want the later to display as DDMMYYYY then , assuming they are date fields, remember that all dates are stored as numbers so just format the display Format([fieldname],"ddmmyyyy").

Brian

Thanks for the reply Brian. The only problem with what you just said is that I'm working with a very large table in which all dates in the field I need to manipulate are text values in which I need to keep them as text formatted. I just need to about half the fields are already in the MM/DD/YYYY test display format. I need nested Iif that will ignore those already correctly formatted and change the #'s displayed as such (no slashes) YYYYDDMM to MM/DD/YYYY.

Below is an example of an update query that needs some tweaking?

UPDATE [Goodrec-All] SET [Goodrec-All].MatDate = IIf(isnull(matdate),matdate,Mid(matdate,5,4) & Mid(matdate,1,4)), origdate = IIf(isnull(origdate),origdate,Mid(origdate,5,4) & Mid(origdate,1,4))

Thanks for any help!!!!!!!!

 

[ajetrumpet]

Instead of this...

UPDATE [Goodrec-All] SET [Goodrec-All].MatDate = IIf(isnull(matdate),matdate,Mid(matdate,5,4) & Mid(matdate,1,4)), origdate = IIf(isnull(origdate),origdate,Mid(origdate,5,4) & Mid(origdate,1,4))

Do this...

UPDATE [Goodrec-All] SET

[Goodrec-All].MatDate = IIF(field = format that needs changed, 

(Right([fieldnameyouareswitching], 2) & "/" & 

Mid([fieldnameyouareswitching], 5, 2) & "/" & 

Left[fieldnameyouareswitching], 4)), "field")

 

[Brianwarnock]

I don,t know why you are looking at nested ifs ,the following will give you the result you want in a query.

IIf(InStr([fd],"/")=0,Right([fd],2) & "/" & Mid([fd],5,2) & "/" & Left([fd],4),[fd])

So if you place this in the update to of your update query, bingo.

Brian

Damn AJE got there first

 

[GOVMATE]

Instead of this...Do this...

UPDATE [Goodrec-All] SET

[Goodrec-All].MatDate = IIF(field = format that needs changed, 

(Right([fieldnameyouareswitching], 2) & "/" & 

Mid([fieldnameyouareswitching], 5, 2) & "/" & 

Left[fieldnameyouareswitching], 4)), "field")

I attempted the above code and I could not get it to run. Even if I could, I believe that I need and instrrev statement to first flip any dates that are in YYYYMMDD to MMDDYYYY and then add slashes MM/DD/YYYY to get the result that I need

 

[GOVMATE]

I don,t know why you are looking at nested ifs ,the following will give you the result you want in a query.

IIf(InStr([fd],"/")=0,Right([fd],2) & "/" & Mid([fd],5,2) & "/" & Left([fd],4),[fd])

So if you place this in the update to of your update query, bingo.

Brian

Damn AJE got there first

Don't worry Brian I wasn't able to get his Update to work nor your Iif statement. I attempted the above code and I could not get it to run. Even if I could, I believe that I need and "instrrev" statement to first flip any dates that are in YYYYMMDD to MMDDYYYY and then add slashes MM/DD/YYYY to get the result that I need.

Thanks for any help!!!

 

[ajetrumpet]

Mate,

I do not understand why Brian's code will not work for you, nor do I understand why mine won't.

No offense, but it sounds to me like either one of two things is happening here...

1) You are not relaying the correct (or ALL relevant) information to us.
OR
2) You do not understand how String Manipulation functions work.

I can say this...

If you posted your database here, we could do one of the following (or maybe both)...

1) Tell you what other relevant information we needed (that we did not get) in order to correctly solve the problem
OR
2) Solve the problem.

 

[GOVMATE]

Don't worry Brian I wasn't able to get his Update to work nor your Iif statement. I attempted the above code and I could not get it to run. Even if I could, I believe that I need and "instrrev" statement to first flip any dates that are in YYYYMMDD to MMDDYYYY and then add slashes MM/DD/YYYY to get the result that I need.

Thanks for any help!!!

My apology Brian and AJE I was to get the code to work once I got home...I really appreciate your help!!!!!!!!

ltr!

Mate

 

[Brianwarnock]

My first reaction to it not working was that you forgot/didn't replace[fd] with your field name, we have had posters copy and paste exactly what's provided without thinking, and I forgot to mention it because of Aje's post, which reminds me that my comment "Damn AJE got there first" could have been misconstrued , it should have been "Damn it,AJE got there first "

Cheers
HTH

Brian

 

[ajetrumpet]

My first reaction to it not working was that you forgot/didn't replace[fd] with your field name, we have had posters copy and paste exactly what's provided without thinking

Amen to that!!

 

[GOVMATE]

My first reaction to it not working was that you forgot/didn't replace[fd] with your field name, we have had posters copy and paste exactly what's provided without thinking, and I forgot to mention it because of Aje's post, which reminds me that my comment "Damn AJE got there first" could have been misconstrued , it should have been "Damn it,AJE got there first "

Cheers
HTH

Brian

Brian!

Question? I was able to get the query to work at home but not at work. Is there a particular library that I need to reference? If so, which one?

Thanks!

 

[GOVMATE]

Brian!

Question? I was able to get the query to work at home but not at work. Is there a particular library that I need to reference? If so, which one?

Thanks!

Brian...please disregard my last post. I was actually refering to another issue I'm having....Thanks again!

 

[GOVMATE]

Instr name flipping assistance!!!!!

Hello,

I need help tweaking the query below.




InStr(UCase([shortname])," JR")>0 Or InStr(UCase([shortname])," II")>0 Or InStr(UCase([shortname])," III")>0,FlipName(UCase([shortname])),UCase([shortname]

This is error message that I receive "undefined function 'flipname' in expression. I work with alot of dirty data I need to clean and load to other subs. The field above "shortname" has various versions of SR, JR, II, III with some spacing before and after the names which makes it diffult for the query to actually catch all variances.

I appreciate any help!!!!!

 

[ajetrumpet]

Well,

Here's a little bit of help on the question you actually implied...

InStr(UCase([shortname])," JR")>0 Or InStr(UCase([shortname])," II")>0 Or InStr(UCase([shortname])," III")>0,FlipName(UCase([shortname])),UCase([shortname]

This is error message that I receive "undefined function 'flipname' in expression.

You're getting the error message because your query reads like this...

Function([field]) OR Function([field]) OR Function([field]), [i][b][color=red]Function[/b][/i][/color]([field], Function([field])

Function = "Flipname".

"Flipname" is not the name of a valid function in Access.

 

[Brianwarnock]

Hi GOV aren't you mixing your threads again?

Brian

 

[GOVMATE]

Well,

Here's a little bit of help on the question you actually implied...You're getting the error message because your query reads like this...

Function([field]) OR Function([field]) OR Function([field]), [i][b][color=red]Function[/b][/i][/color]([field], Function([field])

Function = "Flipname".

"Flipname" is not the name of a valid function in Access.

Hello AJE,

I was told by someone yesterday that flipname was a vba public function...yea I searched and couldn't find anything about it!!! Somehow the script worked at home, but not at the office. I will play with it some more and try to find an alternative function that will do what I need it too.

thks!