IFF Statement 3

[LittleMan22]

Currently I have the following:

Dim strComment As String
Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("CurrentClientApplicationDataPolicies&quot

With rst
Do Until .EOF
strComment = strComment & !Carrier & " " & !PolicyNumber & " " & !Amount & ", "
.MoveNext
Loop
.Close
End With

Me.Remarks1 = strComment

This loops through the three fields and creates a continous string of data. However, how can I get it so that the comma at the end only appears if there is to be something following it? In other words I want the comma to appear if there are two policies, but not if there is only one. I think an IFF statement may do the trick but I don't really know how to work it...

Any ideas?

Thanks again,
Ryan.

 

[PaulF]

strComment = Left(strComment, Len(strComment) - 1)

PaulF

 

[Lightning]

Try this:

IIF(rst.EOF = True, strComment = strComment & !Carrier & " " & !PolicyNumber & " "
& !Amount, strComment = strComment & !Carrier & " " & !PolicyNumber & " " & !Amount & ", &quot


But I'm not sure if the EOF property will let you do this accurately.

HTH
Lightning

 

[LittleMan22]

Lightning: It woouldn't work (I'm assuming, like you said, because of the EOF propoerty within the expression).

Paul F: That doesn't seem to do it either. I'm not exactly sure where that bit of code would go within my current code. I've tried it in a couple of places but to no avail. Can you give me some guidance as to where it goes.

Thanks guys,
Ryan.

 

[PaulF]

just before
Me.Remarks1 = strComment

this little bit of code, removes the trailing comma which isn't required


PaulF

 

[Lightning]

But Paul is on the right track:

Me.Remarks1 = Left(strComment, Len(strComment) - 8) & &quot"

should work.

You need to strip out & ", " from the end of the strComment, not just the comma. Since 8 characters includes the end bracket, you then need to add this back.

Thanks Paul. I was trying to think of this, but couldn't get my mind past "TRIM", which obviously wouldn't work. Thanks for the memory jogger.

Lightning

 

[LittleMan22]

Thanks guys,

It worked beautifully.

One more thing while I have you here...

How can I get the fields to format within this code? For example, when I call their values from the recordset a field like AMOUNT is 1000000 wheras I would like it to show up as $1,000,000.

Is this 'on the fly' formatting possible?

Ryan.

 

[Lightning]

I vaguely remember something similar to that question being asked in another thread only recently (certainly within the last week). Try a search for "formatting" or "on the fly".

Lightning

 

[hennep]

Do you mean this ?

... & format$(!Amount, "$#,##0.00&quot & ...

 

[LittleMan22]

Hennep: worked perfectly.

I have another question for you guys too. I used this formatting on the fly in another instance:

Select Case strField
Case "Policy Adjustment"
Select Case varFieldvalue
Case "Replace"
Fdf_Output.FDFSetValue "AmountReplace", Nz(Format(rstClient!Amount, "$#,###&quot), False

This works great, however, if the Amount field in rstClient is empty (a zero-length string) the code gives me an error. Is there any way to get around this?

Thanks again,
Ryan.

 

[Lightning]

Quote from the Access Helpfile:

"Different Formats for Different String Values (Format Function) (Microsoft Access)
In Microsoft Access versions 1.x and 2.0, you could use the Format function to return one value for a zero-length string and another for a Null value. For example, you could use a format expression such as the following with the Format function to return the appropriate string value from code:

Dim varX As Variant, varStrX As Variant
' Assign some value to varStrX and pass to Format function.
varX = Format(varStrX, "@;ZLS;Null&quot

In Microsoft Access 97, you must test separately for the Null case, then return the appropriate value based on the result. For example, you could use the IIf function in an expression with the Format function such as the following:

varX = IIf(IsNull(varStrX),"Null", Format(varStrX, "@;ZLS&quot)

This change applies only when you use the Format function to format a string dependent on whether it's a zero-length string or a Null value. Other format expressions used with the Format function continue to work as they did in previous versions."

So you need to add a test for a zero-length string as well as your current Null test.

Another IIF statement on rstClient!Amount should do it.

HTH
Lightning