Comparing a field in one table against multiple fields in another

[JK]

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.

 

[pietlinden]

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)

 

[JK]

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?

 

[pietlinden]

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?

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 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.

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?

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)

 

[Jim Burke in Novi]

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.

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.

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?

:
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]

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

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.

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.

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?

:
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)

 

[Klatuu]

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

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.

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?

:
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]

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.

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

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.

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?

:
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)

 

[Klatuu]

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

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

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.

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

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.



:

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?

:
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)

 

[John Spencer]

In a query, I would try the following

SELECT Customers.CustomerName, Customers.InvThisYr, TopX
FROM Customers LEFT JOIN ScoresTable
ON Customers.InvThisYr <= ScoresTable.MaxBreak and
Customers.InvThisYr > ScoresTable.MinBreak


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[JK]

It's working!!! I can't thank you enough... I had to create yet another text
box on the company form becuase sometimes #error appeared in the score field.
I pretty sure this is caused by a null value or "" in the Invoiced This Year
field. So I added another text box with its control source set to
=IIf(IsError([Score1]),0,([Score1])) and it seems to be working perfectly.

Thanks again, I really appreicate all of the advice I've received trying to
accomplish this difficult task (maybe not difficult to you guys - but
difficut to me.)

I think this is a great tool for any database that stores YTD sales. By
displaying a 5 star rating system on the main company form the admin pulling
the account and quickly see how valuable the customer is based on the rating
system.

Anyway - thx again!!!!!

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

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

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.

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


:

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.



:

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?

:
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)

 

[Klatuu]

Rather than =IIf(IsError([Score1]),0,([Score1]))
I would use Nz([Score1],0)
--
Dave Hargis, Microsoft Access MVP

It's working!!! I can't thank you enough... I had to create yet another text
box on the company form becuase sometimes #error appeared in the score field.
I pretty sure this is caused by a null value or "" in the Invoiced This Year
field. So I added another text box with its control source set to
=IIf(IsError([Score1]),0,([Score1])) and it seems to be working perfectly.

Thanks again, I really appreicate all of the advice I've received trying to
accomplish this difficult task (maybe not difficult to you guys - but
difficut to me.)

I think this is a great tool for any database that stores YTD sales. By
displaying a 5 star rating system on the main company form the admin pulling
the account and quickly see how valuable the customer is based on the rating
system.

Anyway - thx again!!!!!

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

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

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.



:

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


:

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.



:

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?

:
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]

That option, Nz([Score1],0) doesn't work like the IsError option. When I get
to a record where the Score1 field is equal to #Error I get a Run-time error
'9': Subscript out of range. This doesn't happen with the IsError option.

Rather than =IIf(IsError([Score1]),0,([Score1]))
I would use Nz([Score1],0)
--
Dave Hargis, Microsoft Access MVP

It's working!!! I can't thank you enough... I had to create yet another text
box on the company form becuase sometimes #error appeared in the score field.
I pretty sure this is caused by a null value or "" in the Invoiced This Year
field. So I added another text box with its control source set to
=IIf(IsError([Score1]),0,([Score1])) and it seems to be working perfectly.

Thanks again, I really appreicate all of the advice I've received trying to
accomplish this difficult task (maybe not difficult to you guys - but
difficut to me.)

I think this is a great tool for any database that stores YTD sales. By
displaying a 5 star rating system on the main company form the admin pulling
the account and quickly see how valuable the customer is based on the rating
system.

Anyway - thx again!!!!!

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

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


:

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.



:

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


:

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.



:

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?

:
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)

 

[Klatuu]

Okay, that would be the way to go then. Glad you got it working.
--
Dave Hargis, Microsoft Access MVP

That option, Nz([Score1],0) doesn't work like the IsError option. When I get
to a record where the Score1 field is equal to #Error I get a Run-time error
'9': Subscript out of range. This doesn't happen with the IsError option.

Rather than =IIf(IsError([Score1]),0,([Score1]))
I would use Nz([Score1],0)
--
Dave Hargis, Microsoft Access MVP

It's working!!! I can't thank you enough... I had to create yet another text
box on the company form becuase sometimes #error appeared in the score field.
I pretty sure this is caused by a null value or "" in the Invoiced This Year
field. So I added another text box with its control source set to
=IIf(IsError([Score1]),0,([Score1])) and it seems to be working perfectly.

Thanks again, I really appreicate all of the advice I've received trying to
accomplish this difficult task (maybe not difficult to you guys - but
difficut to me.)

I think this is a great tool for any database that stores YTD sales. By
displaying a 5 star rating system on the main company form the admin pulling
the account and quickly see how valuable the customer is based on the rating
system.

Anyway - thx again!!!!!

:

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

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


:

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.



:

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


:

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.



:

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?

:
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)