Count Date from Text format field

kabilliu

Registered User.
Local time
Today, 16:50
Joined
Nov 2, 2012
Messages
18
Hi All,
I have a text format field with name “Finalized” that takes values as date format (dd/mm/yyyy) or other simple text or remains blank. I try to make a query (from query builder) counting the records with date> current date. For this I use the formula:

Code:
Exp1: DCount("[CountryofOrigin]","MainTbl","[CountryofOrigin]='ITALY' And DateValue(Left([Finalized],10)) < DATE()").

The above works fine only if field “Finalized” contains date values for all records. If in a record the field “Finalized” is blank or contains different text than a date format I take error msg. I tied also isdate function without success. Please help.

Thanks in advance.
 
You can try this:

Exp1: DCount("[CountryofOrigin]","MainTbl","[CountryofOrigin]='ITALY' And DateValue(Left(Nz([Finalized],#12/31/9999#),10)) < DATE()")
 
Thank you for your reply,

I tried your solution and works fine for dates count if some of the fields is blank. If contains other text is not working.

Thanks again
 
The field "Finalized" is a text format field that takes 3 type of values: Dates as dd/mm/yyyy format, a value of normal text (the words "pending" or "not pending) or still blank. I try to find a way counting the dates < current date in a query , counting blanks and text in another query.
 
See if this works:
Exp1: DCount("[CountryofOrigin]","MainTbl","[CountryofOrigin]='ITALY' And IsNumeric([Finalized]) And IIF(IsDate([Finalized]),DateValue(Nz([Finalized],#12/31/9999#)) < DATE()", 0)
 
Thanks for your interest,
unfortunately I take a syntax error. I tried to change the formula as :
Exp1: DCount("[CountryofOrigin]","MainTbl","[CountryofOrigin]='ITALY' And IsNumeric([Finalized]) And IIF(IsDate([Finalized]),DateValue(Nz([Finalized],#12/31/9999#)) < DATE(), 0)") and runs but I take 0 as result. I would like to remind you that the date format is dd/mm/yyyy and not mm/dd/yyyy. I tried this change also. I tried many ways of formula syntax without success. There is a way to solve the problem? Thanks again.
 
I thing that I founded a solution. I changed the formula as:

Code:
Exp1: DCount("[CountryofOrigin]","MainTbl","[CountryofOrigin]='ITALY' And IIF(IsDate([Finalized]),DateValue(Nz([Finalized],#31/12/9999#)) < DATE(), 0)")
and works fine.

Anytime thanks for your help.
 

Users who are viewing this thread

Back
Top Bottom