Criteria Function Not Working

W

WaterLover

New member
Local time
Yesterday, 16:37
Joined
Jan 26, 2020
Messages
23
I have the following saved Query:

SELECT [Premium History Data].Premium
FROM [Premium History Data]
WHERE ((([Premium History Data].[Pay Period])=GetPriorPayPeriod('County','Selected')))

I've stepped through GetPriorPayPeriod WHILE running this query and it returns the correct result (in this case, "2019-26"), however, the Query operates as though it returned "". When I'm working with OTHER pay periods, the Query operates correctly.

Anybody have any ideas?
 
I'd hard code that value and see what that produces.?
I assume pay periods for that value exist.?
 
Gasman said:
I'd hard code that value and see what that produces.?
I assume pay periods for that value exist.?
Click to expand...
I have done that and the Query works properly. It's only with this Pay Period that it's happening. Yes, values for the Pay Period do exist.
 
Can you upload a stripped down version of your DB,?
Zip it if too large. Not sure what the new software limit is at present.?
 
Gasman said:
Can you upload a stripped down version of your DB,?
Click to expand...
No, but I can tell you this: [Premium History Data] is a linked table from one database and GetPriorPayPeriod accesses [Admin Data] which is a linked table from another database. I've tried making [Admin Data] a native table to the front end database and it's still not working. And, I've had no issue with this until now. When I'm working with other Pay Periods, the Query gets the proper value from GetPriorPayPeriod.
 
Clutching at straws at present from what you have presented.:(

How does the function determine the correct data with values of "County" and "Selected" ?

Can you at least post the code for the function within code tags?
 
Gasman said:
Clutching at straws at present from what you have presented.:(

How does the function determine the correct data with values of "County" and "Selected" ?

Can you at least post the code for the function within code tags?
Click to expand...

Here you go...

Code: 
Public Function GetPriorPayPeriod(SourceStr As String, PayPeriodType As String) As String

    'SourceStr: 'County', 'Aflac' or 'AflacBillPayment'
    'PayPeriodType: 'Current', 'Selected'
   
    Dim CurrentPayPeriodStr As String
    Dim LastPayPeriodOfYear As String
   
    If SourceStr = "County" Then
   
        LastPayPeriodOfYear = MaxCountyPayPeriod
       
    Else
   
        LastPayPeriodOfYear = MaxAflacPayPeriod
       
    End If
   
    CurrentPayPeriodStr = GetPayPeriod(SourceStr, PayPeriodType)

    If Right(CurrentPayPeriodStr, 2) = "01" Then
   
        GetPriorPayPeriod = Str(Val(Left(CurrentPayPeriodStr, 4)) - 1) & "-" & LastPayPeriodOfYear
       
    Else
   
        GetPriorPayPeriod = Left(CurrentPayPeriodStr, 4) & "-" & Format(Val(Right(CurrentPayPeriodStr, 2)) - 1, "00")
       
    End If
   
End Function

To explain, the Pay Periods are in yyyy-pp format where pp is a two digit pay period (i.e., "01", "02"..."26"). Here is the code for GetPayPeriod (it also is working properly):

Code: 
Public Function GetPayPeriod(SourceStr As String, PayPeriodType As String) As String

    'SourceStr: 'County', 'Aflac' or 'AflacBillPayment'
    
    'PayPeriodType: 'Current' or 'Selected'

    Set rstTemp = dbs.OpenRecordset("Admin")
    
    With rstTemp
    
        If SourceStr = "County" Then
        
            If PayPeriodType = "Selected" Then
            
                If IsNull(![Selected Pay Period Year]) Or IsNull(![Selected Pay Period]) Then
                
                    GetPayPeriod = ""
                    
                Else
                
                    GetPayPeriod = ![Selected Pay Period Year] & "-" & ![Selected Pay Period]
                    
                End If
                
            Else
        
                If IsNull(![Pay Period Year]) Or IsNull(![Pay Period]) Then
                
                    GetPayPeriod = ""
                    
                Else
                
                    GetPayPeriod = ![Pay Period Year] & "-" & ![Pay Period]
                    
                End If
                
            End If
            
        ElseIf SourceStr = "Aflac" Then
        
            If PayPeriodType = "Selected" Then
            
                If IsNull(![Selected Billing Period Year]) Or IsNull(![Selected Billing Period]) Then
                
                    GetPayPeriod = ""
                    
                Else
                
                    GetPayPeriod = ![Selected Billing Period Year] & "-" & ![Selected Billing Period]
                    
                End If
                
            Else
        
                If IsNull(![Billing Period Year]) Or IsNull(![Billing Period]) Then
                
                    GetPayPeriod = ""
                    
                Else
                
                    GetPayPeriod = ![Billing Period Year] & "-" & ![Billing Period]
                    
                End If
                
            End If
            
        Else
        
            'SourceStr = 'AflacBillPayment'
            
            If PayPeriodType = "Selected" Then
            
                If IsNull(![Selected Billing Period Paid Year]) Or IsNull(![Selected Billing Period Paid]) Then
                
                    GetPayPeriod = ""
                    
                Else
                
                    GetPayPeriod = ![Selected Billing Period Paid Year] & "-" & ![Selected Billing Period Paid]
                    
                End If
                
            Else
            
                If IsNull(![Billing Period Paid Year]) Or IsNull(![Billing Period Paid]) Then
                
                    GetPayPeriod = ""
                    
                Else
                
                    GetPayPeriod = ![Billing Period Paid Year] & "-" & ![Billing Period Paid]
                    
                End If
                
            End If
            
        End If
        
        .Close
        
    End With

End Function
 
WaterLover said:
Here you go...

Code: 
Public Function GetPriorPayPeriod(SourceStr As String, PayPeriodType As String) As String

    'SourceStr: 'County', 'Aflac' or 'AflacBillPayment'
    'PayPeriodType: 'Current', 'Selected'
  
    Dim CurrentPayPeriodStr As String
    Dim LastPayPeriodOfYear As String
  
    If SourceStr = "County" Then
  
        LastPayPeriodOfYear = MaxCountyPayPeriod
      
    Else
  
        LastPayPeriodOfYear = MaxAflacPayPeriod
      
    End If
  
    CurrentPayPeriodStr = GetPayPeriod(SourceStr, PayPeriodType)

    If Right(CurrentPayPeriodStr, 2) = "01" Then
  
        GetPriorPayPeriod = Str(Val(Left(CurrentPayPeriodStr, 4)) - 1) & "-" & LastPayPeriodOfYear
      
    Else
  
        GetPriorPayPeriod = Left(CurrentPayPeriodStr, 4) & "-" & Format(Val(Right(CurrentPayPeriodStr, 2)) - 1, "00")
      
    End If
  
End Function

To explain, the Pay Periods are in yyyy-pp format where pp is a two digit pay period (i.e., "01", "02"..."26"). Here is the code for GetPayPeriod (it also is working properly):

Code: 
Public Function GetPayPeriod(SourceStr As String, PayPeriodType As String) As String

    'SourceStr: 'County', 'Aflac' or 'AflacBillPayment'
   
    'PayPeriodType: 'Current' or 'Selected'

    Set rstTemp = dbs.OpenRecordset("Admin")
   
    With rstTemp
   
        If SourceStr = "County" Then
       
            If PayPeriodType = "Selected" Then
           
                If IsNull(![Selected Pay Period Year]) Or IsNull(![Selected Pay Period]) Then
               
                    GetPayPeriod = ""
                   
                Else
               
                    GetPayPeriod = ![Selected Pay Period Year] & "-" & ![Selected Pay Period]
                   
                End If
               
            Else
       
                If IsNull(![Pay Period Year]) Or IsNull(![Pay Period]) Then
               
                    GetPayPeriod = ""
                   
                Else
               
                    GetPayPeriod = ![Pay Period Year] & "-" & ![Pay Period]
                   
                End If
               
            End If
           
        ElseIf SourceStr = "Aflac" Then
       
            If PayPeriodType = "Selected" Then
           
                If IsNull(![Selected Billing Period Year]) Or IsNull(![Selected Billing Period]) Then
               
                    GetPayPeriod = ""
                   
                Else
               
                    GetPayPeriod = ![Selected Billing Period Year] & "-" & ![Selected Billing Period]
                   
                End If
               
            Else
       
                If IsNull(![Billing Period Year]) Or IsNull(![Billing Period]) Then
               
                    GetPayPeriod = ""
                   
                Else
               
                    GetPayPeriod = ![Billing Period Year] & "-" & ![Billing Period]
                   
                End If
               
            End If
           
        Else
       
            'SourceStr = 'AflacBillPayment'
           
            If PayPeriodType = "Selected" Then
           
                If IsNull(![Selected Billing Period Paid Year]) Or IsNull(![Selected Billing Period Paid]) Then
               
                    GetPayPeriod = ""
                   
                Else
               
                    GetPayPeriod = ![Selected Billing Period Paid Year] & "-" & ![Selected Billing Period Paid]
                   
                End If
               
            Else
           
                If IsNull(![Billing Period Paid Year]) Or IsNull(![Billing Period Paid]) Then
               
                    GetPayPeriod = ""
                   
                Else
               
                    GetPayPeriod = ![Billing Period Paid Year] & "-" & ![Billing Period Paid]
                   
                End If
               
            End If
           
        End If
       
        .Close
       
    End With

End Function

In the GetPayPeriod code above, ![Selected Pay Period Year] = "2020" and ![Selected Pay Period] = "01"
Click to expand...
 
Gasman said:
Nope, I have nothing. :)
Click to expand...

Same here! I just don't get it. This Query is working properly for Pay Periods before AND after this one Pay Period. And, I've stepped through GetPriorPayPeriod while running this Query and it IS returning the correct value ("2019-26") but the Query's not getting it.
 
So if you changed the function to return "2018-26" by using -2 instead of -1 that would produce data?
 
I haven't tried that. I just hard coded "2019-26" for that one instance to get the Query to work properly and then changed it back to include the GetPriorPayPeriod function (which is working properly for Pay Periods AFTER the one with the issue). It's just weird and I'm wondering if there's an error with Access.
 
I'm stumped, unless there is an errant space somewhere.?
How can you have a pay periods after 2019-26 if there are only 26 periods in the year and we are only on 2020-01 now?
 
WaterLover said:
I haven't tried that. I just hard coded "2019-26" for that one instance to get the Query to work properly and then changed it back to include the GetPriorPayPeriod function (which is working properly for Pay Periods AFTER the one with the issue). It's just weird and I'm wondering if there's an error with Access.
Click to expand...
Try a decompile/compile if you think that.
Allso try creating a new DB and import relevant objects and test in that?
 
When it works for other pay periods and balks at that one, the problem is less likely to be a code error and more likely to be a data error. I.e. code is predictable. Given the same kind of data, it should always do the same kind of thing. Give it something different, it will DO something different. Perhaps the problem is that somewhere in the range you are checking there is a data element with a hidden character that is bollixing up the works.

I remember having a null entry in a check record system I did (for myself) once. I couldn't figure out why things would not balance - but once I looked at the raw table in that area (no filters), I found an incompletely formed record that I had fat-fingered during data entry and my (at the time naive) code didn't catch it. Fixed the bad record, fixed the problem. Not saying your problem is nulls, because it could be some other character. But manually check your data in the area of the miscreant pay period.
 
Gasman said:
Try a decompile/compile if you think that.
Allso try creating a new DB and import relevant objects and test in that?
Click to expand...
I've thought about doing this but hesitate because Access doesn't import form Themes very well and I've got to redo them all. I'll give it a try if all else fails.

Thanks for all your help.
 
The_Doc_Man said:
When it works for other pay periods and balks at that one, the problem is less likely to be a code error and more likely to be a data error. I.e. code is predictable. Given the same kind of data, it should always do the same kind of thing. Give it something different, it will DO something different. Perhaps the problem is that somewhere in the range you are checking there is a data element with a hidden character that is bollixing up the works.

I remember having a null entry in a check record system I did (for myself) once. I couldn't figure out why things would not balance - but once I looked at the raw table in that area (no filters), I found an incompletely formed record that I had fat-fingered during data entry and my (at the time naive) code didn't catch it. Fixed the bad record, fixed the problem. Not saying your problem is nulls, because it could be some other character. But manually check your data in the area of the miscreant pay period.
Click to expand...

I've run into this issue too so I know what you're talking about. What I'm confused about is:

1. When I step through GetPriorPayPeriod while running the Query, it DOES return the correct value! It's just that the Query's not getting it. And,

2. When I hard code "2019-26" as the criteria in place of GetPriorPayPeriod, the Query works properly.

So, I don't think it's my code or data. I think there may be an error in Access. Do you have any ideas about this?
 
Create a string variable to hold the returned value, debug.print that and it's length?
Use that string variable for comparison

How you determine the query is not getting the value?
 
I'll do what you suggest.

I stepped through the function and determined that the correct value was returned. I included the Query's field in my results and it's "" or Null or Blank and the Query does not return any records (whereas, it returns all the proper records when I hard code "2019-26" in the criteria instead of the function).
 
OK, just a crazy thought, but ... where are your subroutines defined? In the class module or in a general module?

IF the answer is "class module" then move them to a general module. If they are already in a general module then you've got me stumped.

Acting like your sub returned "" - yet you can see via debugging that it is returning something else - means you have a scoping problem of some sort, hence my comment about where the code is defined. You see, your saved query runs in the context of ACE (or JET, if this is a much older version of Access) but subs and functions run in the Access interface environment. So there is an implied barrier to be crossed. There are those who would say that even a publicly declared function in a class module should be usable in a query. I've been leery of such claims, but if it is not too hard to test that by moving your code, I'd say to do the experiment. But if the code doesn't need to be moved 'cause it is already in a general module, then I don't know what to suggest.
 
You must log in or register to reply here.

Similar threads

D
Solved to return a ID value if criteria is met
2
Replies
32
Views
634
Gasman
Gasman
J
Solved DateAdd Function acting weird.
Replies
8
Views
261
GPGeorge
GPGeorge
Mike Krailo
Solved Need help with before update code logic?
Replies
12
Views
407
Mike Krailo
Mike Krailo
K
Solved Rec.MoveNext not working?
Replies
4
Views
340
kevnaff
K
S
Solved Using a Does Not Contain Criteria in an IIf Statement
Replies
9
Views
361
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom