Queries, Expressions and Text Fields (1 Viewer)

[waholtmn]

Hello and thank you in advance for your assistance in helping me resolve a problem I am encountering in an Access 2010 query.

I have created a query where a field uses this expression --
Source: IIf([Field 1]<>"" Or [Field 2]<>"","Answer A","Answer B") and this works fine.
Note: Some of the records have empty strings in fields 1 and 2.


When I created a new query where I am joining one Query (Query 1) to another Query that has the expression above (Query 2) I used the relationship Include ALL records from Query 1 and only those records from Query 2 where the joined fields are equal (the joined fields are numeric and ther are no null values in the joined fields). When I use the field that has the expression, it applies that expression to all of the records in Query 1 as well as Query 2. I thought that since I am requesting only those records from Query 2 where the joined fields are equal, I would only get expression results for the Query 2 records and those records in Query 1 which are not joined by the relationship would not have expression results.

So....

What I want to do is take the results of the expression and place them in another field in Query 2 but as the text values of the result of the expression.

I do not yet know VBA or SQL.

 

[VilaRestal]

If I understand what you're asking correctly:

If you put Query2 with the expression into a subquery this new field will display null for those records where there is no entry from the Query2 side.

In pseudo code:

SELECT Query1.*, Q2.*
FROM Query1
LEFT JOIN (SELECT Query2.*, IIf(Query2.[Field 1]<>"" Or Query2.[Field 2]<>"","Answer A","Answer B") As AnswerField FROM Query2) AS Q2 ON Query1.JoinField = Q2.JoinField


(SELECT Query2.*, IIf(Query2.[Field 1]<>"" Or Query2.[Field 2]<>"","Answer A","Answer B") As AnswerField FROM Query2) AS Q2

is the subquery.

Any records in Query1 that don't have a corresponding record in Query2 (Q2) will have NULL in the AnswerField column.

 

[waholtmn]

I'm sorry, you lost me there...still a noob when it comes to code...how could your solution be performed as an expression in the expression builder?

Is there a way that I could define a new field to be the literal text value from the field that has the expression and not an expression itself?

 

[VilaRestal]

It's not an expression (although a bit of it is), it's a query - in SQL.

It's important for you to understand SQL if you're going to build databases in Access. I suggest starting by googling "access sql tutorial"

Once you've got to grips with it you'll probably be able to answer your own questions.

You can always switch any existing query into SQL view to see what it really looks like in SQL code (and it's only in SQL code that it really exists).

 

[waholtmn]

Is there a way that I could define a new field to be the literal text value from the field that has the expression and not an expression itself?

 

[VilaRestal]

I'm not sure I understand:

"literal text value": as opposed to ... the illiteral text value? or the literal numeric value? Is this a text field we're looking at?

"the field that has the expression"?: a calculated field you mean?

but "not an expression itself?": but still equal to the value of a calculated field?

I think what you're talking about is giving the field an alias:

SELECT X.[Calculated Field] AS [A New Name for the Calculated Field] FROM (SELECT 1 + 1 AS [Calculated Field] FROM Table1) AS X

but that would be trivial so I wonder if that's really what you mean.