Search EggHeadCafe's Job Board
EggHeadCafe Silverlight WPF ASP.NET VB.NET C# Excel SQL Server SharePoint
search
Office GroupsView
Excel
Excel Charting
Excel Crashesgpfs
Excel Misc
Excel Newusers
Excel Programming
Excel Setup
Excel Worksheet Functions
Word Application Errors
Word Conversions
Word Customization Menustoolbars
Word Docmanagement
Word Drawing Graphics
Word International Features
Word Mail
Word Mailmerge Fields
Word Newusers
Word Numbering
Word Oleinterop
Word Pagelayout
Word Printingfonts
Word Setup Networking
Word Spelling Grammar
Word Tables
Word Visual Basica Addins
Word Visual Basica Customization
Word Visual Basica General
Word Visual Basica Userforms
Works Win
Office Developer Automation
Office Developer Com Add_Ins
Office Developer Officedev Other
Office Developer Outlook Forms
Office Developer Outlook Visual Basica
Office Developer Visual Basica
Office Misc
Office Setup
Officeupdate
Onenote
Outlook
Outlook Bcm
Outlook Calendaring
Outlook Contacts
Outlook Fax
Outlook General
Outlook Installation
Outlook Interop
Outlook Mac
Outlook Printing
Outlook Program_Addins
Outlook Program_Forms
Outlook Program_Visual Basica
Outlook Teamfolders
Outlook Thirdpartyutil
Project
Project Developer
Project Server
Visio
Visio Developer
Visio General
Word Visual Basica Beginners
Outlookexpress General
Office Communicator
Word Programming
Office Templates Misc

Group SummariesView
.NET Framework
Access
BizTalk
Certifications
CRM
DDK
Exchange Server
FoxPro
French
French .NET
Games
German
German .NET
Graphic Design
IIS
Internet
ISA Server
Italian
Italian .NET
Maps
MCIS
Miscellaneous
Mobile Apps
Money
MSN
Networking
Office
Ops Mgr
Publisher
Security
SharePoint
Small Business
Spanish
Spanish .NET
SQL Server
Systems Management Server
Transaction Server
Virtual PC / Virtual Server
Visual Studio
Win32
Windows 2000
Windows 2003 Server
Windows 7
Windows Live
Windows Media
Windows Update
Windows Vista
Windows XP
 

View All Microsoft Excel Worksheet Functions Posts  Ask A New Question 

Can Database Function Criteria be a Date?

jr posted on Thursday, October 11, 2007 4:57 PM

Can the criteria for a database function be in the form of a Date (e.g.
reply

 

If you mean greater than 1/1/2007?

Peo Sjoblom posted on Thursday, October 11, 2007 5:04 PM

If you mean greater than 1/1/2007?

=">2007-01-01"

or

=">"&TEXT(DATE(2007,1,1),"yyyy-mm-dd")

or


=">"&DATE(2007,1,1)


or

=">1/1/07"


I would advice against the latter 2, the first of those will display the
serial number of the date and the latter will fail if moved to another
regional setting


--


Regards,


Peo Sjoblom
reply

Yes I did mean greater than 1/1/2007. However, It doesn't seem to work.

jr posted on Thursday, October 11, 2007 5:21 PM

Yes I did mean greater than 1/1/2007.  However, It doesn't seem to work.
Here's what I did to check.

I created a simple database with "Date" as the first column and "Check" As
the second column (= to all 1s). I populated dates in the date column in the
syntax mm/dd/yy.  I then established a criteria array with one column two
rows, First row is labeled Date.  In the second row I typed the criteria as
you show below (i.e.  =">2007-01-01").  When I hit enter the cell shows
error.

Any suggestions?
reply

I fixed the #Value!

jr posted on Thursday, October 11, 2007 5:29 PM

I fixed the #Value! error.  However, DSUM is returning 0 on check eventhough
there are dates that meet the criteria.
reply

What's the formula you are using, it certainly works for me if I create the

Peo Sjoblom posted on Thursday, October 11, 2007 5:49 PM

What's the formula you are using, it certainly works for me if I create the
same, assuming you want to sum all checks with dates greater than 01/01/07

=DSUM(DataBase,"Check",E1:E2)

or to count the dates

=DCOUNT(DataBase,"Dates",E1:E2)

the D functions don't work if the source workbook is closed if you have the
formula in another workbook


--


Regards,


Peo Sjoblom
reply

Try=ISTEXT(cell)on both the dates and the check values, if they are imported

Peo Sjoblom posted on Thursday, October 11, 2007 5:52 PM

Try

=ISTEXT(cell)

on both the dates and the check values, if they are imported they may have
trailing/leading spaces



--


Regards,


Peo Sjoblom
reply

I was using the DSUM formula you show below. It's just not working for me.

jr posted on Thursday, October 11, 2007 6:14 PM

I was using the DSUM formula you show below.  It's just not working for me.
There must be something different. Is there anyway you can send me your
reply

Sure, post your email address but do it so no spambots can get a hold of itso

Peo Sjoblom posted on Friday, October 12, 2007 12:17 AM

Sure, post your email address but do it so no spambots can get a hold of it

so if your email address is

johndoe@yahoo.com

post it like

johndoeNOSPAMatNOSPAMyahoo.com

and I will send you a little sample


--

Regards,

Peo Sjoblom
reply

Can Database Function Criteria be a Date?

jr posted on Friday, October 12, 2007 6:56 AM

Thanks a ton.

john.NOSPAMr.voiNOSPAMt@delNOSPAMphi.com

jrv
reply

I just sent you an email with a sample workbook attached-- Regards,Peo

Peo Sjoblom posted on Friday, October 12, 2007 10:13 AM

I just sent you an email with a sample workbook attached


--


Regards,


Peo Sjoblom
reply

 
 

Previous Microsoft Excel Worksheet Functions conversation.