Alias names changing

[jarra_mackem]

I have built an SQL query which sums up various totals and that's working just fine.

As this output links to an outside system it requires the fields to be named exactly as they are in the other system

Using the AS functionality I have

01 - Total outstanding
02 - Total at take on
03 - Total payments

Etc etc

However for some reason when I close and reopen the Results come as

1-Total outstanding
02 - Total at take on
03 - Total payments

The sql code has changed as well dropping the leading zero and removing the spaces. I can't fathom a reason why this only happens on the first instance and not the others.

Any help greatly appreciated

Paul

 

[jdraw]

Please post the query SQL.

 

[jarra_mackem]

Select tblOne.Value1 as [01 - Total outstanding],
tblOne.Value1 as [02 - Total at take on],
tblOne.Value1 as [03 - Total payments]
From tblOne;

Changes to

Select tblOne.Value1 as [1-Total outstanding],
tblOne.Value1 as [2-Total at take on],
tblOne.Value1 as [3-Total payments]
From tblOne;

It's now losing the zero and spaces on all. This seems to only happen when it's coded in SQL view and not the design view which is more baffling

 

[jdraw]

One thing you can do is put the required Caption into the Caption property of the associated field in your tblOne. I think you would need " surrounding the alias (and the " will show up in the name), but see the attached jpgs of how I did it in a test.

The [ ] around field names if they include spaces and special characters. But this is not a field as I see it.

Good luck.

 

[Pat Hartman]

If you are using an Access version earlier than A2010 (possibly A2007), using captions on tables will cause significant problems with everything else in the database. Try adding the caption to the query rather than the table.