Can Database Function Criteria be a Date? - jr

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

If you mean greater than 1/1/2007? - Peo Sjoblom

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

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

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?

I fixed the #Value! - jr

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

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

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

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

Try

=ISTEXT(cell)

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



--


Regards,


Peo Sjoblom

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

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

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

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

Can Database Function Criteria be a Date? - jr

Thanks a ton.

john.NOSPAMr.voiNOSPAMt@delNOSPAMphi.com

jrv

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

I just sent you an email with a sample workbook attached


--


Regards,


Peo Sjoblom