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
> Selectively Format A Value As A Currency In A Continuous Form, Any Version    
 
   
fizzy1
post Jun 26 2019, 06:31 PM
Post#1



Posts: 509
Joined: 26-May 11



Hi,

I have a continuous form that gets its data from a temp table. I fill the temp table by doing a series of DLOOKUPs. It has the same 4 rows each time it gets filled. One of the values I push into the table in one record is a currency, while the other records hold integers in the same field.

I'd like the form to display the currency as such (ie: a $ sign and 2dp) while the other values should display as whole numbers. See attached picture for clarity.

I'm not sure how to go about this. Can someone please help?

Thanks,
Toby.

Attached File  Screenshot_2019_06_26_17.22.26.png ( 19.43K )Number of downloads: 3

--------------------

thanks,
fizzy1.
Go to the top of the page
 
theDBguy
post Jun 26 2019, 08:25 PM
Post#2


Access Wiki and Forums Moderator
Posts: 75,722
Joined: 19-June 07
From: SunnySandyEggo


Hi Toby. Is the continuous form just for displaying the values, not for editing them? If so, why not format the values appropriately when you store them in the temp table?

--------------------
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
 
RJD
post Jun 26 2019, 08:27 PM
Post#3


UtterAccess VIP
Posts: 9,733
Joined: 25-October 10
From: Gulf South USA


Hi: It is a bit odd to mix data types in the same field - and I would not recommend it. However, since you are only displaying the data, not editing, adding or deleting, you can use Format function within an IIf statement, along with a right justify when displaying in the form to do what you seem to want ...

SELECT Sequence, Description, IIf([Description] Like "Items*",Format([Amount],"0"),Format([Amount],"Currency")) AS DisplayAmount
FROM tblMyRecords
ORDER BY Sequence;

This assumes that "Items" in the description will ID the integer amounts, with the currency starting with another word.

See the demo attached.

Just saw theDBguy's response, posted as I was posting - and that is certainly a good option - format the values into a text field in the table as you create the records. My solution above simply takes the values as currency from the table and re-formats them in the query. Same result, different place. Whatever works best for you.

HTH
Joe
Attached File(s)
Attached File  SelectivelyFormat.zip ( 19.83K )Number of downloads: 1
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
fizzy1
post Jun 26 2019, 10:53 PM
Post#4



Posts: 509
Joined: 26-May 11



Gents,

Yes, the form is simply to display data -- no editing or any interaction with it happens.

I did try DBguy's way of storing the values as text, and formatting it as it goes into the table, but it felt so dirty, hahaha.

So then I had a go at RJD's approach but somehow couldn't get the statement to play nice -- it'd fire the IIf = True every time, which is weird because there's only the one record where InventorySnapshotID = 1.

Here's my code for that:
Me.RecordSource = "SELECT InventorySnapshotID, InventorySnapshotName, ' " & IIf([InventorySnapshotID] = 1, Format(InventorySnapshotValue, "Currency"), Format([InventorySnapshotValue], "0")) & " ' AS InvValue FROM tblInventorySnapshotTemp"

Edit: So for now, at least, I'm using the "stored as text" approach. That said, I'd like to understand why my code is not working correctly, but it's not a huge deal.
This post has been edited by fizzy1: Jun 26 2019, 10:57 PM

--------------------

thanks,
fizzy1.
Go to the top of the page
 
RJD
post Jun 26 2019, 11:27 PM
Post#5


UtterAccess VIP
Posts: 9,733
Joined: 25-October 10
From: Gulf South USA


Hi again: I noticed you didn't download the demo I posted, which is working well. And I question how you are formatting the SQL record source you posted. It seems to have out of place quotes.

Did you save the query and try running it by itself?

If you want an answer to why your SQL is not working, while mine is, we'll need to see your table as well as your query. And you should download my demo and see how it is different from your setup as well.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
BruceM
post Jun 27 2019, 07:38 AM
Post#6


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


It is puzzling that you would have different types of data in the same field, even if it is a calculated field. It would be interesting to see how you produce the data displayed in that field.
Go to the top of the page
 
fizzy1
post Jun 27 2019, 10:13 AM
Post#7



Posts: 509
Joined: 26-May 11



Hi Bruce, this is just somewhere that I can collect various bits of data to display on a dashboard. I fill the temp table using various DLOOKUPs, [edit] and then push the values in there with an INSERT statement for each value.
This post has been edited by fizzy1: Jun 27 2019, 10:23 AM

--------------------

thanks,
fizzy1.
Go to the top of the page
 
fizzy1
post Jun 27 2019, 10:22 AM
Post#8



Posts: 509
Joined: 26-May 11



Hi RJD,

I'd run out of time last night to look at your demo before going to bed, so I just posted an update where I was up to. I've since grabbed your demo, and I appreciate what you did, thanks. I'd taken a slightly different route in that I stored my data as a Single not Currency (if that makes a difference), and I'd skipped using a query -- instead, I was trying to do the formatting directly in the continuous form.

I agree that the quotes in my SQL seem odd -- that was me trying to debug it -- prior to adding those it was choking on the "Currency" part of the Format statement, expecting an end of statement. You can see the different SQL statement versions I tried in my demo, which is attached.

Regards,
Toby.

Attached File  continuous_form_varied_formatting_test.zip ( 37.66K )Number of downloads: 2

--------------------

thanks,
fizzy1.
Go to the top of the page
 
RJD
post Jun 27 2019, 03:44 PM
Post#9


UtterAccess VIP
Posts: 9,733
Joined: 25-October 10
From: Gulf South USA


Hi fizzy: Well, you are going about this the really hard - and unnecessary - way. Just put the IIf logic in a textbox in the form instead of the original value control.

=IIf([InventorySnapshotID]=1,Format([InventorySnapshotValue],"Currency"),Format([InventorySnapshotValue],"0"))

That's really all there is to this. Your On Load code is unnecessary, and not appropriate the way it is done. See my revision to your posted db.

Actually, I prefer using a query to do this, since in the query you can specify the sort order to assure the order is what you want, and can test the results before committing it to the form (or a report, which might be a better approach in this case where you do not want to edit or add/delete records).

HTH
Joe
Attached File(s)
Attached File  continuous_form_varied_formatting_test_Rev1.zip ( 23.34K )Number of downloads: 4
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
fizzy1
post Jun 28 2019, 03:27 PM
Post#10



Posts: 509
Joined: 26-May 11



Hi Joe,

Ah, yes, putting the formatting directly into the text control in the form. I didn't think of that; I think I was running on little sleep that day.

Originally I was wanting to avoid a saved query in the middle simply because I figured I could do what I needed without one.

Directly formatting the text control does work well, but I hear you on using a saved query in between. I think I'll do that, thanks.

Best,
Toby.

--------------------

thanks,
fizzy1.
Go to the top of the page
 
cheekybuddha
post Jun 28 2019, 04:28 PM
Post#11


UtterAccess VIP
Posts: 11,419
Joined: 6-December 03
From: Telegraph Hill


To save a few keystrokes, and IMHO clarity, you can just apply the IIf() to the format string:
CODE
=Format([InventorySnapshotValue], IIf([InventorySnapshotID] = 1, "Currency", "0"))


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
RJD
post Jun 28 2019, 05:19 PM
Post#12


UtterAccess VIP
Posts: 9,733
Joined: 25-October 10
From: Gulf South USA


You are very welcome, Toby. Glad that works for you. Continued success.

And David ... yes, more compact and a bit easier to decipher. Thanks for that.

Regards to you both,

Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th July 2019 - 01:01 AM