Strange: Query Field Alias Not Working??? 1

[kjv1611]

This is not the first time I've come across this, and it may have been in the same database, not sure.

I'm using MS Access 2010 on Windows 7.

So, I have a query where in Query Design, I use an alias, something like ShowThisName[highlight #FCE94F]:[/highlight]OriginalFieldName.

Well, since it does not show correctly in datasheet view (it still shows OriginalFieldName, I thought I'd check the underlying SQL. So I did - I went to SQL View, and copied the text out to the MS SQL Text editor, b/c it's easier to work with, in my opinion, and there, I see:

SELECT OriginalFieldname AS ShowThisName

So, is this caused by some odd form of database corruption, or a bug, or just some checkbox that I've set at some point and having the faintest as to what it is?

Thanks for any information or suggestions.



"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

One more piece of information in case anyone needs to know. The table I'm querying from is a linked table from another Access database (front-end to back-end setup). So the query is in the front-end, and the table is in the back-end, linked to the front.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

Another piece of information: There are records being returned in the query.

I have also tried putting brackets around the alias in SQL view, and that changed nothing.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

I just now tried to build another new query, this time directly in the back-end database, and get the same results - it simply ignores the Alias when showing in datasheet view.

Any clues?

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[dhookom]

I expect someone has applied a Caption property on the field in the table design. I hate it when that happens. I typically don't set lookups, captions, formats, input masks,... in table/field designs.

Duane
Hook'D on Access
MS Access MVP

 

[kjv1611]

Oh yeah - I bet that is it! I've seen that before in this database, as well as MANY other odd annoying things... It's one that's been around for years, and I've been trying to fix things along the way, including importing the old objects into a new database a while back..

I'll check that and post back.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

Yep, I looked, and doggone did they put an annoying caption in the field properties. The caption is the SAME as the field name, only with spaces between words. Argh!

So that's another change I'm going to schedule soon (have to make sure everyone is out for table changes) - remove ALL CAPTIONS - WE DON'T NEED NO STINKIN' CAPTIONS!



"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

Another update - this is a large table in Access, and at least half the fields, I think, had captions. And just another thing that's driven me bananas with this database. So the table field names would be FieldName and the caption? Take a guess..... Field Name - that's it - just adding spaces to the names.

Of course, the most annoying thing with fields, I think? There are SEVERAL fields that I've found the hard way (I pretty much have them memorized now) they will be named one thing in the table, and one thing NOT EVEN CLOSELY RELATED on the form. When I ask a user or manager in the department about a table field name, they are usually like, "hu? I have no idea what that field is or what it means." Then I find the form label for it, and they say, "Oh, it's ...." . So if we do hold onto this one long enough, I'll hopefully get around to making the field names make sense, but it's not something I just want to just jump in and start doing before making sure I find all locations the change will affect first.



"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57