A couple of guys helped me create a function that takes the 8,000+ customers
in my customer table and using the "Invoiced This Year" field ($) creats
another table with five break points. I'm using this new table to create a
five star rating system. Based on the Invoiced This Year field in my customer
table (which is updated nightly) the function does a calculation on the
entire table and gives me a min - max for each break point 1 - 5 (see below.)

So now I have a table called "ScoresTable" and it looks like this in
datasheet.

TopX MaxBreak MinBreak
5 18790390.9 4594.24
4 4594.24 2223.6
3 2223.6 1082.98
2 1082.98 450
1 450 .12

My main customer table is called tblCompanies and the field in question is
called InvThisYr; it's a Number - Decimal field.

If the customer's InvThisYr value is $500 I want to compare it against the
ScoresTable and display the number 2; If a customer's InvThisYr amount is
$3000, I want to display 4 - and so on...

Any idea how I could do this comparison? I thought this part of the project
was going to be easier that it appears to be. And I don't want to ask the
guys that initially helped me becuase they've done a lot already.

Any help would be appreciated.

use something like this in a query.

Point the variable at your column name instead of a control on a form.

DMin("TopX", "tblTopX", "MaxBreak>=" & Me.txtDollars)

My frmCompanies form is bound directly to my tblCompanies table. I'm not
using a query and I'm pretty sure I'm doing that for a reason. I think when I
used a query I had trouble adding records... I don't know. Anyway, I copied
the form and added a query and tried to use your example and was unable to
make it work.

What about adding something like this to the Form_AfterUpdate event?
I just can't seem to make it work.

Depending on where the customer's invthisyr amount falls in the ScoresTable
I want to populate an unbound text box on my customer form with matching TopX
(1-5)

If Not IsNothing(Me.InvThisYr) Then
Me.TopX = DLookup("[TopX]", "ScoresTable", "([InvThisYr]) >= " &
[ScoresTable].[MinBreak] & " And ([InvThisYr]) >= " &
[ScoresTable].[MaxBreak])
End If

That would do it, wouldn't it?




"(E-Mail Removed)" wrote:

> use something like this in a query.
>
> Point the variable at your column name instead of a control on a form.
>
> DMin("TopX", "tblTopX", "MaxBreak>=" & Me.txtDollars)
>

On Aug 6, 1:31*pm, JK <jasonk at necoffeeco dot com> wrote:
> My frmCompanies form is bound directly to my tblCompanies table. I'm not
> using a query and I'm pretty sure I'm doing that for a reason. I think when I
> used a query I had trouble adding records... I don't know. Anyway, I copied
> the form and added a query and tried to use your example and was unable to
> make it work.
>
> What about adding something like this to the Form_AfterUpdate event?
> I just can't seem to make it work.
>
> Depending on where the customer's invthisyr amount falls in the ScoresTable
> I want to populate an unbound text box on my customer form with matching TopX
> (1-5)
>
> * * If Not IsNothing(Me.InvThisYr) Then
> * * * * Me.TopX = DLookup("[TopX]", "ScoresTable", "([InvThisYr]) >= " &
> [ScoresTable].[MinBreak] & " And ([InvThisYr]) >= " &
> [ScoresTable].[MaxBreak])
> * * End If
>
> That would do it, wouldn't it?
>
> "pietlin...@hotmail.com" wrote:
> > use something like this in a query.
>
> > Point the variable at your column name instead of a control on a form.
>
> > DMin("TopX", "tblTopX", "MaxBreak>=" & Me.txtDollars)

Try If Not IsNull(Me.InvThisYr) then...

IsNothing tests to see if an object variable is pointing at something.
(like a recordset)

I can't get it to work... I've spent all day on this... I'm ready to shoot
myself.
I'm not even sure I'm going at this correctly. I thought a DLookup would be
the easiest way to pull the TopX value.



"(E-Mail Removed)" wrote:

> On Aug 6, 1:31 pm, JK <jasonk at necoffeeco dot com> wrote:
> > My frmCompanies form is bound directly to my tblCompanies table. I'm not
> > using a query and I'm pretty sure I'm doing that for a reason. I think when I
> > used a query I had trouble adding records... I don't know. Anyway, I copied
> > the form and added a query and tried to use your example and was unable to
> > make it work.
> >
> > What about adding something like this to the Form_AfterUpdate event?
> > I just can't seem to make it work.
> >
> > Depending on where the customer's invthisyr amount falls in the ScoresTable
> > I want to populate an unbound text box on my customer form with matching TopX
> > (1-5)
> >
> > If Not IsNothing(Me.InvThisYr) Then
> > Me.TopX = DLookup("[TopX]", "ScoresTable", "([InvThisYr]) >= " &
> > [ScoresTable].[MinBreak] & " And ([InvThisYr]) >= " &
> > [ScoresTable].[MaxBreak])
> > End If
> >
> > That would do it, wouldn't it?
> >
> > "pietlin...@hotmail.com" wrote:
> > > use something like this in a query.
> >
> > > Point the variable at your column name instead of a control on a form.
> >
> > > DMin("TopX", "tblTopX", "MaxBreak>=" & Me.txtDollars)
>
> Try If Not IsNull(Me.InvThisYr) then...
>
> IsNothing tests to see if an object variable is pointing at something.
> (like a recordset)
>

If your DLookup is coded exactly as you have it shown in your example it
won't work - you have '>=' specified for both the min and the max. Other than
that your DLookup looks like it should work.

"JK" wrote:

> I can't get it to work... I've spent all day on this... I'm ready to shoot
> myself.
> I'm not even sure I'm going at this correctly. I thought a DLookup would be
> the easiest way to pull the TopX value.
>
>
>
> "(E-Mail Removed)" wrote:
>
> > On Aug 6, 1:31 pm, JK <jasonk at necoffeeco dot com> wrote:
> > > My frmCompanies form is bound directly to my tblCompanies table. I'm not
> > > using a query and I'm pretty sure I'm doing that for a reason. I think when I
> > > used a query I had trouble adding records... I don't know. Anyway, I copied
> > > the form and added a query and tried to use your example and was unable to
> > > make it work.
> > >
> > > What about adding something like this to the Form_AfterUpdate event?
> > > I just can't seem to make it work.
> > >
> > > Depending on where the customer's invthisyr amount falls in the ScoresTable
> > > I want to populate an unbound text box on my customer form with matching TopX
> > > (1-5)
> > >
> > > If Not IsNothing(Me.InvThisYr) Then
> > > Me.TopX = DLookup("[TopX]", "ScoresTable", "([InvThisYr]) >= " &
> > > [ScoresTable].[MinBreak] & " And ([InvThisYr]) >= " &
> > > [ScoresTable].[MaxBreak])
> > > End If
> > >
> > > That would do it, wouldn't it?
> > >
> > > "pietlin...@hotmail.com" wrote:
> > > > use something like this in a query.
> > >
> > > > Point the variable at your column name instead of a control on a form.
> > >
> > > > DMin("TopX", "tblTopX", "MaxBreak>=" & Me.txtDollars)
> >
> > Try If Not IsNull(Me.InvThisYr) then...
> >
> > IsNothing tests to see if an object variable is pointing at something.
> > (like a recordset)
> >

Yeah, I changed that and it still doesn't work. I've hit a brick wall. I've
even tried to hard code the min & max breaks into the before update event of
my company form and then I tried to display the stars accordingly and I can't
even make this work.

I'm getting strange results. The stars are being displayed but they don't
match the min / max breaks...

My goal is to make this work based on the table, hard coding the numbers
into an event will not work in the long run, but I can't believe this won't
even work...

If IsNull(Me.InvThisYr) Then
Me.Star1.Visible = False
Me.Star2.Visible = False
Me.Star3.Visible = False
Me.Star4.Visible = False
Me.Star5.Visible = False
Else
If Me.InvThisYr >= 0.12 Then
Me.Star1.Visible = True
Else
Me.Star1.Visible = False
End If
If Me.InvThisYr >= 450 Then
Me.Star2.Visible = True
Else
Me.Star2.Visible = False
End If
If Me.InvThisYr >= 1082.98 Then
Me.Star3.Visible = True
Else
Me.Star3.Visible = False
End If
If Me.InvThisYr >= 2223.6 Then
Me.Star4.Visible = True
Else
Me.Star4.Visible = False
End If
If Me.InvThisYr >= 4594.24 Then
Me.Star5.Visible = True
Else
Me.Star5.Visible = False
End If
End If


"Jim Burke in Novi" wrote:

> If your DLookup is coded exactly as you have it shown in your example it
> won't work - you have '>=' specified for both the min and the max. Other than
> that your DLookup looks like it should work.
>
> "JK" wrote:
>
> > I can't get it to work... I've spent all day on this... I'm ready to shoot
> > myself.
> > I'm not even sure I'm going at this correctly. I thought a DLookup would be
> > the easiest way to pull the TopX value.
> >
> >
> >
> > "(E-Mail Removed)" wrote:
> >
> > > On Aug 6, 1:31 pm, JK <jasonk at necoffeeco dot com> wrote:
> > > > My frmCompanies form is bound directly to my tblCompanies table. I'm not
> > > > using a query and I'm pretty sure I'm doing that for a reason. I think when I
> > > > used a query I had trouble adding records... I don't know. Anyway, I copied
> > > > the form and added a query and tried to use your example and was unable to
> > > > make it work.
> > > >
> > > > What about adding something like this to the Form_AfterUpdate event?
> > > > I just can't seem to make it work.
> > > >
> > > > Depending on where the customer's invthisyr amount falls in the ScoresTable
> > > > I want to populate an unbound text box on my customer form with matching TopX
> > > > (1-5)
> > > >
> > > > If Not IsNothing(Me.InvThisYr) Then
> > > > Me.TopX = DLookup("[TopX]", "ScoresTable", "([InvThisYr]) >= " &
> > > > [ScoresTable].[MinBreak] & " And ([InvThisYr]) >= " &
> > > > [ScoresTable].[MaxBreak])
> > > > End If
> > > >
> > > > That would do it, wouldn't it?
> > > >
> > > > "pietlin...@hotmail.com" wrote:
> > > > > use something like this in a query.
> > > >
> > > > > Point the variable at your column name instead of a control on a form.
> > > >
> > > > > DMin("TopX", "tblTopX", "MaxBreak>=" & Me.txtDollars)
> > >
> > > Try If Not IsNull(Me.InvThisYr) then...
> > >
> > > IsNothing tests to see if an object variable is pointing at something.
> > > (like a recordset)
> > >

Jk,
I think it is a matter of where you are putting the DLookup and the correct
syntax.
You should have a text box to display the rank and the control source should
have the Dmin function in it preceeded with an =

=DMin("TopX", "tblTopX", Me.txtDollars & " >= MinBreak")


--
Dave Hargis, Microsoft Access MVP


"JK" wrote:

> I can't get it to work... I've spent all day on this... I'm ready to shoot
> myself.
> I'm not even sure I'm going at this correctly. I thought a DLookup would be
> the easiest way to pull the TopX value.
>
>
>
> "(E-Mail Removed)" wrote:
>
> > On Aug 6, 1:31 pm, JK <jasonk at necoffeeco dot com> wrote:
> > > My frmCompanies form is bound directly to my tblCompanies table. I'm not
> > > using a query and I'm pretty sure I'm doing that for a reason. I think when I
> > > used a query I had trouble adding records... I don't know. Anyway, I copied
> > > the form and added a query and tried to use your example and was unable to
> > > make it work.
> > >
> > > What about adding something like this to the Form_AfterUpdate event?
> > > I just can't seem to make it work.
> > >
> > > Depending on where the customer's invthisyr amount falls in the ScoresTable
> > > I want to populate an unbound text box on my customer form with matching TopX
> > > (1-5)
> > >
> > > If Not IsNothing(Me.InvThisYr) Then
> > > Me.TopX = DLookup("[TopX]", "ScoresTable", "([InvThisYr]) >= " &
> > > [ScoresTable].[MinBreak] & " And ([InvThisYr]) >= " &
> > > [ScoresTable].[MaxBreak])
> > > End If
> > >
> > > That would do it, wouldn't it?
> > >
> > > "pietlin...@hotmail.com" wrote:
> > > > use something like this in a query.
> > >
> > > > Point the variable at your column name instead of a control on a form.
> > >
> > > > DMin("TopX", "tblTopX", "MaxBreak>=" & Me.txtDollars)
> >
> > Try If Not IsNull(Me.InvThisYr) then...
> >
> > IsNothing tests to see if an object variable is pointing at something.
> > (like a recordset)
> >

Well, now it's at least doing something.
I'm getting #Name? in the score field instead of the score.

Field1: InvThisYr (bound)
Field2: Score (unbound)
Field2 Control Source: =DMin("TopX","ScoresTable",Me.InvThisYr & " >=
MinBreak")

I made both fields the same data type and that didn't seem to help.



"Klatuu" wrote:

> Jk,
> I think it is a matter of where you are putting the DLookup and the correct
> syntax.
> You should have a text box to display the rank and the control source should
> have the Dmin function in it preceeded with an =
>
> =DMin("TopX", "tblTopX", Me.txtDollars & " >= MinBreak")
>
>
> --
> Dave Hargis, Microsoft Access MVP
>
>
> "JK" wrote:
>
> > I can't get it to work... I've spent all day on this... I'm ready to shoot
> > myself.
> > I'm not even sure I'm going at this correctly. I thought a DLookup would be
> > the easiest way to pull the TopX value.
> >
> >
> >
> > "(E-Mail Removed)" wrote:
> >
> > > On Aug 6, 1:31 pm, JK <jasonk at necoffeeco dot com> wrote:
> > > > My frmCompanies form is bound directly to my tblCompanies table. I'm not
> > > > using a query and I'm pretty sure I'm doing that for a reason. I think when I
> > > > used a query I had trouble adding records... I don't know. Anyway, I copied
> > > > the form and added a query and tried to use your example and was unable to
> > > > make it work.
> > > >
> > > > What about adding something like this to the Form_AfterUpdate event?
> > > > I just can't seem to make it work.
> > > >
> > > > Depending on where the customer's invthisyr amount falls in the ScoresTable
> > > > I want to populate an unbound text box on my customer form with matching TopX
> > > > (1-5)
> > > >
> > > > If Not IsNothing(Me.InvThisYr) Then
> > > > Me.TopX = DLookup("[TopX]", "ScoresTable", "([InvThisYr]) >= " &
> > > > [ScoresTable].[MinBreak] & " And ([InvThisYr]) >= " &
> > > > [ScoresTable].[MaxBreak])
> > > > End If
> > > >
> > > > That would do it, wouldn't it?
> > > >
> > > > "pietlin...@hotmail.com" wrote:
> > > > > use something like this in a query.
> > > >
> > > > > Point the variable at your column name instead of a control on a form.
> > > >
> > > > > DMin("TopX", "tblTopX", "MaxBreak>=" & Me.txtDollars)
> > >
> > > Try If Not IsNull(Me.InvThisYr) then...
> > >
> > > IsNothing tests to see if an object variable is pointing at something.
> > > (like a recordset)
> > >

My mistake, take out the me. In control sources, it doesn't work.

=DMin("TopX","ScoresTable", InvThisYr & " >= MinBreak")
--
Dave Hargis, Microsoft Access MVP


"JK" wrote:

> Well, now it's at least doing something.
> I'm getting #Name? in the score field instead of the score.
>
> Field1: InvThisYr (bound)
> Field2: Score (unbound)
> Field2 Control Source: =DMin("TopX","ScoresTable",Me.InvThisYr & " >=
> MinBreak")
>
> I made both fields the same data type and that didn't seem to help.
>
>
>
> "Klatuu" wrote:
>
> > Jk,
> > I think it is a matter of where you are putting the DLookup and the correct
> > syntax.
> > You should have a text box to display the rank and the control source should
> > have the Dmin function in it preceeded with an =
> >
> > =DMin("TopX", "tblTopX", Me.txtDollars & " >= MinBreak")
> >
> >
> > --
> > Dave Hargis, Microsoft Access MVP
> >
> >
> > "JK" wrote:
> >
> > > I can't get it to work... I've spent all day on this... I'm ready to shoot
> > > myself.
> > > I'm not even sure I'm going at this correctly. I thought a DLookup would be
> > > the easiest way to pull the TopX value.
> > >
> > >
> > >
> > > "(E-Mail Removed)" wrote:
> > >
> > > > On Aug 6, 1:31 pm, JK <jasonk at necoffeeco dot com> wrote:
> > > > > My frmCompanies form is bound directly to my tblCompanies table. I'm not
> > > > > using a query and I'm pretty sure I'm doing that for a reason. I think when I
> > > > > used a query I had trouble adding records... I don't know. Anyway, I copied
> > > > > the form and added a query and tried to use your example and was unable to
> > > > > make it work.
> > > > >
> > > > > What about adding something like this to the Form_AfterUpdate event?
> > > > > I just can't seem to make it work.
> > > > >
> > > > > Depending on where the customer's invthisyr amount falls in the ScoresTable
> > > > > I want to populate an unbound text box on my customer form with matching TopX
> > > > > (1-5)
> > > > >
> > > > > If Not IsNothing(Me.InvThisYr) Then
> > > > > Me.TopX = DLookup("[TopX]", "ScoresTable", "([InvThisYr]) >= " &
> > > > > [ScoresTable].[MinBreak] & " And ([InvThisYr]) >= " &
> > > > > [ScoresTable].[MaxBreak])
> > > > > End If
> > > > >
> > > > > That would do it, wouldn't it?
> > > > >
> > > > > "pietlin...@hotmail.com" wrote:
> > > > > > use something like this in a query.
> > > > >
> > > > > > Point the variable at your column name instead of a control on a form.
> > > > >
> > > > > > DMin("TopX", "tblTopX", "MaxBreak>=" & Me.txtDollars)
> > > >
> > > > Try If Not IsNull(Me.InvThisYr) then...
> > > >
> > > > IsNothing tests to see if an object variable is pointing at something.
> > > > (like a recordset)
> > > >