query formula

[bwlytkr]

Would someone please help me convert this excel formula to use in a query. Below is the info. Any help would be appreciated. Thanks

=IF(AND(H2="YES"),IF(AV2>=$BK$2,"YES","NO"),IF(H2="NO","NO"))

The name of this column will be PSC Ticket. H2= LU company column, AV2 = the NTG Start Date Column , and BK2 = a finite date of 6/29/21.

NTG Start Date Column looks like this in the query / LU Company Column Look Like this in the query

Field: NextToGoStartDate / LU Company?: IIf(Left([NJUNS MEMBER]![NAME],4)="Gulf","Yes","No")
Table: ALFN NTG / This field is blank

 

[bwlytkr]

Not sure where that access formula came from. Sorry for the confusion.

=IF(AND(H2="YES"),IF(AV2>=$BK$2,"YES","NO"),IF(H2="NO","NO"))

The name of this column will be PSC Ticket. H2= LU company column, AV2 = the NTG Start Date Column , and BK2 = a finite date of 6/29/21.

NTG Start Date Column looks like this in the query / LU Company Column Look Like this in the query

Field: NextToGoStartDate / LU Company?:
Table: ALFN NTG / This field is blank

 

[JonXL]

How are you handling the absolute reference $BK$2 in your table?

And what are your field names?

 

[bwlytkr]

How are you handling the absolute reference $BK$2 in your table?

And what are your field names?

Field names and table names are at the bottom of the message. Mextogostartdate is one and Lu Company? Is the other. Table names are below those. The date is in cell nw2 of the excel spreadsheet.

 

[JonXL]

Well then it's mostly a simple replacement with the consideration that Access has IIF() instead of IF() and requires # # around literal date values.

IIF([LU company]="YES",IIF([NTG Start Date ]>= # 6/29/21 # ,"YES","NO"),IIF([LU company]="NO","NO"))

 

[bwlytkr]

Formula works great!! Thanks for the help..