cannot record your changes because a value... error Access 2010 make/append table query

Question

Query works, first time to make table (or append).

Does not work the second, UNTIL edit one expression to remove the blank before the  Left$(... or Left(...

Then works.

Next time fails.

Blank is back before Left.

Makes macros fail.

What am I doing wrong.

When expression looks like this:

Ctr dash ck:Left$([Container No#],10)+"-"+Right$([Container No#],1)

it works.

BUT when expression looks like this:

Ctr dash ck: Left$([Container No#],10)+"-"+Right$([Container No#],1)

It doesn't.

And it changes to the nonworking by itself after working once.

;(

Please help.

Answer 1

Query works, first time to make table (or append).

Does not work the second, UNTIL edit one expression to remove the blank before the  Left$(... or Left(...

Then works.

Next time fails.

Blank is back before Left.

Makes macros fail.

 

What am I doing wrong.

 

When expression looks like this:

Ctr dash ck:Left$([Container No#],10)+"-"+Right$([Container No#],1)

it works.

 

BUT when expression looks like this:

Ctr dash ck: Left$([Container No#],10)+"-"+Right$([Container No#],1)

 

It doesn't.

And it changes to the nonworking by itself after working once.

;(

Please help.

 

 

I would change to not using the + sign for concatenation.  That can give you unexpected results, especially if nulls are encountered.  And, I would change from using the functions with the $ in front of them as they are actually older and only there for backwards compatibility.  The newer ones, without the dollar sign, actually can handle null values whereas the earlier ones didn't.

So,

Ctr dash ck:Left([Container No#],10) & "-" & Right([Container No#],1)