How to return value that is not in combo box(form) but in database table into report

[jamesz]

Hi everyone, i have some problems.



At my form, there is a list of category code which i'm taken from middle of patient code(Mid([patient_cd],4,2). For example, category code is ST from 112STML(patient code). I use combo box for category code. If I want to see all patient records regardless of category code, definitely I just let combo box blank and straight away it will return all records. Unfortunately, it's not happen. Because there are some new data which does not relate with category code. For this situation, I'll return "Others" for data which have patient code that is not in category code. For example, I have 10 patient code, 8 of them are related to category code, but 2 of them that not,(112GTML and 112HLKP which GT and HL are new category ant not in category code that already exist. However, there is thousand of patient code which does not in category code that already exist.So, it is impossible to create new category code.

Code that i used:
Category code:Like [forms]![Patient].[ComboCtgory] & "*" And Mid([patient_cd],4,2).

Thanks.

[June7]

Why is it impossible to create new record in category code table?

[jamesz]

because we work with thousand of patient code with different category code...so,for me it's more easy if we just create "Others" for data that is not in category

[jamesz]

category code

[June7]

Still not understanding what the issue is. If you are using Mid function to pull category code from patient code, then you do have a source for all category codes. Use that as the RowSource for the combobox.
SELECT DISTINCT Mid([patient_cd],4,2) As CategoryCode FROM PatientTable;

So searching for all patient records with that category code should be simple. The criteria for the patient code field would be like:

LIKE "*" & [forms]![Patient].[ComboCtgory] & "*"

[jamesz]

thanks June7! it works! I'm just beginner in Access.

[jamesz]

I want to set patient code that are not in category code as "others" ,how?

I already put <>[TableName][FieldName]="OTHERS",but it not work.

Thanks.

[June7]

Review attached Access project for ideas. EDIT: Purpose served, file removed.

[jamesz]

Thanks June7!

[jamesz]

i have the other question, how to take value from report which is not in table before. I try use Count but it said it is too complex.

[June7]

I don't understand. Take what value to where? Used Count where? Need to show query or code or picture or attach project for analysis.

[jamesz]

Firstly, I group patient code using category code which Category_Code: IIf([patient].[patient_code]= Mid([patient_code],4,2),Mid([patient_code],4,2),"NOT_CATEGORY_CODE"). For example, I have DL,MH,JG which not in table and also not in query. Then i want to count how many patient code which each have DL,MH,JG. I use TOTAL:Count(Category_Code),it said it too complex.
Thanks.

[June7]

The IIf expression doesn't make sense. Patient codes are like '112GTML'. That will never = Mid([patient_code],4,2). Is each reference to [patient_code] the same field? That also doesn't make sense.

You tried to do the TOTAL in the query? Don't do in query. Do in textbox on report in a header/footer section.

I need your project to analysis this situation.

[jamesz]

Ok, based on PatientsCatCode(Report), I already Count for category code. However, I want to ask on how to calculate total count. For example, how to calculate total of MS or GT. I only manage to calculate count for each cateogory. Really thanks and appreciate for a great help.

[June7]

That Count expression in the query doesn't serve any purpose.

You need to create a group for the CategoryCode in the report. Then you can have a textbox in the group header/footer to calculate the count. Put the PatientID in the Detail Section. The result will organize the records by CategoryCode groups. The patients in each group will be listed with that group.

In the Group/Sorting designer at bottom of report, click 'Add a group', select CatCode from the list. Click 'More', select 'with no totals' and change to 'totals', select Count Type and show subtotal in group header/footer.

You might want to ungroup the controls in Page header and Detail sections so you can resize and arrange them. Select all the controls on form, right click > Layout > Remove.