Access 2010 criteria to seek last 4 digits of 6 digit number

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

Originally Posted by alcharbonneau

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.

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

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

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

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;

Bingo. Worked perfectly.

Thank you very much.

Al