Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 220
  • Last Modified:

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.

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
ebrandeis
Asked:
ebrandeis
  • 5
  • 4
  • 3
  • +3
1 Solution
 
udikCommented:
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
 
gladxmlCommented:
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
 
gladxmlCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Zmey2Commented:
Name is reserved word. Use [Name], [Value] and [Preoprerties] - should work.
0
 
shanesuebsahakarnCommented:
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
 
gladxmlCommented:
ebrandeis,

Try to check out the link for reserved word in access

http://www.btgi.net/reservedwords.cfm
0
 
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
---------
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
 
shanesuebsahakarnCommented:
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
 
Computer101Commented:
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
 
shanesuebsahakarnCommented:
I would like to know how the problem was solved ?
0
 
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
   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
 
shanesuebsahakarnCommented:
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
 
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.
0
 
shanesuebsahakarnCommented:
Sorry you're right, the quotes shouldn't be there.
0
 
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).
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 4
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now