Reserved words

S

Sandy H

Hi
I am making some changes to a database that I didn't create and ran into a
problem where the original creator used the word 'Time' as a field. I am
trying to run an INSERT sql statement on this table and naturally, it
doesn't work because of the Time field. Is there a way around this problem
without changing the field name (The database is linked to a data file and
the client is not in the same city as me so changing the field name is
somewhat difficult).

Thanks in advance if anyone can offer a solution.

Sandy
 
A

Allen Browne

Try enclosing the field name in square brackets, and including the table
name, e.g.:
[Table1].[Time]
 
S

Sandy H

Hi Allen,
Thanks for the reply. Unfortunately, I tried that but still no luck. My sql
statement looks like this:

strSql = "INSERT into Orders (CustId, OrderDate, [Orders].[Time], Quantity,
Publication, Dist, DespatchDate, Comments) VALUES ("
strSql = strSql & CustId & ", #" & Format(Me.txtDate, "mm/dd/yyyy") & "#, #"
& Format(Me.txtTime, "Short Time") & "#, " & Me.txtQty & ", " & rs!PubID
strSql = strSql & ", '" & Me.cmbDistribution & "', #" & Format(Me.txtddate,
"mm/dd/yyyy") & "#, '"
strSql = strSql & Me.txtComments & "')"

It works fine if I rename the time field in my copy of the table but not at
all while the name is Time. Any other thoughts.

Sandy

Allen Browne said:
Try enclosing the field name in square brackets, and including the table
name, e.g.:
[Table1].[Time]

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Sandy H said:
Hi
I am making some changes to a database that I didn't create and ran into
a problem where the original creator used the word 'Time' as a field. I
am trying to run an INSERT sql statement on this table and naturally, it
doesn't work because of the Time field. Is there a way around this
problem without changing the field name (The database is linked to a data
file and the client is not in the same city as me so changing the field
name is somewhat difficult).
Click to expand...
Click to expand...
 
S

Sandy H

Can't believe it was that simple.

Thank you. You have finally solved my frustration.

Sandy

Van T. Dinh said:
Try changing [Order].[Time] to simply [Time]

--
HTH
Van T. Dinh
MVP (Access)


Sandy H said:
Hi Allen,
Thanks for the reply. Unfortunately, I tried that but still no luck. My sql
statement looks like this:

strSql = "INSERT into Orders (CustId, OrderDate, [Orders].[Time], Quantity,
Publication, Dist, DespatchDate, Comments) VALUES ("
strSql = strSql & CustId & ", #" & Format(Me.txtDate, "mm/dd/yyyy") & "#, #"
& Format(Me.txtTime, "Short Time") & "#, " & Me.txtQty & ", " & rs!PubID
strSql = strSql & ", '" & Me.cmbDistribution & "', #" & Format(Me.txtddate,
"mm/dd/yyyy") & "#, '"
strSql = strSql & Me.txtComments & "')"

It works fine if I rename the time field in my copy of the table but not at
all while the name is Time. Any other thoughts.

Sandy
Click to expand...
Click to expand...
 
V

Van T. Dinh

I have just spent 90 mins trying to solve a Maths problem (helping my
daughter in year 11 3-Unit Maths, advanced question) that should have taken
30 seconds if I had had the correct thinking hat on (I wrongly thought the
problem was more complex than it is really is).

BTW, when you ask about Queries / SQL, always post the SQL String. I am
sure Allen and other respondents would have seen the problem straight away.
 
Top