ACC2000: Parameter Query Returns No Records with Replication ID Field

ID: Q210612

The information in this article applies to:
This article applies only to a Microsoft Access database (.mdb).

Moderate: Requires basic macro, coding, and interoperability skills.


SYMPTOMS

When you supply a GUID value as a criteria in a query field that is based on an AutoNumber field that is set to a Replication ID, no fields are returned. You might see this if you reference a form field that contains the Replication ID in the Criteria row of the query.


CAUSE

The Datasheet view of a table/query that contains a field of data type AutoNumber (with its FieldSize property set to ReplicationID) is returning/displaying the ReplicationID as type String, instead of a GUID (Byte array).


RESOLUTION

You can return the correct records by converting the GUID being supplied as a criteria to a string. The following demonstrates how to use the Parameters dialog box to force a form field reference to be converted to a string in the query criteria.

  1. Complete the steps in the "Steps to Reproduce Problem" section in the "More Information" section.


  2. Open the Query1 query in Design view.


  3. On the Query menu, click Parameters, and in the Parameter box, type the following:


    4. [Forms]![Form1]![Field1]
  4. In the Data Type box, enter Text, and then click OK.


  5. With the Form1 form open and displaying the first record, run the Query1 query.



STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.


MORE INFORMATION

The reference to a parameter (for example, a form's control) is returning the actual Byte array (GUID) value of the Replication ID instead of the structured text format of the GUID.

Steps to Reproduce Problem

  1. Open the sample database Northwind.mdb.


  2. Create the following new table:


    3. Field Name Data Type Field Size Indexed
    Field1 AutoNumber Replication ID No
    Field2 Text 50 No
  3. Close and save the table as Table1. Click No when prompted to create a primary key.


  4. Use the Autoform: Columnar Wizard to create a new form based on the Table1 table.


  5. Type some text in Field2 so that a new Replication ID is generated for this first record.


  6. On the Records menu, click Save Record (stay on this new record; do not move to a new, blank record).


  7. Save the form as Form1, but do not close the form.


  8. Create a new select query based on the Table1 table, setting it up as follows:


    9. Field: Field1 Field2
    Table: Table1 Table1
    Criteria: [Forms]![Form1]![Field1]
  9. Save the query as Query1, and then run the query. Note that no records are returned, even though the form's current record contains a Replication ID that exists in the table.



REFERENCES

For more information about Replication ID AutoNumbers, click Microsoft Access Help on the Help menu, type "Which type of AutoNumber field should I create?" in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Additional query words: 


Keywords          : kbdta QryParm 
Version           : WINDOWS:2000
Platform          : WINDOWS 
Issue type        : kbbug

Last Reviewed: May 13, 1999