DMax function Help

[AgentLozen]

Hi everyone.



I've been learning Access recently and I've come across a problem that's stopped me dead in my tracks. It seems like this should be OMEGA LEVEL EASY, but instead it's giving me a ton of trouble. Here's the situation.

I have a table "ASSIGNMENTS" whose primary key "ASSIGNMENT ID" I'm changing from Auto-Number -> a DMax numbering convention. All of the table information is entered through a form [ASSIGNMENT ENTRY]. I want the form to have a button that, when clicked, puts the Dmax value of the primary key into a text box "txtTest".

The table "ASSIGNMENTS" primary key "ASSIGNMENT ID" has data in the Number format like this:
1, 2, 3, 4, 5, 6, 7, 8

My VB code for the button looks like:
[Forms]![ASSIGNMENT ENTRY]![txtTest] = DMax("ASSIGNMENT ID", "ASSIGNMENTS")
The control source for txtTest is the "ASSIGNMENT ID" column.

I expect the output to the text box to be 8. Then 8 should appear in the ASSIGNMENTS table when I save the record. Instead, I'm getting the error "Syntax error (missing operator) in query expression 'Max(ASSIGNMENT ID)'. I can feed an arbitrary number into the button like "130". That works and changes the value of the text box to "128". The trouble seems to be with the DMax function itself.

Has anyone experienced a problem like this before and has some advice to offer? I would appreciate the help.

-AgentLozen

[NoellaG]

try DMax("[ASSIGNMENT ID]", "ASSIGNMENTS") with square brackets. You have used a space in a fieldname, which is never a good idea.
Also, if you want to assign a new ID-value which has to be unique, I think you probably want to set it to DMax("[ASSIGNMENT ID]", "ASSIGNMENTS") + 1

Kind regards
Noëlla

[AgentLozen]

Oh wow. That did it.
I guess using spaces makes things needlessly complicated. I'll keep that in mind in the future.
I appreciate your help Noella.

[June7]

Advise no punctuation/special characters (underscore only exception) as well. Camel case is best for easy reading, such as: AssignmentID.