Access Formula Question

[bwlytkr]

I created the formula below to return a YES when the ticket is date is > 9/30/2024 and the LU Company is YES G and Job Type name is a Transfer. However, I need to add something to this. I need to exclude any tickets in PXFR NTG column name TicketType and a TRANSFER in this column. Is there a way to add this function to the formula below? I never tried to exclude something from a column before in a formula. Thanks, John

PSC Ticket: IIf([LU Company]="YES G",IIf([PXFR NTG]![JobTypeName]="Transfer",IIf([PXFR NTG]![NextToGoStartDate]>=#09/30/2024#,"YES","High Priority")

 

[welshgasman]

Why not just use ANDs ?

 

[bwlytkr]

Because I am not sure how to write the formula to exclude the word transfer"TRANSFER" in the Ticket Type column. Any help writing a formula to exclude something in a column would be greatly appreciated. Thx

 

[bwlytkr]

I figured out how to exclude a text in a column however, now I can't seem to get the query to recognize the last part of this formula, IIf([LU Company]="YES NCSC",IIf([PXFR NTG]![NextToGoStartDate]>=#12/23/23#,"YES"). I have tried comma, and, or, and ) after the "YES" and the expressions prior to this work fine. I just don't get any results from this expression. Below is the complete formula. Any help would be appreciated.

PSC Ticket: IIf([LU Company]="YES G",IIf([PXFR NTG]![JobTypeName]="TRANSFER",IIf([PXFR NTG]![TicketTypeName]<>"Make Ready",IIf([PXFR NTG]![NextToGoStartDate]>=#01/01/2024#,"YES",
IIf([LU Company]="YES NCSC",IIf([PXFR NTG]![NextToGoStartDate]>=#12/23/23#,"YES"))))))

 

[Micron]

That is getting too complicated to maintain as you can see. It might get worked out, then at some point you'll need to add or modify criteria and will be back to zero. You don't say where you are using that, so maybe it's a form or report field but could be a query.

Why not call a vba function from your query (or control), then you can return just about anything you want from that? Or maybe create a query that returns the result you need and use that as a query field with appropriate criteria on it ( Is Not Null )?

 

[welshgasman]

I was gong to suggest your own function next as well.

 

[Micron]

Sorry, I missed the part where it was stated that this is being done in a query.

 

[bwlytkr]

thanks for all the info. I have no clue about vba is why I have not used it.

 

[bwlytkr]

thanks for all the info. I have no clue about vba is why I have not used it.

I was hoping I didn't have to separate the two expressions into 2 coulumns

 

[Micron]

No time like the present to start learning vba. Some people google for code samples, some use AI. Some get code from forums if they can provide enough supporting information.