IIf and Trim

Hi,

I use IIf([ReserveDate] Is Null,Null,([ReserveDate] & " & " & 
[ReserveDateTo])) to display ReserveDate & ReserveDateTo if ReserveDate is 
Null. It works fine.

However, when ReserveDate has value, it displayed with &.

Ex: 12/22/08 &.

Would you please show me how to take the “&” off? 

Thanks
Chi


0
Utf
3/19/2008 1:44:00 PM
access.reports 4434 articles. 0 followers. Follow

3 Replies
761 Views

Similar Articles

[PageSpeed] 43

Try:
[ReserveDate] & IIf(IsNull([ReserveDateTo]),Null," & " & [ReserveDateTo])

-- 
Duane Hookom
Microsoft Access MVP


"Chi" wrote:

> Hi,
> 
> I use IIf([ReserveDate] Is Null,Null,([ReserveDate] & " & " & 
> [ReserveDateTo])) to display ReserveDate & ReserveDateTo if ReserveDate is 
> Null. It works fine.
> 
> However, when ReserveDate has value, it displayed with &.
> 
> Ex: 12/22/08 &.
> 
> Would you please show me how to take the “&” off? 
> 
> Thanks
> Chi
> 
> 
0
Utf
3/19/2008 2:29:02 PM
Hi Duane,

It works perfectly! Thank you so much.
Chi 

"Duane Hookom" wrote:

> Try:
> [ReserveDate] & IIf(IsNull([ReserveDateTo]),Null," & " & [ReserveDateTo])
> 
> -- 
> Duane Hookom
> Microsoft Access MVP
> 
> 
> "Chi" wrote:
> 
> > Hi,
> > 
> > I use IIf([ReserveDate] Is Null,Null,([ReserveDate] & " & " & 
> > [ReserveDateTo])) to display ReserveDate & ReserveDateTo if ReserveDate is 
> > Null. It works fine.
> > 
> > However, when ReserveDate has value, it displayed with &.
> > 
> > Ex: 12/22/08 &.
> > 
> > Would you please show me how to take the “&” off? 
> > 
> > Thanks
> > Chi
> > 
> > 
0
Utf
3/19/2008 2:46:03 PM
Chi wrote:
>I use IIf([ReserveDate] Is Null,Null,([ReserveDate] & " & " & 
>[ReserveDateTo])) to display ReserveDate & ReserveDateTo if ReserveDate is 
>Null. It works fine.
>
>However, when ReserveDate has value, it displayed with &.
>
>Ex: 12/22/08 &.
>
>Would you please show me how to take the �&� off? 


IIf([ReserveDate] Is Null, Null, [ReserveDate] & (" & " +
[ReserveDateTo]))

-- 
Marsh
MVP [MS Access]
0
Marshall
3/19/2008 4:18:59 PM
Reply:

Similar Artilces:

IIF statement??
I was going to use Payroll ID numbers as the unique identifier for each person that I enter into a database template that I recently downloaded, but realized on Friday that that may not work as we have some folks to be put into the database who do not have ID numbers. I would like to use IDs for those who have them, and then have the database randomly or sequentially generate them for those that I do not have IDs for. Would an IIF statement be the best way to do this? And if it is, how would I go about doing it? Thank you, Teri. Hi Teri, I'd be more inclined to add...

IIF Divide
I have a report and i'm trying to divide the [PremiumAmt] by 12 if the [PremiumFreq] equals "Annual". If [PremiumFreq] is not Annual, then it should just use [PremiumAmt] value. The [PremiumAmt] is a currency field and the report is based on a query. I tried this statement in the control source of the text box in the report but i'm, getting a syntx error. IIF([PremiumFreq]="Annual",[PremiumAmt]/12,[PremiumAmt]) Suggestions: a) Don't forget the = at the start of the expression. b) What is the name of this text box? Access gets confused if it has the same...

Object trimming
I am trying to trim an object. Such as a circle so I can add lines at two points and make a coltalsak or end of the street. Does any one know how to trim objects? -- Eric You can find Trim and many other object combination functions under Shape > Operations. In your example, I'm not sure what you want to trim. You might try overlaying a circle and rectangle and then use the Union operation. This would make a keyhole shape. -- Mark Nelson Microsoft Corporation This posting is provided "AS IS" with no warranties, and confers no rights. "eric" <eric@discuss...

Sum(IIF statement
I have a large table that I want to query 2 things from. Please be patient because I am pretty new at this. I need to count the number of times that something appears in some fields in the table. Below is what I have tried (keep in mind there are many more fields to count)... test: [Exp1]=Sum(IIf([Account number] Is Not Null,1,0)+(IIf([Address] Is Not Null,1,0)) I would like my output to look like: KEY TEST 1 2 2 0 3 5 etc... And then I want to look in each of those same fields and if there is data concatenate it into one field on the query output and...

trim fields
Hi, When I create a mail merge in Publisher 2007 and I use an existing Access database, how would I trim the field lengthes in my template when mail merging? Thanks Steve I would suggest trimming them in Access before the merge. -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "Steve" <adelxt@hotmail.com> wrote in message news:eIzSY%23EFKHA.4220@TK2MSFTNGP02.phx.gbl... > Hi, > > When I create a mail merge in Publisher 2007 and I use an existing Access > database, how would I trim the field lengt...

Compile error :: Trim, ...
I have an excel 2003 xls which works fine on my pc. When I send it to a colleague, it spits out errors. When he compiles it it highlight a Trim() command. When we delete the Trim(), just to test further, it then highlights an integer variable 'i' which is simply a counter. If a change that and compile further, it highlight UCase$... and I stopped trying to fix it. What is going on? Anyone ever seen this before. I check the references libraries and they are all OK. Thank you for your input, QB Hi, I don't understand why it would fail to compile TRIM(...

TRIM FUNCTION in QUERY
Hello, What do I put to trim off the spaces after a value in query? The table is obtained from ODBC link and the values on a field somehow got spaces. Example: My field in the query is: WC: PO_WORKCENTER_ID Not sure WC: TRIM(PO_WORKCENTER_ID) ???? That should work. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Cam" wrote: > Hello, > > What do I put to trim off the spaces after a value in query? The table is > obtained from ODBC link and the values on a field somehow got spaces. Example: > > My...

Trim out last seven characters from a string...
Is this easy to do? The last seven characters are constants, I just want what precedes them... DubboPete give this a try =MID(A1,1,LEN(A1)-7) -- Gary Keramidas Excel 2003 "DubboPete" <osnabruc@tpg.com.au> wrote in message news:a1eb7259-21b8-406b-baea-e65c0669aaec@k2g2000pro.googlegroups.com... > Is this easy to do? The last seven characters are constants, I just > want what precedes them... > > DubboPete On Feb 2, 3:04=A0pm, "Gary Keramidas" <gkerami...@XXMSN.com> wrote: > give this a try > > =3DMID(...

IIf function for 3 possible outcomes?
I want to calculate a rate for 3 types of Gears (coded "OT04S", "OT16S", and "POT02S". I've used IIf in the past when the database contained only 2 Gear types, but can't seem to get the expression correct now that there are 3 Gear types. Can I use the IIf function here or should I be going in another direction? Here's my best attempt at a 3-outcome IIf: 'Rate: IIf([Gear]="OT04S",([Count]/([Distance]*"2.93")),(IIf([Gear]="OT16S",([Count]/([Distance]*"3.11"))),([Count]/([Distance]*"1.75"))))'...

make field with sql or iif
have this: Key Field1 Field2 1 X 100 2 ABC 123 3 111 aaa 4 X 101 5 asd 1212 6 frr 321 Need this: Key Field1 Field2 Field3 1 X 100 100 2 ABC 123 100 3 111 aaa 100 4 X 101 101 5 asd 1212 101 6 frr 321 101 If field1=X then Field3 = Field2 (so far so good - no problemo) If field1 is not X then Field3 = Field 3 of preceeding record per Key sequence am struggling with...

IIF statement 06-14-07
Hi, need some help I created in the detailsection of a report an unbound Textbox with an IIF statement if field A is empty then print fieldB; if field A is not empty then print field C the contents of Field A - B - and C are text =IIF([fieldA]="";[fieldB];[fieldC]) this wont work in my Access (in Excel it works) In the Dutch version I have to write ; instead of , What is going wrong? Thanks in advance for your prompt reply Best regards Ron On Thu, 14 Jun 2007 13:08:07 -0700, MEERPAREL wrote: > Hi, need some help > > I created in the detailsection o...

Dictionary<key,item> trimming internally
I was playing around with using Dictionary<> to implement a sparse array using something like class Key { byte x, y, z; } where the "space" is 16,777,216 entries but there are only ever going to be maximum of 10,000 entries but it seems to me the internal hash array only grows - it never shrinks looking at the code with reflector shows a private Resize() method that only grows the number of buckets ideally I would like something that would automatically recycle buckets and so not generate any garbage any ideas? John Rivers wrote: > [...] &...

Help with IIF / DLookup formula
Hello - I am getting an error with this formula: =IIf(NZ(DLookUp([PymtTotal],"q_PymtTotalsBySessionSub","[FamilyID]=" & [Family_ID])),0,(DLookUp([PymtTotal],"q_PymtTotalsBySessionSub","[FamilyID]=" & [Family_ID]))) The error only shows if there is no payment data in the q_PymtTotalsBySessionSub query. If there is data, then the formula returns "0", which is incorrect. This formula works properly if there is payment data, but gives an error if there is none: =DLookUp([PymtTotal],"q_PymtTotalsBySessionSub","[Fami...

Trim function #2
How can I have displayed the figures (cells) used, when I run a Trim function. Hi not really sure what you're rying to achieve. Could you give an example? >-----Original Message----- >How can I have displayed the figures (cells) used, when I run a Trim function. >. > When you use the TRIM Worksheet Function by itself the result is displayed in the cell. =TRIM(B1) If you want to know what B1 actually contains, you could use CODE for each byte, but easier would be Chip Pearson's Cell View -- http://www.cpearson.com/excel/CellView.htm For a maco to trim all cells i...

iif expected end of statement
Hello. I'm trying to use this code that omits weekend days from a date calculation. I get an Expected: end of statement error. I am not sure what to do. I took this code off of another post I found on this site. so I am not too advanced at this. Thanks! ..iMin = DateDiff("d", sPrevDate, sAppDate) - _ DateDiff("ww", sPrevDate, sAppDate, vbSunday) * 2 _ IIf(Weekday(sAppdate, vbSunday)= vbSaturday, _ IIf (Weekday(sPrevDate, vbSunday) = vbSaturday, 0, 1), _ IIf (Weekday(sPrevDate, vbSunday) = vbSaturday, -1, 0)) "Cristen&q...

Trim function #3
In cell A2, I use the trim function to trim cell A1 containing a date eg 1202/09, the result in A2 is 39856. However, this 39856 is text and not date value. I therefore cannot convert it back to date after the trimming. Can someone please help? Many thanks... -- exalan Just format the cell as date to display: 2/12/2009 -- Gary''s Student - gsnu200833 On Feb 14, 7:17=A0am, exalan <exa...@discussions.microsoft.com> wrote: > In cell A2, I use the trim function to trim cell > A1 containing a date eg 1202/09, the result in A2 > is 39856. However, this 39856 is text an...

IIF statement in query criteria, help!
I have a query in which one date need to be filtered based on another field. If the field [last] =1 , the data needs to be filtered showing only data where [ShiftDate]>=[Start1]. If the field [last]=2, it is [shiftdate]>=[Start2]. So in the criteria for field [ShiftDate], I entered "iif([last]=1,[shiftdate]>=[start1],[shiftdate]>=[start2])". I don't get an error, but I also don't get any results. If I enter ">=[start1]" or [shiftdate]>=[start1] instead of the iif statement, I get the results expected. I'm pretty sure that I...

IIF function 02-20-08
Please help me set up the following calculated field. Need a calculated field that equals Active if the MembershipStatus field is equal to Active and equals Not Active otherwise. -- jj On Wed, 20 Feb 2008 09:35:04 -0800, JJ wrote: > Please help me set up the following calculated field. Need a calculated > field that equals Active if the MembershipStatus field is equal to Active and > equals Not Active otherwise. NewColumn:IIf([MembershipStatus] = "Active","Active","Not Active") -- Fred Please respond only to this newsgroup. I do not reply to p...

Trim Data
I know this may be answered somewhere but I am new at this and need very specific steps. Screen shots also help. I am running a query. In one of the fields "addr: it has information like this example. 236 West Rezanof Drive, Kodiak, AK 99615 or Kodiak, AK 99615 or Some may be blank. The only thing I want from this field is the street address without the city state or zip. So I guess something that will trim everything after the first comma knowing that sometime the field is blank. Unfortunately, address data is not standard enough for the computer to be able to pick the st...

Trim Dialog according to the shape of image
Hi I've to make an application in which i have to load a page on IE control placed on the dialog. This page would only contain an image. Now i want my dialog to be exactly like the edges of that image. such that user may feel that he/she has just that image in front of him. I cannot use the image directly on dialog because the image has buttons on it that would be mapped to different URLs and i have to get those button clicks in my application along with the URLs they are mapped on. Please tell me if there is a way to make a dialog align edge to edge with an image that iteself it on an IE....

How to preserve null value in a access calculated field using iif
Hi, I have a field in sql server table named hasmajor. Now this field is of type smallint. The front end is access connected to sql server via odbc. Now the field value in Access for this field is: 1 0 0 1 0 1 I am using a function =IIf([HasMajor],"Y","N") as the control source for another column that will display the above field value as Y N. I am expecting the values of the above field in the calculated column as the following: Y N N Y N Y This means I am expecting neither N or Y when there is null value. However the null value is being displayed as N. I have ...

Iif Statement Problem
I am trying to run the below query to simply find data in a column that is a certain criteria, then taking data in another column and multiplying it and it is not returning the correct amounts based on the below. Can someone help me to write this correctly. Thank you. Adjusted DR Payout: IIf([Rep % of Average]<80,[REP DR PAYOUT]*0.8,IIf([Rep % of Average]<90.01 And [Rep % of Average]>79.99,[REP DR PAYOUT]*0.9,IIf([Rep % of Average]<105.01 And [Rep % of Average]>90.01,[REP DR PAYOUT]*1,IIf([Rep % of Average]<115.01 And [Rep % of Average]>105.01,[REP DR PAYO...

Iif then statement needed for Access 2007 please
Hi all, I'm trying to figure out how to write an Iif Then statement in access 2007 for: iif columnZ = 10 then columnZ = A please help, SQL and query table builder Thanks Heather Heather I'm not clear why you need to use an IIF() expression... Have you looked into creating an update query? (CAUTION: backup before running a query that alters your data!) Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not ...

Need better understanding of TRIM so I can get rid of trailing spa
I imported a listof names to creat labels. All columns have trailing spaces. I have imported some into Word then done a relace it worked but it was a not the right solution. I understand TRIM in Excel will get rid of Trailing spaces. The examples HELP gives is not clear to me. Please help me get rid of these trailing spaces. Tom Trim should work, Tom, but it's tough to say without seeing your data. What happens if you open the data directly from Excel? What format is it now? Any txt file opened from Excel should give you delimiter options. Are the number of trailing spaces always ...

IIF Statement Help 01-29-10
I am getting an error messge saying a I am missing a ( or|and I can not figure out what is wrong. Can you please assist with debugging the following code: Test: IIF(Month(Date())>10 AND Month([Contract Info].[Start Date])<3, Year(Date())+1, Year(Date()) Essentially I want this calculated field to take a look at the current month and for January and February add one the the current year, otherwise the field should be the current year you are missing a parenthesis at the very end. that last parenthesis you have is ending only the Year function, but you also n...