Replies

You guys are AWESOME!!!! Thanks for your time and effort. Have a great day.

WOW!!!! Now that’s what I’m talking about….it does exactly what I was looking for….RG, you rock.

Sorry about not uploading the sample spreadsheet, however, with your solution, it turns out I didn’t need it; with that said, I’m still curious if what you applied could work with your original post and my adaption? The sample spreadsheet is attached this time.

However, I understand if you don’t have time and others need your help.

Again, thanks a bunch for everyone’s time and effort, it’s greatly appreciated.

RG

I was wondering if we could take your code a step further and am attaching a sample spreadsheet for clarification purposes.

Again, thanks in advance.

Thank all of you very much. Each suggestion works, however, the one that ultimately will provide what I want (since I eventually want to use it on any given cell) is RG’s. You guys rock.

Paul,

Yes, I want the name of the spreadsheet that is currently open. I only want a portion of the name, the first left 4 characters. Since my post however, I put a function together to capture the current open spreadsheet and is below:

Function GetMyName() As StringDim sAgency As String

GetMyName = ActiveWorkbook.Name
sAgency = Left(GetMyName, 4)

End Function

This will give return the value I’m looking for but what I want to do next is place that value in a cell in the spreadsheet if another cell IS NOTNULL. I understand that a Function can’t return values to cells so I need an IF THEN ELSE statement and trigger it with an OnEvent action; something like this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If IsNull(Me.ActiveSheet!L3) Then
Me.ActiveSheet!b3 = “”
Else
Me.ActiveSheet!b3 = sAgency
End If

End Sub

So this is my newest delima, it doesn’t work! If you could offer a suggestion, I would be most grateful.

What I need, is it acheivable if I change the form to “Single” and apply a list box and apply code to the AfterUpdate Event of the list box? Along the lines of:

[codebox]if IsNull(lstOutcome.column(4)) then
msgbox “1”
else
msgbox “2”
end if[/codebox]

Thanks Wendell, much appreciated.

Changing to single isn’t an option. What if I removed the option group and put a field with in the dataset, say “int_OutcomeAssigned”, make it a bound control on the form. Put code in the OnLoad event along these lines.

[codebox]If int_OutcomeAssigned=0 then
int_OutcomeAssigned.visible=False
Else
int_OutcomeAssigned.visible=True
end if
[/codebox]

What type of form are you using? A single form or a continuous form?

Continuous

Create a query based on the table.
Add a calculated column to the query grid:

d: [dDateOfAttempt]-Weekday([dDateOfAttempt],2)

Also add the Count field.
Select View | Totals.
Change the Total option for the Count field to Sum.
Switch to datasheet view.

The SQL for this query is

SELECT [dDateOfAttempt]-Weekday([dDateOfAttempt],2) AS d, Sum(NameOfTable.Count) AS SumOfCount
FROM NameOfTable
GROUP BY [dDateOfAttempt]-Weekday([dDateOfAttempt],2);

where NameOfTable is the name of the table.

Sorry for slow response, thanks Hans, works perfectly

By the way, do you want to do this in Access or in Excel? You have posted in the Access forum, but the attachment is an Excel workbook.

I want to do this in Access (in a query preferrably. Even if the function is in a module, I can call the function within a query). I only put the example in Excel.

Thanks.

Why do most weeks end on Friday, but one of them on Thursday (July 9)?

In the attached example that was an error on my part. The week actually ended on 7/10/2009. I’ve made the change and have attached a new example. With that said, if a holiday is in the middle of the week, it has to be accounted for as well. i.e. if 7/4/2009 was on a Friday, the company would be off work, therefore there would be 3 days between activity.

The On Load event of the subform does not occur when it is made visible, but when the main form is loaded, whether the subform is visible or not. So the code you wrote is not executed when the subform is made visible.
You’ll have to show/hide controls on the subform from your main form code.

Thanks Hans,

That did the trick. Much appreciated.

Thanks Hans,

That works….I don’t know why I insist on overlooking the obvious and making my work harder than it needs to be.

Much appreciated.

Thanks Hans,

Sorry for the delayed response, however your suggestion worked perfectly.

As always Hans, thank you and YOU ARE THE BEST.