[missing operator]

[Guest]

I've Inserted these fields from a FORM except the AgentID which is the
primary key for the table.
The AgentID equal the first letter of the firstname, middleInitial,and
lastname.
This is the SQL But it gave me hard time executing it it shows error missing
operator in the expression.please someone give an idea.

INSERT INTO Agents
(AgentID,FirstName, MiddleInitial, LastName, EmailAddress, Address, City,
State, ZipCode, WorkPhone)
VALUES

(AgentID:'#Left(Form.AgentFirstName,1)#' '#(Form.MiddleInitial)#'
'#Left(Form.AgentLastName,1)#',
'#Form.AgentFirstName#','#Form.MiddleInitial#', '#Form.AgentLastName#',
'#Form.EmailAddress#', '#Form.Address#', '#Form.City#',
'#Form.State#', '#Form.ZipCode#', '#Form.WorkPhone#')

 

[Guest]

I dare say that you are going to have problems some day with a primary key
composed of only 3 characters. What happens when Jerry J. Jacobs, John J.
Jones and Jeff J. Johnson all need to be entered into the database?

Besides that, I see four things wrong at least.

The # surrounding characters means that it is a Date/Time. Remove them.

You don't have anything to concatenant the fields. Access accepts the & and
+ but be careful with the plus sign as it could add fields together.

You are surrounding the fields with single quotes. Unless you want the
literal values typed in between the quotes, drop them.

If this is being run in a query, the proper method to reference a field on
the form is
[Forms]![TheFormName]![TheFieldName] .

If the code is running within the form, you can get away with
Me![TheFieldName] .

All told it should look something like:

AgentID: Left([Forms]![TheFormName]![AgentFirstName],1) &
[Forms]![TheFormName]![MiddleInitial] &
Left([Forms]![TheFormName]![AgentLastName],1)