UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> It's A Puzzle!, Access 2013    
 
   
Jeff B.
post Jun 13 2018, 08:20 PM
Post#1


UtterAccess VIP
Posts: 10,082
Joined: 30-April 10
From: Pacific NorthWet


pullhair.gif iconfused.gif <headbanger>

I have a procedure I'm putting together with nested loops (some recordsets, some For/Next).

As I start up each loop, I assign values to variables that have values that have become available within that loop. (yes, I Dim'd them all)

When I get to the innermost loop, I try to use those variables in expressions. ... "try" is the keyword here.

Some of the variables (not all, not the same one) sometimes have spontaneously changed value. I've verified multiple times that I'm not doing anything that would/should change the value.

So far, it has only been variables Dim'd as (and assigned) Date/Time values ... but again, not all of the Date/Time variables are spontaneously morphing.

I've worked out a seriously kludgy solution (hold off assigning values until inside the innermost loop, and immediately before using in an expressioni), but I'd appreciate any insight. Has anyone else run into this? Has anyone else come up with a more elegant solution?

Thanks!

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
GroverParkGeorge
post Jun 13 2018, 08:31 PM
Post#2


UA Admin
Posts: 33,230
Joined: 20-June 02
From: Newcastle, WA


Maybe global variables are at work?

--------------------
Go to the top of the page
 
tina t
post Jun 13 2018, 08:47 PM
Post#3



Posts: 5,471
Joined: 11-November 10
From: SoCal, USA


QUOTE
I've verified multiple times that I'm not doing anything that would/should change the value.


could it be one of those "can't see for lookin'" times? can you post the procedure?

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
projecttoday
post Jun 13 2018, 09:36 PM
Post#4


UtterAccess VIP
Posts: 9,959
Joined: 10-February 04
From: South Charleston, WV


Have you done much debugging? Can you pinpoint in the code where these changes occur? If you repeat the exact same test, are these spontaneous values different each time or are they the same?

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
Jeff B.
post Jun 13 2018, 11:12 PM
Post#5


UtterAccess VIP
Posts: 10,082
Joined: 30-April 10
From: Pacific NorthWet


Thanks, George.

I'll recheck the global variables I set, but this is a new routine, so I really doubt I have an equivalently-named global behind the curtains. 'sides, it isn't always the same one!

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
Jeff B.
post Jun 13 2018, 11:13 PM
Post#6


UtterAccess VIP
Posts: 10,082
Joined: 30-April 10
From: Pacific NorthWet


Thanks, Tina.

I'd need to get permission from the 'owner' to post it. In the mean while, I'll take another look.

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
Jeff B.
post Jun 13 2018, 11:17 PM
Post#7


UtterAccess VIP
Posts: 10,082
Joined: 30-April 10
From: Pacific NorthWet


Thanks, Robert.

I've been stepping through the code, inspecting values at each step. Somewhere between when I assign a value and when I need to use the variable/value, it gets wonky. So far, only on date-related variables (Dim'd as Date/Time). The year is changing in the most recent instance, so an in-the-dataset value of 7/1/2018 starts out in the variable as 7/1/2018, but morphs into 7/1/2019.

Are there other debugging processing/steps you might suggest?

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
JonSmith
post Jun 14 2018, 02:42 AM
Post#8



Posts: 3,688
Joined: 19-October 10



Yes!

Add some watches against all the variables. Set it to break anytime the value changes, you can see how it gets from value A to B and when. You'll instantly spot the offending code that is changing it before you expect, I bet its just the obtuse nature of nesting thats causes a path you cannot see right now but as soon as the code breaks as it switches values you'll kick yourself and realise how it makes sense all along.
Go to the top of the page
 
cheekybuddha
post Jun 14 2018, 02:53 AM
Post#9


UtterAccess VIP
Posts: 10,254
Joined: 6-December 03
From: Telegraph Hill


>> So far, only on date-related variables <<

How are the variables being loaded? What date format are they?

Might it be an issue where ambiguous dates are having their day/month flipped from some weird Regional setting on conversion from string to date?

--------------------


Regards,

David Marten
Go to the top of the page
 
projecttoday
post Jun 14 2018, 05:40 AM
Post#10


UtterAccess VIP
Posts: 9,959
Joined: 10-February 04
From: South Charleston, WV


I think Jon got it with the watch.

Dates only? Hmmm ...

Again, although it might not help that much, are they always the same or do these morphing values change from test to test?

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
BuzyG
post Jun 14 2018, 07:20 AM
Post#11



Posts: 380
Joined: 20-September 12
From: Cornwall UK


All other things aside. It's a computer. It will do what it's told to do. compute.gif

So... as above. Set watches on the variables and break into the code when they change.

Unless of course you have created life within the code. notworthy.gif

--------------------
Live to Surf
Go to the top of the page
 
Jeff B.
post Jun 14 2018, 07:39 AM
Post#12


UtterAccess VIP
Posts: 10,082
Joined: 30-April 10
From: Pacific NorthWet


Thanks, Jon. Sounds like a winner...

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
Jeff B.
post Jun 14 2018, 07:42 AM
Post#13


UtterAccess VIP
Posts: 10,082
Joined: 30-April 10
From: Pacific NorthWet


<BuzyG> yah, I can't remember how many times I've told a user something very much like that <g>

... and as others have pointed out, I'll probably kick myself in the head when I find what's been causing this (hmmm, I'D pay to see that!).

Thanks to all for the help... sometimes I just have to whine about it to figure it out ...

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
JonSmith
post Jun 14 2018, 08:12 AM
Post#14



Posts: 3,688
Joined: 19-October 10



Good luck Jeff! Let us know if that helped you pinpoint it!
Go to the top of the page
 
GroverParkGeorge
post Jun 14 2018, 09:04 AM
Post#15


UA Admin
Posts: 33,230
Joined: 20-June 02
From: Newcastle, WA


We're all in this together, Jeff.

--------------------
Go to the top of the page
 
Jeff B.
post Jun 14 2018, 02:29 PM
Post#16


UtterAccess VIP
Posts: 10,082
Joined: 30-April 10
From: Pacific NorthWet


Thanks David. Regional settings are probably not the culprit, as I have more than one date-related variable, but not all of my date-related variables are showing the same irascibility...

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
Jeff B.
post Jun 14 2018, 02:44 PM
Post#17


UtterAccess VIP
Posts: 10,082
Joined: 30-April 10
From: Pacific NorthWet


OK, good news! First thanks to all for help and offers, and special thanks to Robert {<JonSmith> get first mention - Robert & others just pig-piled on <g>}and others for the idea of setting a Watch.

I got on it this morning, set a Watch on a change-of-value for the one date-related variable that's been most problematic. No, as I suspected, there was nothing in my code that told that variable to morph.

But ... (big but) ...

I Dim'd the variable and assigned a value. Subsequently in my code, I passed that variable into a helper routine I had adopted to calculate Working Days between two dates. The call goes something like: WorkingDays(Start, End).

Now, for bonus extra points, are parameters passed into a function/sub treated as values or as references?

(HAH, trick question! If you handle the parameters ByRef, they're handled by reference. If you handle the parameters ByVal, the value is used.)

Second bonus extra point question -- if you don't declare a handling method, which way will the parameters be handled? No fair Googling...)

What I recalled, and what I expect, is that the default is ByVal. Hmmm, maybe I still have a problem. That helper function didn't explicitly set handling, so it should have used ByVal, right?

<buzzer!>

For whatever reason, once the variable got passed into the helper function, the variable started morphing! The helper function takes the "Start" parameter and counts how many week days (un-weekend days) fall between Start and End. In the process, it increments "Start" until it reaches "End". All other things being equal, I would have expected that incrementing to ONLY take place within the helper function ... but noooooo! I watched (Watch'd) while the underlying variable got incremented too.

My solution was to explicitly state handling in the helper function (ByVal). But I'm still puzzled why it treats the parameter(?s) as ByRef...

Any ideas folks?

(and once more, thanks for the support!)

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
PhilS
post Jun 14 2018, 03:18 PM
Post#18



Posts: 489
Joined: 26-May 15
From: The middle of Germany


QUOTE
But I'm still puzzled why it treats the parameter(?s) as ByRef...

ByRef is the default for each argument to any procedure or function unless you specify otherwise!

--------------------
Go to the top of the page
 
projecttoday
post Jun 14 2018, 04:05 PM
Post#19


UtterAccess VIP
Posts: 9,959
Joined: 10-February 04
From: South Charleston, WV


I don't know. I think if I were writing a function like this I would assign the start date to a variable defined within the function and increment that rather than actually incrementing start date. Perhaps you could post the code.




--------------------
Robert Crouser

My company's website
Go to the top of the page
 
Jeff B.
post Jun 14 2018, 08:54 PM
Post#20


UtterAccess VIP
Posts: 10,082
Joined: 30-April 10
From: Pacific NorthWet


<PhilS>

Do you have a source for that? I was able to find authoritative-sounding assertions for BOTH! One said "the default is ByVal"; another said "the default is ByRef". Still others talked about the differences between VB/VBA and other programming languages.

I'm still on the fence.

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search
RSSSearch   Top   Lo-Fi    14th August 2018 - 10:22 AM