Missing records (1 Viewer)

[John Sh]

I have a smallish, 12k records, table which is controlled by a fairly basic query. Some records consistently do not appear in the resulting form.
It is always the same records, regardless of the search criteria. These records always appear when the form is populated by the table and not the query. Obviously I have checked, and double checked for inconsistencies in the data without success.
Any suggestions gratefully accepted and much anticipated.

 

[Isaac]

The first step would be to post the SQL of the query which sometimes returns results different than the table so that we can have a chance to speculate on what the problem might be..

 

[The_Doc_Man]

Invariably, in the case you defined (direct use of table works, query does not), there is something in the query that excludes some records. This can be either in a WHERE clause or perhaps a SELECT DISTINCT clause. That would be the first two places to look.

 

[John Sh]

Thank you both for quick replies. Below is the sql code.
I know it's bad practise but some field names do have spaces. I inherited this DB and am in the process of fixing it and writing, learning, vba code to get operators away from the raw table. All fields in the table are text except for a few date and number fields. The number fields are either double or integer.

SELECT [All].AccessionNumber, [All].Family, [All].Suprafamily, [All].Genus, [All].[Species epithet], [All].[Coll Date], [All].Collector, [All].BoxNo, [All].Phenology, [All].Locality, [All].[Create date], [All].CreatedBy, [All].[Infra family], [All].[Create institution], [All].Notes, [All].Comments, [All].[Private comments], [All].BoxedAsCollection, [All].Record, [All].Herbarium, [All].Specimen, [All].Review, [All].[Species author], [All].[Infra rank], [All].[Infra name], [All].[Infra author], [All].PriorName, [All].InAustralia, [All].VerificationLevel, [All].Determinavit, [All].Identifier, [All].Confirmavit, [All].[Taxon authority], [All].[Ident date], [All].[Botanical country], [All].Region, [All].[Lat deg], [All].[Lat min], [All].[Lat sec], [All].[Lat direction], [All].[Long deg], [All].[Long min], [All].[Long sec], [All].[Long direction], [All].[Grid ref], [All].Elevation, [All].Habitat, [All].Geology, [All].Aspect, [All].Substrate, [All].Soils, [All].Vegetation, [All].[Associated species], [All].[Coll team], [All].CollectorNo, [All].[Coll date 2], [All].KindOfCollection, [All].ProvenanceType, [All].Donor, [All].PlantDescription, [All].Repstate, [All].[Flower colour], [All].[Non comput], [All].ConservationStatus, [All].[Con status NSWNPWS], [All].[Duplicates to], [All].[Updated by], [All].[Update Date], [All].[Update institution], [All].Status, [All].[Bay No], [All].NameStatus, [All].[Intergeneric hybrid], [All].[Cultivar name], [All].WeedStatus, [All].[Study site]
FROM [All]
WHERE ((([All].Family) Like [Forms]![Multi-search]![cboFamily] & "*") AND (([All].Genus) Like [Forms]![Multi-search]![cboGenus] & "*") AND (([All].[Species epithet]) Like [Forms]![Multi-search]![Species] & "*") AND (([All].[Coll Date]) Like "*" & [Forms]![Multi-search]![Collected] & "*") AND (([All].Collector) Like "*" & [Forms]![Multi-search]![Collector] & "*") AND (([All].Locality) Like "*" & [Forms]![Multi-search]![Locality] & "*"))
ORDER BY [All].AccessionNumber;

 

[John Sh]

Just to clarify.
The operator has the option of sorting the data on one, any mix, or all of six criteria. The system works fine except for the odd missing record. Most notable is the first record when indexed on the primary field. This field is the only double and the first number is 100. It is never shown in this form.

 

[arnelgp]

what is the primary key field type again? double?
it should be change to Long Integer.

btw, Not Having Space on the fieldname is the acceptable way. not the other way around.

 

[John Sh]

Sorry, it's not the primary KEY field and is a double because it has decimal points. It is the main index field and all data is tied back to this field. It is automatically calculated and cannot be duplicated.

"btw, Not Having Space on the fieldname is the acceptable way. not the other way around"
As I said, I know it's bad practise.

 

[arnelgp]

I know it's bad practise but some field names do have spaces

maybe wrong interpretation.
you said all records shows when you use the table?
maybe you can build the Filter through VBA.

 

[The_Doc_Man]

The reason you are missing records when using the query is that you have AND conjunctions in the WHERE clause. That means that if even ONE of those ANDed conditions is FALSE, the record will be excluded.

Just as a tip to save you typing, you have qualified all of your SELECT and WHERE elements with [All]. as a prefix, but you have only [All] in the FROM clause, no other tables or other sources. Since there is only one source, you don't need to use a prefix as a qualifier. You can omit it with no loss of functionality and a LOT less typing. I.e. since there is only one source, Access will KNOW that all of the fields have to come from there without you having to tell it.

 

[bastanu]

Is it possible that the missing records have at least one of the 6 filter fields empty (Is Null)?

 

[arnelgp]

possible that the missing records have at least one of the 6 filter fields empty (Is Null)?

possible so you need to modify the criteria:

WHERE (((Nz([All].Family, "*")) Like [Forms]![Multi-search]![cboFamily] & "*") AND ((Nz([All].Genus, "*")) Like [Forms]![Multi-search]![cboGenus] & "*") AND ((Nz([All].[Species epithet], "*")) Like [Forms]![Multi-search]![Species] & "*") AND ((Nz([All].[Coll Date], "*")) Like "*" & [Forms]![Multi-search]![Collected] & "*") AND ((Nz([All].Collector, "*")) Like "*" & [Forms]![Multi-search]![Collector] & "*") AND ((Nz([All].Locality, "*")) Like "*" & [Forms]![Multi-search]![Locality] & "*"))
ORDER BY [All].AccessionNumber;

 

[Isaac]

The reason you are missing records when using the query is that you have AND conjunctions in the WHERE clause. That means that if even ONE of those ANDed conditions is FALSE, the record will be excluded.

Just as a tip to save you typing, you have qualified all of your SELECT and WHERE elements with [All]. as a prefix, but you have only [All] in the FROM clause, no other tables or other sources. Since there is only one source, you don't need to use a prefix as a qualifier. You can omit it with no loss of functionality and a LOT less typing. I.e. since there is only one source, Access will KNOW that all of the fields have to come from there without you having to tell it.

Nothing super important here, but I think the reason many posters end up with this SQL in their AWF posts is simply because Access - among many other superfluous things it does, like frenetically adding parenthesis - adds all this.