Help removing Error upon Use of DMax (1 Viewer)

[C.F.]

DMax is returning an "#Error" as I try to use it to find the largest "Acqusition Number" in the table "ACQ FY08":

In the text box "Acquisition Number", on the data tab under Default Value I've entered --
=DMax("Acquistion Number","ACQ FY08")+1

I am also getting an Access error message: "Syntax error (missing operator) in query expression 'Max(Acqusition Number)'."


A past consultant set up this database with a "front end" ( gui file) and a "back end" ( .data file). Could that be causing a problem with the functioning of DMax? How do I get this to work?

 

[pbaldy]

Due to the inadvisable spaces, you probably need to bracket both names:

=DMax("[Acquistion Number]","[ACQ FY08]")+1

 

[C.F.]

RESOLVED! Read thru one of the similar threads, saw a difference in the way DMax was set up -- added brackets to the Acqusition Number [Acqusition Number] -- and it works! PTL!

 

[C.F.]

Thank you, Paul! The brackets appeared to be the problem, although I just bracketed the field name and not the table name.

 

[pbaldy]

No problem, and welcome to the site. Most of us recommend against spaces in object names because of problems like this. If it's not too late, you may want to change the names. If it is, keep the brackets in mind because you'll need them a lot!

 

[sameep]

Due to the inadvisable spaces, you probably need to bracket both names:

=DMax("[Acquistion Number]","[ACQ FY08]")+1

The problem I have with this function is am able to use it in 1 cell but i is giving me an error if i try to drag or copy the formula

=DMAX(C2:E6,E2,A22:A23)

 

[pbaldy]

This thread was about using it in Access, not Excel. What's the error? What cell is it working in, and where are you trying to copy it?

 

[sameep]

This thread was about using it in Access, not Excel. What's the error? What cell is it working in, and where are you trying to copy it?

I am using it on excel.

give me an email where I can email it to you? I will email you the excel file. It is only working on the first cell but cant drag or past the formula in the following cell

 

[pbaldy]

You should be able to attach it here.

 

[sameep]

AGENTSALESPERIODA1001-JanA2002-FebB3003-MarC4004-AprAGENTSALESA2-Feb-11B#VALUE!
=DMAX(C$2:E$6,E2,C9:C10)

 

[sameep]

here is the file

 

[pbaldy]

The second and third arguments get goofed up when you copy. The second you can fix by locking it with the $, the third by partially locking it (the top).

 

[sameep]

The second and third arguments get goofed up when you copy. The second you can fix by locking it with the $, the third by partially locking it (the top).

I still get the same error. Can you please put it on that sheet and email it to me

sameep.dixit at gmail.com

I am doing a project and whole of it is stuck because of this 1 error.

I will really be grateful for your help!

 

[pbaldy]

=dmax(c$2:e$6,$e$2,$c$9:c10)

 

[sameep]

=dmax(c$2:e$6,$e$2,$c$9:c10)

Sir still face the problem it only is working in the 2nd cell rest are giving errors! help!

 

[pbaldy]

You may have better luck posting this in the Excel forum. I don't do that much analysis with Excel. My gut is that it prefers the criteria on one line rather than stacked like that, but there are people far more knowledgable than I with Excel.