Linked value in append query gives symbol value

[Steve in MN]

I have a form that has field Record # and once the record is established,
then an append query runs that appends a set of questions (survey) into the
subform to answer. in the append query (which was working for 2 months) i
have RecordID:forms!frmSurvey![Record #] which takes the main form record #
and puts it in with the subform records so they are linked. However now for
some reason it was not appending, so i opened the append query while the main
form was open to make sure that it was grabbing the record number from the
main form. The query is showing symbol or lower case "a" or lower case "b"
or some other symbols.
The record number field is an autonumber set as integer and it displays in
the tables and on the main survey form, however now that this append query is
trying to use it to append to another table, it doesn't recognize it or
display it properly.

any ideas?

 

[Allen Browne]

From your description, it sounds like there is a problem with the data type
of this field.

Your append query is reading the value from the [Record #] text box on your
form. If this is an unbound text box, try setting its Format property to a
numeric type (e.g. General Number.)

Then in your query, declare the parameter.
Choose Parameters on the Query menu.
Access opens a dialog.
Enter a row like this:
[forms]![frmSurvey]![Record #] Long Integer
Make sure the name entered here is exactly the same as in the Field row of
your query.

Finally, look at the data type of the field you are appending to. Open the
target table in design view. Select this field, and make sure its type is
Number.

If it still show incorrectly, the next step will be to ask Access how it
understands the data type of this field in the query, using the Immediate
Window (Ctrl+G) to examine the Type of the Field or Parameter in the
QueryDef.

 

[Steve in MN]

Thanks,

This was working during testing for the last 2 months, now it decided it
didn't like the format...weird.

I changed the parameter value in the query menu as suggested and made it
long integer (as is the table) and it worked.

Thanks.

From your description, it sounds like there is a problem with the data type
of this field.

Your append query is reading the value from the [Record #] text box on your
form. If this is an unbound text box, try setting its Format property to a
numeric type (e.g. General Number.)

Then in your query, declare the parameter.
Choose Parameters on the Query menu.
Access opens a dialog.
Enter a row like this:
[forms]![frmSurvey]![Record #] Long Integer
Make sure the name entered here is exactly the same as in the Field row of
your query.

Finally, look at the data type of the field you are appending to. Open the
target table in design view. Select this field, and make sure its type is
Number.

If it still show incorrectly, the next step will be to ask Access how it
understands the data type of this field in the query, using the Immediate
Window (Ctrl+G) to examine the Type of the Field or Parameter in the
QueryDef.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a form that has field Record # and once the record is established,
then an append query runs that appends a set of questions (survey) into
the
subform to answer. in the append query (which was working for 2 months) i
have RecordID:forms!frmSurvey![Record #] which takes the main form record
#
and puts it in with the subform records so they are linked. However now
for
some reason it was not appending, so i opened the append query while the
main
form was open to make sure that it was grabbing the record number from the
main form. The query is showing symbol or lower case "a" or lower case
"b"
or some other symbols.
The record number field is an autonumber set as integer and it displays in
the tables and on the main survey form, however now that this append query
is
trying to use it to append to another table, it doesn't recognize it or
display it properly.

any ideas?