Query Data Incorrect

[toppock]

Hi all,

I have a database which acts as a project tracker for jobs at the company I work at.

The issue in question regards a query, which forms the basis of a report. The report is "Active Jobs" and will show all the jobs with the status "Active". It has been very robust and has not let me down, until now. There is one record which was entered in the normal way, but does not appear when the query is executed. It appears in the "All Jobs" query/report, whereby the criteria "Active" is removed.

The user who entered the record was working from home and using the company VPN, although I don't see why this would be an issue as everyone is accessing and writing to the same back end file.

It is only the one record and it is driving me insane!! Any help would be appreciated. Thanks in advance.

 

[pr2-eugin]

What is the Normal way? You mean written directly by accessing the table?

Is there any relationship between the table(s) is question?

 

[plog]

Not to be too condescending, but its not meeting the criteria of your query. For lack of better terms, a query can have explicit and implicit criteria. Explicit criteria is anything you state as criteria in your WHERE clause (i.e. [Status]='Active'). Implicit criteria occurs when you join tables. If you use an INNER JOIN on two tables on their ID fields and one table doesn't have that ID, the query will not return a result for that ID.

I would make a copy of your query and start ripping it apart until I could get the record that should show in it to show. First, check for explicit criteria--Is [Status]='Active' the only criteria in your query? Are you sure there isn't another bit of criteria way off to the right in Design View that you forgot about? Remove all explicit criteria and see if your record appears. If it does, start adding it back one at a time to see which criterion makes if fail. If it doesn't appear when you remove all criteria, then you have a failure in an underlying query and you should use this process on the underlying query.

If that doesn't fix it, start deleting tables from the query that are joined to the table that holds the record. If it appears after you take a joined table out, then that means however that last table was linked, it doesn't contain a value that matches.

 

[Daz]

I had a similar problem and after messing with the query and wasting a lot of time I found the problem was in the data itself. There was an extra space at the end of the data in the field. Maybe an easy solution. Depending on how much time you have in investigating another alternative is recreate the record

 

[MSAccessRookie]

I had a similar problem and after messing with the query and wasting a lot of time I found the problem was in the data itself. There was an extra space at the end of the data in the field. Maybe an easy solution. Depending on how much time you have in investigating another alternative is recreate the record

It appears as if toppock (or at least the user) has uncovered a possible Design shortcoming. While your approach would most likely eliminate the problem that occurred, it would also have the effect of covering it up, and the possibility that the problem could return would remain.

toppock should take time to investigate the cause. If the issue is the data, then Forms or Queries can be redesigned, or user procedures can be modified to accomodate the situation.