Recent content by WaterLover

I know this post is old but wanted to add my input for anyone who will find it helpful. The problem with entering "" or Null when the value is 0 is that you can't total these values and you'll get an error if the field's Format is set to a numerical format (i.e., Standard). And, while...

You're right. For me, it's just my way of coding. I like to do things "right" the first time and not have issues instead of putting in code to clean up messes like this later.

I haven't tried Val(tt). I assume it works but I'm going to keep it the way I have it because I feel it's proper coding form to do so.

That's an interesting point! A bit odd combining a numeric value and a string but if it works!

I'm not aware of the issue with Val but I'll keep it in mind in the future.

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...

Interesting! I didn't know that either and now I do!

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.

Thanks for your help! I looked right at the result and didn't see it!

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!

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...

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...

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"...

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...