Can Database Function Criteria be a Date?
 

Can the criteria for a database function be in the form of a Date (e.g.
1/1/07). If so how?

Can Database Function Criteria be a Date?
 

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



"jrv" wrote in message
...
Can the criteria for a database function be in the form of a Date (e.g.
1/1/07). If so how?

Can Database Function Criteria be a Date?
 

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
2007-01-01. However, the cell with the Database Function shows the #VALUE!
error.

Any suggestions?

"Peo Sjoblom" wrote:

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



"jrv" wrote in message
...
Can the criteria for a database function be in the form of a Date (e.g.
1/1/07). If so how?

Can Database Function Criteria be a Date?
 

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

"jrv" wrote:

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
2007-01-01. However, the cell with the Database Function shows the #VALUE!
error.

Any suggestions?

"Peo Sjoblom" wrote:

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



"jrv" wrote in message
...
Can the criteria for a database function be in the form of a Date (e.g.
1/1/07). If so how?

Can Database Function Criteria be a Date?
 

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




"jrv" wrote in message
...
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
2007-01-01. However, the cell with the Database Function shows the
#VALUE!
error.

Any suggestions?

"Peo Sjoblom" wrote:

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



"jrv" wrote in message
...
Can the criteria for a database function be in the form of a Date (e.g.
1/1/07). If so how?

Can Database Function Criteria be a Date?
 

Try

=ISTEXT(cell)

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



--


Regards,


Peo Sjoblom




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

"jrv" wrote:

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
2007-01-01. However, the cell with the Database Function shows the
#VALUE!
error.

Any suggestions?

"Peo Sjoblom" wrote:

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



"jrv" wrote in message
...
Can the criteria for a database function be in the form of a Date
(e.g.
1/1/07). If so how?

Can Database Function Criteria be a Date?
 

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
"test" file so I can take a look?

"Peo Sjoblom" wrote:

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




"jrv" wrote in message
...
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
2007-01-01. However, the cell with the Database Function shows the
#VALUE!
error.

Any suggestions?

"Peo Sjoblom" wrote:

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



"jrv" wrote in message
...
Can the criteria for a database function be in the form of a Date (e.g.
1/1/07). If so how?

Can Database Function Criteria be a Date?
 

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

so if your email address is



post it like

johndoeNOSPAMatNOSPAMyahoo.com

and I will send you a little sample


--

Regards,

Peo Sjoblom




"jrv" wrote in message
...
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
"test" file so I can take a look?

"Peo Sjoblom" wrote:

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




"jrv" wrote in message
...
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
2007-01-01. However, the cell with the Database Function shows the
#VALUE!
error.

Any suggestions?

"Peo Sjoblom" wrote:

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



"jrv" wrote in message
...
Can the criteria for a database function be in the form of a Date
(e.g.
1/1/07). If so how?

Can Database Function Criteria be a Date?
 

Thanks a ton.



jrv

"Peo Sjoblom" wrote:

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

so if your email address is



post it like

johndoeNOSPAMatNOSPAMyahoo.com

and I will send you a little sample


--

Regards,

Peo Sjoblom




"jrv" wrote in message
...
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
"test" file so I can take a look?

"Peo Sjoblom" wrote:

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




"jrv" wrote in message
...
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
2007-01-01. However, the cell with the Database Function shows the
#VALUE!
error.

Any suggestions?

"Peo Sjoblom" wrote:

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



"jrv" wrote in message
...
Can the criteria for a database function be in the form of a Date
(e.g.
1/1/07). If so how?

Can Database Function Criteria be a Date?
 

I just sent you an email with a sample workbook attached


--


Regards,


Peo Sjoblom




"jrv" wrote in message
...
Thanks a ton.



jrv

"Peo Sjoblom" wrote:

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

so if your email address is



post it like

johndoeNOSPAMatNOSPAMyahoo.com

and I will send you a little sample


--

Regards,

Peo Sjoblom




"jrv" wrote in message
...
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
"test" file so I can take a look?

"Peo Sjoblom" wrote:

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




"jrv" wrote in message
...
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
2007-01-01. However, the cell with the Database Function shows the
#VALUE!
error.

Any suggestions?

"Peo Sjoblom" wrote:

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



"jrv" wrote in message
...
Can the criteria for a database function be in the form of a Date
(e.g.
1/1/07). If so how?