Passing value to string expression

[terrytech]

Hi,
I am trying to pass a value to a string expression so that I can get a note
to display saying, for example, "The last form number used was 12." (with12
being an example of the value passed from a query qryMaxFormNumber, where the
Total of the FormNumber field is set to Max).

I have tried writing a query which concatenates "The last form number used
was"&" "&[qryMaxFormNumber]![MaxOfFormNumber]&"." , but then I get an "Enter
Parameter Value" screen where it asks for [qryMaxFormNumber]![MaxOfFormNumber]
.. Having to look up and enter the value defeats the whole purpose of the
query.

I realize I am a rank beginner if I can't figure this out on my own, but
would appreciate help!

Thanks in advance,
Terrytech

 

[Guest]

Hi Terry,

In the query add another column. This time fill in the field name as "A
unique name you want to call this field":[Field 1] & [Field 2]

To enter the field names properly, it is easier to use the query build
function. To access this right click in the drop down arrow on the right
hand side of the field portion of the query and this will bring up the
expression builder. Simply select the table and fields from the lists and
paste them in. You may get <<expr>> in the builder, erase these.

when you run the query it will create another field with the two fields
concatenated.

For formating purposes you may what a space bewteen the two field results,
in which case place &" " after [Field 1]

 

[terrytech]

David--I did what you said, and am still getting an "Enter Parameter Value".
The query has three unique fields, which are (1)Note:="The last form number
used was", (when I just had Note: "The last form number used was" (without
the equals sign) it was prompting me to enter a value for Note as well!!); (2)
[FormNumber] with Max chosen under totals, so it appears as [MaxOfFormNumber]
in the built expression, and (3) my concatenated field as CurrentFormNumber:
[Note] & " " & [MaxOfFormNumber] & "."

I used the Builder before I wrote to you and when "rebuilding" the expression
after reading your reply, so I'm pretty sure it's not a spelling error or
anything like that.

Hi Terry,

In the query add another column. This time fill in the field name as "A
unique name you want to call this field":[Field 1] & [Field 2]

To enter the field names properly, it is easier to use the query build
function. To access this right click in the drop down arrow on the right
hand side of the field portion of the query and this will bring up the
expression builder. Simply select the table and fields from the lists and
paste them in. You may get <<expr>> in the builder, erase these.

when you run the query it will create another field with the two fields
concatenated.

For formating purposes you may what a space bewteen the two field results,
in which case place &" " after [Field 1]

Hi,
I am trying to pass a value to a string expression so that I can get a note

[quoted text clipped - 13 lines]

Thanks in advance,
Terrytech

 

[Guest]

Hi Tery,

You probably have left out the & symbol between the text you want to preceed
the returned values.

If I have read it correctly your query field should be

Note: "The last form number was " & [Field 1] &" " & [Field 2]

without the & symbols it is looking for an input from you into field 1/2.
Note that in the criteria field to be prompted for a value on which to query
you use the format of

[ Prompt message]

and this of course looks exactly like the field name in the above builder.

Hope this answers the question.

Regards

DavidC

David--I did what you said, and am still getting an "Enter Parameter Value".
The query has three unique fields, which are (1)Note:="The last form number
used was", (when I just had Note: "The last form number used was" (without
the equals sign) it was prompting me to enter a value for Note as well!!); (2)
[FormNumber] with Max chosen under totals, so it appears as [MaxOfFormNumber]
in the built expression, and (3) my concatenated field as CurrentFormNumber:
[Note] & " " & [MaxOfFormNumber] & "."

I used the Builder before I wrote to you and when "rebuilding" the expression
after reading your reply, so I'm pretty sure it's not a spelling error or
anything like that.

Hi Terry,

In the query add another column. This time fill in the field name as "A
unique name you want to call this field":[Field 1] & [Field 2]

To enter the field names properly, it is easier to use the query build
function. To access this right click in the drop down arrow on the right
hand side of the field portion of the query and this will bring up the
expression builder. Simply select the table and fields from the lists and
paste them in. You may get <<expr>> in the builder, erase these.

when you run the query it will create another field with the two fields
concatenated.

For formating purposes you may what a space bewteen the two field results,
in which case place &" " after [Field 1]

Hi,
I am trying to pass a value to a string expression so that I can get a note

[quoted text clipped - 13 lines]

Thanks in advance,
Terrytech

 

[BruceM]

There are a couple of things here. When you say "so that I can get a note
to display", do you mean a message box or what? If so, and if the purpose
of the message box is so the user can enter the next number, would an
incremented field for the form number work for you instead (i.e. the number
would be assigned automatically)? What is the format of the number? Also,
if this is a multi-user database you will need to take precautions against
the number being duplicated if two users create a new record at the same
time.
If your intention is to make a message appear on the form, you could include
the message in the form's record source query. Is [Note] a field in the
table on which the query is based, or is it a calculated field in the query?
In any case, Note is a reserved word (in Jet, which is the database engine;
that's all I can really tell you about it), so the problem may be due in
part to your use of that word.
When you make a calculated query field you need to use fields that are
available in the query's data source (the tables or queries you add to the
top of the design view). If there is just one query or table supplying data
for the query in question, all you need to use is the name of the field. If
there are several tables or queries, and if the same field name appears in
two or more of them, you will need to be specific about which one you mean,
which is where naming the query comes in.
To build a calculated field, open the query in design view and type the
expression at the top of a blank column, as DavidC described. However, do
not put the name of the field in quotes. I think David must have done that
to distinguish it from the rest of his comments. If the expression for the
new field is:
NewField: "Last form number used was " & [Field1] & " " & [Field2]
then both [Field1] and [Field2] need to be available in one of the tables or
queries at the top of the query design view (above the design grid).

David--I did what you said, and am still getting an "Enter Parameter
Value".
The query has three unique fields, which are (1)Note:="The last form
number
used was", (when I just had Note: "The last form number used was" (without
the equals sign) it was prompting me to enter a value for Note as well!!);
(2)
[FormNumber] with Max chosen under totals, so it appears as
[MaxOfFormNumber]
in the built expression, and (3) my concatenated field as
CurrentFormNumber:
[Note] & " " & [MaxOfFormNumber] & "."

I used the Builder before I wrote to you and when "rebuilding" the
expression
after reading your reply, so I'm pretty sure it's not a spelling error or
anything like that.

Hi Terry,

In the query add another column. This time fill in the field name as "A
unique name you want to call this field":[Field 1] & [Field 2]

To enter the field names properly, it is easier to use the query build
function. To access this right click in the drop down arrow on the right
hand side of the field portion of the query and this will bring up the
expression builder. Simply select the table and fields from the lists and
paste them in. You may get <<expr>> in the builder, erase these.

when you run the query it will create another field with the two fields
concatenated.

For formating purposes you may what a space bewteen the two field results,
in which case place &" " after [Field 1]

Hi,
I am trying to pass a value to a string expression so that I can get a
note

[quoted text clipped - 13 lines]

Thanks in advance,
Terrytech

 

[terrytech via AccessMonster.com]

Bruce--Thanks for your reply. After reading it, I was able to fix the problem.


I did away with the calculated field [Note] altogether.

I had a field in my query called [FormNumber]. In that column of the query,
under "Totals", I had "Max" checked. When I used the builder to make the
expression for my calculated field, it looked like this:

CurrentFormNumber: "The last form number used was"&" "&[MaxOfFormNumber]&"."

The query "did not like" [MaxOfFormNumber]--that's where it was asking me to
enter a parameter value.

After reading your reply, (>>When you make a calculated query field you need
to use fields that are

available in the query's data source (the tables or queries you add to the
top of the design view). <<), I did a new query on [FormNumber] only that just returned its Max value. I then used that query as a record source for my original query to get this:

CurrentFormNumber: "The last form number used was" & " " &
[qryMaxFormNumberUsed]![MaxOfFormNumber] & "."

I'm still not entirely sure why my original field didn't work, but it works
absolutely fine now.

Thanks so much for the help!

Terry

There are a couple of things here. When you say "so that I can get a note
to display", do you mean a message box or what? If so, and if the purpose
of the message box is so the user can enter the next number, would an
incremented field for the form number work for you instead (i.e. the number
would be assigned automatically)? What is the format of the number? Also,
if this is a multi-user database you will need to take precautions against
the number being duplicated if two users create a new record at the same
time.
If your intention is to make a message appear on the form, you could include
the message in the form's record source query. Is [Note] a field in the
table on which the query is based, or is it a calculated field in the query?
In any case, Note is a reserved word (in Jet, which is the database engine;
that's all I can really tell you about it), so the problem may be due in
part to your use of that word.
When you make a calculated query field you need to use fields that are
available in the query's data source (the tables or queries you add to the
top of the design view). If there is just one query or table supplying data
for the query in question, all you need to use is the name of the field. If
there are several tables or queries, and if the same field name appears in
two or more of them, you will need to be specific about which one you mean,
which is where naming the query comes in.
To build a calculated field, open the query in design view and type the
expression at the top of a blank column, as DavidC described. However, do
not put the name of the field in quotes. I think David must have done that
to distinguish it from the rest of his comments. If the expression for the
new field is:
NewField: "Last form number used was " & [Field1] & " " & [Field2]
then both [Field1] and [Field2] need to be available in one of the tables or
queries at the top of the query design view (above the design grid).

David--I did what you said, and am still getting an "Enter Parameter
Value".

[quoted text clipped - 37 lines]

 

[terrytech via AccessMonster.com]

David--I definitely had all of the ampersands; my calculated field looked
like this:

CurrentFormNumber: "The last form number used was"&" "&[MaxOfFormNumber]&"."

The problem was with the field [MaxOfFormNumber]--see my reply to Bruce below.


Thanks for trying to help--I appreciate it!

Terry

Hi Tery,

You probably have left out the & symbol between the text you want to preceed
the returned values.

If I have read it correctly your query field should be

Note: "The last form number was " & [Field 1] &" " & [Field 2]

without the & symbols it is looking for an input from you into field 1/2.
Note that in the criteria field to be prompted for a value on which to query
you use the format of

[ Prompt message]

and this of course looks exactly like the field name in the above builder.

Hope this answers the question.

Regards

DavidC

David--I did what you said, and am still getting an "Enter Parameter Value".
The query has three unique fields, which are (1)Note:="The last form number

[quoted text clipped - 30 lines]