?
Solved

Evaluating text within an SQL statement for Microsoft Access

Posted on 2003-03-03
15
Medium Priority
?
218 Views
Last Modified: 2012-06-21
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.

Example:

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

And would like the results of the query to be:

Color
-----
Blue


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

Thanks,

Eden
0
Comment
Question by:ebrandeis
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +3
15 Comments
 

Expert Comment

by:udik
ID: 8062319
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 ..
Udi


0
 
LVL 15

Expert Comment

by:gladxml
ID: 8062628
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


0
 
LVL 15

Expert Comment

by:gladxml
ID: 8062650
ebrandeis,


SELECT * FROM Properties WHERE ID = 1

assuming that your recordset object is RS

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

RS("Name")
RS("Value")


0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 4

Expert Comment

by:Zmey2
ID: 8063143
Name is reserved word. Use [Name], [Value] and [Preoprerties] - should work.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8063234
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:
TRANSFORM Max(Value) AS PropertyValue SELECT ID FROM Properties WHERE ID=1 GROUP BY ID PIVOT Name
0
 
LVL 15

Expert Comment

by:gladxml
ID: 8063268
ebrandeis,

Try to check out the link for reserved word in access

http://www.btgi.net/reservedwords.cfm
0
 

Author Comment

by:ebrandeis
ID: 8065927
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
---------
A-1
B-1
C-1

instead of

StringValue
-----------
A-1
B-1
C-1


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.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8066166
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.
0
 
LVL 1

Expert Comment

by:Computer101
ID: 8076162
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

Computer101
E-E Admin
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8076427
I would like to know how the problem was solved ?
0
 

Author Comment

by:ebrandeis
ID: 8076813
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
   30
Color Blue
Width  5
Length 10
                 RES_RC_RE_1_1   X        
                                           SC        4.123
                                           SL          0
                                           SS
                 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:
tblLookups
ID   LookupTable   LookupField  LookupID
1      tblColors       Color         3
2      tblTastes       Taste         2

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

tblTastes
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.
0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 400 total points
ID: 8079283
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"
Else
   GetLookupVal = rst(strField)
End If
rst.Close
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
0
 

Author Comment

by:ebrandeis
ID: 8081899
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.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8081996
Sorry you're right, the quotes shouldn't be there.
0
 

Author Comment

by:ebrandeis
ID: 8083048
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).
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question