VBA function to count # of days between two dates

Function DayCount(DateBeg As Date, DateEnd As Date, Optional D1 As Integer, Optional D2 As Integer, Optional D3 As Integer, Optional D4 As Integer, Optional D5 As Integer, Optional D6 As Integer, Optional D7 As Integer)
For i = 0 To DateEnd - DateBeg If Weekday(DateBeg + i) = D1 Then Cnt = Cnt + 1 Else End If
If Weekday(DateBeg + i) = D2 Then Cnt = Cnt + 1 Else End If
If Weekday(DateBeg + i) = D3 Then Cnt = Cnt + 1 Else End If
If Weekday(DateBeg + i) = D4 Then Cnt = Cnt + 1 Else End If
If Weekday(DateBeg + i) = D5 Then Cnt = Cnt + 1 Else End If
If Weekday(DateBeg + i) = D6 Then Cnt = Cnt + 1 Else End If
If Weekday(DateBeg + i) = D7 Then Cnt = Cnt + 1 Else End If
Next i
DayCount = Cnt
End Function

Interview Questions from Employers

The underlying purpose of all questions is to probe or explore your personality, attitude, or behaviour characteristics to gauge if you will be a good fit. Before going to interviews, always take the time to think of questions that you may be asked and compile a list of responses. Remember that most interviewers will be looking for three things when you answer questions:

• your answer;

• how well you can organize your thinking; and

• how well you express yourself.

“Breaking the Ice” Interview Questions

• Painting (or whatever you wrote in your resume) has always intrigued me. How did you become interested in it?

• The campus looks very busy. How is your semester going?

• I see you are involved in sports. How is your season going?

• I have an extra hour and it's my first visit to the area. What should I see?

Work History Interview Questions

• What were your responsibilities? Which was most / least rewarding?

• What were your expectations for the job and to what extent were they met?

• What were your starting and final levels of compensation?

• What major challenges and problems did you face? What was your toughest job assignment?

• How would you evaluate your present firm?

• What were your biggest accomplishment / failure in this position?

• What was it like working for your supervisor? What were his strengths and shortcomings?

• What did you like the most and least about your previous job?

• What mistakes have you made in your career? How did you fix them?

• Would you describe a few situations in which your work was criticized?

• Why are you leaving your job? Why are you looking for a new career? Why did your business fail?

• Why were you fired? Why have you not obtained a job so far?

• Why are you not earning more by now?

• Have you helped increase sales? Profits? Reduce costs?

• Where do you relate best - up one level, down one level, or with your peers?

• What do your subordinates think of you?

• How do you evaluate your subordinates?

• Describe a situation in which you and a co-worker have disagreed. How did you work it out?

• Have you ever taken charge as a leader in a work situation without being formally assigned to that role by your boss?

• Have you ever gone beyond the call of duty in helping a client?

• How many hours do you normally work?

• How would you describe the pace at which you work?

• What were you doing during the period of time not covered in your resume?

• Have you kept up in your field with additional training?

Personal and Motivational Interview Questions

• Tell me about yourself. How would you describe your own personality?

• Can you describe yourself using one-word adjectives?

• How do you handle rejection?

• Who or what had the greatest influence on your life?

• What are some of the greatest personal challenges you have faced during your lifetime?

• What are some of your personal goals and have you achieved them?

• What satisfies you, bothers you, or motivates you?

• List three personal attributes that you would like to improve? Have you made any efforts to improve in these areas?

• How would others describe your weaknesses?

• What are your greatest achievements at this point in your life?

• What accomplishments are you most proud of?

• What are your strong points? What are your weak points?

Work-Related Interview Questions

• Have you worked under deadline pressures? Provide examples.

• How do you handle stress and pressure?

• Can you discuss a time when you had to perform multiple tasks on very strict time lines? How did you ensure everything was completed on time?

• Can you describe a typical work week?

• What have you done that shows initiative and willingness to work?

• What are the most important rewards you expect in your career?

• What do you look for in a job?

• What do you find are the most difficult decisions to make?

• If you could start again, what would you do differently?

• If the people who know you were asked why you should be hired, what would they say? How would your colleagues describe you? How would your boss describe you? How would you describe yourself?

• Do you prefer to work independently or on a team? Give some examples of team work.

• How do you build a team under you?

• What type of work environment do you prefer?

• Can you describe a difficult work situation / project and how you overcame it?

• How do you evaluate success?

• Can you describe your ideal work environment? What kind of people do you enjoy working with?

• How would you handle an upset customer / client?

• What is the difference between a good position and an excellent one?

• What is your philosophy of management?

• How has your early career or background influenced your progression and current management style?

• How has your management style changed in the last ten years?

• Can you discuss some of your past leadership roles?

• How do you rate yourself as a professional? As an executive?

• Can you discuss the importance of your job as it relates to your family?

• What was the most difficult ethical decision you have had to make? What was the result?

• How do you show your anger or frustration?

• What is the worst situation you have faced in your professional life? How did you deal with it? What happened?

• Are you a leader? A good manager? Analytical? Give an example we can verify.

• Have you fired people? When and why?

• Will you be out to take your boss’s job?

• Can you tell me about someone who has influenced you personally or professionally?

• Have you ever stolen anything from a company?

• Can you tell me about the last time you broke the rules?

• What is the funniest thing that has ever happened to you?

• What was the worst career mistake you have ever made and what have you learned from it?

Position-Specific Interview Questions

• What interests you about this job? What prompted you to apply with our company?

• What do you know about this company?

• Why do you want to work for this organization?

• Why are you the best person for the job?

• What can you do for us that someone else cannot do?

• In what ways do you think you can make a contribution to our firm?

• How long would it take you to make a contribution to our firm?

• What qualifications do you have that makes you think you will be successful in this business? Why should we hire you? Why should we hire you instead of any of the other well-qualified candidates?

• Do you prefer large or small organizations? Why?

• What interests you most about the position we have? The least?

• What challenges are you looking for in a position?

• Is there anything I haven't told you about the job or company that you would like to know?

• What business, credit, or character references can you give us?

• How long do you expect to work here? When will you know when it is time to leave?

• Would you object to working for a woman?

• Do you generally speak to people before they speak to you?

• Do you not feel you might be better off with a different size firm than ours?

• What do you think it takes to be successful in an organization such as ours?

• In what ways do you think you can make a contribution to our organization?

• Have you ever been a member of a union? Worked with union members?

• Are you willing to work overtime?

• What type of work environment are you most comfortable with?

• Why do you think you might like to live in the community in which our organization is located?

• Can you list five things about yourself that would make you an asset to any organization that hired you?

• In addition to your educational and professional experiences, what else would you like us to know about you in order to make an appropriate decision?

• What kind of hours are you used to working or would like to work?

Interview Questions Related to Remuneration and Benefits

• What kind of salary are you worth and why? What are your salary requirements?

• What starting salary do you expect as an employee?

• What company benefits are most important to you?

• How do you feel about an income made up totally of commissions?

• When comparing one company offer to another, what factors will be important to you besides starting salary?

• How important is starting salary to you when considering our company's job offer?

• Can you explain your salary history?

Relocation & Travelling Interview Questions

• Are you willing to travel?

• Are you willing to go where the organization sends you? Are you willing to relocate?

• Do you mind traveling?

• How do you feel about relocating during a career with our company?

Future Career Goals Interview Questions

• What are you looking for in your next job? What is important to you?

• What are your goals for the next five years / ten years? How do you plan to achieve those goals?

• What are your salary requirements - both short-term and long-term?

• How much money do you hope to earn five years from now?

• What new goals or objectives have you established recently? Why?

• What do you know about opportunities in your field?

• What are the most important rewards you expect from your career?

• What would you be giving up in your present job to take our position?

• What position have you held that has been most meaningful to you and why?

• What improvements would you have made in you last job?

• What kind of challenge are you looking for?

• What do you think determines a person's progress in a good company?

• How do you determine or evaluate success?

• What personal characteristics are necessary for success in your field?

Interview Questions Related to Education

• How does your education and experience relate to this position?

• Why did you not do better in college?

• Can you summarize your educational background for me?

• Why did you major in _____________?

• What courses did you like the most? The least? Why?

• Do you feel you have done the best scholastic work for which you are capable?

• Can you describe your most rewarding accomplishment since you've started college?

• Can you describe your study habits?

• How did you finance your education?

• Do you feel you received a good general education?

• Why did you drop out of school?

• How do you spend college vacations?

• What extra-curricular activities are you involved in? What have you gained from these experiences?

• Do you have plans for furthering your education?

• If you could start college over, what would you do differently?

• Why did you choose to major in this field of study? Why did you choose your particular college or university?

• What were your favorite college courses, those you liked most? Least? Why? Please tell me about accomplishments in your academic program that are relevant to your future career goals.

• What is your grade point average (GPA)? How do you feel about this? Should grades be used as an indicator of future career potential when an organization is considering new college graduates?

• What were your reactions to instructors or college faculty during your academic program? How would you relate your academic accomplishments to future career aspirations? How satisfied are you with your accomplishments in this academic program?

• What was the most difficult aspect of obtaining a college degree? What are your academic strengths? What courses gave you the most difficulty?

• If you could, what changes would you make in your school's academic program?

Interview Questions Related to Extracurricular Activities and College Experiences

• How do you spend your spare time? What are your hobbies? What was the last book you read? Movie you saw? Sporting event you attended?

• How do you spend your free time? What would you do if you had more of it?

• What extracurricular activities have you been involved in?

• Can you tell me about your accomplishments in extracurricular activities?

• What have been your greatest challenges for improvement of a campus organization? What do you enjoy doing most?

• Can you describe your most rewarding college experiences?

• Can you give examples of the challenges you experienced during your leadership positions with campus activities?

• If you could relive your college experiences, what would you do differently?

• What changes would you make in your overall campus life?

• During your campus activities, what positions did you hold?

Hypothetical Interview Questions

• What could you see as the major objectives of this job?

• If you were hired by our organization, how would you identity the major roles and responsibilities of your new position?

• What is your purpose in life?

• How would you resolve conflict in a group situation?

• If you were given this assignment, how would you proceed?

Closing Interview Questions

• What are your salary expectations?

• When can you start work?

• If we invite you to our facility in [city], would you be able to come?

• Is there anything else I should know about you?

• Do you have any other questions?

Table Index Optimization to Improve Dynamics GP Performance

I recently touched up some code I wrote for a client back in 2003. For them, Bob and I integrated GP Depot Management Work Order Entry/Update with Sales Order Processing (Document Number = Work Order Number) for quoting and invoicing repair work and after sales service. Part of this customization enabled them to inquire on their quotes and orders directly from Work Order Entry/Update.

The code simply queries the Sales Order Processing Work (SOP10100) and Sales Order Processing History (SOP30200) tables where the ORIGNUMB = Work Order Number and then opens the Sales Order Processing Document Inquiry, sets the From and To Document Number, and sets the Unposted or History radio button accordingly. This saves the service center personnel significant time when researching work order detail for customers which increases customer satisfaction.

After 6+ years they've accumulated a little history in SOP. The code behind the inquiry buttons placed on Work Order Entry/Update window was taking too long to run. With customer service agents on the phone with customers, waiting more than a few seconds for the system to return data was too long. It didn't take long to realize that there wasn't an index on the SOP30200.ORIGNUMB column. The impact of adding that index was phenomenal reducing wait time from 10+ seconds to what seems like just milliseconds.

To create a new index in SQL Server Management Studio:

1. Right Click on the table and click Design from the menu.
2. From the Table Design menu option select Indexes/Keys.
3. Click Add to create a new index.

4. Select the column(s) on which you want to create the index.

5. Name your indexes consistently so that you can query all them out of sysobjects later when you need to.

6. Set other properties as needed.

There is a lot of great information widely available about when and how to create new indexes. I strongly recommend you educate yourself and engage an expert to optimize your indexes.

Of course, you can use T-SQL to load new indexes on your tables. Here's a sample:

CREATE NONCLUSTERED INDEX IX_SOP30200_ORIGNUMB ON dbo.SOP30200
(
ORIGNUMB
)

WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Remember that when you upgrade GP, it's likely that you will need to reload your indexes as the upgrade process will drop the old tables and therefore your indexes. Plan your upgrades accordingly and make sure you script your indexes before you do. Otherwise, GP will generally react positively to the new indexes.

http://mbsguru.blogspot.com/2009/04/table-index-optimization-to-improve.html

Simple Table Backups with T-SQL

I was working on a project recently with a very experienced and respected GP consultant. He has taught me many things but I was able to show him something very simple that made his life much easier. Simple table level backups with T-SQL.

The following will select all of the data in SOP10100 into a new SOP10100_Bkup_03012009 table:

select *
into SOP10100_Bkup_03012009
from SOP10100

Remember that inserts, updates, and deletes often fire off events that could alter data in other tables. This won't backup those dependent tables so be mindful of the potential that a simple update to one table could affect data on many others.


http://mbsguru.blogspot.com/2009/03/simple-table-backups-with-t-sql.html

Custom Business Alerts

GP does a good job of giving you the ability to create business alerts to keep you informed of events that have or may occur based on conditions in your database. Sometimes, the need to create an alert outside of the functionality in GP does, amazingly, come up. Just today a post at http://groups.google.com/group/microsoft.public.greatplains/topics and a response by Polino @ DynamicsAccounting.net drove me to create a sample business alert using only T-SQL that you could use as a starting point to developing your own.

This alert, if scheduled to run periodically, will e-mail a list of users that have been logged into GP for longer than 12.5 hours or 750 minutes. It's pretty simple!

IF EXISTS
(
select datediff(mi,logindat+logintim, getdate()) as DURATION,--convert(datetime, convert(varchar(15), GetDate(), 114), 114) - LOGINTIM as DURATION,
USERID,
CMPNYNAM,
LOGINDAT,
LOGINTIM
from DYNAMICS.dbo.ACTIVITY
where datediff(mi,logindat+logintim, getdate()) > 750
)
BEGIN

DECLARE @SQL varchar(8000)

SET @SQL = 'select datediff(mi,logindat+logintim, getdate()) as DURATION,
USERID,
CMPNYNAM,
LOGINDAT,
LOGINTIM
from DYNAMICS.dbo.ACTIVITY
where datediff(mi,logindat+logintim, getdate()) > 750'

print @SQL

EXEC master.dbo.xp_sendmail @recipients = 'youralias@yourcompany.com',
@subject = 'Users Logged in beyond limit',
@message = 'Attached is a list of users that have been logged in beyond the limit',
@query = @SQL,
@attach_results = 'TRUE',
@width = 250
END

http://mbsguru.blogspot.com/2009/02/custom-business-alerts.html

Who has that record locked?

I love the GP Newsgroup. Helping others with their problems helps me learn more about GP. Here's what I tought myself today in response to a newsgroup post.

Run this against your company database to select the users which users have SOP Documents locked:

select distinct l.row_id, t.dex_row_id, t.SOPNUMBE, a.USERID
from tempdb.dbo.Dex_Lock l
inner join SOP10100 t
on l.row_id = t.dex_row_id
inner join DYNAMICS.dbo.Activity a
on l.session_id = a.SQLSESID
where right(rtrim(table_path_name), 8) = 'SOP10100'

http://mbsguru.blogspot.com/2007/12/who-has-that-record-locked.html

SQL Nugget - shortcut to create INSERT into

Here's a SQL nugget I love to use when writing stored procs, triggers, and the like. Copy and paste this into query analyzer and replace %TableName% with the name of the Table in which you want to insert records. Execute it to return the INSERT into statement complete with default values for every field in the table. Paste the results into your object and populate the fields with your values. This can be a real time save for developers and when doing data conversions.

A slick cat we'll call Mo gave this to me. I can't take credit for it.

set nocount on
DECLARE @sTableName varchar(128)set @sTableName = '%TableName%'
select @sTableName as sTableName into #tmpTableName
DECLARE @lTableID intset @lTableID = NullSELECT @lTableID = [ID] from sysobjects where (objectproperty(id, N'IsTable') = 1) and (id = object_id(@sTableName))
if (@lTableID is Null)begin print 'Table not found! Aborting.' returnend
SELECT [name], xtype, prec, scale, colorder, isnullable into #tmpColumns from syscolumns where ([id] = @lTableID) and (colstat = 0) order by colorder
alter table #tmpColumns ADD lRowID int not null identity, -- add an identity column to the temp table sDefault varchar(40) -- add a column to store the default that we want to enter for new rowsGO
UPDATE #tmpColumns set sDefault = case xtype when 34 then ''' ''' -- image when 35 then ''' ''' -- text when 36 then Null -- unique identifier when 48 then '0' -- tinyint when 52 then '0' -- smallint when 56 then '0' -- int when 58 then '''1/1/1900''' -- smalldatetime when 59 then '0.0' -- real when 60 then '0' -- money when 61 then '''1/1/1900''' -- datetime when 62 then '0.0' -- float when 99 then ''' ''' -- ntext when 104 then '0' -- bit when 106 then '.00' -- decimal when 108 then '0.0' -- numeric when 122 then '0.0' -- smallmoney when 165 then '0' --'convert(varbinary, '' '')' -- varbinary when 167 then ''' ''' -- varchar when 173 then '0' --'convert(binary, '' '')' -- binary when 175 then ''' ''' -- char when 189 then Null -- timestamp when 231 then ''' ''' -- nvarchar when 239 then ''' ''' -- nchar endDELETE #tmpColumns where sDefault is Null--select sDefault, xtype, [name] from #tmpColumns order by colorder, lRowID

DECLARE cur insensitive scroll cursor for select sDefault, [name] from #tmpColumns order by colorder, lRowIDOPEN cur
declare @sDefault varchar(40), @sName varchar(128)declare @sWork varchar(200)declare @sWork2 varchar(100)
select top 1 @sWork2 = sTableName from #tmpTableNameprint 'INSERT into ' + @sWork2print ' ('
FETCH first from cur into @sDefault, @sNameWHILE ( @@fetch_status = 0 )begin set @sWork = char(9) + @sName
FETCH next from cur into @sDefault, @sName -- if the fetch is good, add a ',' to the end if ( @@fetch_status = 0 ) set @sWork = @sWork + ','
print @sWorkendprint ' )'
print 'select'
FETCH first from cur into @sDefault, @sNameWHILE ( @@fetch_status = 0 )begin set @sWork = char(9) + @sDefault set @sWork2 = @sName
-- get the next record from the cursor FETCH next from cur into @sDefault, @sName
-- if the fetch is good, add a ',' to the end if ( @@fetch_status = 0 ) set @sWork = @sWork + ','
set @sWork = @sWork + char(9) + '-- ' + @sWork2 print @sWorkend
CLOSE curDEALLOCATE CUR
DROP table #tmpColumnsDROP table #tmpTableName

http://mbsguru.blogspot.com/2006/04/sql-nugget-shortcut-to-create-insert.html

Find tables, with data, that have a spefic column

We're going to change some Item Numbers in GP. I know, I can use PS Tools to do this but to use that you have to turn off your replication. Long story short, I don't wanna.

Anyway, I had to figure out which tables to update so I wrote a query that would return to me all of the tables, with data, that have an ITEMNMBR column:

select distinct o.Name
from SysColumns c
inner join SysObjects o
on c.id = o.id
inner join SysIndexes i
on c.id = i.id
where c.name = 'ITEMNMBR'
and o.xtype = 'u'
and rowcnt <> 0

http://mbsguru.blogspot.com/2007/12/find-tables-with-data-that-have.html

Deleting Empty Batches in SOP

Schedule this script to run periodically against your company database to delete empty SOP batches automatically. It will check to verify that there aren't any transactions in the batch and that there is not a batch activity record first.

DECLARE @INTERID varchar(10),
@CMPNYNAM varchar(31)

SET @INTERID = DB_Name()
SELECT @CMPNYNAM = CMPNYNAM from DYNAMICS.dbo.SY01500 where INTERID = @INTERID

DELETE SY00500
where BCHSOURC = 'Sales Entry'
and BACHNUMB not in (select BACHNUMB from SOP10100)
and BACHNUMB not in (select BACHNUMB from DYNAMICS.dbo.SY00800 where CMPNYNAM = @CMPNYNAM and TRXSOURC = 'Sales Transaction Entry')

http://mbsguru.blogspot.com/2007/11/deleting-empty-batches-in-sop.html

To Check or Not to Check: Understanding Landed Cost's Invoice Match and Revalue Inventory Options in Microsoft Dynamics GP

Dynamics GP presents two selection options in the Landed Cost Maintenance screen: Invoice Match and Revalue Inventory. These two options have profound accounting effects in how inventory and purchases are tracked in GP. The following is provided as is and was posted by Marge Swanson, Senior Software Development Engineer at Microsoft in response to a user's question on the Dynamics GP community board, but I felt it was important to rescue as it contains valuable information not found in the manuals or elsewhere:

You should determine whether or not to mark the Invoice Match checkbox based on how you want your distributions to be tracked for the Landed Cost. If you do not mark Invoice Match, the distributions will be reversed from the accrued purchases account used on the Shipment – which defaults from the Landed Cost card. If you mark Invoice Match, the distributions will be created with the Purchase Price Variance account on the Landed Cost Maintenance window.

This helps you track the variances to a separate account if you want to. If you also mark Revalue IV when you mark Invoice Match, the distributions will assigned to the inventory account associated with the item the landed cost is applied to. The cost basis for the item will also be updated for any cost variance on the Landed Cost.

Example: Invoice matching and distributions for landed costs

Marking the Invoice Match option for a landed cost record will affect account distributions. For example, suppose that a shipment is recorded for 10 items at $1 each. The landed cost uses the Flat Amount cost calculation method, and the flat amount is $0.50.

The distributions for the shipment would look like this:

Inventory                                  $10.50
Accrued Purchases - Landed Cost            ($0.50)
Accrued Purchases – Inventory             ($10.00)

Suppose that when the invoice is received, the cost of the goods is unchanged, but the landed cost has increased to $0.75. If Invoice Match is not marked, the account distributions would be as follows.

Accrued Purchases - Landed Cost             $0.75
Accrued Purchases – Inventory              $10.00
Accounts Payable                          ($10.75)

If Invoice Match is marked and the Revalue Inventory option is marked for the cost variance, the account distributions would be as follows:

Accrued Purchases - Landed Cost             $0.50
Accrued Purchases - Inventory              $10.00
Inventory                                   $0.25
Accounts Payable                          ($10.75)

If Invoice Match is marked and Revalue Inventory option is not marked for the cost variance, the account distributions would be as follows:

Accrued Purchases - Landed Cost             $0.50
Accrued Purchases - Inventory              $10.00
Purchase Price Variance                     $0.25
Accounts Payable                          ($10.75)

Until next post!

GP 10 SDK

http://www.microsoft.com/downloads/thankyou.aspx?familyId=16d5bcea-c965-4f9a-a965-f4a81b0dd564&displayLang=en