#  Other Applications & Softwares  > Access Tables & Databases >  > [SOLVED] Access 2010 criteria to seek last 4 digits of 6 digit number

## alcharbonneau

Hi

I am working with the Medicare Provider Number.  The number always consists of 6 digits.  The first two identify the State in which the provider is located.  The last 4 digits signify the type of facility.  Critical Access Providers, for example, all have a number with the last 4 digits ranging from 1300-1399. My question is can I add a criterion to an Access 2010 query that scans the provider numbers ranging from 000001-759999 and returns only those providers with the last four digits ranging from 1300-1399?

Thank you for taking the time to read/consider this request.

Al Charbonneau

----------


## tigeravatar

Al Charbonneau,




```
Please Login or Register  to view this content.
```

----------


## alcharbonneau

Hi

Thank you for responding to my query.  Unfortunately, I couldn't get it to work.  After entering the statement in the PRVDR_NUM column, I got the following message" check subquery's syntax and enclose the subquery in parentheses."  It is very possible that I did not (1) adequately describe what I am working with and (2) did not put the correct table names in that statement.  Here is what I did: 




```
Please Login or Register  to view this content.
```


I am working with two linked files: Hosp_2010_RPTa and Hosp_2010_NMRCa.  The value common to both is the record number.  The provider number (PRVDR_NUM) resides in the Hosp_2010_RPTa file.

Hope this helps you to help me.

Thanks again for your help.

Al Charbonneau

----------


## alansidman

What happens when you run the query.  Do you get an error message?  Erroneous data?  No data?  Help us to help you by giving us some information.

----------


## alcharbonneau

Thanks for responding.

I got the following error message "check subquery's syntax and enclose the subquery in parentheses" which did not allow me to run the query.  

Thanks

Al

----------


## alansidman

Al
Is this your total query, because the error message says you have a subquery, but I don't see one here




> "SELECT [Hosp_2010_RPTa].[PRVDR_NUM]
> FROM [Hosp_2010_RPTa]
> WHERE Right([Hosp_2010_RPTa].[PRVDR_NUM],4) Between 1300 And 1399;"

----------


## alcharbonneau

Yes.  That is the total guery.  Tigeravatar recommended it in the following generic form.  

SELECT [Table Name].[Provider Number]
FROM [Table Name]
WHERE Right([Table Name].[Provider Number],4) Between 1300 And 1399;

I filled in the tables guessing what needs to be in place.  My original question was posted at 4:50 PM today followed by Tigeravatar's recommendation at 5:37 PM.

Thanks for your interest.

Al

----------


## Norie

> Hi
> 
>  After entering the statement in the PRVDR_NUM column



That isn't where the query goes.

Try this.

1 Create a new query

2 Open it's SQL view.

3 Paste the query, with your changes, tigeravatar posted 

4 Run the query.

----------


## alcharbonneau

Hi

Thanks for responding.  Frankly, I don't like to leave things hanging but I don't know enough about Access to contribute much to solving my problem.  I tried the copy and paste into the SQL view in a new query.  I got an error message: characters found at the end of the SQL statement. Then I began to realize that I had not explained that I am trying to edit the data extracted from two linked files so I though I would paste the SQL statement for a query that I have (1) used to successfully data and (2) would like to edit.

SELECT Hosp_2010_NMRCa.RPT_REC_NUM, 
           Hosp_2010_RPTa.PRVDR_NUM, 
           Hosp_2010_NMRCa.WKSHT_CD, 
           Hosp_2010_NMRCa.LINE_NUM, 
           Hosp_2010_NMRCa.CLMN_NUM,
           Hosp_2010_NMRCa.ITM_VAL_NUM, 
           Hosp_2010_RPTa.FY_BGN_DT, 
           Hosp_2010_RPTa.FY_END_DT
FROM Hosp_2010_RPTa INNER JOIN Hosp_2010_NMRCa ON Hosp_2010_RPTa.RPT_REC_NUM = Hosp_2010_NMRCa.RPT_REC_NUM
WHERE (((Hosp_2010_NMRCa.WKSHT_CD)="S200000") AND ((Hosp_2010_NMRCa.LINE_NUM)="01900") AND ((Hosp_2010_NMRCa.CLMN_NUM)="0100"));

This query returns the requested information for 3000+ hospitals with 6 digit provider numbers ranging from 000001 - 759999.  I would like the query to retrieve the data for hospitals with provider numbers that have 1300-1399 as the last 4 digits (from the right).

Thanks for taking the time to weigh in on this solution.

I hope this helps to clarify my problem.

Al Charbonneau

----------


## Norie

Is that query returning all hospitals?

Does it do what you want if you change the WHERE clause to this.

WHERE (((Hosp_2010_NMRCa.WKSHT_CD)="S200000") 
    AND ((Hosp_2010_NMRCa.LINE_NUM)="01900") 
    AND ((Hosp_2010_NMRCa.CLMN_NUM)="0100")) 
    AND Right([Hosp_2010_RPTa].[PRVDR_NUM],4) Between 1300 And 1399;

----------


## alcharbonneau

Bingo.  Worked perfectly.  

Thank you very much.

Al

----------

