Have searched on DMax still can't figure out.

[Crapaw]

I just can't get a grip on this. Any help is appreciated.

All I am trying to do is use DMax to search a table to return the number of times a certain value is stored in a particular field and increment a number in an unbound text field on a form by one if it exists and or just return 1 if it does not.

tblContract
PreControlNumber = Text

Form=frmContract

Me.Field=Nz(DMax"[PreControlNumber]","tblContract","[PreControlNumber] = " & Me.PreControlNumber),0)+1

 

[ghudson]

All I am trying to do is use DMax to search a table to return the number of times a certain value is stored in a particular field and increment a number in an unbound text field on a form by one if it exists and or just return 1 if it does not.

DMax will return the highest value that meets your criteria.

DCount will count the number of values that meet your criteria.

I am just taking a stab at this but try...

Me.Field=Nz(DCount"[PreControlNumber]","tblContract","[PreControlNumber] = " & Me.PreControlNumber),0)+1

You will need to look up DCount in the help files for more info on its syntax.

HTH

 

[Crapaw]

Getting There

Thanks a bunch, using DCount instead of DMax gave an error so I have placed the following in its place to work up to my desired result.

=DCount("[PreControlNumber]","tblContract")

This gives the total number of times the field "PreControlNumber" is found in the table however does not give the exact number of times that a specific value for "PreControlNumber" is found in the table.

If your game to answer this one it would be great if not i will wrestle with tomorrow, any way thanks a million for the previous help.

How would i alter the above using an expression based on the value of "PreControlNumber" on a form "frmContract"?

 

[ghudson]

The syntax looks okay. Is the PreControlNumber field in the table 'text' or 'numeric'? Text requires more work vs numeric. That will make a difference on how to use the parameters for DCount. Look in the examples for DCount for that should point you in the right direction. You want to use the example that refreshes the parameters for the current record.

 

[Crapaw]

Thanks it now works, but on to more DCount stuff

Thanks ghudson, i was able to figure it out using your help and the somewhat helpful help file. the final versions was

= Nz(DCount("[PrePControlNumber]", "tblContract", "PrePControlNumber= '" & Me.PrePControlNumber & "'"), 0)

Am on to more DCount...