I get the above error when compiling my code in MS Access 2003. My code is:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If [Anger] = -1 Then Me.Text01 = "Anger"
If [Selfishness] = -1 And [Anger] = 0 Then Me.Text01 = "Selfishness"
If [Selfishness] = -1 And [Anger] = -1 Then Me.Text02 = "Selfishness"
If [Laziness] = -1 And [Anger] = 0 And [Selfishness] = 0 Then Me.Text01 = "Laziness"
If [Laziness] = -1 And [Anger] = -1 And [Selfishness] = 0 Then Me.Text02 = "Laziness"
If [Laziness] = -1 And [Anger] = -1 And [Selfishness] = -1 Then Me.Text03 = "Laziness"
If [Hatred] = -1 And [Anger] = 0 And [Selfishness] = 0 And [Laziness] = 0 Then Me.Text01 = "Hatred"
If [Hatred] = -1 And [Anger] = -1 And [Selfishness] = 0 And [Laziness] = 0 Then Me.Text02 = "Hatred"
If [Hatred] = -1 And [Anger] = -1 And [Selfishness] = 0 And [Laziness] = -1 Then Me.Text03 = "Hatred"
If [Hatred] = -1 And [Anger] = -1 And [Selfishness] = -1 And [Laziness] = -1 Then Me.Text04 = "Hatred"
If [Horseplay] = -1 And [Anger] = 0 And [Selfishness] = 0 And [Laziness] = 0 And [Hatred] = 0 Then Me.Text01 = "Horseplay"
If [Horseplay] = -1 And [Anger] = -1 And [Selfishness] = 0 And [Laziness] = 0 And [Hatred] = 0 Then Me.Text02 = "Horseplay"
If [Horseplay] = -1 And [Anger] = -1 And [Selfishness] = 0 And [Laziness] = -1 And [Hatred] = 0 Then Me.Text03 = "Horseplay"
If [Horseplay] = -1 And [Anger] = -1 And [Selfishness] = -1 And [Laziness] = -1 And [Hatred] = 0 Then Me.Text04 = "Horseplay"
If [Horseplay] = -1 And [Anger] = -1 And [Selfishness] = -1 And [Laziness] = -1 And [Hatred] = -1 Then Me.Text05 = "Horseplay"

and more code. I get an error highlighted over [Hatred} on the last line shown; yet, you can see that this was successfully used several times previously. I've read something about a 'bug' in Access2003 which causes something like this and the answer was to put in quote on the line where the error is shown. I don't understand this.

I'm not very familiar with Access and even less so with VB.

Can anyone help?

Thanks Ultimo01

I would be inclined to do this instead:

If [Anger] = True Then Me.Text01 = "Anger"
If [Selfishness] = True And [Anger] = False Then Me.Text01 = "Selfishness"
If [Selfishness] = True And [Anger] = True Then Me.Text02 = "Selfishness"
If [Laziness] = True And [Anger] = False And [Selfishness] = False Then Me.Text01 = "Laziness"
If [Laziness] = True And [Anger] = True And [Selfishness] = False Then Me.Text02 = "Laziness"
If [Laziness] = True And [Anger] = True And [Selfishness] = True Then Me.Text03 = "Laziness"
...

You may also speed up this process by doing this:

Dim bAnger As Boolean
Dim bSelfish As Boolean
Dim bLazy As Boolean
...

bAnger = Me.Anger
bSelfish = Me.Selfishness
bLazy = Me.Laziness
...

Then check these values. That way the code doesn't have to keep going back and checking the fields but can use the booleans in memory. And that will definitely prevent the problem you are currently having.

As I think of it, I would also do this...

Dim sT1 As String
Dim sT2 As String
...
Then

If bAnger = True Then sT1 = "Anger"
If bSelfish = True And bAnger = False Then sT1 = "Selfishness"
If bSelfish = True And bAnger = True Then sT2 = "Selfishness"
...

Me.Text01 = sT1
Me.Text02 = sT2
...

This will also prevent the report from placing and replacing values on the form instead of in the variables in memory, which will also gain speed.


Did that help?

__________________
mmcdonal

Look it up at: http://wrox.books24x7.com

Sorry, it is placing values on the report, not the form.

__________________
mmcdonal

Look it up at: http://wrox.books24x7.com

Thank you very much for the input mmcdonal.

Unfortunately you are way ahead of me when it comes to VB.

Isn't:
If [Anger] = True Then Me.Text01 = "Anger"
the same as my
If [Anger] = -1 Then Me.Text01 = "Anger" ?

Also I don't know what
Dim bAnger As Boolean
bAnger = Me.Anger
bAnger = Me.Anger
etc.

I have 26 "Character Concerns" (Anger, Selfishness, Laziness, Hatred etc) to check. I want to put out a report showing what concerns are associated with specific people but I don't want to show blanks where the concern isn't checked. That's what this is all about.

Anyway, thank you very much for your input.

Access doesn't like to take values at runtime. That's what I think the nature of your original problem is. The way I suggest below is the preferred method of writing this kind of code in Access. This is also the fastest way. If you wrote a report with a few hundred records, the processing could take some time.

So my final suggestion would be to declare variables (boolean and string), then take the values needed in the booleans (True / False) from your controls, then compare the booleans, then place the values in the strings variables, then post the results in the controls.

So if there were two controls and a textbox output:

Dim bAnger As Boolean 'boolean variable's initial value is false
Dim bSelfish As Boolean
Dim sT1 As String 'string variables initial value is ""

bAnger = Me.Anger
bSelfish = Me.Selfishness

If bAnger = True and bSelfish = False Then sT1 = "Anger"
If bAnger = False and bSelfish = True Then sT1 = "Selfishness"
If sT1 = "" Then sT1 = "Undetermined"

Me.Text01 = sT1

Of course you supply your own analysis. Don't forget to add the last check there if you don't want empty controls. With two booleans I had 4 possible states (TT, TF, FT, FF) but you will have many more and can't check them all.

Did that help?

__________________
mmcdonal

Look it up at: http://wrox.books24x7.com