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;