clarification:
~~~
remember: the Format Function always returns a string
keep the data type by using the Format PROPERTY.
Warm Regards,
Crystal
*
(: have an awesome day :)
*
--- On Mon, 8/29/11, Crystal <strive4peace> wrote:
> Hi Art,
>
> adding on to Duane's great advice...
>
> also print the Help for the Format Function to learn the
> different Format codes ... about 4 pages. Quickest way
> to the right help article is this:
>
> 1. Ctrl-G
> go to the debuG (Immediate) window
>
> 2. type --> Format
> and press F1 while you are still on the word -- help for
> the Format Function will appear
>
> a good way to learn is to press F1 on keywords in a module
> sheet -- or type a word in the debuG window (CTRL-G) and
> press F1
>
> ~~~~~~~~~~~~~~~~~~~
> * Object Browser *
>
> (Alt-F11 gets you to VBE - Visual Basic Editor)
>
> press F2 in VBE to View the Object Browser
> or, from the menu --> View, Object Browser
>
> In Object Browser, change the library to "VBA" (for
> instance, instead of <all libraries>) and look at the
> classes (categories) of functions -- click on a class and
> then click on a function in the right pane. To get the help
> for that function, press F1
>
> The VBA library is the most basic library and a great place
> to start exploring. to see all the date functions for VBA,
> choose the "DateTime" Class. Whenever you select
> something, look at the bottom of the window for specific
> notes. Press F1 for the Help article
>
> To lookup properties and methods for different objects like
> forms, tabledefs, etc, change the library to Access
>
> Explore the different libraries you have to pick from and
> see what is available in each. Remember to remove them
> after you look ;)
>
> the Object Browser is a GREAT way to learn
>
> ********************
>
> on Format codes, Bill Jelen (MrExcel) has excellent video
> tutorials on YouTube. The same rules apply in Access
>
> ~~~~~~~~~~~~~~
> *Format Codes*
>
> Quarters or Weeks - 1074 - Learn Excel from MrExcel
> Podcast
> http://www.youtube.com/user/bjele123#p/u/216/Tv2D8M9F_p0
>
>
> in Access, you do not need a VBA wrapper function to handle
> the extended codes -- they all work in the Format property
> too <smile>
>
> ~~~~~~~~~~~~~~
> *Format Zones*
>
> Formatting Zones - 1063 - Learn Excel from MrExcel Podcast
> http://www.youtube.com/watch?v=52-7UQKzHdw
>
> Access has 4 zones: positive, negative, zero, null
> In Excel, the 4th zone is for text
>
> ~~~~~~~~~~~~~~
> *Colors in Format Codes*
>
> [Color 53] - 1142 - Learn Excel from MrExcel Podcast, by
> Bill Jelen
> http://www.youtube.com/watch?v=kyFVDk0Lma0
>
> ~~~
> remember: Format always returns a string
>
> Warm Regards,
> Crystal
>
> *
> (: have an awesome day :)
> *
>
>
> --- On Sun, 8/28/11, Duane Hookom wrote:
>
> >
> > Art,
> > Do yourself a favor and lookup the various date
> functions
> > and formats. Some important functions are:
> > DatePart(), Month(), Year(), DateSerial(), Format(),
> > DateAdd(), and others.
> >
> > Then open the debug window (press Ctrl+G) and try them
> out.
> >
> > ? Format(Date(),"yyyyq")
> > ? DatePart("q",Date())
> > ? your fun tests go here....
> >
> >
> =DCount("ApplicationID","qryWaitingListCurrentMonth","Format(StatusDate,"yyyyq")
> > = Format(Date(),"yyyyq")")
> >
> > Duane Hookom
> > MS Access MVP
> >
> >
> > From: dbalorenzini@yahoo.com
> > Date: Sun, 28 Aug 2011 21:11:47 -0700
> >
> > This was the winner for the previous month:
> >
> >
> =DCount("ApplicationID","qryWaitingListCurrentMonth","
> > StatusDate BETWEEN
> > DateSerial(Year(Date()),Month(Date())-1,1) And
> > DateSerial(Year(Date()),Month(Date()),0)")
> >
> > Can you send an example of the quarter expression?
> >
> > With warm regards,
> >
> > Arthur Lorenzini| SQL Server/Access Developer l alorenzin@live.com
> > Office: 605-338-0947| Mobile: 605-857-9137 | Fax:
> > 605-338-0947
> >
> >
> > From: Duane Hookom
>
> > Subject: RE: [MS_AccessPros] Dcount not working
> > The previous month is available by subtracting 1 from
> the
> > current month. You can use
> Format(DateExpression,"yyyyq") to
> > compare year and quarter.
>
> Duane Hookom
> MS Access MVP
>
> > dbalorenzini:
>
> > The original code
> > was something I found on the internet I think it came
> from
> > Access Junkies or somewhere.This work though.
> >
> Thanks.=DCount("ApplicationID","qryWaitingListCurrentMonth","Year(StatusDate)
> > = Year(Date()) AND Month(StatusDate) =
> Month(Date())")How
> > would I find the previous month's count and also I
> would
> > need to do it by quarters?With warm regards,Arthur
> > Lorenzini| SQL Server/Access Developer l alorenzin@live.comOffice:
> > 605-338-0947| Mobile: 605-857-9137 | Fax:
> 605-338-0947
> > 1316 E. 7th Street Sioux Falls, SD 57103 SQL
> > Server DevelopmentDatabase Adminstration
> ServicesMicrosoft
> > Access
> > Development Grant Writing TA ServicesIT Assessment
> > ServicesSoftware Application Training From: Duane
> Hookom
> > <duanehookom@hotmail.com>To:
> > Access Professionals Yahoo Group <ms_access_professionals@yahoogroups.com>Sent:
> > Sunday, August 28, 2011 2:09 PMSubject: RE:
> [MS_AccessPros]
> > Dcount not workingI don't understand what you are
> attempting
> > to do with the date stuff. Is StatusDate an actual
> Date
> > field? I would
> >
> try:=DCount("ApplicationID","qryWaitingListCurrentMonth","Year(StatusDate)
> > = Year(Date()) AND Month(StatusDate) =
> >
> Month(Date())")or=DCount("ApplicationID","qryWaitingListCurrentMonth","Format(StatusDate,'yyyymm')
> > = Format(Date(),'yyyymm')")Duane HookomMS Access
> MVPTo: MS_Access_Professionals@yahoogroups.comFrom:
> > dbalorenzini@yahoo.comDate:
> > Sun, 28 Aug 2011 11:22:43 -0700Subject: Re:
> [MS_AccessPros]
> > Dcount not working Great
> >
> Catch.=DCount("ApplicationID","qryWaitingListCurrentMonth","
> > StatusDate BETWEEN Year(StatusDate) = Year(Date())
> AND
> > Month(StatusDate) = Month(Date()) ")But this express
> should
> > be returning records in the current month but it is
> > returning 0. I checked the table and there are records
> in it
> > for August. It should return all the records from the
> 1st
> > day of the month to the last day of the month.With
> warm
> > regards,ArthurLorenzini| SQL Server/Access Developer l
> alorenzin@live.comOffice:
> > 605-338-0947| Mobile: 605-857-9137 | Fax:
> 605-338-09471316
> > E. 7th Street Sioux Falls, SD 57103 SQL Server
> > DevelopmentDatabase Adminstration ServicesMicrosoft
> Access
> > Development Grant Writing TA ServicesIT Assessment
> > ServicesSoftware Application Training From: Duane
> Hookom
> > <duanehookom@hotmail.com>To:
> > Access Professionals Yahoo Group <ms_access_professionals@yahoogroups.com>Sent:
> > Sunday, August 28, 2011 12:23 PMSubject: RE:
> [MS_AccessPros]
> > Dcount not workingThere is no field
> > [ApplicationStatusTypeID] exposed in the SELECT
> section of
> > qryWaitingListCurrentMonth. Why is this even included
> > in the DCount() since records in the query are
> already
> > filtered?Duane HookomMS Access MVPTo: MS_Access_Professionals@yahoogroups.comFrom:
> > dbalorenzini@yahoo.comDate:
> > Sun, 28 Aug 2011 09:50:59 -0700Subject: Re:
> [MS_AccessPros]
> > Dcount not working
> >
> =DCount("ApplicationID","qryWaitingListCurrentMonth","ApplicationStatusTypeID
> > =3 AND StatusDate BETWEEN Year(StatusDate) =
> Year(Date())
> > AND Month(StatusDate) = Month(Date()) ")THis is what I
> have
> > now and it's still throwing a error. The is the syntax
> for
> > qryWaitingListCurrentMonth.SELECT
> > tblApplicationStatus.ApplicantID,
> > tblApplicationStatus.StatusDate,
> > tlkpApplicationStatusType.ApplicationStatusTypeName,
> > tblApplicationStatus.ApplicationIDFROM
> tblApplicationStatus
> > INNER JOIN tlkpApplicationStatusType ON
> > tblApplicationStatus.ApplicationStatusTypeID
> >
> =tlkpApplicationStatusType.ApplicationStatusTypeIDWHERE
> >
> (((tblApplicationStatus.ApplicationStatusTypeID)=3));With
> > warm regards,Arthur Lorenzini| SQL Server/Access
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
> MS_Access_Professionals-fullfeatured@yahoogroups.com
>
>
>
Tidak ada komentar:
Posting Komentar