CONTROL CURRENCY FORMAT

[Sammie]

Is there a way to control the dollar sign on a currency field on a
report so that the dollar sign only appears on the first item in the list?

Also, how can I format the dollar sign so that there is a space between
the dollar sign and the number (like the accounting format in Excel)?

Thanks.
Sammie

 

[fredg]

Is there a way to control the dollar sign on a currency field on a
report so that the dollar sign only appears on the first item in the list?

Also, how can I format the dollar sign so that there is a space between

Thanks.
Sammie

This should take care of both questions.
Leave the Format property blank.

Add an unbound control to the section.
Set it's control source to
=1
Set it's Running Sum property to either Over All or Over Group,
whichever is appropriate for what you are doing.
Name this control FirstTime

Code the Detail Format event:
If FirstTime = 1 Then
[ControlName].Format = "$ #,##0.00;-$ #,##0.00;$ 0.00"
Else
[ControlName].Format = "#,##0.00;-#,##0.00;0.00"
End If



See Access help
Format Property + Number and Currency datatypes

 

[Sammie]

Is there a way to control the dollar sign on a currency field on a
report so that the dollar sign only appears on the first item in the list?

Also, how can I format the dollar sign so that there is a space between

Thanks.
Sammie

This should take care of both questions.
Leave the Format property blank.

Add an unbound control to the section.
Set it's control source to
=1
Set it's Running Sum property to either Over All or Over Group,
whichever is appropriate for what you are doing.
Name this control FirstTime

Code the Detail Format event:
If FirstTime = 1 Then
[ControlName].Format = "$ #,##0.00;-$ #,##0.00;$ 0.00"
Else
[ControlName].Format = "#,##0.00;-#,##0.00;0.00"
End If



See Access help
Format Property + Number and Currency datatypes

Thanks. I follow you. I am getting the following error in the Detail
Format event:
"Compile error. Method or data member not found."
FYI - ".FORMAT" is highlighted in the code when the error occurrs.
Also, I'm not sure it's picking up the value of 1 in the "FirstTime"
control.
Thanks.
Sammie

 

[fredg]

Is there a way to control the dollar sign on a currency field on a
report so that the dollar sign only appears on the first item in the list?

Also, how can I format the dollar sign so that there is a space between

Thanks.
Sammie

This should take care of both questions.
Leave the Format property blank.

Add an unbound control to the section.
Set it's control source to
=1
Set it's Running Sum property to either Over All or Over Group,
whichever is appropriate for what you are doing.
Name this control FirstTime

Code the Detail Format event:
If FirstTime = 1 Then
[ControlName].Format = "$ #,##0.00;-$ #,##0.00;$ 0.00"
Else
[ControlName].Format = "#,##0.00;-#,##0.00;0.00"
End If


See Access help
Format Property + Number and Currency datatypes

Thanks. I follow you. I am getting the following error in the Detail
Format event:
"Compile error. Method or data member not found."
FYI - ".FORMAT" is highlighted in the code when the error occurrs.
Also, I'm not sure it's picking up the value of 1 in the "FirstTime"
control.
Thanks.
Sammie

Please check your references.

Open any module in Design view (or click Ctrl + G).
On the Tools menu, click References.
Click to clear the check box for the type library or object library
marked as "Missing:."

An alternative to removing the reference is to restore the referenced
file to the path specified in the References dialog box. If the
referenced file is in a new location, clear the "Missing:" reference
and create a new reference to the file in its new folder.

See Microsoft KnowledgeBase articles:
283115 'ACC2002: References That You Must Set When You Work with
Microsoft Access'
Or for Access 97:
175484 'References to Set When Working With Microsoft Access' for
the correct ones needed,
and
160870 'VBA Functions Break in Database with Missing References' for
how to reset a missing one.

For even more information, see
http://members.rogers.com/douglas.j.steele/AccessReferenceErrors.html

If that doesn't take care of it, copy and paste into a reply message
the exact FirstTime control source as well as the exact code you wrote
in the Format event.

 

[Sammie]

fredg wrote:

On Fri, 04 Feb 2005 17:21:23 -0600, Sammie wrote:


Is there a way to control the dollar sign on a currency field on a
report so that the dollar sign only appears on the first item in the list?

Also, how can I format the dollar sign so that there is a space between

Thanks.
Sammie





This should take care of both questions.
Leave the Format property blank.

Add an unbound control to the section.
Set it's control source to
=1
Set it's Running Sum property to either Over All or Over Group,
whichever is appropriate for what you are doing.
Name this control FirstTime

Code the Detail Format event:
If FirstTime = 1 Then
[ControlName].Format = "$ #,##0.00;-$ #,##0.00;$ 0.00"
Else
[ControlName].Format = "#,##0.00;-#,##0.00;0.00"
End If


See Access help
Format Property + Number and Currency datatypes

Thanks. I follow you. I am getting the following error in the Detail
Format event:
"Compile error. Method or data member not found."
FYI - ".FORMAT" is highlighted in the code when the error occurrs.
Also, I'm not sure it's picking up the value of 1 in the "FirstTime"
control.
Thanks.
Sammie

Please check your references.

Open any module in Design view (or click Ctrl + G).
On the Tools menu, click References.
Click to clear the check box for the type library or object library
marked as "Missing:."

An alternative to removing the reference is to restore the referenced
file to the path specified in the References dialog box. If the
referenced file is in a new location, clear the "Missing:" reference
and create a new reference to the file in its new folder.

See Microsoft KnowledgeBase articles:
283115 'ACC2002: References That You Must Set When You Work with
Microsoft Access'
Or for Access 97:
175484 'References to Set When Working With Microsoft Access' for
the correct ones needed,
and
160870 'VBA Functions Break in Database with Missing References' for
how to reset a missing one.

For even more information, see
http://members.rogers.com/douglas.j.steele/AccessReferenceErrors.html

If that doesn't take care of it, copy and paste into a reply message
the exact FirstTime control source as well as the exact code you wrote
in the Format event.

I have no missing references. I have the following references:
Visual Basic for Applications
Microsoft Access 11.0
OLE Automation library
Microsoft ActiveX data Objects 2.0
Microsoft DAO 3.6 oBJECT lIBRARY
Microsoft Word 11.0
Is there a particular reference I need for this procedure?
Here is the info you requested. Not much to copy and paste on the
control source, unless I'm missing something.
Thanks.
Sammie

Text box name: FirstTime
Control source: =1
Running Sum: Over all

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If FirstTime = 1 Then
[UnitSelling].Format = "$ #,##0.00;-$ #,##0.00;$ 0.00"
[ExtendedPrice].Format = "$ #,##0.00;-$ #,##0.00;$ 0.00"
Else: [UnitSelling].Format = "#,##0.00;-#,##0.00;0.00"
[ExtendedPrice].Format = "#,##0.00;-#,##0.00;0.00"
End If

 

[fredg]

On Fri, 04 Feb 2005 20:09:51 -0600, Sammie wrote:
** snipped **

Please check your references.

Open any module in Design view (or click Ctrl + G).
On the Tools menu, click References.
Click to clear the check box for the type library or object library
marked as "Missing:."

An alternative to removing the reference is to restore the referenced
file to the path specified in the References dialog box. If the
referenced file is in a new location, clear the "Missing:" reference
and create a new reference to the file in its new folder.

See Microsoft KnowledgeBase articles:
283115 'ACC2002: References That You Must Set When You Work with
Microsoft Access'
Or for Access 97:
175484 'References to Set When Working With Microsoft Access' for
the correct ones needed,
and
160870 'VBA Functions Break in Database with Missing References' for
how to reset a missing one.

For even more information, see
http://members.rogers.com/douglas.j.steele/AccessReferenceErrors.html

If that doesn't take care of it, copy and paste into a reply message
the exact FirstTime control source as well as the exact code you wrote
in the Format event.

I have no missing references. I have the following references:
Visual Basic for Applications
Microsoft Access 11.0
OLE Automation library
Microsoft ActiveX data Objects 2.0
Microsoft DAO 3.6 oBJECT lIBRARY
Microsoft Word 11.0
Is there a particular reference I need for this procedure?
Here is the info you requested. Not much to copy and paste on the
control source, unless I'm missing something.
Thanks.
Sammie

Text box name: FirstTime
Control source: =1
Running Sum: Over all

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If FirstTime = 1 Then
[UnitSelling].Format = "$ #,##0.00;-$ #,##0.00;$ 0.00"
[ExtendedPrice].Format = "$ #,##0.00;-$ #,##0.00;$ 0.00"
Else: [UnitSelling].Format = "#,##0.00;-#,##0.00;0.00"
[ExtendedPrice].Format = "#,##0.00;-#,##0.00;0.00"
End If

What happens if you open the references again and move the references
(using the up and down arrows) into this order:

Visual Basic for Applications
Microsoft Access 11.0
Microsoft DAO 3.6 Objext Library
Microsoft ActiveX data Objects 2.0
OLE Automation library
Microsoft Word 11.0

Also, if that doesn't help, uncheck the Word, OLE ActiveX , and the
DAO 3.6 libraries if you can.
Try the report again without them.

Then re-check the DAO 3.6. Try it again. Then the OLE. Try it. Then
the Word, Then if you are using it the ActiveX, check that. Try again.
By the way, why ActiveX 2.0? Try the highest version # on your
computer.

 

[Sammie]

On Fri, 04 Feb 2005 20:09:51 -0600, Sammie wrote:
** snipped **

Thanks. I follow you. I am getting the following error in the Detail
Format event:
"Compile error. Method or data member not found."
FYI - ".FORMAT" is highlighted in the code when the error occurrs.
Also, I'm not sure it's picking up the value of 1 in the "FirstTime"
control.
Thanks.
Sammie





Please check your references.

Open any module in Design view (or click Ctrl + G).
On the Tools menu, click References.
Click to clear the check box for the type library or object library
marked as "Missing:."

An alternative to removing the reference is to restore the referenced
file to the path specified in the References dialog box. If the
referenced file is in a new location, clear the "Missing:" reference
and create a new reference to the file in its new folder.

See Microsoft KnowledgeBase articles:
283115 'ACC2002: References That You Must Set When You Work with
Microsoft Access'
Or for Access 97:
175484 'References to Set When Working With Microsoft Access' for
the correct ones needed,
and
160870 'VBA Functions Break in Database with Missing References' for
how to reset a missing one.

For even more information, see
http://members.rogers.com/douglas.j.steele/AccessReferenceErrors.html

If that doesn't take care of it, copy and paste into a reply message
the exact FirstTime control source as well as the exact code you wrote
in the Format event.

I have no missing references. I have the following references:
Visual Basic for Applications
Microsoft Access 11.0
OLE Automation library
Microsoft ActiveX data Objects 2.0
Microsoft DAO 3.6 oBJECT lIBRARY
Microsoft Word 11.0
Is there a particular reference I need for this procedure?
Here is the info you requested. Not much to copy and paste on the
control source, unless I'm missing something.
Thanks.
Sammie

Text box name: FirstTime
Control source: =1
Running Sum: Over all

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If FirstTime = 1 Then
[UnitSelling].Format = "$ #,##0.00;-$ #,##0.00;$ 0.00"
[ExtendedPrice].Format = "$ #,##0.00;-$ #,##0.00;$ 0.00"
Else: [UnitSelling].Format = "#,##0.00;-#,##0.00;0.00"
[ExtendedPrice].Format = "#,##0.00;-#,##0.00;0.00"
End If

What happens if you open the references again and move the references
(using the up and down arrows) into this order:

Visual Basic for Applications
Microsoft Access 11.0
Microsoft DAO 3.6 Objext Library
Microsoft ActiveX data Objects 2.0
OLE Automation library
Microsoft Word 11.0

Also, if that doesn't help, uncheck the Word, OLE ActiveX , and the
DAO 3.6 libraries if you can.
Try the report again without them.

Then re-check the DAO 3.6. Try it again. Then the OLE. Try it. Then
the Word, Then if you are using it the ActiveX, check that. Try again.
By the way, why ActiveX 2.0? Try the highest version # on your
computer.

Sorry, Fred. Still no joy.
I tried everything you suggested - each reference one at a time, highest
ActiveX version. As I write the code for the report detail event:
[controlname].format - the only option I am prompted for is
[controlname].value. Format is not an option. Does that tell you anything?

Is Access broken, is there somewhere else we can look, or is there an
alternative approach to this problem besides the detail format procedure
in report detail?

My sincere thanks. This is an important issue for my customer.
Sammie

 

[fredg]

Sorry, Fred. Still no joy.
I tried everything you suggested - each reference one at a time, highest
ActiveX version. As I write the code for the report detail event:
[controlname].format - the only option I am prompted for is
[controlname].value. Format is not an option. Does that tell you anything?

The fact that Intellesense doesn't offer Format is because
Intellesense doesn't always work well. You can show this for yourself
by trying to enter the BackColor property after the dot. It won't show
up either, but the control does have a backcolor property.

I don't show Format either, but it is a property of the control and
the code does work.

Are the 2 controls placed in the Detail Section? If so, then the code
as originally sent to yo by me should work.
However, if you placed those controls in a different section, then you
should move the code to that section's Format event.

Is Access broken, is there somewhere else we can look, or is there an
alternative approach to this problem besides the detail format procedure
in report detail?

My sincere thanks. This is an important issue for my customer.
Sammie

More than what I have already written, I don't know where to go with
this any more.

 

[Sammie]

Sorry, Fred. Still no joy.
I tried everything you suggested - each reference one at a time, highest
ActiveX version. As I write the code for the report detail event:
[controlname].format - the only option I am prompted for is
[controlname].value. Format is not an option. Does that tell you anything?

The fact that Intellesense doesn't offer Format is because
Intellesense doesn't always work well. You can show this for yourself
by trying to enter the BackColor property after the dot. It won't show
up either, but the control does have a backcolor property.

I don't show Format either, but it is a property of the control and
the code does work.

Are the 2 controls placed in the Detail Section? If so, then the code
as originally sent to yo by me should work.
However, if you placed those controls in a different section, then you
should move the code to that section's Format event.

Is Access broken, is there somewhere else we can look, or is there an
alternative approach to this problem besides the detail format procedure
in report detail?

My sincere thanks. This is an important issue for my customer.
Sammie

More than what I have already written, I don't know where to go with
this any more.

Fred,
Thanks for all your help. I'm going to try to do some more research on
the references when I get some time. If it's not a reference issue, it
seems that there may be a bug in Access. Do you have any ideas of where
else I might go for help with this?
Thanks
Sammie