numeric format using iif function.

  • Thread starter Thread starter Frank
  • Start date Start date
F

Frank

I have a text box with the following Control source IIF function. It returns
data such as: record 1500 of 2289; 1501 of 2289; etc.

I would like the numbers to have commas separate thousands. For example, I
would like the return to be 1,500 of 2,228; 1,501 of 2,228, etc. Any
assistance is most welcomed.


=IIf([CurrentRecord]>(Count(*)),'New Record',('Record ' & [CurrentRecord] &
' of ' & Count(*)))
 
something like this
=IIf([CurrentRecord]>(Count(*)),'New Record',('Record ' &
clng(format([CurrentRecord],"#,###")) & ' of ' & clng(Format(Count(*),
"#,###"))))

I might have too many or not enough "()" but check into the format function.

BUT because it is in the control source this may not work.

You might want to make a public function to solve this


----Air CODE----
Public Function fFillTxtBox(lngCurrentRec as long, lngRecCount as long)
as string

if lngCurrentRec > lngRecCount then
fFillTxtBox = "New Record"
Else
ffillTxtBox = "Record " & Format(lngCurrentRec, "#,###") & _
" of " & Format(lngRecCount, "#,###")
end if

end function

in the record source use
= fFillTxtBox ([CurrentRecord],count(*))

Like I said this is aircode but I know the answer is somewhere in the
"FORMAT" function.
 
Hi Fredrick:

I tried your first option by placing it in the Control Source. It did not
work. I'm not sure how to create a Public Function or exactly where to put
it as I am relative new to VBA coding. Any assistance is appreciated in this
area.
 
I don't think that you want the clng(), it will convert the formatted number
back to a long. Try:

=IIf([CurrentRecord]>Count(*), 'New Record', ('Record ' &
format$([CurrentRecord],"#,###") & ' of ' & Format$(Count(*), "#,###"))

HTH
John
 
Hi John:

Thanks a ton for most helpful suggestion. I tried your recommendation and it
works beautifully. Once again, thanks for your time and effort in solving my
formatting problem.


John Smith said:
I don't think that you want the clng(), it will convert the formatted
number back to a long. Try:

=IIf([CurrentRecord]>Count(*), 'New Record', ('Record ' &
format$([CurrentRecord],"#,###") & ' of ' & Format$(Count(*), "#,###"))

HTH
John

Frederick said:
something like this
=IIf([CurrentRecord]>(Count(*)),'New Record',('Record ' &
clng(format([CurrentRecord],"#,###")) & ' of ' & clng(Format(Count(*),
"#,###"))))
I might have too many or not enough "()" but check into the format
function.
BUT because it is in the control source this may not work.
You might want to make a public function to solve this
Click to expand...
Click to expand...
 
Very interesting. Thanks for setting me straight. I guess if I'd applied
another brain cell to it it would have only made sense to keep it in a
string format.

Sorry for any confusion.

FRANK: I take it you do not need that public function anymore? But as a
side note, if you make a function like I presented, it can go into a
standalone module or it can go in the appropriate form module. If you
use the first, then it is available globally in your database. If you
put it in a form, it is only available if that form is open. Hope I did
not confuse you.

John said:
I don't think that you want the clng(), it will convert the formatted
number back to a long. Try:

=IIf([CurrentRecord]>Count(*), 'New Record', ('Record ' &
format$([CurrentRecord],"#,###") & ' of ' & Format$(Count(*), "#,###"))

HTH
John

Frederick said:
something like this
=IIf([CurrentRecord]>(Count(*)),'New Record',('Record ' &
clng(format([CurrentRecord],"#,###")) & ' of ' & clng(Format(Count(*),
"#,###"))))
I might have too many or not enough "()" but check into the format
function.
BUT because it is in the control source this may not work.
You might want to make a public function to solve this
Click to expand...
Click to expand...
 
Fred:
Thanks for the follow up. Got everything working cool now. Have a good one.


Fred Wilson said:
Very interesting. Thanks for setting me straight. I guess if I'd applied
another brain cell to it it would have only made sense to keep it in a
string format.

Sorry for any confusion.

FRANK: I take it you do not need that public function anymore? But as a
side note, if you make a function like I presented, it can go into a
standalone module or it can go in the appropriate form module. If you use
the first, then it is available globally in your database. If you put it
in a form, it is only available if that form is open. Hope I did not
confuse you.

John said:
I don't think that you want the clng(), it will convert the formatted
number back to a long. Try:

=IIf([CurrentRecord]>Count(*), 'New Record', ('Record ' &
format$([CurrentRecord],"#,###") & ' of ' & Format$(Count(*), "#,###"))

HTH
John

Frederick said:
something like this
=IIf([CurrentRecord]>(Count(*)),'New Record',('Record ' &
clng(format([CurrentRecord],"#,###")) & ' of ' & clng(Format(Count(*),
"#,###"))))
I might have too many or not enough "()" but check into the format
function.
BUT because it is in the control source this may not work.
You might want to make a public function to solve this
Click to expand...
Click to expand...
Click to expand...
 
Back
Top