Passing an expression from a table field to a query

[mallettdj]

The quick question (Access 2003): If I store an expression in a text
field in a table, can a query convert it from text and use it? The
expression would contain fields from another table that is joined when
the query runs.

The longer explanation:

I have a table which is a list of "activities" to track the progress
of manuscript submissions to a journal. The table (tblActivities)
only contains two fields - ActivityID and ActivityDesc. There are
about 25 activities.

The data tables involved are a main manuscript table and a sub-table
of the activities for each manuscript. Each specific activity
requires different fields to be filled in the sub-table.

Rather than show all the fields for an activity in some forms/reports,
I would like to display a short summary "sentence". And rather than
build it using if-then-else or select-case, I was wondering if it was
possible to add a field to tblActivities and store an expression for
each specific activity - then have the query interpret the
expression. The expression would be using fields from the sub-table.

I've tried various things and searched through Help and the
newsgroups, but can't seem to find it and am not even sure what to
search on. All I can get it to do is display the actual expression -
not the result of the expression. Probably because it's stored in a
text field and I need to convert it somehow. I've tried Eval several
different ways, but not sure if I managed to try it the right way.

I'm trying to store something like: "Submission received: " & [aDate]
& " Title: " & [msTitle]

Have tried it with and without an "=" at front.

This is kind of long-winded and still I'm not sure I've explained it
well. Just curious if a query can convert a text expression from a
table field to one it can use. If so, can you point me in the right
direction?

Thanks for any advice,
Deb

 

[× ×ª× ×ל ×לירז]

Hi there,
I'm not sure I understood your wish. I understand that there's Activity
table, which its IDs appear in some other table. What do you want the query
to do?

 

[mallettdj]

Hi! Thanks for replying. Sorry I took so long to get back here. I
figured I didn't explained it very well, so I'll try again.

I would have a table of activities, which would have a field called
ActivitySentence.

As an example, I'd like the value of ActivitySentence to be something
like the following (different for each activity):
"Submission received: " & [aDate] & " Title: " & [msTitle]

I would like to use that field in a query - and have the query convert
it so it returns:
Submission received: Dec 7/07 Title: The Red Planet

So far I haven't been able to get it to convert - it just shows the
original contents. Not sure if it's because it's in a text field or
if I've done something wrong in the query or ... whatever else.

Thanks again,
Deb

Hi there,
I'm not sure I understood your wish. I understand that there's Activity
table, which its IDs appear in some other table. What do you want the query
to do?

The quick question (Access 2003):  If I store an expression in a text
field in a table, can a query convert it from text and use it?  The
expression would contain fields from another table that is joined when
the query runs.

The longer explanation:

I have a table which is a list of "activities" to track the progress
of manuscript submissions to a journal.  The table (tblActivities)
only contains two fields - ActivityID and ActivityDesc.  There are
about 25 activities.

The data tables involved are a main manuscript table and a sub-table
of the activities for each manuscript.  Each specific activity
requires different fields to be filled in the sub-table.

Rather than show all the fields for an activity in some forms/reports,
I would like to display a short summary "sentence".  And rather than
build it using if-then-else or select-case, I was wondering if it was
possible to add a field to tblActivities and store an expression for
each specific activity - then have the query interpret the
expression.  The expression would be using fields from the sub-table.

I've tried various things and searched through Help and the
newsgroups, but can't seem to find it and am not even sure what to
search on.  All I can get it to do is display the actual expression-
not the result of the expression.  Probably because it's stored in a
text field and I need to convert it somehow.  I've tried Eval several
different ways, but not sure if I managed to try it the right way.

I'm trying to store something like:  "Submission received: " & [aDate]
& "  Title: " & [msTitle]

Have tried it with and without an "=" at front.

This is kind of long-winded and still I'm not sure I've explained it
well.  Just curious if a query can convert a text expression from a
table field to one it can use.  If so, can you point me in the right
direction?

Thanks for any advice,
Deb- Hide quoted text -

- Show quoted text -

 

[John W. Vinson]

Hi! Thanks for replying. Sorry I took so long to get back here. I
figured I didn't explained it very well, so I'll try again.

I would have a table of activities, which would have a field called
ActivitySentence.

As an example, I'd like the value of ActivitySentence to be something
like the following (different for each activity):
"Submission received: " & [aDate] & " Title: " & [msTitle]

I would like to use that field in a query - and have the query convert
it so it returns:
Submission received: Dec 7/07 Title: The Red Planet

So far I haven't been able to get it to convert - it just shows the
original contents. Not sure if it's because it's in a text field or
if I've done something wrong in the query or ... whatever else.

No. You don't need to store this derived field in any Table. Just generate it
as needed, using the expression you typed as a calculated field in a Query.
Simply put

ActivitySentence: "Submission received: " & [aDate] & " Title: " & [msTitle]

in a vacant Field cell in a query containing the fields aDate and msTitle, and
base your form or report or further query on this Query.

John W. Vinson [MVP]

 

[mallettdj]

Hi John. Thanks for replying. The reason I wanted to put the
expression in a field in the table of Activities is because each
activity has a different calculated sentence (there are so far about
25 of them). So I'd have to do a fairly long if-then-else or select-
case that would need to be run for every record (or possibly a
lookup). I'll do that if I have to, but I wanted to know first
whether putting it into a field in a table was possible.

On a perhaps politically incorrect note, I'm not being paid as a
programmer in this job - so I wanted to do this as simply as possible
- especially if I have to pass it on to the next person to do the job,
who very likely wouldn't have any Access experience at all, let alone
any programming experience. I've had quite a bit of experience myself
in Access, but now I only work with Access when I want it for my own
needs. But there's this expectation to train and share what you do
with others who may do the same type of work or who may come after
you. It's been extremely awkward in the past when I've done a lot of
programming in something, so I'm just trying to do this very simply.
If it can't be done this way, I'll do it the proper way.

Thanks very much for your help on this.

Deb

No. You don't need to store this derived field in any Table. Just generateit
as needed, using the expression you typed as a calculated field in a Query..
Simply put

ActivitySentence: "Submission received: " & [aDate] & "  Title: " & [msTitle]

in a vacant Field cell in a query containing the fields aDate and msTitle,and
base your form or report or further query on this Query.

             John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -

Hi! Thanks for replying. Sorry I took so long to get back here. I
figured I didn't explained it very well, so I'll try again.

I would have a table of activities, which would have a field called
ActivitySentence.

As an example, I'd like the value of ActivitySentence to be something
like the following (different for each activity):
"Submission received: " & [aDate] & " Title: " & [msTitle]

I would like to use that field in a query - and have the query convert
it so it returns:
Submission received: Dec 7/07 Title: The Red Planet

So far I haven't been able to get it to convert - it just shows the
original contents. Not sure if it's because it's in a text field or
if I've done something wrong in the query or ... whatever else.

 

[John W. Vinson]

Hi John. Thanks for replying. The reason I wanted to put the
expression in a field in the table of Activities is because each
activity has a different calculated sentence (there are so far about
25 of them). So I'd have to do a fairly long if-then-else or select-
case that would need to be run for every record (or possibly a
lookup). I'll do that if I have to, but I wanted to know first
whether putting it into a field in a table was possible.

How do they differ? If the "Submission received:" and other constant text are
what's changing, maybe you need to store THAT information in a table too!

You can put it in a table.... but it will make your code MORE complex, not
simpler; you'll need VBA code to update the table, and to change the
ActivitySentence whenever any of the underlying information changes. If you
want simple... use Access properly and relationally; store as much of the
information that you want to print *in tables*, and minimize the amount of
printed information which needs to be generated from code.

Note also that I was NOT suggesting using anyu programming. You were the only
one to bring that up!

On a perhaps politically incorrect note, I'm not being paid as a
programmer in this job - so I wanted to do this as simply as possible
- especially if I have to pass it on to the next person to do the job,
who very likely wouldn't have any Access experience at all, let alone
any programming experience. I've had quite a bit of experience myself
in Access, but now I only work with Access when I want it for my own
needs. But there's this expectation to train and share what you do
with others who may do the same type of work or who may come after
you. It's been extremely awkward in the past when I've done a lot of
programming in something, so I'm just trying to do this very simply.
If it can't be done this way, I'll do it the proper way.

John W. Vinson [MVP]

 

[mallettdj]

You sound annoyed, so I suspect my last post sounded like I was
disputing your advice or not understanding it. But that's not the
case, I was just trying to explain why I wanted to try it this way.
So I expect I've not explained it well again - sorry about that. This
is what I've done...

I have normalized the data and the database is already relational.
The information I want to print is already in tables, I'm not trying
to update the "sentence" in the activity table, and I really am trying
to minimize the code. These are the tables and queries that I have
that apply to this question:

Tables:
dManuscripts:
- one record for each manuscript received
- includes title in [msTitle]
dMsActivities:
- subtable for dManuscripts which contains one record for
each activity that happens to the manuscripts
- includes activity code from table below
- includes activity date in [aDate]
tblActivities:
- a list of all unique activities (one is "Submission Received")
- when entering activities in the dMsActivities subtable, the
activities are picked from this table (through a combo box
in the entry form)
- has the field ActivitySentence (text field), which holds the
expression I want to use to build the "sentence" for each
unique activity: ex: the value of ActivitySentence for the
"Sub Received" activity could be:
"Submission received: " & [aDate] & " Title: " & [msTitle]
- each activity (currently 25) would need a different sentence
using different fields from the other two tables

Query:
qryActivityxDate:
- uses all three tables above
- queries for all activities between certain dates
- includes the ActivitySentence field in query

When the query runs, I would like the ActivitySentence field to show -
Submission received: Dec 7/07 Title: The Red Planet - i.e. with
[aDate] and [msTitle] filled in.

My problem is that it is showing the actual value of ActivitySentence
- i.e. "Submission received: " & [aDate] & " Title: " & [msTitle]

I've tried using Eval various ways, which hasn't worked for me so far,
but I may not be using it correctly - and it may not be what I should
be using. Or maybe it's a silly idea and just not possible. If
that's true, no problem, I'll just move on.

My reasons are that I'm thinking about when I have to pass this job on
to someone else who doesn't know Access (it's not a qualification they
ask for on this job). With 25 different activities, each with a
different sentence, I just think it would be easier to maintain them
(change/add/delete) in a table than it would be in coding in the query
or whatever else. However could be very wrong about that - been wrong
before!

Anyway, that's it. Thanks again for your time.

Deb