Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2007
    Posts
    7

    Unanswered: locating specific files using a query

    Thanks to the help I have received in this forum, I have been able to restore several key reports and queries that were deleted by my predecesor on my job. I am now in the process of building a few more that I think will be helpful for tracking certain client data.

    I have built a database, a form for inputting the data, a query and a report to display this data. The thing I do not know how to do is to get the query to allow me to pull ONLY the individual report I am looking for.

    I have dozens of names in the database already. I want to look for ONE particular person using that persons Social Security Number as the criteria for the search (I chose that because is is obviously unique to each person).

    I go to the query, and I put the actual SS# in the criteria under "Social Security Number" field. When I run the report, however, the report prints every record in the database, not just the one I am looking for.

    I obviously missed a page when reading my "Access for Dummies"! Can anyone enlighten me as to where I am going wrong?

    Thanks.

    Mike

  2. #2
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Mike, could you post the SQL for your query, then we could maybe see what the problem is.

  3. #3
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    A general-purpose way of providing parameters to SQL queries (and maybe reports (I am not sure) is to append a WHERE CLAUSE equating a field in the database to a local variable. In the current case, one might say "WHERE SocialSecurityNo (or whatever the field name is) = SSNO (WHich is not a field and is not known to MS Acces). When you run the query, Access prompts for a value for SSNO and uses it to select the required data.
    Regards,
    Jim Wright.

  4. #4
    Join Date
    Mar 2007
    Posts
    7

    locating specific files using a query

    Vic,
    The SQL seems to be written properly:

    SELECT [Tracking Log].[SocialSecurityNumber], [Tracking Log].[FirstName], [Tracking Log].[LastName], [Tracking Log].[Tracked By]
    FROM [Tracking Log]
    WHERE ((([Tracking Log].[SocialSecurityNumber])=111-11-1111));


    When I enter a specific SS#, the query table shows only the specific record I am searching for. When I generate the report, however, rather than showing the one page I am requesting, it will print a page for EVERY record in my table.

    Mike

  5. #5
    Join Date
    Mar 2006
    Posts
    163
    Mike

    Is that the SQL the report is based on?

  6. #6
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Mike,
    WHERE ((([Tracking Log].[SocialSecurityNumber])=111-11-1111));
    It looks like what you need is single quotes around the Social Security Number (in this case 111-11-1111). At least it looks like the field is text, and without the single quotes, Access is not processing the SS number as text when you run the report. Could you show us the actual SQL that you have in place for the report? Please don't put the SSN into it, but do a straight copy from the SQL view of the query into the forum. Please place [ code] and [ /code] around the actual SQL statement to preserve all the formatting that is in the SQL statement. When you use the [code] and [ /code] you will need to take the space out. If I tried to show you without space, it would not show, but would consider the "and" as code. Without your actual SQL statement, without modifications, I can not show you want needs to be "fixed". I could discribe several possibilities, but there is nothing as good as seeing the "real" thing.
    BTW, is the Social Security Number defined in the table as text or number?

  7. #7
    Join Date
    Mar 2007
    Posts
    7
    Vic,

    This is the actual SQL copied and pasted from the query.


    Code:
    SELECT [Tracking Log].[SocialSecurityNumber], [Tracking Log].[FirstName], [Tracking Log].[LastName], [Tracking Log].[Tracked By]
    FROM [Tracking Log];
    The "SocialSecurityNumber" field is set up as a text field, but I tried putting in the quotes and get the same results.

    I also tried to run a query using "last name", but again, no good.

    Thanks again.

    Mike

  8. #8
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    I don't see a WHERE clause anywhere in this SQL. If this is the query from the report, that would explain why you are getting all the records. This query will give you all the records because there is no criteria to restrict any records.

  9. #9
    Join Date
    Mar 2007
    Posts
    7

    locating specific files using a query

    Vic,

    When I type the social security number into the criteria field on my query, the "where" starement does appear in the SQL statement.
    The report does, however, continue to print all of the records.


    Code:
    SELECT [Tracking Log].[SocialSecurityNumber], [Tracking Log].[FirstName], [Tracking Log].[LastName], [Tracking Log].[Tracked By]
    FROM [Tracking Log]
    WHERE ((([Tracking Log].[SocialSecurityNumber])="111-11-1111"));
    Mike

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •