InfoPath Dev

Hello, I am trying to count a field based on a date. So I have a SQL database which holds data, in that data we have a status field. We are trying to use a calculated value to count the instance of the data depending if the create date matches a selected date and if two instances of the field match. Here is what I have so far: count(@CurrentIncidentStatusId[(@CreateDate = MonDate) and (. = "OPEN" or . = "REFR")]) count(xdXDocument:GetDOM("SQL Status Data Source")/dfs:myFields/dfs:dataFields/d:Incident/@CurrentIncidentStatusId[(../@CreateDate = xdXDocument:get-DOM()/my:MAIN/my:VisualData/my:VisualDataDates/my:DayDates/my:MonDate) and (. = "OPEN" or . = "REFR")]) I will break it down, so the CurrentIncidentStatusID can be 1 of 7 options, but only want it to check if OPEN or REFR. The CreateDate is when the incident was created, and the MonDate is a date picker selected to query against the CreateDate. Works find if I take out the date validation, but need it so it counts for that date selected. Any ideas? Thanks

When you say the date validation, do you maybe mean the part of the XPath filter comparing "my:MonDate" with "@CreateDate"? One thought I have is that your date formats may not be the same. So, on your form, drag the @CreateDate onto the canvas. Make sure you are displaying a text box. Set the format to display the actual value. Do the same with MonDate. Do they match? Or does your SQL date include time?

Hi Hilary, thanks for your reply. Yes the Xpath filter comparing "my:MonDate" with "@CreateDate". The date formats are correct, as they are both dd/mm/yyyy. Yeah the SQL database holds the time value as well, but in a separate field with in @CreateDate. I have tried use both a text field set to a whole number and a calculated value and both will not calculate the number of occurrences. When I have the @CreateDate present in a repeating table, and use a filter on the repeating table: "CreateDate WeekDateEnd = Hide this control" and select the dates I want it only shows the dates selected which is correct, however it still does not count the amount of instances. Issue is just with it counting how many are displayed.

A separate field? Are you meaning just that the time is shown in a different control? Change the CreateDate date picker to a text box. Set the format to show the XML value. I'd bet that your data from SQL is yyyy/MM/ddTHH:mm:ss and that to compare your two dates, you need to use substring-before on your SQL data to get the date only.

I managed to get this sorted :) Thanks or the help Hilary :)