RE: [MS_AccessPros] Dcount not working -- Format, Help, Object Browser

 

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

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (12)

Recent Activity:

• New Members 3
• New Files 3

Visit Your Group

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___