Query Syntax error for Concatenate

[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));

 

[Ken Snell]

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));

 

[Tom Ellison]

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

 

[DBarker]

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));

.

 

[Duane Hookom]

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

--
Duane Hookom
MS Access MVP

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));

 

[Ken Snell]

I must admit...I have no idea for what you're seeing right now.


--
Ken Snell
<MS ACCESS MVP>

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));

.

 

[John Spencer (MVP)]

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.

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

--
Duane Hookom
MS Access MVP

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));

 

[Tom Ellison]

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.

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

--
Duane Hookom
MS Access MVP

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));