Query builder enclosing fieldnames in quotes

[CompleteNewb]

Hi All.

I'm trying to use the Trim function in the Query builder, where I started
with one SQL statement:

UPDATE tblName SET FieldName = Trim(FieldName)

I then went into Design view, and added all my other fields and tried to
copy the Trim function into all of them. however, whenever I left that
field (column) in the builder, Access auctomatically enclosed my FieldName
in quotes. So as soon as I clicked out of the field, this:

=Trim(FieldName2)

became this:

=Trim("Fieldname2")

When I go into the Update To part to delete the quotes, it puts them back
again as soon as I click out. I discovered that if I went into SQL view and
removed the quotes, then BACK into Design view, it did not do that, and
didn't do it if I clicked in and out of the field.

So what's with the Query Builder in Design view trying to add quotes, but
going from SQL to Design it doesn't? I would have thought this would be
acommon question, but I could find no posts about it.

Thanks for any input on this.

 

[Marshall Barton]

I'm trying to use the Trim function in the Query builder, where I started
with one SQL statement:

UPDATE tblName SET FieldName = Trim(FieldName)

I then went into Design view, and added all my other fields and tried to
copy the Trim function into all of them. however, whenever I left that
field (column) in the builder, Access auctomatically enclosed my FieldName
in quotes. So as soon as I clicked out of the field, this:

=Trim(FieldName2)

became this:

=Trim("Fieldname2")

When I go into the Update To part to delete the quotes, it puts them back
again as soon as I click out. I discovered that if I went into SQL view and
removed the quotes, then BACK into Design view, it did not do that, and
didn't do it if I clicked in and out of the field.

So what's with the Query Builder in Design view trying to add quotes, but
going from SQL to Design it doesn't? I would have thought this would be
acommon question, but I could find no posts about it.

The query builder is not all that smart about adding
delimiters. You can add the [ ] around the field name
yourself so Access knows not to mess with it.

 

[John Nurick]

The solution to the unwanted quotes is to enclose the field name in
square brackets, e.g.
=Trim([Fieldname])

But IIRC the Update To cell of the query design grid needs the
expression but not the equals sign:
Trim([Fieldname])

 

[CompleteNewb]

You guys are both right.

That was a pretty seriously newb question, maybe that's why I didn't see it
out there anywhere.

I tried without the "=" as suggested, and apparently they both work.
Probably some backwards compatibility and whatnot. Thanks for the tip on
how it's not needed, that saves time for a slow typer like myself.

Thanks again everyone.

The solution to the unwanted quotes is to enclose the field name in
square brackets, e.g.
=Trim([Fieldname])

But IIRC the Update To cell of the query design grid needs the
expression but not the equals sign:
Trim([Fieldname])

Hi All.

I'm trying to use the Trim function in the Query builder, where I started
with one SQL statement:

UPDATE tblName SET FieldName = Trim(FieldName)

I then went into Design view, and added all my other fields and tried to
copy the Trim function into all of them. however, whenever I left that
field (column) in the builder, Access auctomatically enclosed my FieldName
in quotes. So as soon as I clicked out of the field, this:

=Trim(FieldName2)

became this:

=Trim("Fieldname2")

When I go into the Update To part to delete the quotes, it puts them back
again as soon as I click out. I discovered that if I went into SQL view
and
removed the quotes, then BACK into Design view, it did not do that, and
didn't do it if I clicked in and out of the field.

So what's with the Query Builder in Design view trying to add quotes, but
going from SQL to Design it doesn't? I would have thought this would be
acommon question, but I could find no posts about it.

Thanks for any input on this.