Inequality signs in the WHERE clause - Undesired Results 3

[IngDep]

Have the following query that is just not extracting all persons who are over 17 years of age as desired.

Some of the records in the query result have persons who are less than 18!

Any ideas as to the solution?

Thanks.

SELECT [tblCensus-Centerville].[Facility ID]
, [tblCensus-Centerville].[Facility Name]
, [tblCensus-Centerville].[Admit Date]
, [tblCensus-Centerville].[COB TPL Ind], [tblCensus-Centerville].[Member ], [tblCensus-Centerville].[Institution #], [tblCensus-Centerville].[Eff Date], [tblCensus-Centerville].[Patient Name], [tblCensus-Centerville].DOB, [tblCensus-Centerville].Age, [tblCensus-Centerville].Group, [tblCensus-Centerville].[Sub-Group], [tblCensus-Centerville].Class, [tblCensus-Centerville].[Plan ID],
[tblCensus-Centerville].[Plan Desc], [tblCensus-Centerville].[Ref ID], [tblCensus-Centerville].[Room Type], [tblCensus-Centerville].[Room Type Start Date], [tblCensus-Centerville].[Room Type End Date], tblBedType.BED_TYPE_DESC, [tblCensus-Centerville].[Admitting Diagnosis], [tblCensus-Centerville].[Att Phys ID], [tblCensus-Centerville].[Att Phys (Phone)], [tblCensus-Centerville].[PCP ID], [tblCensus-Centerville].PCP, [tblCensus-Centerville].[Prior Admits], [tblCensus-Centerville].[Prior Date], [tblCensus-Centerville].[Open CM Diag], [tblCensus-Centerville].[Open CM Pri User], [tblCensus-Centerville].[Nurse Reviewer], tblUSERS.USUS_DESC, IIf([SITE_ID] Is Null,"44",IIf([SITE_ID]="15","00",[SITE_ID])) AS AltSiteID, tblSITE_DESCR.SITE_NAME, tblSITE_DESCR.AREA_NAME, tblSITE_DESCR.ALT_AREA_NAME, [tblCensus-Centerville].[HCIA LOS], [tblCensus-Centerville].[10 mo LOS], [tblCensus-Centerville].[Req LOS], [tblCensus-Centerville].[Auth LOS], [tblCensus-Centerville].[Next Review], [tblCensus-Centerville].[Exp DC], [tblCensus-Centerville].[Surgical Procedure], [tblCensus-Centerville].[Surgical Proc Date], qryStopLoss_2.EstCharges, qryStopLoss_2.Threshold, qryComorbid_2.CCS_Code, qryComorbid_2.CCS_Description, qryComorbid_2.CCS_Group, tblRiskRank.RiskRank
FROM (((([tblCensus-Centerville] LEFT JOIN (tblUSERS LEFT JOIN tblSITE_DESCR ON tblUSERS.USUS_SITE_CODE = tblSITE_DESCR.SITE_ID) ON [tblCensus-Centerville].[Nurse Reviewer] = tblUSERS.USUS_ID) LEFT JOIN tblBedType ON [tblCensus-Centerville].[Room Type] = tblBedType.BED_TYPE_CD) LEFT JOIN qryStopLoss_2 ON ([tblCensus-Centerville].[Ref ID] = qryStopLoss_2.[Ref ID]) AND ([tblCensus-Centerville].[Facility ID] = qryStopLoss_2.[Facility ID])) LEFT JOIN tblRiskRank ON [tblCensus-Centerville].[Institution #] = tblRiskRank.MBR_IDENT_NBR) LEFT JOIN qryComorbid_2 ON ([tblCensus-Centerville].[Ref ID] = qryComorbid_2.[Ref ID]) AND ([tblCensus-Centerville].[Room Type End Date] = qryComorbid_2.LastRoomTypeDate)

WHERE ((([tblCensus-Centerville].[Member ])<>"") AND (([tblCensus-Centerville].Age)>="18") AND (([tblCensus-Centerville].[Plan Desc]) Not Like "*SRP*"));

 

[jrbarnett]

Is the Age field numeric or string in the database?

John

 

[dhookom]

In addition to John's question/comment, I'm not sure about the reliability of
[tt][blue]
([tblCensus-Centerville].[Member ])<>""
[/blue][/tt]
Most Access tables don't allow storing a zero-length-string. I believe a better criteria would be
[tt][blue]
([tblCensus-Centerville].[Member ]) Is Not Null
[/blue][/tt]
or
[tt][blue]
([tblCensus-Centerville].[Member ] & "" )<>""
[/blue][/tt]



Duane MS Access MVP

 

[IngDep]

I forgot to mention that "tblCensus-Centerville" is a linked MS Excel table.

On the Excel file, the format of the "Member" field is General and the format of the "Age" field is Number.

 

[dhookom]

If Age is numeric then change your criteria to:

   (([tblCensus-Centerville].Age)>=18)

Duane MS Access MVP

 

[IngDep]

Tried the suggestion and receive a data type mismatch error.

When I sort the query results by age in ascending order, "18" is the age of the person on the first record. Records with an age of "2" are between "19" and "20."

Still trying to resolve...

 

[dhookom]

I guess that is an issue with using Excel for database storage. Is every value in the Age column numeric? You may need to try convert the Age to a number with:

([b][red]Val[/red][/b]([tblCensus-Centerville].Age)>=18)

Duane MS Access MVP

 

[PHV]

I wonder why storing a constantly changing value like age instead of the birthday's date ...

 

[IngDep]

Upon viewing all of the fields in the linked Excel table within Access, I discover that almost all of them are in the text format.

Appears that linking to Excel results in quite a few data type conversion issues.

I will try including "Val" in the sql script for the query.

If this does not work, maybe will try converting the Excel file to CSV format and then linking to Access.

The Excel file is modified and linked to the Access database daily! Therefore, ideally, it would be beneficial to have a method to automate an import into an Access table every day.

Any additional advice is appreciated.

 

[dhookom]

I generally import the Excel file into a temporary table and then udpate and/or append to permanent tables.

Duane MS Access MVP

 

[IngDep]

The addition of "Val" and elimination of the quotes around "18" worked!

Thanks.