Comment On Access Denied

I know what it does!



It hunts down and KILLs sane programmers... what else could it be used for?

Not knowing access at all:



What does IIf() do?

IIf is normally a very efficient operator in Access, but since you divided it up into multiple lines it will lose much of its efficiency, since the compiler has to execute each line individually.  This is not so bad.  At least he didnt use a buch of separate if statements, then we would really be saying wtf, right?

Using Access is the WTF

IIF is like a ternary operator:



evaluate ? true expression : false expression



IIF (evaluate, true expression, false express)



Even knowing that, good luck.

:

Using Access is the WTF

From looking at the code, I get the distinct impression that it has something to do with formatting a date.

Jeff S:

No, IGNORANCE is the WTF.  Using Access is usually a pretty damn smart thing to do, if you are using it for the right purpose and you know how to use it.

I can't believe you actually said that...ROFL

You must be joking...either that or you have no true programmatic experience with Access.

Your comment is a major WTF.

Holy Crap! [:'(] Does anyone, anyone know what this does??! Alex. I applaud you for your attempt to format this. I gather it's looking at some date criteria.. but, who the hell know what, exactly.

About it being access: No: this is certanly VB. Why they've bracked the varaible name is beyond me, but with it being on multiple lines and having too many continuations (I was going to try it in VB to see what it did... I gave up, after realising that stepping through 1 line of code would be useless and formtting this into a sensible(HAH) set of nested ifs seems to be an impossibility) I conclude that the person who wrote this should be taken behind the shed and shot.

 

 

Jeff S:

wrote:

Using Access is the WTF

 

No, IGNORANCE is the WTF.  Using Access is usually a pretty damn smart thing to do, if you are using it for the right purpose and you know how to use it.

LOL.. Yep, using Access is the best thing if you want corrupt data and unusable databases :)

BTW, the really sad thing is if this mess was trying to be efficient, it's horribly ineffecient. VB Does not have short circuit evaluations unlike other, real programming languages. So, it has to do, a couple dozen format statements, a bunch of comparisons, and a whole litany of function calls.

Looks like the code tries to find out if a date (sol_date_reqd) is
within 10 weeks of today, but hasn't happened yet? Following that code
hurts my brain.



If Access has a datediff() function or somesuch, this is an amazing WTF indeed :)

if the current year is after the 'Sol' date required year
	return -1
else if we are before the 'Sol' date required year
	if the 'Sol' required week is more than ten weeks hence
		return ten weeks hence
	else
		return the 'Sol' date required week
else
	if we are after the 'Sol' date required week
		return -1
	else
		return the earlier of ten weeks hence and the 'Sol' date required week

It appears that this function checks if the requested date is in a fiscal year that is 10 weeks off of the calendar year. (WTF if I am right...)

Eddie Garmon

WTF????  [:|]

I love how he's using + 0 to convert all the outputs of the Format functions to integers for the conversions...

Best I could do in the circumstances. I probably messed it up somehow, but I expect forgiveness, given the circumstance.



Rik

Wow the posts come fast. I meant that that pseudocode above was the
best I could do. Not the original code. If I'd written that, I'd shoot
myself.



Rik

rikkus:

Wow the posts come fast. I meant that that pseudocode above was the best I could do. Not the original code. If I'd written that, I'd shoot myself.

Rik

Wow, I was going to say that you're brave..but...

You wouldn't have had to shoot yourself, I think the participants would have taken care of that for you [:)]

I give up.  I made an attempt to discern what this does but decided that I may not want to know.

And what's with the "+ 0" sprinkled throughout the code?  The "+ 10" I thought might be some type of Fiscal Year to Calandar Year conversion but W(ho)TFK(nows)

Bustaz Kool:

And what's with the "+ 0" sprinkled throughout the code?  The "+ 10" I thought might be some type of Fiscal Year to Calandar Year conversion but W(ho)TFK(nows)

See my post above for my guess as to what they're for...

Translation Key:
Format(Date, "yyyy") + 0
gets the year but since format returns a string you add 0 to get a number so it can be compared properly.
Format(Date, "ww", 1, 1)
get the week of the year starting at 1 and assuming sunday starts the week.

So the translation assuming [sol_date_reqd] is an inputted date:

If CurrentYear > InputYear Then
   -1
Else
   If CurrentYear < InputYear Then
      If InputWeek > Ten Weeks From CurrentWeek Then
         Ten Weeks From CurrentWeek
      Else
         InputWeek
      End If
   Else ' CurrentYear = InputYear
      If InputWeek < CurrentWeek Then
         -1
      Else
         If InputWeek > CurrentWeek + 10 Then
            CurrentWeek + 10
         Else
            InputWeek
         End If
      End If
   End If
End If
Which boils down to figure out the week number of up to ten weeks from the input date.
Or something like that.

This actually looks like a lot of the MDX (similar to SQL, but for
OLAP) code that I have to maintain.  The difference is that in the
case of MDX -- that would be considered good code.



Of course, with MDX you HAVE to do everything in-line.  So yes,
nested IIf loops all over the place -- and debugging is Hell!

Pseudo-Code:

IF YEAR(requestdate) in the past:
     return -1
ELSE IF YEAR(requestdate) in the future:
     return MIN( WEEK(requestdate), 10 weeks from now )
ELSE /* this year */
     IF WEEK(requestdate) in the past:
          return -1
     ELSE IF WEEK(requestdate) in the future:
          return MIN( WEEK(requestdate), 10 weeks from now )
     ELSE /* this week */
          IF this_week == 53: this_week = 1
          return this_week

To
the code's credit, it does wrap weeks (though it could do the same by
just adding the damn ten weeks and calling the modulo operator, which
hopefully exists in this language); in doing so, it ignores carrying to
the year, though, and it has issues with a week 53 (which would be 4
days of a week at the end of a year).
I have no idea why anyone
would be interested in just the number of the week of a requested date,
limiting it to be between this week (which might be up to six days ago)
and 10 weeks (plus or minus six days) from now, particularly when
throwing away the year in the process.


As for the efficiency (ha!): sometimes, trading efficiency for
maintainability can be the right decision. In this case, the codemonkey
chose to forego one in order to get rid of the other, so ignoring what
the code actually does, that design decision strikes me as the major (WT)F-up.

Basically this code figures out when to do something, based on
sol_date_reqd.  If this date is equal to the current date, it
returns the current week number, otherwise it returns the number of the
week 10 weeks from now, wrapping around the end of the year.  If
the required date has passed, then just return -1.



There, that wasn't so bad after all.

Mike R:

BTW, the really sad thing is if this mess was trying to be efficient, it's horribly ineffecient. VB Does not have short circuit evaluations unlike other, real programming languages. So, it has to do, a couple dozen format statements, a bunch of comparisons, and a whole litany of function calls.

Sorry to burst your anti-VB bubble but VB has short circuit evaluations now. - AndAlso and OrElse.

 

 

Mike R:

Jeff S wrote:

wrote: Using Access is the WTF   No, IGNORANCE is the WTF.  Using Access is usually a pretty damn smart thing to do, if you are using it for the right purpose and you know how to use it.

LOL.. Yep, using Access is the best thing if you want corrupt data and unusable databases :)

BTW, the really sad thing is if this mess was trying to be efficient, it's horribly ineffecient. VB Does not have short circuit evaluations unlike other, real programming languages. So, it has to do, a couple dozen format statements, a bunch of comparisons, and a whole litany of function calls.

skicow:

Mike R wrote:

BTW, the really sad thing is if this mess was trying to be efficient, it's horribly ineffecient. VB Does not have short circuit evaluations unlike other, real programming languages. So, it has to do, a couple dozen format statements, a bunch of comparisons, and a whole litany of function calls.

Sorry to burst your anti-VB bubble but VB has short circuit evaluations now. - AndAlso and OrElse.

 

 

 

I do not consider VB.NET and VB the same thing.

The refactoring is called "introduce explaining variable"

thisYear = Format(Now(),"yyyy") + 0
thisWeek = Format(Now(),"ww",1,1) + 0
solYear = Format([sol_date_reqd],"yyyy") + 0

IIf(
  thisYear > solYear
  ,-1
  ,IIf(
     thisYear < solYear
     ,IIf(
        solYear > IIf(thisWeek + 10 > 52, thisWeek - 42 ,thisWeek + 10)
        ,IIf(thisWeek + 10 > 52, thisWeek - 42, thisWeek + 10)
        ,solYear
         )
     ,IIf(
        solYear < thisWeek
        ,-1
        ,IIf(solYear > thisWeek + 10, 
                thisWeek + 10,
                IIf(solYear > 52, 1, solYear)
               )
            )
         )
      )
   )

Mike R:

skicow wrote:

Mike R wrote: BTW, the really sad thing is if this mess was trying to be efficient, it's horribly ineffecient. VB Does not have short circuit evaluations unlike other, real programming languages. So, it has to do, a couple dozen format statements, a bunch of comparisons, and a whole litany of function calls. Sorry to burst your anti-VB bubble but VB has short circuit evaluations now. - AndAlso and OrElse.

 

I do not consider VB.NET and VB the same thing.

I agree - the difference between VB6 and VB.NET is huge - though IMHO it should have been bigger - e.g. less 'backwards compatibility'.

 

Mike R:

Jeff S wrote:

wrote: Using Access is the WTF   No, IGNORANCE is the WTF.  Using Access is usually a pretty damn smart thing to do, if you are using it for the right purpose and you know how to use it.

LOL.. Yep, using Access is the best thing if you want corrupt data and unusable databases :)

BTW, the really sad thing is if this mess was trying to be efficient, it's horribly ineffecient. VB Does not have short circuit evaluations unlike other, real programming languages. So, it has to do, a couple dozen format statements, a bunch of comparisons, and a whole litany of function calls.

Jeff S:

 

As I said, if you use it for the right purpose.  for single user databases, it works very well.  for simple data-entry interfaces or reports, again it works well.  does it work well as a back-end DB for a multi-user environment?  No.  does is work well as a back-end for a web server? no? 

:

Jeff S wrote:

As I said, if you use it for the right purpose.  for single user databases, it works very well.  for simple data-entry interfaces or reports, again it works well.  does it work well as a back-end DB for a multi-user environment?  No.  does is work well as a back-end for a web server? no?

 Just to throw my 2 cents in, for a single user database I'd much rather use something like MSDE than Access since it acts like a real database instead of pretending to be like one

Sometimes, as a developer, you don't have a choice of language or backend database.  You just have to make do with what's already there.  Especially if you're doing maintenance work on code you didn't write.

Was this actually written out in VBA code, or was it an expression used in a query or in the "ControlSource" property of a report or form control?

If it was the latter, the use of IIF() is explained.  Using a bunch of nested IIFs allows you to write the thing as one big expression.  Otherwise you would have to write a VBA function.

Which is what I would have done.  But I suppose it's possible -- possible -- that there is a performance difference between doing this in a function and doing it as an in-line expression.  If this were part of some gargantuan report or batch update, there might be enough of a difference to make this worthwhile.

But I do think they must not have known about Year() and DateDiff(). 

:

The refactoring is called "introduce explaining variable"

thisYear = Format(Now(),"yyyy") + 0
thisWeek = Format(Now(),"ww",1,1) + 0
solYear = Format([sol_date_reqd],"yyyy") + 0

You've accidentally changed the behavior of the code.  Just think
of the differences if you ran your code near midnight on New Year's or
a Saturday.

This looks to me like a LISP programmer who's being forced to use
Access, which is reason enough for ?him to want to kill himself. 
If he were a better programmer, he would have checked and found that
IIF is a function, not a special form, and therefore the if-true and
if-false parts get evaluated even when they're discarded.



So no - arguing against the fairly clueless (or poorly-expressed)
comment about the IIF "operator", it would have been better as a bunch
of IF statements.



And if I were doing it in LISP or some other functional language
(Python, ML, JavaScript, etc), I'd have broken it up into subfunctions
and let them document themselves...

wait.. javascript is considered a functional language? I thought it was
just a normal procedural language. Then again I havn't really studied
it a whole lot.

Javascript is quite an amazing language, closer to "real" languages
like Python and LISP than to the pseudo-assemblers like C and C++ or
the text-processing scripting languages like Perl.  Unfortunately,
it was so badly implemented that you never get to see a tenth of its
theoretical power.  Pity.

(But you're right - it's not really a functional language.  I just
threw that in to see if anyone was watching...)  

:

If this were part of some gargantuan report or batch update, there might be enough of a difference to make this worthwhile.

If this were part of some gargantuan report or batch update, it ought to be implemented in something other than Access.

Come on, why are you all trying to make this CLEARER?

Let's see what it must have looked originally:

IIf(Format(Now(),"yyyy") + 0 > Format([sol_date_reqd],"yyyy") + 0,-1,IIf(Format(Now(),"yyyy") + 0 < Format([sol_date_reqd],"yyyy") + 0,IIf(Format([SOL_DATE_REQD],"ww",1,1) + 0 > IIf(Format(Now(),"ww",1,1) + 10 > 52,Format(Now(),"ww",1,1) - 42,Format(Now(),"ww",1,1) + 10,IIf(Format(Now(),"ww",1,1) + 10 > 52,Format(Now(),"ww",1,1) - 42,Format(Now(),"ww",1,1) + 10),Format([SOL_DATE_REQD],"ww",1,1) + 0),IIf(Format([sol_date_reqd],"ww",1,1) + 0 < Format(Now(),"ww",1,1) + 0,-1,IIf(Format([sol_date_reqd],"ww",1,1) + 0 > Format(Now(),"ww",1,1) + 10,Format(Now(),"ww",1,1) + 10,IIf(Format([sol_date_reqd],"ww",1,1) + 0 > 52,1,Format([sol_date_reqd],"ww",1,1) + 0)))))

Ah, now THAT'S a real WTF :)

"Um, I think there is a problem with a formula in this report. Can you check them out for me?"

A couple of good posts, especially Rikkus who done a good job of figuring it out. 



One of our customers  had  paid many thousands of pounds to
have this written and it was the "developers" choice to use
Access.  Which is hilarious when you consider that our software is
all humming away on a HUGE MSSQL Server in their computer room and that
[SOL_DATE_REQD] column is from a table that is *copied* into Access so
this thing can process it...



Anyway, the thing stopped working around about the start of the year
and they couldn't work out what was wrong, so in the end they asked my
company if we could have a quick look.  The experience was a realt
WTF!  (shouted).  Having dug out some online Access manuals,
the problem was that the "programmer" had used a Format for weeks
("ww",1,1) that only worked for 2004 - My understanding is that it
should have been "ww",1,2 (I think).



Apparently, the guy that wrote it has changed career in the last year and now owns a pub (bar).



Says it all.

If this code was being used as an expression, other than the use of
Format instead of DatePart, + 0 instead of CInt and manual modulo
instead of the mod operator, I don't think it's too bad.

Public Function DueWeek() As Integer

    Dim CurrentYear As Integer, CurrentWeek As Integer
    Dim PlusTenWeeks As Integer, PlusTenWeeksWrap As Integer
    Dim InputYear As Integer, InputWeek As Integer

    CurrentYear      = Year( Now() )
    CurrentWeek      = DatePart( "ww", Now() )
    PlusTenWeeks     = CurrentWeek + 10
    PlusTenWeeksWrap = PlusTenWeeks Mod 52
    InputYear        = Year( [sol_date_reqd] )
    InputWeek        = DatePart( "ww", [sol_date_reqd] )

    If ( CurrentYear > InputYear ) Then
        DueWeek = -1

    ElseIf ( CurrentYear < InputYear ) Then

        If ( InputWeek > PlusTenWeeksWrap ) Then
            DueWeek = PlusTenWeeksWrap
        Else
            DueWeek = InputWeek
        End If

    Else  '  ( CurrentYear = InputYear )

        If ( InputWeek < CurrentWeek ) Then
            DueWeek = -1
        ElseIf ( InputWeek > CurrentWeek + 10 ) Then
            DueWeek = PlusTenWeeks
        ElseIf ( InputWeek > 52 ) Then
            DueWeek = 1
        Else
            DueWeek = InputWeek
        End If
    End If

End Function

If I could make an educated guess I would say that the requirements are:

• Return -1 is we are past the due week


• If the due week is more than ten weeks away return now + 10 weeks.


• If due date is less than ten weeks away return due week.
  
  

In which case there is a logic error in the CurrentYear < InputYear
block and we could do it a little nicer with the help of DateDiff and
DateAdd.

Public Function DueWeek() As Integer

    If ( Year(Now()) = Year([sol_date_reqd]) And
        DatePart("ww", Now()) = DatePart("ww", [sol_date_reqd]) ) Then

          '' It's due this week.
          '' Send the enforcers...
          DueDate = DatePart("ww", Now())

    ElseIf ( Now() > [sol_date_reqd] ) Then

          '' It's past the due date.
          '' Tell Mickey to break some legs...
          DueDate = -1

    ElseIf ( DateDiff("ww",  Now(), [sol_date_reqd]) > 10 ) Then

          '' More than ten weeks till due date.
          '' Send another reminder in ten weeks...
          DueDate = DatePart("ww", DateAdd("ww", 10, Now()))
    Else

          '' Less than ten weeks till due date.
          '' Give them a little incentive to pay on time
          '' and remind us when it's due...
          DueDate = [sol_date_reqd]
    EndIf

End Function

The single line formatting is so that when this is ported to linux, the
ms access parser doesn't get confused by the line endings. Simple
enough once you know the reasoning behind it, no?

Do you know the spec? Maybe he was trying to build a LISP-Interpreter in Access...

 

I am sitting here laughing reading these posts...I love it how people try to defend Access as a legitimate DB for the corporate environment...or how Javascript is a real programming language...you guys crack me up.

There's nothing wrong with JavaScript, especially compared to
abominations like Basic and The Quick-And-Dirty PDP Assembler That Men
Call "C".  Its only really flaws are:

1. the name (it's not a scripting language and it doesn't have anything to
   do with Java - might as well call it Predicate-Logic COBOL instead)


2. the libraries, both the laughabl standard and all implemented extensions
   
   


3. the debugger in existing implementations


4. the bugs in existing implementations


5. nearly every available tutorial or book that tries to teach it

These are implementation issues.  An implementation of JavaScript
that fixed these would be at least as powerful as early versions of
Python.  The one language issue I dislike is the C-like syntax,
but then I'm a fan of LISP, Forth, Prolog and Delphi, so you'd expect
that...



: Bat :

Jeff S:

 

As I said, if you use it for the right purpose.  for single user databases, it works very well.  for simple data-entry interfaces or reports, again it works well.  does it work well as a back-end DB for a multi-user environment?  No.  does is work well as a back-end for a web server? no? 

 

You know, somehow I thought this was supposed to be a place to review stupid code and comment ON THE CODE, not pick fights about "real" programming languages. In the world that I have had to work in, I all to often have to hold my nose to do the work because I am saddled with old systems and not given the time to redo things "right". As I read in a book "you don't send a regiment to squash a fly." I have even heard "real" programmers, you know the ones who work in C & Assembler, say that VB makes sense for some things. So if you don't have anything useful to contribute ABOUT THE CODE, shut up.

You know, somehow I thought this was supposed to be a place to review
stupid code and comment ON THE CODE, not pick fights about "real"
programming languages. In the world that I have had to work in, I all
to often have to hold my nose to do the work because I am saddled with
old systems and not given the time to redo things "right". As I read in
a book "you don't send a regiment to squash a fly." I have even heard
"real" programmers, you know the ones who work in C & Assembler,
say that VB makes sense for some things. So if you don't have anything
useful to contribute ABOUT THE CODE, shut up.

Isn't this post a bit hypocritcal? Part of what makes reading these
threads fun is the distance elimination contests that always seem to
follow..[:D]

I think I know why it was one line.  It was a property of a form
or something like that.  It wasn't even entered in the code behind
module.