Hi everybody! I am trying to mak

[SheilaAlighieri]

Hi everybody!

I am trying to make a complicated code in VB. But I am not even sure if what I am trying is possible. I have a data entry form in which a date is entered. Now I want the system to derive some information automatically from this date and enter it two other fields in the table, namely the budget year and the quarter. I got these things working in a query:

Budget Year: IIf(Month([Date])>3;Year([Date]) & "/" & Year([Date])+1;Year([Date])-1 & "/" & Year([Date]))

Quarter: Switch(Format([Date];"q&quot="1";"4";Format([Date];"q&quot="2";"1";Format([Date];"q&quot="3";"2";Format([Date];"q&quot="4";"3&quot

Is there a way to integrate these codes in VB or achieve the same result without using queries? It would decrease my use of queries and solve a nasty problem I am having with reports If I hook it up to an AfterUpdate sub on the date field I should get it to work.

Thanks for reading!

Sheila

 

[seagoing]

You may like to consider using the two functions I've included below to return the values you want.

Private Function getBudgetPeriod(d As Date) As String
If IsDate(d) Then
If Month(d) > 3 Then
getBudgetPeriod = CStr(Year(Date)) & "/" & CStr(Year(Date) + 1)
Else
getBudgetPeriod = CStr(Year(Date) - 1) & "/" & CStr(Year(Date))
End If
Else
getBudgetPeriod = "Error!!!"
End If
End Function

Private Function getBudgetQuarter(d As Date) As String
If IsDate(d) Then
Select Case DatePart("q", d)
Case 1
getBudgetQuarter = "4"
Case Else
getBudgetQuarter = CStr(DatePart("q", d) - 1)
End Select
Else
getBudgetQuarter = "Error!!!"
End If
End Function

 

[scriverb]

Assuming you have two controls on your form called Me![Budget Year] and Me![Quarter] and the control where the date is being entered is Me![DateEntry] the following expressions will fill the two controls with the same info as your query:

Me![Budget Year] = IIf(Month(Me![DateEntry])>3;Year(Me![DateEntry]) & "/" & Year(Me![DateEntry])+1;Year(Me![DateEntry])-1 & "/" & Year(Me![DateEntry]))

Me![Quarter] = Switch(Format(Me![DateEntry];"q&quot="1";"4";Format(Me![DateEntry];"q&quot="2";"1";Format(Me![DateEntry];"q&quot="3";"2";Format(Me![DateEntry];"q&quot="4";"3&quot


Post back with any questions.


Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[MichaelRed]

For a RARE occassion, I would disagree w/ scriverb in these fora. If the functions need to be saved in the "table" AND presented on the form (or are used elsewhere in the app), I would recommend using the FUNCTION format and placing this in a general module. Including (redundant) calculations is generally a POOR choice from a maintenance persprctive. The "Function" format of the calculation can be used throughout the application while the 'expression' format is only applicable to the instnce of it's use. Thus, a change -for the module format needs to be implemented once and is then effective thrtoughout the app, while the expression must be modified in each instance / occurance.

Having gone the'great leap' of faith toward a (drunken?) sailor seagoing and they are ALL "Drunken" - at least as celebrated in song and seaports around the world). I DO find it curious that the functions are declared "private" - which restricts their usage and that although a REQUIRED input parameter is declared (in hte prototype / header/ call) as Date, it is AGAIN checked to be a valid "Date" within the body of both procedures.

Some further minor issues cause me to 'embellish' the thoughts with teh following contribution:

In both functions, I provided the inpput parameter as Optional, defaulting to the current date. This provides some convenience to the user / programmer, as (then) current dates are not required. I also provided a "standard" error return ("-1&quot, which I feel is easier to deal with than a specific text string. It may be evaluated with a simple conditional (If Block), although the Caller (User) is still required to deal with the error.

In the FY quarter calculation, I replaced the "Date" (which is ALWAYS the current DATE) with the input parameter. In the instance of calling w/o an argument, this (of course) makes no difference. If a date is supplied however, hte results (return value) could at least SEEM to be weird. With the modification, the return will at least appear to consistient with the argument parameter.

Public Function basBudgYr(Optional dtIn As Variant) As Integer

    'Michael Red    7/12/2003; FY for Tek-Tips thread702-601115

    If (IsMissing(dtIn)) Then
        dtIn = Date
    End If

    If (Not IsDate(dtIn)) Then
        basBudgYr = -1
        Exit Function
    End If

    If Month(dtIn) > 3 Then
        basBudgYr = Year(dtIn) & "/" & Year(dtIn) + 1
     Else
        basBudgYr = Year(dtIn) - 1 & "/" & Year(dtIn)
    End If

End Function


Public Function basBudgQtr(Optional dtIn As Variant) As Integer

    'Michael Red    7/12/2003; Offset FY Quarter for Tek-Tips thread702-601115

    If (IsMissing(dtIn)) Then
        dtIn = Date
    End If

    If (Not IsDate(dtIn)) Then
        basBudgQtr = -1
        Exit Function
    End If

    If (DatePart("q", dtIn) = 1) Then
        basBudgQtr = 4

     Else
        basBudgQtr = (DatePart("q", dtIn) - 1)
    End If

End Function

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[scriverb]

MichaelRed: Your comments and criticisms are always taken in a positive and helpful manner. I do appreciate you keeping me on my toes. Yes, I agree with you that recurring calculations should be should be used in public subs and functions. Yes, this way they can be utilized easily anywhere within the database and code only has to be changed in one place should that be necessary. And, you code certainly will provide the necessary calculation for Sheila's problem.

That taken there is a reason that I recommended a straight expression value assignment to the two bound form controls. Sheila said: Is there a way to integrate these codes in VB or achieve the same result without using queries? It would decrease my use of queries and solve a nasty problem I am having with reports If I hook it up to an AfterUpdate sub on the date field I should get it to work.

Sheila already had two expressions working for queries. Her request was how can she use these codes in VB without using a query. She also hit upon the technique of putting the code in the AfterUpdate of the date entry field. In attempting to direct her to use her already created codes and techniques the direct assignment supported her request. Secondly, and more importantly I would think that the caclulations to determine the Budget Year and Quarter are and store in a table would be something done once in a database. My instinct tells that these fields aren't going to change for a particular record so why use the function capability when its universal benefits won't be utilized. I may have assumed too much in thinking that these fields are in the master table for the database where all queries and processes would have access to these values, but I am betting that to be true.

I usually use the Function capability when a process is to be called multiple times. I didn't think that to be the case here.

But, now Sheila knows how to create and use a Function and also make a direct expression assignment to a control. Sheila benefits two ways.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[MichaelRed]

hmmmmmmmmmm, ER, ... ERK! Caught in the turmoil of a connundrum of my own making.

Re-reading (at least partially), you scriverb are correct. On the other hand, my original commentary was flawed, so the two are only realted coincidentally. WHT on earth (or anywhere in the known universe) would I ever even entrtain the thought of storing reaadily computable 'information'? You are, of couse, entirely correct is that IF the 'sttuuuffff' is to be stored, then it is (?should not?) be re-calculated, so "why bother" with the module. On the other hand, the general consensus is that if it is just a simple 'derived' value, readily obtainiable for the records' fields then it is a redundancy and SHOULD NOT be saved in the "Table".


Alas-ALACK-AWRY, what is the world (and this connundrum!) comming to. [b[I[/b] at least can blame it on senility and lack of (at least commercial) practice.


On the next hand (I got tired of just switching back and forth to the "other" one). You are also exactly correct in that the response is the specific answer to the question -as posted. Although (another new and different "hand"?) it DOES ignore the more general question of wheather (and when) you (the great "GENERIC" you) should answer the question in the narrow sense as posed, qand when should you provide a more generalized response - - - as in the generic issue of giving the fish or teaching fishing, not to mention the continuing and lingering question of HOW much further to go down the teaching path -IF it is selected? I, in general, start down the 'path less traveled' (involving a lot of 'lecture material' which is often NOT appreciated), but often get frustrated on ~~ the third explination and try to ignore the continuing babble which I have engendered.

Well, probably EVERY ONE is realizing this is being done on a not very nice (weather wise) sunday afternoon, so I'll just let the babble trail off to a murmer of discontent.

Conclusion:

You were 'right'! - AND it only took 200 lines of text for me to admit it!



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[seagoing]

Forgive me, my understanding was Sheila simply wanted a couple of VB Functions to return the appropriate budget year(s) and Quarter!!

 

[scriverb]

seagoing: Our responses were in no way trying to undermine the response that you provided. Michael and I provided alternate soluttions to the same problem and they all will probably work equally as well. The dialog here has been more philosophy as to whether to use a function or an expression. And, more importantly whether to store this data in the table or not. According to standard normalized practice data that easily calculated should not be stored in a table. Since Sheila's table will always have EntryDate then both of these values could be derived when needed using yours, mine, or michael's code.

But, to get back to your last posting I don't read where Sheila asked for a "function" to replace her query code. She merely stated that is there a way in VB to duplicate what she already had accomplished with her expressions. I merely updated her working expressions with the DateEntry control name and assigned it to the bound controls. Simple solution to her problem and no additional functions necessary.

Six of one half-dozen of another I guess.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[SheilaAlighieri]

Hi guys!
Thanks for helping me out with my problem When I use scriverb his solution, I get a compile "expected list seperator or )" at the ";" symbol. My code is as follows:

Private Sub DateEntry_AfterUpdate()

Me![Budget Year] = IIf(Month(Me![DateEntry])>3;Year(Me![DateEntry]) & "/" & Year(Me![DateEntry])+1;Year(Me![DateEntry])-1 & "/" & Year(Me![DateEntry]))
Me![Quarter] = Switch(Format(Me![DateEntry];"q&quot="1";"4";Format(Me![DateEntry];"q&quot="2";"1";Format(Me![DateEntry];"q&quot="3";"2";Format(Me![DateEntry];"q&quot="4";"3&quot

End Sub

What am I doing wrong? (sorry I am a complete VB newbie).

Scriverb was right by the way The calculations to determine the Budget Year and Quarter are just made once and stored in the database. The are not going to be changed for a particular record.

Thanks again

Sheila!

 

[scriverb]

Never noticed it and just copied your code because you said it was working. The semi-colons in your expressions should be changed to commas:

Me![Budget Year] = IIf(Month(Me![DateEntry])>3,Year(Me![DateEntry]) & "/" & Year(Me![DateEntry])+1,Year(Me![DateEntry])-1 & "/" & Year(Me![DateEntry]))
Me![Quarter] = Switch(Format(Me![DateEntry],"q&quot="1","4",Format(Me![DateEntry],"q&quot="2","1",Format(Me![DateEntry],"q&quot="3","2",Format(Me![DateEntry],"q&quot="4","3&quot


That should do it.


Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[SheilaAlighieri]

^v^ It works great! Thanks a lot everybody!

Sheila

 

[scriverb]

Great!! Glad that we could help you.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???