Help with macro to calculate a date

[rivkovitch]

I am working with the Bug Tracking database that i downloaded from microsoft's website and have changed it to suit the needs of my business. I need help with a macro that will classify an account type by account number, and also calculate data based on that customer state.

An example would be for the following criteria to be used

1. Account 12345678901 = lease and 12345678901234567 = purch
2. date of issue
3. customer state
4. time to have resolved

Customer A has a lease in new york, and from date of issue to resolve is 25 days.
Customer B has a purchase in New York, and from date of issue to resolve is 15 days.




I need to either import an existing excel macro or create a new one for these calculations. Can anyone help with this? Due to proprietary info in database, I cannot be more specific, but I have no access macro experience and will accept any help that can be offered.

[June7]

So a lease account number is only 11 characters and a purch account number is 17 characters? Is that the criteria to determine the type?

Does the period change by state?

Learn about DateAdd function. However, default unit is day when adding a value to a date. So something like:

[Date of Issue] + IIf(Len(Account)=11, 25, 15)

[rivkovitch]

[QUOTE=June7;144129]So a lease account number is only 11 characters and a purch account number is 17 characters? Is that the criteria to determine the type?

The length of the account number is what determines the account type

Does the period change by state? Yes. It will be 10 days in must places, and circumstances and will vary between 15-25 days in only certain exceptions.

I had already considered the date add function, but I knew I had to have the account type and state specific rules put in. I just don't know how that is going to work


Thank you for the help

[June7]

If the location is also a factor, use a table the has the periods associated with each state, have fields for lease and purch. Include that table in query that joins to other table with state data. Then the period fields will be available to use in calculation.

[Date of Issue] + IIf(Len(Account)=11, [leaseperiod], [purchperiod])

Otherwise, have fields in the primary table for the period values.

[rivkovitch]

If the location is also a factor, use a table the has the periods associated with each state, have fields for lease and purch. Include that table in query that joins to other table with state data. Then the period fields will be available to use in calculation.

[Date of Issue] + IIf(Len(Account)=11, [leaseperiod], [purchperiod])

Otherwise, have fields in the primary table for the period values.

How do I tell it to go to the appropriate state code on each record? Is that something that would just be executed in the query to pull the correct code?

Or do I instead need to put in a macro command to match the state code and then open the query to calculate the date?

Based off of what you sent before, here is the query that I created to define based on length of account number to determine which value will be used:

 [RepoDate]+IIf("len[account]"=11,[LeasePeriod],[RetailPeriod])

does that look right?

[June7]

It knows the appropriate state code because the tables are joined on the state code fields in each table.

Don't put quote marks in the expression. Missing parens for the Len function. Look at my suggestion again.