IF Statement for checkboxes

[Ran]

Hello,



I wanted to include an IF statement into my mySQL statement to where if once checkbox "REDO" is checked it will uncheck two other check boxes called "CODED' and OLIED". These checkboxes havent been formated to Yes/No or True/False or On/Off, all three work. The reason for this is I cant account for my coworkers to be consistant when checking "REDO" they will uncheck "CODED" and "OLIED". If SQL statement needed, I can paste in.

Thank you ion advance.

[June7]

You are running an UPDATE sql in VBA? Post the complete SQL.

[Ran]

Hey June7, thanks for replying again. No UPDATE sql this time, that works fine for me now(thanks)! I have queries built for each coder by alpha split, which=6 queries. Each query has all the same data rows from HIM RAW DATA, but i have added Yes/No checkboxes to the end of my table, in which the queries have them too, and they go in the this process CODED or DO NOT BILL, OLIED and REDO, each time one is checked it removes it from their Alpha split and moves to the following queries CODED or DO NOT BILL, etc, but as they get to the last step, once the surgery is CODED and OLIED into our billing system, there can errors on the OLIE, that's when the coder must go back in and check the REDO checkbox, but I cant rely on them to uncheck CODED and OLIED once they check REDO, REDO moves it back to their 1st step by alpha split).

SELECT [HIM RAW DATA].Coder, [HIM RAW DATA].[Charge Status], [HIM RAW DATA].[RCA Edits], [HIM RAW DATA].[CM Edits], [HIM RAW DATA].[Research: Details], [HIM RAW DATA].[Charge ID], [HIM RAW DATA].[File ID], [HIM RAW DATA].FC, [HIM RAW DATA].[FC Groups], [HIM RAW DATA].[S ins], [HIM RAW DATA].[Payor Dtls], [HIM RAW DATA].[Pt Acct], [HIM RAW DATA].[MR No], [HIM RAW DATA].[Pt Last Name], [HIM RAW DATA].DOB, [HIM RAW DATA].Provider, [HIM RAW DATA].[Provider Name], [HIM RAW DATA].[Claim Info], [HIM RAW DATA].[Addl Description], [HIM RAW DATA].[Note Description], [HIM RAW DATA].Auth, [HIM RAW DATA].[PBS Flag], [HIM RAW DATA].[Dept / Spec], [HIM RAW DATA].Department, [HIM RAW DATA].[CM Mcal], [HIM RAW DATA].Inpt, [HIM RAW DATA].Started, [HIM RAW DATA].[Dif Add 1], [HIM RAW DATA].[Dif Add 2], [HIM RAW DATA].[RVU-RCA], [HIM RAW DATA].[CPT-RCA], [HIM RAW DATA].CPT, [HIM RAW DATA].[TM ID], [HIM RAW DATA].Units, [HIM RAW DATA].Mod1, [HIM RAW DATA].GP, [HIM RAW DATA].Mod2, [HIM RAW DATA].Mcal50, [HIM RAW DATA].Dx, [HIM RAW DATA].Dx2, [HIM RAW DATA].Dx3, [HIM RAW DATA].Dx4, [HIM RAW DATA].[CPT Description], [HIM RAW DATA].AssistNo, [HIM RAW DATA].AssistName, [HIM RAW DATA].AttendNo, [HIM RAW DATA].AttendName, [HIM RAW DATA].[CPT Type], [HIM RAW DATA].CODED, [HIM RAW DATA].OLIED, [HIM RAW DATA].[OLIE RPT ID], [HIM RAW DATA].[OLIED DATE], [HIM RAW DATA].REDO, [HIM RAW DATA].[DO NOT BILL]
FROM [HIM RAW DATA]
WHERE ((([HIM RAW DATA].[Pt Last Name]) Like "[R,S]*") AND (([HIM RAW DATA].CODED)<>0) AND (([HIM RAW DATA].OLIED)=Off) AND (([HIM RAW DATA].REDO)=Off) AND (([HIM RAW DATA].[DO NOT BILL])=Off));

[June7]

So CODED and OLIED fields must be unchecked if REDO is checked and checked if REDO is not? So maybe update them to the opposite of REDO. Under each CODED and OLIED on UpdateTo line: Not [REDO]

[Ran]

Yes correct CODED and OLIED fields must be unchecked if REDO is checked and checked if REDO is not.
So you are saying change the SELECT query to an UPDATE query and enter the Not [REDO] under CODED and OLIED fields?

[June7]

Sorry, my mixed signals. You are not running an UPDATE. The SQL statement is the RecordSource of a form?

Use AfterUpdate event of REDO checkbox to change value of the other two.

If you don't want users accidentally changing those two, better set Locked Yes and TabStop No.

[Ran]

No its just SELECT query, the reason for this process, is for my director to have a snapshot of the process from the charge/surgery going from their Alpha Split, then once they code the surgery it goes to CODED query because they checked CODED BOX, then once the charges have been billed they check the OLIED checkbox, then those charges go to an OLIED query to so my director can see what has been done, plus all these queries have to be updateable and enter data in certain fields cause charges can change.

Can the AfterUpdate work in a query not just a form? I have suggested forms lots of times and they won't work out of forms becausethere are thousand of lines of data per coder and a form won't let them see all charges associated with that specific patient.

[June7]

You want to give the director a 'snapshot' that shows the CODED and OLIED fields as not checked if REDO is checked, even if in the table they are checked? You don't want to actually change the value in table?

No, AfterUpdate is an event of forms and controls, not tables (unless you upgrade to Access 2010) and queries.

A form or report certainly can be structured to show all charges associated with a specific patient.

[Ran]

No REDO checkbox is made for charge errors by the billing system and need to be looked again in the database, so since the CODED and OLIED checkboxes have been checked already thoose charges are not in their query alpha split. I have queries built to give my director a snapshot of what is being processed but the coders/analyst are the ones going through a step by step process. First Alpha split, then the charge becomes CODED by checking the box or DO NOT BILL box which removes it from their alpha, then once the charge is OLIED, the coder checks the OLIED checkbox and that charge is done with.
I have subdatasheets in their alpha split linking child/master fields to show data from another table.
I just dont trust some of the analyst if the charge is in error and needs to be looked at again, by them going into thier own alpha OLIED/CODED queries they can check the REDO checkbox which then would remove the CODED and OLIED checkbox and that specific charge will go right to the beginning of their process ALPHA Split.
So if there is a sql IF statement that can be written to say when REDO is checked CODED and OLIED will be unchecked. ???
Thank you once again for being patient and understanding!

[June7]

Are the analysts viewing the data from a query or on a form? If they are viewing the query directly and checking REDO then there is no way to run code. If they view data on a form, use the AfterUpdate event of REDO to set the values of the other fields of the same record. No sql needed. Assuming the 3 fields are included in the form's RecordSource. These 3 fields are in the same table?

Me!CODED = Not Me.REDO
Me!OLIED = Not Me.REDO

[Ran]

Yeah I know they are making it tough on me, NO FORM. I had a form built for each analyst with a subform to confirm patient data by Alpha, but they didnt like it because they couldnt see all charges at the same time. A patient can be seen once or six times by a doctor on same day with all different procedures and different docotrs, so they need a view to see all charges, the form can only show one at a time, but as I have explained to them as a beginning Access developer forms make life so much easier. I will tell them I need 2010 access

[June7]

Can't get what you want without a form in 2007. 2010 has a new capability called Data Macros. They can be executed from a table. I haven't tried any yet so can't say would do what you want. They make the database incompatible with 2007.

Again, a form can be structured to show multiple records.

[Ran]

Yes I know I can't, and I have explained this to them many times.

So a form and not a subform can show multiple records in the same view?

[June7]

No, main form cannot be in Datasheet view. I thought you were talking about subforms showing only one record.

[Ran]

So you are saying I can have a form show multiple records for the same patient based off a query (query record source HIM RAW DATA) I built per alpha split and show that same patient data from another table (SIS RAW DATA)? something like master/child linked fields based off medical record # and date of service?