Criteria Function Not Working

My code is in a general module. I'm wondering if there's an issue with Access. This Query / function work for ALL other pay periods (before AND after the pay period in question).

I'm wondering if it's just a coincidence that this is happening when I'm trying to deal with the first pay period in a new year ("2020-01") but the GetPriorPayPeriod is returning the correct value ("2019-26") but the Query's operating as though nothing was returned (I can't tell if the Query is operating on "", Null or Empty).
 
That is why I suggested 2018-26 ?How long has this been running.? Is this the first new year period it has run against?
You have already said that you included the function as a field in your query?, that is how you know nothing is being returned, to the query at least.?

You can use ISNULL() and ISEMPTY() in the query.?
 
Last edited:
Yes, this is the first new year we've run this process through but we've got data going back 15 years.

I'm wondering if the new year is the issue or simply a coincidence because GetPriorPayPeriod does return the proper result ("2019-26") but the Query's not getting it, and, when I hard code "2019-26" in the Query's criteria, the Query does work properly. I'm wondering if it's an issue with Access.
 
Well amend the function to return 2018-26. it will only take a second?

Personally I do not think it is an Access issue. There will be a valid reason behind this, what I call silly errors, but they are the hardest to track down.

Returning 2018-26 by the function, not hardcoding it, would test for that.?
 
Looking at your code again, I have one more simple experiment that makes NO SENSE WHATSOEVER - but again is easy to try.

Inside that sub, you have a With statement for your recordset. You also have a .Close for the recordset.

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

...

    Set rstTemp = dbs.OpenRecordset("Admin")
   
    With rstTemp
   
        If SourceStr = "County" Then
       
...      
           
        End If
       
        .Close
       
    End With

End Function

There are those who will disagree with this, but if it works, it is easy. And again, if it doesn't work, it is easy to undo.

After the close, insert

Code: 
Set rstTemp = Nothing

You should NOT have to do that, but I have seen arguments both ways on the necessity of this operation. So try it and if it doesn't work, I won't be surprised - but if it DOES work, a lot of OTHER people here will be astounded. (The ones on the other side of the argument about the necessity of resetting the recordset variable to Nothing.)
 
SOLVED! My code Str(Val(Left(CurrentPayPeriodStr, 4)) - 1) was putting a space before the result - i.e., " 2019-26". I've replaced it with Trim(Str(Val(Left(CurrentPayPeriodStr, 4)) - 1)) and it's working fine!

I can't believe I missed that! Thanks for all your help!
 
WaterLover said:
SOLVED! My code Str(Val(Left(CurrentPayPeriodStr, 4)) - 1) was putting a space before the result - i.e., " 2019-26". I've replaced it with Trim(Str(Val(Left(CurrentPayPeriodStr, 4)) - 1)) and it's working fine!

I can't believe I missed that! Thanks for all your help!
Click to expand...
See, silly error. :)
That is why I suggested checking it's length. Also the 2018-26 test would have hinted to the problem.

We got there in the end.:D

Although, saying that, I cannot see how the code you posted would do that.? :o

A quick test confirms it, and I would never thought Str() would add a space. :(
Code: 
tt="2020"
? len(Str(Val(Left(tt, 4)) - 1) )
 5 
? len(str(2019))
 5
 
Last edited:
Thanks for your help! I looked right at the result and didn't see it!
 
Good catch. And spaces are often hard to find when embedded using a proportional font.
 
The_Doc_Man said:
Good catch. And spaces are often hard to find when embedded using a proportional font.
Click to expand...
Yes! I still can't believe I missed that because I'm aware it's an issue and normally would look for it. Maybe, I looked too quickly and saw what I wanted to see.
 
WaterLover said:
Interesting! I didn't know that either and now I do! I also learned something else new... when I included the PriorPayPeriod in my results, it came back blank and I thought the criteria was returning "", Null or Empty when in fact the criteria result (" 2019-26") didn't match any of the records in the database and the blank field in the recordset was from the data not being matched not the criteria's result.
Click to expand...
 
Gasman said:
I would never thought Str() would add a space.
Click to expand...
I think you will find that it is Val that is adding the space although I don't know why. Perhaps a new bug from an update that was supposed to fix some other bug? Try ?Val("2020")

EDIT - then again, maybe that's not your issue and Str is what was really the issue here. Val seems to add a space at the end, Str at the beginning.
 
Micron said:
I think you will find that it is Val that is adding the space although I don't know why. Perhaps a new bug from an update that was supposed to fix some other bug? Try ?Val("2020")

EDIT - then again, maybe that's not your issue and Str is what was really the issue here.
Click to expand...

Str does add a space before. Read Microsoft's writeup on Str. It reserves the first space for a negative sign and adds the space for positive numbers (the '+' is implied). Personally, I don't get why Microsoft does this. When would someone want a space in front of a number? If they want a space in front of a number, let them add it. But, the way Microsoft does this, makes code prone to error - one which is difficult to catch.
 
I modified my post while you were composing. Note that for me at least, Val adds a space at the end.
 
Does not do that for me.?
Code: 
tt="2019"
? val(tt) & "last char"
2019last char

@WaterLover FWIW I believe you could get away with
Code: 
Val(Left(CurrentPayPeriodStr, 4)) - 1 & "-" & LastPayPeriodOfYear
 
That's an interesting point! A bit odd combining a numeric value and a string but if it works!
 
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
632
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
360
Pat Hartman
P

Users who are viewing this thread

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