Hi everyone

It's been at least 2 years since I have done any Access work as I am doing
more in Excel than anything nowadays, that said..!!

I have an old DB I built for a friend a long, long, long time ago which
needs some updating and I would like to do a DMAX() lookup from a table so I
can display the highest value within a matching group on said form.

=DMax([txtBreedID],[tblBreeds],[txtcategory]=[Forms]![frmBreeds]![txtcategory])

The syntax for this is wrong as it displays NAME#

Appreciate the assist.

TIA
Mick

"Vacuum Sealed" <noodnutt (AT) gmail (DOT) com> wrote

[Forms]![frmBreeds]![txtcategory] & "'")

Note the single quote around [Forms]![frmBreeds]![txtcategory]

Hi Rin

Appreciate the assist.

Although your equation evaluated to #Error


Thx again
Mick

Vacuum Sealed wrote:
determine what the string concatenation in Ron's solution is evaluating to.
Change the control source to:
="[txtcategory]='" & [Forms]![frmBreeds]![txtcategory] & "'"

Run the form. Does it display what you would expect it to contain?

Bob

Your suggestion netted me the repsonse of " txtCategory='6' "

so in retrospect, it gave me the Category number.

I may have been a little vague in my explanation, what I am actually looking
for is when the user selects a Category it will display the last number used
in the BreedID Field for that Category.

eg

Category # 1 = Dogs

Breed # 1077 = Shih Tzu

If the user then wishes to enter another breed he/she will know that the
next available Breed ID # in Category # 1 is 1078.

I know..!! Why not use Autonumbering, the owners of this DB have their
quirky numbering system long placed and lets not go there as this is a love
job....

Thx again.
Mick

On Oct 5, 6:00*am, "Vacuum Sealed" <noodn... (AT) gmail (DOT) com> wrote:
shouldn't this be

txtBreedId = dmax("breedId","tblBreeds", "category = " & [Forms]!
[frmBreeds]![txtcatego*ry])

ie. the first parameter must be a field in tblBreeds, so does part of
the criteria

Vacuum Sealed wrote:
Is that the correct criteria to pass to the DMax function?
I.E., if you create a query like:
SELECT Max(BreedID) FROM tblBreeds WHERE txtCategory='6'

Will that give you the correct answer?
If not, what is the proper query to give you the correct answer? we need to
know that first before we can properly construct the DMax call.

What did you expect it to give you?

Hi Bob

I was expecting the highest BreedID number for the Category.

so in Query form

SELECT Max(BreedID) FROM tblBreeds WHERE Category =
Forms!frmBreeds!txtCategory.

Thx again
Mick.

Vacuum Sealed wrote:

What do you have to substituted for "Forms!frmBreeds!txtCategory" in an
_actual query_ to get the desired result? In other words: open up the query
builder and create a query that gives you a desired result. This will tell
you two things:
1. what needs to be entered into Forms!frmBreeds!txtCategory to get the
result you need.
2. what arguments to create for the DMax call.

Going back to your previous message, where you seemed to be puzzled about
txtCategory containing 6, would this query work:

SELECT Max(BreedID) FROM tblBreeds WHERE Category = 6

If so, the DMax arguments need to be:

=Dmax("BreedID","tblBreeds","Category=" & Forms!frmBreeds!txtCategory )

If Category is a text field, you have to use


SELECT Max(BreedID) FROM tblBreeds WHERE Category = '6'

and

=Dmax("BreedID","tblBreeds","Category='" & Forms!frmBreeds!txtCategory &
"'")

Bob

When applying either suggestion:

=Dmax("BreedID","tblBreeds","Category=" & Forms!frmBreeds!txtCategory )

or

=Dmax("BreedID","tblBreeds","Category='" & Forms!frmBreeds!txtCategory &
"'")

The conrolfield in question flickers as though it is searching for the
result in an endless loop but not actually finding it..

I may just scrap the idea and use a query rather than extracting it directly
from the table.

Appreciate your patience.
Mick.