Evaluating text within an SQL statement for Microsoft Access

The problem I am working on is quite complicated, so I will illustrate it with a simpler example.

I have a table called Properties with fields Name and Value.  Here is what I want to do:

SELECT Value As Name FROM Properties WHERE ID = 1

But obviously Access throws this error (The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect. (Error 3141)).

Is there a way to force Access to evaluate the text that Name represents?  Name should be the Name of the property and I would like to display it as the header for my results.


Table Properties
ID   Name   Value
1    Color  Blue
2    Width    5
3    Length   10

And would like the results of the query to be:


Please let me know if this is unclear and I will post more details.


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

What you need to do is to build your table like that:
ID        1      2       3
Color     Blue   Null    Null
Width     Null   5       Null
Lenght    Null   Null    10

Then use the following SQL query:
SELECT * FROM properties WHERE ID=1

Then create a report (or Form) which contains the format you need, with the headigs you need:

ID                    Name
-------------------   ------------------------------------
control soucre = ID   c.s. = IIF(Color<>Null,Color, Width)

Let me know if you like this ..

Value ia reserverd word in access

You can try enclosed it in a bracket like this

SELECT [Value] As Name FROM Properties WHERE ID = 1

or much better chnage the column name


SELECT * FROM Properties WHERE ID = 1

assuming that your recordset object is RS

then you can simply refer to clumn value like this...


Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Name is reserved word. Use [Name], [Value] and [Preoprerties] - should work.
You could do something like this - this will also give an ID column, and will only work properly for a single record at a time:

Try to check out the link for reserved word in access

ebrandeisAuthor Commented:
Ok, I flubbed up my example.  My real query does not use Value by itself as I showed in my example.  Here is another example of my SQL.  Again this is just part of what the final statement will be, but should work on its own and doesn't.

SELECT prop.StringValue as pt.Name FROM tblProperties prop INNER JOIN tblPropertyTypes pt ON prop.PropertyTypeID = pt.PropertyTypeID WHERE prop.PropertyTypeID = 3;

In this case I would like a column returned with a list of StringValue properties (designated by PropertyTypeID = 3) and the column header to have the value of pt.Name (in this case 'Job Title').  So the results of the query should be:

Job Title

instead of


The problem appears to be that MS Access will not evaluate a conditional statement or evaluation type statement after AS.  For an example of this I tried the following:

SELECT prop.StringValue as iif(1 = 1, 'hello', 'goodbye') FROM tblProperties prop INNER JOIN tblPropertyTypes pt ON prop.PropertyTypeID = pt.PropertyTypeID WHERE prop.PropertyTypeID = 3;

Which gives the same error.  Removing the iif statement and replacing with static text gets rid of the error, but I want to dynamically assign a value to the column header based on the value of tblPropertyTypes.Name.

I am starting to get the feeling that what I want to do is just not possible, but I will investigate the solutions suggested by udik and shanesuebsahakarn as well as some other ideas I had this morning.
The reason you can't put the condition after the As is because you would effectively be trying to give a single field more than one name.

If it is the title you want, you would probably be best off placing your query into something like a datasheet form. You can then change the column header name (by altering the Caption property of an attached label) from code without any problems.
Hello all.  Here is the post from the cs question that was posted.

"I did not end up using the information in any of the comments to my question.  How should the points be handled?  I would not be adverse to splitting part of the points up as a "thanks for the help" type reward or to just refunding the whole bundle."

How do you all think this should be handled

E-E Admin
I would like to know how the problem was solved ?
ebrandeisAuthor Commented:
What I ended up doing was creating a subreport based on tblProperties.  This subreport is shown once for each ID in the main report.  The tblProperties subreport just displays the Name and Value fields side by side to give the effect I was looking for.

So I would end up with something like this:

MeasurementID   FeatureLabel   Passed  Property   Actual
Color Blue
Width  5
Length 10
                 RES_RC_RE_1_1   X        
                                           SC        4.123
                                           SL          0
                 RES_LC_LE_1_1   X    
                                           SC        4.154
                                           SL          0
                                           SS          0

and so on.  Of course this probably doesn't make much sense to you on its own, but the key for me was to have the Name and Value fields show up next to each other.  When I posed this question I was stuck on the idea of building a query that would return the results with Name as the column header for a list of associated values.

Part of my original question was purely theoretical.  Is there a way to evaluate the text within a field and use the value within your query (as opposed to just outputting it as a query result).  For example, given the table:
ID   LookupTable   LookupField  LookupID
1      tblColors       Color         3
2      tblTastes       Taste         2

and the tables:
ID  Color
1    Green
2    Red
3    Blue

ID   Taste
1    Sweet
2    Sour
3    Salty

Is there a way to do this:

SELECT * FROM tblLookups a, a.LookupTable b WHERE b.ID = a.LookupID;

You can see that this won't work unless we have a way to force a.LookupTable to return a value within the query and become the table name used in the query itself.  Does that make sense?  If someone can tell me how to solve this or explain a way to evaluate text fields within the SQL statement itself, I will give them all the points for this question.
You can write a VBA function to do it quite simply - you can't do it purely via Jet SQL. You can then call the function in a query to return a result for each row, ie:

Function GetLookupVal(strTable As String, strFieldName As String, lngLookupID As Long) As String
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("SELECT [" & strField & "] FROM [" & strTable & "] WHERE ID=" & lngLookupID)
If rst.EOF Then
   GetLookupVal = "Not found"
   GetLookupVal = rst(strField)
End If
End Function

This code uses DAO, so in A2K or later, you will need to make sure you have a reference set.

You would then call it from a query with something like:
SELECT *,GetLookupVal("LookupTable","LookupField","LookupID") As LookupResult FROM tblLookups

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ebrandeisAuthor Commented:
Thank you for your comment shanesuebsahakarn.

"You would then call it from a query with something like:
SELECT *,GetLookupVal("LookupTable","LookupField","LookupID") As LookupResult FROM tblLookups

Did you mean to write:

SELECT *, GetLookupVal(LookupTable,LookupField,LookupID) As LookupResult FROM tblLookups;

(No quotes on LookupTable, LookupField, LookupID)

I will give this a try today.  I don't really like the idea of using VB to do this for portability reasons though.
Sorry you're right, the quotes shouldn't be there.
ebrandeisAuthor Commented:
Thank you everyone for your comments.  shanesuebsahakam came up with a solution to part of the problem I was facing and most clearly explained a work around for how to process text results and use it in the query (albeit through a VBA function).
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.