InfoPath Dev

 I am having issues with counting data returned from an Access DB Table using InfoPath 2010.

The data source (Timecards) is secondary.  I need to filter out all data greater than 12 months old.

So far I have xPath as follows:

 count(xdXDocument:GetDOM("Timecards")/dfs:myFields/dfs:dataFields/d:Timecards[@NotesMon = "A" and @ID = current() and substring-before(@StartDate, " ") > xdDate:AddDays(xdDate:Today(), -365)])

The first part returns a count as expected, the last part (BOLD) is what I am having issues with. It draws no errors, but returns a value of zero

Please help.

I think that I have found the issue......   I have one date in Format :  dd/mm/yyyy   and the other is    yyyy-mm-dd

Now that I have that part straight in my head can anyone help with the concat( substring-before  and substring-after) to reverse the first date??

Then I can translate() to get a number that might return a valid count.

 For those interested - the solution was:

Set all date formats to yyyy-mm-dd (you may need to change the format saved when using Access DB tables)

Convert date to integer in a second field using :   number(concat(substring(string(xdDate:AddDays(xdDate:Today(), -365)), 1, 4), substring(string(xdDate:AddDays(xdDate:Today(), -365)), 6, 2), substring(string(xdDate:AddDays(xdDate:Today(), -365)), 9, 2)))

Note that this returns a value 1 year (365 days) before today

I converted the second date to an integer in the conditional statement using the following :

 number(concat(substring(string(@StartDate), 1, 4), substring(string(@StartDate), 6, 2), substring(string(@StartDate), 9, 2)))

Note that relative xPath has been used. The absolute xPath was used in the first part of the statement

Complete statement used was : 

 count(xdXDocument:GetDOM("Timecards")/dfs:myFields/dfs:dataFields/d:Timecards[number(concat(substring(string(@StartDate), 1, 4), substring(string(@StartDate), 6, 2), substring(string(@StartDate), 9, 2))) > xdXDocument:get-DOM()/dfs:myFields/my:FormFields/my:CountFields/my:AnotherDate and @ID = current() and @NotesSun = "A"])

 Hi L Mac,

    Sorry that nobody responded to your question sooner, but once you've got all your dates into yyyy-MM-dd format, the formula to use is much simpler than what you've got there. It's simply the following:

count(xdXDocument:GetDOM("Timecards")/dfs:myFields/dfs:dataFields/d:Timecards[translate(@StartDate, 'T:-', '') > translate(xdDate:AddDays(xdDate:Today(), -365), 'T:-')]

That should do it!

 Jimmy,

 Thanks. I'll insert that when they ask me for the next set of changes to that form.

L Mac