UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Iif, Access 2016    
 
   
mike60smart
post Feb 12 2019, 01:16 PM
Post#1


UtterAccess VIP
Posts: 13,034
Joined: 6-June 05
From: Dunbar,Scotland


Hi Everyone

I have the following as a field in an Update query:-

Type: IIf([Royalty Type]="","Paperback","")

I have now been informed that [Royalty Type] may sometimes contain data.

How would I modify this to insert "Paperback" if [Royalty Type] contains data sometimes?

Any help appreciated



--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
theDBguy
post Feb 12 2019, 01:19 PM
Post#2


Access Wiki and Forums Moderator
Posts: 74,503
Joined: 19-June 07
From: SunnySandyEggo


How about?

CODE
Type: IIf([Royalty Type]="","Paperback",[Royalty Type])

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
mike60smart
post Feb 12 2019, 01:37 PM
Post#3


UtterAccess VIP
Posts: 13,034
Joined: 6-June 05
From: Dunbar,Scotland


Hi theDBGuy

I might not have explained my problem correctly.

When I say it might sometimes contain data, the data appears to be random ie sometimes it is "N/A" another time "60%"

So if it contains No data insert "Paperback" in the Type field

If it contains any sort of string then also insert "Paperback" in the Type field


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
tina t
post Feb 12 2019, 01:40 PM
Post#4



Posts: 5,747
Joined: 11-November 10
From: SoCal, USA


QUOTE
When I say it might sometimes contain data, the data appears to be random ie sometimes it is "N/A" another time "60%"

So if it contains No data insert "Paperback" in the Type field

If it contains any sort of string then also insert "Paperback" in the Type field

hi Mike, when the field already contains data, do you want to overwrite that data with "Paperback"? or...?

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
dashiellx2000
post Feb 12 2019, 01:49 PM
Post#5


UtterAccess VIP
Posts: 9,823
Joined: 11-March 05
From: Maryland


Ok. I'm not sure I'm understanding.

What is the point of the field in the query? You say it is an update query, are you attempting to update every entry in the table regardless of the actual Royalty Type?

--------------------
William
“We're run by the Pentagon, we're run by Madison Avenue, we're run by television, and as long as we accept those things and don't revolt we'll have to go along with the stream to the eventual avalanche"
Go to the top of the page
 
mike60smart
post Feb 12 2019, 02:33 PM
Post#6


UtterAccess VIP
Posts: 13,034
Joined: 6-June 05
From: Dunbar,Scotland


Hi William & Everyone

My apologies I should have said Append query

The data is imported from an excel file to a Temp table and then the Append is based on the Temp table to append to another table.

So the field "Royalty Type" in the Temp table will contain no data or a random string.

Whatever this field Contains I want to append "Paperback" into the Type field


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
BruceM
post Feb 12 2019, 02:50 PM
Post#7


UtterAccess VIP
Posts: 7,827
Joined: 24-May 10
From: Downeast Maine


In the append query you could just use the hard-coded valued "Paperback" instead of a field value:

INSERT INTO SomeTable (ID, SomeData, RoyaltyType) SELECT ID, SomeData, "Paperback" FROM tblTemp
Go to the top of the page
 
mike60smart
post Feb 12 2019, 02:57 PM
Post#8


UtterAccess VIP
Posts: 13,034
Joined: 6-June 05
From: Dunbar,Scotland


Hi Everyone

My apologies the answer is so simple I don't know why I did not get this in the first place.

I just made it this:-

Type:"Paperback"

Works just fine


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
mike60smart
post Feb 12 2019, 02:58 PM
Post#9


UtterAccess VIP
Posts: 13,034
Joined: 6-June 05
From: Dunbar,Scotland


Hi Bruce

Yes so simple I missed it all together


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
BruceM
post Feb 12 2019, 03:15 PM
Post#10


UtterAccess VIP
Posts: 7,827
Joined: 24-May 10
From: Downeast Maine


OK, good to know you sorted it out. Best of luck with the project.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    23rd February 2019 - 04:13 AM