"Type mismatch" in Format()

  • Thread starter Thread starter John Milward
  • Start date Start date
J

John Milward

Hi

I am trying to set a number format according to the contents of each line of
detail in a report. This is to set the number of decimal places printed on
the report..
I have code in the Detail 'On Print' event that takes the numbers and
attempts to format them.
I keep getting a "Type Mismatch" error at the format function and cannot see
why.

I have eventually by-passed all my data and still get the error with this
code;--

Dim MyString as string
MyString = Format(334.9, "###0.00")

Since this is pasted straight from access help I can't believe it doesn't
work.
I must be doing somethibg totaly stupid!

Any ideas would be appreciated

TIA

John
 
Have you tried choosing Fixed as the text box format, and leaving out the
code?

Where does the 334.9 come from? What is the point of defining a string if
you don't use it somewhere? What do you mean by "I have eventually
by-passed all my data"?
 
Bruce

Sorry, perhaps not very well explained.

In the finished job the number will be calculated from field values.
Another field will define the format for printing.

The actual code line is
Me.txtError.Value = Format(Round(Me.txtResult - Me.txtTest,
txtDecPlaces), txtFormat)
Where;- txtResult, txtTest, txtDecPlaces are numbers from the data
and txtFormat contains a format string

In an effort to find out where the error message is being generated I have
got to this as a test!
MyString = Format(334.9, "###0.00")
This throws up the same error message as my line of code.

I was trying to simplify the problem as far as possible.

Any ideas?

John
 
If a field is going to define the format for printing you will need to
define that field as a string:
Dim strFormat as String
strFormat = txtFormat
Me.YourTextBox = Format([NumberField],strFormat)

I think that would work, but I can't see the point of making yourself jump
through so many hoops. For one thing, you would need to define the format
for every record.
Same thing goes for txtDecPlaces. Are you planning to define the format for
each record? Is that why you have a txtFormat field and a txtDecPlaces
field? Or are those something other than fields?

You don't need .Value after txtError.

If you are trying to subtract one number from another, you could put the
result into an unbound text box named txtError. In the Detail section Print
event:

Me.txtError = Me.txtResult - Me.txtTest

Or you could just put the following as the Control Source of an unbound text
box:

= [txtResult] - [txtTest]

Or you could create a calculated field in a query. In any case, select
Fixed as the format for the text box in order to display the result to two
decimal places. The last few suggestions assume that txtResult and txtTest
are number fields.

I have added your test code: MyString = Format(334.9, "###0.00") to a
report's Print event, but it does not generate an error. Is it part of
other code?

To summarize, my suggestion is to format the text box in which the result is
to appear. If you need to define the format for every record (if there are
multiple ways in which you want to format the data) you will need to explain
further.

If you need further assistance, don't assume that anybody will know anything
about txtResult or txtWhatever. Those are names you gave to something, but
you need to say what.
 
Hi Bruce

Thanks for the input.
Yes. The whole point is that the eventual aim is to be able to define the
format for each record. (The underlying query does all this and looks
right.)
I have no choice in this, as I am attempting to comply with a standard.
And anyway what would life be without a challenge!

Most of what you say I am aware of, and with a clearer mind after several
glasses of red wine and leaving the beast alone for a day I tried just
putting those two lines of code in a
new report.

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim MyString as string
MyString = Format(334.9, "###0.00")
End Sub

They work as one would expect. (Phew I am not going mad!)

Same two lines in my report and I get the 'Type Mismatch' error
I seem to have a report that is corrupted in soime way.
(I recall having a similar situation before and only solved it by starting
again)

Thanks again

John


BruceM said:
If a field is going to define the format for printing you will need to
define that field as a string:
Dim strFormat as String
strFormat = txtFormat
Me.YourTextBox = Format([NumberField],strFormat)

I think that would work, but I can't see the point of making yourself jump
through so many hoops. For one thing, you would need to define the format
for every record.
Same thing goes for txtDecPlaces. Are you planning to define the format for
each record? Is that why you have a txtFormat field and a txtDecPlaces
field? Or are those something other than fields?

You don't need .Value after txtError.

If you are trying to subtract one number from another, you could put the
result into an unbound text box named txtError. In the Detail section Print
event:

Me.txtError = Me.txtResult - Me.txtTest

Or you could just put the following as the Control Source of an unbound text
box:

= [txtResult] - [txtTest]

Or you could create a calculated field in a query. In any case, select
Fixed as the format for the text box in order to display the result to two
decimal places. The last few suggestions assume that txtResult and txtTest
are number fields.

I have added your test code: MyString = Format(334.9, "###0.00") to a
report's Print event, but it does not generate an error. Is it part of
other code?

To summarize, my suggestion is to format the text box in which the result is
to appear. If you need to define the format for every record (if there are
multiple ways in which you want to format the data) you will need to explain
further.

If you need further assistance, don't assume that anybody will know anything
about txtResult or txtWhatever. Those are names you gave to something, but
you need to say what.

John Milward said:
Bruce

Sorry, perhaps not very well explained.

In the finished job the number will be calculated from field values.
Another field will define the format for printing.

The actual code line is
Me.txtError.Value = Format(Round(Me.txtResult - Me.txtTest,
txtDecPlaces), txtFormat)
Where;- txtResult, txtTest, txtDecPlaces are numbers from the data
and txtFormat contains a format string

In an effort to find out where the error message is being generated I have
got to this as a test!
MyString = Format(334.9, "###0.00")
This throws up the same error message as my line of code.

I was trying to simplify the problem as far as possible.

Any ideas?

John



printed
on
Click to expand...
Click to expand...
 
A final salutary note

I had a field called 'Format'
Not a good idea!
I shall hang my head in shame.

John


John Milward said:
Hi Bruce

Thanks for the input.
Yes. The whole point is that the eventual aim is to be able to define the
format for each record. (The underlying query does all this and looks
right.)
I have no choice in this, as I am attempting to comply with a standard.
And anyway what would life be without a challenge!

Most of what you say I am aware of, and with a clearer mind after several
glasses of red wine and leaving the beast alone for a day I tried just
putting those two lines of code in a
new report.

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim MyString as string
MyString = Format(334.9, "###0.00")
End Sub

They work as one would expect. (Phew I am not going mad!)

Same two lines in my report and I get the 'Type Mismatch' error
I seem to have a report that is corrupted in soime way.
(I recall having a similar situation before and only solved it by starting
again)

Thanks again

John


BruceM said:
If a field is going to define the format for printing you will need to
define that field as a string:
Dim strFormat as String
strFormat = txtFormat
Me.YourTextBox = Format([NumberField],strFormat)

I think that would work, but I can't see the point of making yourself jump
through so many hoops. For one thing, you would need to define the format
for every record.
Same thing goes for txtDecPlaces. Are you planning to define the format for
each record? Is that why you have a txtFormat field and a txtDecPlaces
field? Or are those something other than fields?

You don't need .Value after txtError.

If you are trying to subtract one number from another, you could put the
result into an unbound text box named txtError. In the Detail section Print
event:

Me.txtError = Me.txtResult - Me.txtTest

Or you could just put the following as the Control Source of an unbound text
box:

= [txtResult] - [txtTest]

Or you could create a calculated field in a query. In any case, select
Fixed as the format for the text box in order to display the result to two
decimal places. The last few suggestions assume that txtResult and txtTest
are number fields.

I have added your test code: MyString = Format(334.9, "###0.00") to a
report's Print event, but it does not generate an error. Is it part of
other code?

To summarize, my suggestion is to format the text box in which the
Click to expand...
result
is
to appear. If you need to define the format for every record (if there are
multiple ways in which you want to format the data) you will need to explain
further.

If you need further assistance, don't assume that anybody will know anything
about txtResult or txtWhatever. Those are names you gave to something, but
you need to say what.
Click to expand...
Click to expand...
 
Back
Top