Replies

LAST TIME:

I FIGURED OUT WHAT I NEED:

1. TODAY + 8 days = Next friday (5/2/2003) This will always give me a date somewhere within the next WorkWeek.

2. I need a function that will return BASED on 5/2/2003, the date range for M – F ( 4/28/2003 – 5/2/2003) This will be my range for the 1st week report.

3. I also need a function to just return the Monday of the week being calculated (5/2/2003, based on query 1, above) = 4/28/2003

IF I GET THIS:, then I can just modify the 1st query to TODAY + 16 days 2 weeks out |and| TODAY + 24 days for 3 weeks out.

FURTHER Update:

I am playing around with this function:

Week: Date()+[Number of days till next monday]

If I can MARK each Weekday with a number: Su=1 M=2 T=3 W=4 Tu=5 F=6 Sa=7

The I can make this formula: TODAY = 6 and MONDAY = 2 therefore there are 3 days until Monday

or: Week: Date()+3m = 4/28/2003

Maybe I need to get drunk?

I AM CHANGING MY REQUIREMENTS A LITTLE.

This is a little tricky to explain, but I can see something formulating in my head.
I want to report upcomming events for the next 3 weeks. So this will be a floating function.
Currently I have a Parameter Query that asks the person for the Monday Date and the Friday Date of the range they want to report on.

I want to indentify the M-F for the next three weeks. That way I can print a calendar of events for the next three weeks, ONE week at a time. If I can do this, I can create 3 identical reports that can be printed out individually as the user wants.

Scenario:
Today is Friday April 25. I have a [Date] field to work with as well.

Report1: Calendar for the Week of 4/28/2003 or [Calculating from TODAY, what is the NEXT MONDAY and capture only dates for Monday to Friday]

Report2: Calendar for the Week of 5/5/2003 or [Calculating from TODAY, what is the NEXT MONDAY + 7 DAYS TO FOLLOWING MONDAY and capture only dates for Monday to Friday]

Report1: Calendar for the Week of 5/12/2003 or [Calculating from TODAY, what is the NEXT MONDAY + 14 DAYS TO FOLLOWING MONDAY and capture only dates for Monday to Friday]

Thanks
Paul

Thanks HANSV. That worked!!!!!

Thanks for your concerns Andy. The numbers are a type of ID. But it was imported from Excel. It originally came out of a mainframe program and the operator stripped out the leading Zero’s. The ID starts from #1 to 9999999. I asked him to keep the zeros for the next export, but in the mean time I was thinking up a way for Access to handle it.

I am happy with the fix a created. Although its a two step process, it can be wrapped up in a macro.

Thanks guys but i figured it out.

Field: CostCent

In a query I put this: FullNum:Right(“000000″&[CostCent]),7)

Basically I made the query add 6 zeros then took the 7 digits from the right to left.

so:

12
34555
456789
34
1

Becomes:
00000012
00000034555
000000456789
00000034
0000001

Then taking the last 7 digits:
0000012
0034555
0456789
0000034
0000001

I simply created a Make Table Query to capture the numbers for later use.

Thanks and I hope this helps someone else.

:

I work with Access databases. That is first and foremost. Therefore I will take your advice to heart. I want to be able to Web-enable my Access Databases to offer my clients the flexibility of browser based databases without having to have larger servers in the back end like SQL. I will eventually work towards that for larger projects, but for smaller databases, its all about the front end. Thanks for the info. Other than that, I would like to also pick up some programming for advanced web design. I have been playing around with Dreamweaver and Flash, but its very basic stuff. I see myself writing code for some advanced designs, but I want to do the Database designs first. ASP looks like the way to go to ghet me off the ground.

Thanks againn for the information. I was looking at VB and ASP as well. Looks like I’ll be doing alot of reading and trying thinks out. What might also help is that I have VB. Microsoft C# is $100.00. So I’ll try the VB first before looking at a totally new system. That being said. Does anyone have any good books to read for VB. I got the ‘C# in 24 hrs’ from SAMS because Amazon .com readers found it very good. I’ll go back there to see what is a good VB book, but I’d like to get your opinions.

I spoke in detail to a friend of mine who has his own software company and he suggested Visual C#. We spoke in length and he said that based on the fact that I did not have any prior programming language, this would give me more flexibility in the long run. I’ll try this out as well as look into MS Frontpage. Thanks all

Nevermind: I figured it out. I used the SendObject funtion in a macro!

Actually I figured out the first one. I always come up with the answer right after I post. This is what I did. I created a Query that combined a bullet character (using the charmap) with the text. So in the query, it shows the data with a bullet before it. Then in the report, I put YES in the ‘Can shrink’ field. So not it prints only if Data is presend AND it containes a bullet.

http://support.microsoft.com/search/previe…b;en-us;Q209045%5B/url%5D Edited by WendellB to activate link 31-Oct-02 18:07 MST

ACC2000: Sample Expressions to Extract Portion of Text String

Maybe you build a small form with just the ID, Parets names and last name. In the form, the parents first names will be in separate fields. You can also program some check boxes, one for each name. So, you open a record and select one or both names, then click on a button that uses that information to open a full form with the volunteer info. It will use the names based on the check boxes you progammed.

This is just brainstorming. If you’re a tinkerer… this could be an afternoon project.

What happens if the parents are split up. Do you have First and Last for one Parent and a different LAST for another (remarried, etc?)

Since I was pressed for time. I simply created the report in column.
I have 4 reports all inmeded in one master report. The are stacked up vertcally.
So it lookes like this:

Mandated:
Name01—————–##——##
Name02—————–##——-##
Name03—————–##——##
Total———————-##——-##

CIRCLED:
Name01—————–##——##
Name02—————–##——-##
Name03—————–##——##
Name04—————–##——-##
Name05—————–##——##
Total———————-##——-##

CIRCLED:
Name01—————–##——##
Name02—————–##——-##
Name03—————–##——##
Name04—————–##——-##
Name05—————–##——##
Name06—————–##——-##
Name07—————–##——##
Total———————-##——-##

Grand Total————-##——-##

The main table has deals that have a status of Mandated Circled and closed.
I have three queries that pick out each of the status.
I have three separate reports that report on the 3 status
I have a MAIN report that has these three embedded in them.

The problem is that of the 8 people…. only 6 have Mandated… 7 have Circled… But all 8 have Closed

The first report has two fields: the Salesperson’s Name and their totals for 2002-2003
The Second report just has the totals for 2002-2003 because it I put the name in, it will be redundant
The Third report just has totals for 2002-2003 sam as report two.

I guess that I need to do is:

For the Mandated report, I need to report a ZERO total for the two salesreps that are missing… so their names will show up, in Alphabetical.

For the Circled report, I need to report a ZERO total for the salesrep that are missing… so their TOTALS will line up with the names in the first report.

For the Circled report, since all the reps have TOTALS, then their numbers should line up with the names on the left.

Hope this explains it.

Again, the focus of the queries is the Status field.