Query Question

[rshaw63]

Happy New Year everyone



I am working on a Query that matches the project name and then returns the next number in another field for numbering purposes


on the report i made a text box with the following as a control =IIf([ProjectName]=[ProjectName],DMax("RFI_Number","RFI")+1,Null)

the result of the query as is gives me the max value of the table for all projects not the one i am searching. Can someone help me figure this problem out. I think i am close


currently all items in table so the query is going to have to match the project name and then return the MAx value of that project

Update : all though looking at the above the next available number for both would be correct however i just entered another one today that was number 3 on the table and it still said 4 was the next available but there was already a # 3 for a different project. I hope this is doable.

thanks everyone in advance for your help.

[pbaldy]

I'm not sure I understand what you're asking, but with no criteria the DMax() will return the largest value in the table regardless of project. Perhaps you need a criteria:

http://www.theaccessweb.com/general/gen0018.htm

[Gicu]

Maybe change the formula on the report to:
=DMax("RFI_Number","RFI","[ProjectName]='" &[ProjectName] & "'")+1
Cheers,
Vlad