Format problem in Text box (1 Viewer)

BJF

Registered User.
Local time
Today, 13:45
Joined
Feb 19, 2010
Messages
133
Hi,

I'm having a weird format problem.
I have a text box on a form that I use to display a number from a subform.
I use it because its on another tab of the form.

It works fine but the format only works under one condition.

The control source is:

=[Forms]![frmSalesOrders]![fsubProductNew].[Form]![QtyYardsOrdered] & " Yds"

and its working fine but i noticed it didnt have a comma if it was a number in the thousands so i set the format to Standard and decimal places zero.

ex. it displays 30000 Yds but i want it to display 30,000 Yds

Basically Access ignores the format conditions i set unless i remove the end portion of the control source ....... & " Yds"

If i make the control source:
=[Forms]![frmSalesOrders]![fsubProductNew].[Form]![QtyYardsOrdered]

then the format works.

Is there a way around this or is this a bug in access?

Thanks for any input.

Brian
 

June7

AWF VIP
Local time
Today, 09:45
Joined
Mar 9, 2014
Messages
5,423
It's not a bug and it's not weird. Appending the string "yds" turns the value into text string and cannot apply number formatting on string that includes non-number characters (alpha, symbols). You can use Format function on the data before appending the string. Or put the "yds" in a label that sits next to the textbox.
 

BJF

Registered User.
Local time
Today, 13:45
Joined
Feb 19, 2010
Messages
133
Thanks for the response,

I forgot it turns it into a text string!

i was able to use format function before the number portion of my control source and it worked however i cant seem to control the decimal places.

my control source is now:

=Format([Forms]![frmSalesOrders]![fsubProductNew].[Form]![QtyYardsOrdered],"Standard",0) & " Yds"

and it displays as 30,000.00 Yds

Can anyone tell me how to lose the .00?

Only examples i find online are to put the Format("Standard",0)

Thanks for any suggestions

Brian
 

Micron

AWF VIP
Local time
Today, 13:45
Joined
Oct 20, 2018
Messages
3,476
I don't believe there's any built in format option that provides separators and no decimal places. Try Format(your expression,"#,#")


P.S. I think what you want to look for is "custom format" options.
 
Last edited:

BJF

Registered User.
Local time
Today, 13:45
Joined
Feb 19, 2010
Messages
133
Thanks for the response Micron but unfortunately that wont work, however i can live with the decimal places.

Thanks for everyones input
 

BJF

Registered User.
Local time
Today, 13:45
Joined
Feb 19, 2010
Messages
133
I dont know why,

maybe because i have the word standard in quotes before it????


my code was:

=Format([Forms]![frmSalesOrders]![fsubProductNew].[Form]![QtyYardsOrdered],"Standard",0) & " Yds"

and i get 30,000.00 Yds

if i swap out the 0 with "#,#"

i get #type!

and if i just put #,# withour quotes, the control source wont accept it - it tells me its an invalid date value
 

June7

AWF VIP
Local time
Today, 09:45
Joined
Mar 9, 2014
Messages
5,423
The "#,#" replaces "Standard". The 0 does not have anything to do with number format, it defines first day of week for date formatting.

=Format([Forms]![frmSalesOrders]![fsubProductNew].[Form]![QtyYardsOrdered], "#,#") & " Yds"
 

BJF

Registered User.
Local time
Today, 13:45
Joined
Feb 19, 2010
Messages
133
Thank you both June7 and Micron!

Finally got it between both of your help and input.

I really appreciate it
 

Users who are viewing this thread

Top Bottom