Code Code Code...

[Allilue]

Maybe someone can help me with this...

i have some code that worked before, but i changed some criteria and now i get an error message. the previous code was very similar to this..and worked! i was hoping that maybe another pair of eyes can take a look at this and see if there is something wrong....

Private Sub Form_Open(Cancel As Integer)

' assign myCriteria
myCriteria = "((Abs([YTDVarvsFcst]) >= "
myCriteria = myCriteria & 1000
myCriteria = myCriteria & " And "
myCriteria = myCriteria & "Abs([myYTDPctvsFcst]) >= "
myCriteria = myCriteria & 0.05
myCriteria = myCriteria & &quot"
myCriteria = myCriteria & " Or "
myCriteria = myCriteria & "(Abs([PerVarvsFcst]) >= "
myCriteria = myCriteria & 1000
myCriteria = myCriteria & " And "
myCriteria = myCriteria & "Abs([myPerPctvsFcst]) >= "
myCriteria = myCriteria & 0.05
myCriteria = myCriteria & &quot"
myCriteria = myCriteria & " Or "
myCriteria = myCriteria & "([AccountDes] = "
myCriteria = myCriteria & "Other Expense"
myCriteria = myCriteria & " And "
myCriteria = myCriteria & "Abs([PerVarvsFcst]) >= "
myCriteria = myCriteria & 1000
myCriteria = myCriteria & " And "
myCriteria = myCriteria & "Abs([YTDVarvsFcst]) >= "
myCriteria = myCriteria & 1000
myCriteria = myCriteria & &quot)"

End Sub

 

[PaulF]

Could it be this line do?

myCriteria = myCriteria & "Other Expense"

is this suppose to be [Other Expense]
or OtherExpense


PaulF

 

[Allilue]

But "Other Expense" is not a field. It is text within a field. Is there another way to state that?

 

[TheInsider]

Ouch! First thing, don't define your criteria string like that... you'll scare programmers off =).

The following is easier to read:

myCriteria = "((Abs([YTDVarvsFcst]) >= 1000 And Abs([myYTDPctvsFcst]) >= 0.05) Or (Abs([PerVarvsFcst]) >= 1000 And Abs([myPerPctvsFcst]) >= 0.05) Or ([AccountDes] = 'Other Expense' And Abs([PerVarvsFcst]) >= 1000 And Abs([YTDVarvsFcst]) >= 1000))"

I placed '' around "Other Expense". You have so many AND/OR conditions that I can't tell at a glance what you are doing. You may need more brackets for this to evaluate properly.

Rob Marriott
rob@career-connections.net

 

[PaulF]

if it is text in a field shouldn't it be enclosed in quotes... so it should be

myCriteria = myCriteria & "'Other Expense'"

or doublequote - singlequote - Other Expense - singlequote - doublequote

PaulF

 

[Allilue]

let me try that. something tells me that there is something else also causing the error though... i'll figure it out and let you know if there's anything else.

thanks!

 

[MichaelRed]

It might help if you posted what the error is and marked what part of the code you changed. This is a lot to just eyeball (at least for my tired old eyes).



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[THoey]

I don't know about anyone else, but when I start looking at a complexe string like this, I format into groups like:

myCriteria = 
  "(
      (
       Abs([YTDVarvsFcst]) >= 1000 And Abs([myYTDPctvsFcst]) >= 0.05
      ) Or 
      (
       Abs([PerVarvsFcst]) >= 1000 And Abs([myPerPctvsFcst]) >= 0.05
      ) Or 
      (
       [AccountDes] = 'Other Expense' And 
       Abs([PerVarvsFcst]) >= 1000 And 
       Abs([YTDVarvsFcst]) >= 1000
      )
   )"

As far as I can see, I think PaulF is right. I don't see anything else.

As Michael said, please post the erro and maybe it will make more sense...

Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?

 

[Allilue]

Thank you...

I made the change to the "Other Expense" line and there was just one other problem. The formatting for the percentage (0.05) wasn't working, so I had to change the format to Standard and then add a % sign as a text box on my form. There was probably another way to solve the format problem, but this one works fine.

Thanks again!