Evaluating text within an SQL statement for Microsoft Access

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

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

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")

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

ebrandeis,

Try to check out the link for reserved word in access

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

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.

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

Computer101
E-E Admin

I would like to know how the problem was solved ?

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.

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.

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).