Excel for Life: 10 new messages in 8 topics

microsoft.public.access.modulesdaovba
http://groups.google.com/group/microsoft.public.access.modulesdaovba?hl=en

microsoft.public.access.modulesdaovba@googlegroups.com

Today's topics:

==============================================================================
TOPIC: how to code sum if (...) in access same like sumif(......) in exce
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/cfc6faaeb5f4ddcb?hl=en
==============================================================================

== 1 of 2 ==
Date: Sun, Nov 18 2007 4:20 pm
From: hisham

i want to sum if data in table in access database. means same item must be
calculate sum.
sum if like sum if in excel. how to write code?
any body can help me?

== 2 of 2 ==
Date: Sun, Nov 18 2007 7:47 pm
From: Marshall Barton

hisham wrote:

>i want to sum if data in table in access database. means same item must be
>calculate sum.
>sum if like sum if in excel. how to write code?

Try this kind of expression:
Sum(IIf(condition, item, 0))

--
Marsh
MVP [MS Access]

==============================================================================
TOPIC: create calender code
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/4e605b01bc9b7c06?hl=en
==============================================================================

== 1 of 1 ==
Date: Sun, Nov 18 2007 4:41 pm
From: "Tony Toews [MVP]"

balu <balu@discussions.microsoft.com> wrote:

>is their any code which can create an TABLE / QUERY containing all the days
>of the
> current year like from 1/1/07 to 31/12/07 and
> 1/1/08 to 31/12/08 on next year and so on,,

Why? What is the objective or problem? Maybe we can suggest an
alternative?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at

http://www.granite.ab.ca/accsmstr.htm

Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

==============================================================================
TOPIC: 8787
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/19bce1ee1a3d81da?hl=en
==============================================================================

== 1 of 1 ==
Date: Sun, Nov 18 2007 5:25 pm
From: "Detenciones 44"

8787

==============================================================================
TOPIC: Visible = True Vs Visble = False
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/ab35e58d40612388?hl=en
==============================================================================

== 1 of 1 ==
Date: Sun, Nov 18 2007 5:36 pm
From: dsc2bjn

It kind of worked, but I had a couple of little cliches with the displaying
of information which started on one page, but could not finish on the same
page. Sometimes a Function would display without lines and all the
activities would start on the next page.

After I changed how the "Keep Together" attribute worked for the Sorting and
Grouping and in the Section headers I finally got it working the way it
should.

Thanks!!

Simple usually is better.

Thanks again!!

"Marshall Barton" wrote:

> I already understood that. Try my suggestion and see how
> close it comes.
> --
> Marsh
> MVP [MS Access]
>
>
> dsc2bjn wrote:
> >Customer doesn't like using the Single Header lines. They want it to look
> >similiar to an Excel spreadsheet, but only to display the FunctionOrder,
> >Function, ActivityOrder, and Activity when the value changes.
> >
> >F-1 Function1Text A-1 Activity1Text Risk
> > Risk
> > Risk
> > A-2 Activity2Text Risk
> > Risk
> >F-2 Function2Text A-1 Activity1Text Risk
> > Risk
> > A-2 Activity2Text Risk
> >F-3 Function3Text A-1 Activity3Text Risk
> >
> >
> >"Marshall Barton" wrote:
> >> You might want to try a completely different approach that I
> >> think(?) does what you want without any complicated logic.
> >>
> >> Create a group with header for each of the fields you want
> >> to hide. Add a text box for the field to the group header
> >> and make sure the group header sections are the same height
> >> as the detail section. The code for the group header
> >> sections' Format event would simply be:
> >> Me.MoveLayout = False
> >>
> >> The only controls in the detail section would be the ones
> >> you want to display on every line. The only(?) code needed
> >> for the detail section would be the line drawing code.
> >>
> >>
> >> dsc2bjn wrote:
> >> >I have a report which returns a series of records for which I wish to
> >> >suppress the duplicate values for a few fields. The "Hide Duplicate"
> >> >attribute will only hide the duplicates on a single page of the report. I
> >> >which to hide all duplicates for like values throughout the report.
>
>

==============================================================================
TOPIC: Runtime Error 2448
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/0c996128704eb16f?hl=en
==============================================================================

== 1 of 1 ==
Date: Sun, Nov 18 2007 5:39 pm
From: Philippe Perrault

After much poking and prodding and searching the site I found this solution:

Forms!APFTMainform.APFTSubform!PUScore = Dlookup(...)is required to write to
the control in the subform and now the code works as desired.

"Philippe Perrault" wrote:

> I am trying to write a an Afterupdate Sub that takes a person's age and
> gender and goes to the table PushUps and gets the correct score based on the
> number of pushups compelted, age and gender and writes it to the record.
> When I changed the variable myPUScore to the following
> Forms!APFT_Main!PUScore I get the run time error 2448: You can't assign a
> value to this object.
>
> The variable myPUScore is how I was testing the DLookup to make sure it was
> retrieving the correct value.
>
> Private Sub PURaw_AfterUpdate()
>
> Dim myGender As String
> Dim myAge As Integer
> Dim myPUScore As Integer
>
> myGender = DLookup("[APFTFormQuery]![Gender]", "[APFTFormQuery]",
> "[AlphaRosterID] = " & Forms!APFT_Main!AlphaRosterID)
> myAge = DLookup("[APFTFormQuery]![Age]", "[APFTFormQuery]", "[AlphaRosterID]
> = " & Forms!APFT_Main!AlphaRosterID)
>
> If myGender = "Male" Then
> If myAge <= 21 Then
> Forms!APFT_Main!PUScore = DLookup("[PushUp]![Group 1M]", "[PushUp]",
> "[PushupID]= " & Forms!APFT_Main!PURaw)
>
> 'The Else statement checks for the age of the female and runs the
> appropriate DLookUp
> Else
> If myAge <= 21 Then
> myPUScore = DLookup("[PushUp]![Group 1F]", "[PushUp]", "[PushupID]= " &
> Forms!APFT_Main!PURaw)
> End Sub

==============================================================================
TOPIC: Dlookup error message 'M'
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/4d5d4a746b54365b?hl=en
==============================================================================

== 1 of 2 ==
Date: Sun, Nov 18 2007 6:23 pm
From: Tknows

yes you are absolutely correct. i corrected the code by adding but now I get
an invalid use of null error.

strCode = Me.Combo58
intPictureYear = DLookup("SportYear", "Sport", "SportCode=" & "'strCode'")

I have a feeling the strcode is not being passed correctly and the lookup is
coming up with a null value because it can't access the record

"Marshall Barton" wrote:

> Tknows wrote:
>
> >The below code has given me the following error
> >The expression you entered as a query parameter produced this error: 'M'
> >
> >code:
> >Private Sub Combo58_AfterUpdate()
> >Dim intPictureYear As String
> >
> >intPictureYear = DLookup("SportYear", "Sport", _
> > "SportCode=" & Me!Combo58)
> >
> >End Sub
>
>
> That usually means that the SportCode field is a text field
> and that you need to enclose the value in quotes:
>
> "SportCode='" & Me!Combo58) & "' "
>
> --
> Marsh
> MVP [MS Access]
>

== 2 of 2 ==
Date: Sun, Nov 18 2007 7:42 pm
From: Marshall Barton

Tknows wrote:

>yes you are absolutely correct. i corrected the code by adding but now I get
>an invalid use of null error.
>
>strCode = Me.Combo58
>intPictureYear = DLookup("SportYear", "Sport", "SportCode=" & "'strCode'")
>
>I have a feeling the strcode is not being passed correctly and the lookup is
>coming up with a null value because it can't access the record

Your feeling is correct, but your translation of my
suggested code is not ;-)

. . . , "SportCode='" & strCode & "' ")

--
Marsh
MVP [MS Access]

==============================================================================
TOPIC: Birthdays this month
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/e9b69c449420462f?hl=en
==============================================================================

== 1 of 1 ==
Date: Sun, Nov 18 2007 8:15 pm
From: Chuck

Hi, i have a form (frmbirthdays) with a combo box (cboMonth) with months of
the year starting at January, February etc. I have a subform
(subfrmBirthdays) with following fileds; client name, gender, Client_DoB,
Current Age, Happy Birthday.
Current Age is calculated with formula:
=DateDiff("yyyy",[Client_DoB],Now())+Int(Format(Now(),"mmdd")<Format([Client_DoB],"mmdd"))

Client_DoB is Date Data Type. dd/mm/yyyy
I would like the subfrm filtered by the month in the combo box when selected
to reflect birthdays in November for example. I would also like Happy
Birthday to reflect Age + 1 but only if the curent date is < the DoB. Can you
help with VBA please...thanks

==============================================================================
TOPIC: Visible = True and Visible = False
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/6b1da785c92f7cd8?hl=en
==============================================================================

== 1 of 1 ==
Date: Sun, Nov 18 2007 10:56 pm
From: John Nurick

On Sat, 17 Nov 2007 11:24:01 +0100, "Wolfgang Kais"
<w.kais@removethisgmxthistoo.de> wrote:

>False

<g>
--
John Nurick - Access MVP

==============================================================================

You received this message because you are subscribed to the Google Groups "microsoft.public.access.modulesdaovba"
group.

To post to this group, visit http://groups.google.com/group/microsoft.public.access.modulesdaovba?hl=en

To unsubscribe from this group, send email to microsoft.public.access.modulesdaovba-unsubscribe@googlegroups.com

To change the way you get mail from this group, visit:
http://groups.google.com/group/microsoft.public.access.modulesdaovba/subscribe?hl=en

To report abuse, send email explaining the problem to abuse@googlegroups.com

==============================================================================
Google Groups: http://groups.google.com?hl=en

Excel for Life

Senin, 19 November 2007

10 new messages in 8 topics - digest

Tidak ada komentar:

Arsip Blog

Blogeroll

Mengenai Saya