Query Syntax error for Concatenate

  • Thread starter Thread starter DBarker
  • Start date Start date
D

DBarker

I tried this on test data and it worked, but when I try
to edit it for my current database I get a join error and
it points to the "ON [stito]" section, I have tried
taking brackets away and leaving them in. I have tried
everything. I don't know why this is not working any
help would be appreciated.



UPDATE To INNER JOIN [stifrom]
ON [stito].[CaseID] = [stifrom].[CaseID]
SET stito.Journal = [stito].[Journals]+": " & [stifrom].
[Journals]
WHERE (((stifrom.Journals) Is Not Null));
 
You don't have a table named stito in the SQL statement; instead, you have a
table named To. One or the other has to change to match the one that you
don't change, for example:

UPDATE [stito] INNER JOIN [stifrom]
ON [stito].[CaseID] = [stifrom].[CaseID]
SET stito.Journal = [stito].[Journals]+": " & [stifrom].
[Journals]
WHERE (((stifrom.Journals) Is Not Null));
 
Dear DBarker:

Very confusing at first, but now I think I see where something may
have gone wrong. So here's a guess.

In your test data you had a table named To, but in your actual data
this table is stito. However, you left one reference to To in the
query. Change it to stito as well and try again:

UPDATE stito INNER JOIN [stifrom]
ON [stito].[CaseID] = [stifrom].[CaseID]
SET stito.Journal = [stito].[Journals]+": " & [stifrom].[Journals]
WHERE (((stifrom.Journals) Is Not Null));

Was that it?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
OK this is where it gets BIZZARE. Apparently I have it
working now. But get this when I view the table it does
not appear that the data in there, I even closed the
database and opened back up. BUT when I copy the entire
table and paste into an excel spreadsheet the DATA IS
THERE. I think I have entered the twighlight zone but I
could be wrong. ANY IDEAS PLEASE BECAUSE THIS IS NEW TO
ME.

Debbie
-----Original Message-----
You don't have a table named stito in the SQL statement; instead, you have a
table named To. One or the other has to change to match the one that you
don't change, for example:

UPDATE [stito] INNER JOIN [stifrom]
ON [stito].[CaseID] = [stifrom].[CaseID]
SET stito.Journal = [stito].[Journals]+": " & [stifrom].
[Journals]
WHERE (((stifrom.Journals) Is Not Null));



--
Ken Snell
<MS ACCESS MVP>

I tried this on test data and it worked, but when I try
to edit it for my current database I get a join error and
it points to the "ON [stito]" section, I have tried
taking brackets away and leaving them in. I have tried
everything. I don't know why this is not working any
help would be appreciated.



UPDATE To INNER JOIN [stifrom]
ON [stito].[CaseID] = [stifrom].[CaseID]
SET stito.Journal = [stito].[Journals]+": " & [stifrom].
[Journals]
WHERE (((stifrom.Journals) Is Not Null));
Click to expand...


.
Click to expand...
 
Also, I am confused why you are using both + and &. I would expect just the
&.

--
Duane Hookom
MS Access MVP


Tom Ellison said:
Dear DBarker:

Very confusing at first, but now I think I see where something may
have gone wrong. So here's a guess.

In your test data you had a table named To, but in your actual data
this table is stito. However, you left one reference to To in the
query. Change it to stito as well and try again:

UPDATE stito INNER JOIN [stifrom]
ON [stito].[CaseID] = [stifrom].[CaseID]
SET stito.Journal = [stito].[Journals]+": " & [stifrom].[Journals]
WHERE (((stifrom.Journals) Is Not Null));

Was that it?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


I tried this on test data and it worked, but when I try
to edit it for my current database I get a join error and
it points to the "ON [stito]" section, I have tried
taking brackets away and leaving them in. I have tried
everything. I don't know why this is not working any
help would be appreciated.



UPDATE To INNER JOIN [stifrom]
ON [stito].[CaseID] = [stifrom].[CaseID]
SET stito.Journal = [stito].[Journals]+": " & [stifrom].
[Journals]
WHERE (((stifrom.Journals) Is Not Null));
Click to expand...
Click to expand...
 
I must admit...I have no idea for what you're seeing right now.


--
Ken Snell
<MS ACCESS MVP>

DBarker said:
OK this is where it gets BIZZARE. Apparently I have it
working now. But get this when I view the table it does
not appear that the data in there, I even closed the
database and opened back up. BUT when I copy the entire
table and paste into an excel spreadsheet the DATA IS
THERE. I think I have entered the twighlight zone but I
could be wrong. ANY IDEAS PLEASE BECAUSE THIS IS NEW TO
ME.

Debbie
-----Original Message-----
You don't have a table named stito in the SQL statement; instead, you have a
table named To. One or the other has to change to match the one that you
don't change, for example:

UPDATE [stito] INNER JOIN [stifrom]
ON [stito].[CaseID] = [stifrom].[CaseID]
SET stito.Journal = [stito].[Journals]+": " & [stifrom].
[Journals]
WHERE (((stifrom.Journals) Is Not Null));



--
Ken Snell
<MS ACCESS MVP>

I tried this on test data and it worked, but when I try
to edit it for my current database I get a join error and
it points to the "ON [stito]" section, I have tried
taking brackets away and leaving them in. I have tried
everything. I don't know why this is not working any
help would be appreciated.



UPDATE To INNER JOIN [stifrom]
ON [stito].[CaseID] = [stifrom].[CaseID]
SET stito.Journal = [stito].[Journals]+": " & [stifrom].
[Journals]
WHERE (((stifrom.Journals) Is Not Null));
Click to expand...


.
Click to expand...
Click to expand...
 
Been working with this user in a different thread.

I suspect that the problem is the user is looking at the data in datasheet view
and the data is bigger than the cell. So the data doesn't show in the limited
area available.

The reason for using the combination of + and & is that Stito.Journals may be
null and if so, the user wants to suppress any leading colon space when adding
stiFrom.Journals to the existing field.

Duane said:
Also, I am confused why you are using both + and &. I would expect just the
&.

--
Duane Hookom
MS Access MVP

Tom Ellison said:
Dear DBarker:

Very confusing at first, but now I think I see where something may
have gone wrong. So here's a guess.

In your test data you had a table named To, but in your actual data
this table is stito. However, you left one reference to To in the
query. Change it to stito as well and try again:

UPDATE stito INNER JOIN [stifrom]
ON [stito].[CaseID] = [stifrom].[CaseID]
SET stito.Journal = [stito].[Journals]+": " & [stifrom].[Journals]
WHERE (((stifrom.Journals) Is Not Null));

Was that it?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


I tried this on test data and it worked, but when I try
to edit it for my current database I get a join error and
it points to the "ON [stito]" section, I have tried
taking brackets away and leaving them in. I have tried
everything. I don't know why this is not working any
help would be appreciated.



UPDATE To INNER JOIN [stifrom]
ON [stito].[CaseID] = [stifrom].[CaseID]
SET stito.Journal = [stito].[Journals]+": " & [stifrom].
[Journals]
WHERE (((stifrom.Journals) Is Not Null));
Click to expand...
Click to expand...
Click to expand...
 
I wondered about that (not sizing columns), but it was in someone
else's part of the thread where the OP talked about this problem.

I'm sure Duane knows about the difference between + and &. I think it
just didn't occur to him this might be deliberate. Anyway, I didn't
think that was the main thrust of the post. I rather like to fix one
thing at a time, then look at the next problem. It's my "one disaster
at a time" philosophy at work.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Been working with this user in a different thread.

I suspect that the problem is the user is looking at the data in datasheet view
and the data is bigger than the cell. So the data doesn't show in the limited
area available.

The reason for using the combination of + and & is that Stito.Journals may be
null and if so, the user wants to suppress any leading colon space when adding
stiFrom.Journals to the existing field.

Duane said:
Also, I am confused why you are using both + and &. I would expect just the
&.

--
Duane Hookom
MS Access MVP

Tom Ellison said:
Dear DBarker:

Very confusing at first, but now I think I see where something may
have gone wrong. So here's a guess.

In your test data you had a table named To, but in your actual data
this table is stito. However, you left one reference to To in the
query. Change it to stito as well and try again:

UPDATE stito INNER JOIN [stifrom]
ON [stito].[CaseID] = [stifrom].[CaseID]
SET stito.Journal = [stito].[Journals]+": " & [stifrom].[Journals]
WHERE (((stifrom.Journals) Is Not Null));

Was that it?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Wed, 31 Mar 2004 06:10:48 -0800, "DBarker"

I tried this on test data and it worked, but when I try
to edit it for my current database I get a join error and
it points to the "ON [stito]" section, I have tried
taking brackets away and leaving them in. I have tried
everything. I don't know why this is not working any
help would be appreciated.



UPDATE To INNER JOIN [stifrom]
ON [stito].[CaseID] = [stifrom].[CaseID]
SET stito.Journal = [stito].[Journals]+": " & [stifrom].
[Journals]
WHERE (((stifrom.Journals) Is Not Null));
Click to expand...
Click to expand...
Click to expand...
 
Back
Top