Compare two dates

[ridawg]

Hey,

I have a form with two date fields. The first field is called File Date and
the second field is called Action Date. I tried to create a Data Validation
rule on the Action Date field that states Action Date < File Date.
Esssentially, saying pop up a an error message - Action Date can't be less
than File Date.

The problem I'm having is when the Action Date and File Date have the same
date the error message pops up. This isn't correct - it should only pop up
when it is less than the File Date. If they are equal that's fine.

Not sure why this is happening. Both dates are entered in the form by the
user. The form is tied to a backend database. Both fields are setup as
optional_dateTime types. The database fields are using the Date/Time datatype
and short date as the format.

Thanks!

 

[JonB]

Hey,

I have a form with two date fields. The first field is called File Date and
the second field is called Action Date. I tried to create a Data Validation
rule on the Action Date field that states Action Date < File Date.
Esssentially, saying pop up a an error message - Action Date can't be less
than File Date.

Are you doing Less Than or Less Than or Equal to??? They are very
different.

 

[ridawg]

Just Less Than. It's ok if the dates are equal to each other.

 

[Kalyan Reddy]

Hi ,

I tried with just less then and is working correctly. Why dont you remove
that data validation and do it fresh.

 

[ridawg]

Thanks for the suggestion. I removed the validation and saved the form then
closed it. Then I opened the form back up and added the validation back on.
Still doesn't work. I don't get it. Everything I've read says this should
work.

 

[S.Y.M. Wong-A-Ton]

I think the problem is in the database field's date/time format you are
using. You must make sure that the date format in your database is the same
as that used by InfoPath in the background, otherwise you'll get
unpredictable results like you are getting right now when trying to compare
dates.

Refer to this article
http://enterprise-solutions.swits.net/infopath/date-time-basics-infopath.htm
to find out how InfoPath uses dates behind the scenes.

Refer to this article
http://enterprise-solutions.swits.net/infopath/filter-repeating-table-date-range-rules.htm
to find out how you can change the format of the date field in your database
to suit InfoPath for date comparison.

 

[ridawg]

I think I understand what you're getting at but let me run this by you.

My database date fields are all setup as date/time datatypes and as a result
my InfoPath form fields are setup as optional_dateTime datatypes. But
InfoPath stores the date differently internally so this is likely why my
comparison validation isn't working. Plus I'm not actually using time.

So in order to fix this I think all I need to do is edit my SQL statements
to correct the format. For example, Format(MyTable.File_Date, "yyyy-mm-dd")
As File_Date, Format(MyTable.Action_Date, "yyyy-mm-dd") As Action_Date.

From there I should be able to use the validation rule that I've already
created (Action Date < File Date) because I'm assuming both fields will come
through as date datatypes that InfoPath can work with or I may need to use an
expression like you outline on your site.

Does my thought process make sense on this?

 

[S.Y.M. Wong-A-Ton]

Yes, so far so good. No need to implement the comparison like on my site,
since your method should work. Try it out and let me know.

 

[ridawg]

I gave this a try but I'm running into problems. Here's what happening:

When I edit my query to add the formatting and test the SQL - no problems.
But the next message I get after editing the SQL states that it can't
represent the SQL in the tree view. Not a big deal but after clicking ok all
my query fields disappear. So I can see my data fields (the date fields are
converted into simpletype data types and I thought they would be converted
into date datatypes).

Not having the query fields essentially makes the form useless.

So do I need to completely re-write the SQL statement? Something to be aware
of is the database I'm connected to consists of 2 tables so my SQL statement
also includes the shape command. Not sure if that might be throwing things
off.

 

[S.Y.M. Wong-A-Ton]

The plot thickens...

Let's back up for a while, shall we? Can you explain from the start - in as
few words as you can - how you set up your form? You are using MS Access,
right? And when do you get that "tree view" error? In InfoPath or elsewhere?
Perhaps you can post the shape SQL statement (and mask/change sensitive field
names), so that I can try to reconstruct your query and form?

Sorry I cannot offer more help/advice at this time, but my hands are pretty
much tied until I fully understand your scenario...

 

[ridawg]

Ok, here goes.

I have an Access database with several tables. A couple of the tables are
used as lookup tables (secondary data sources) for some dropdown fields. I
have 2 main tables that are related (T_Filing and T_Action_Items) -
one-to-many relationship.

When I created my form I did it by going through the create InfoPath form
based on data source. The data source being an Access database - the main
table is T_Filing and using the add table button I added the T_Action_Items
table. This is my main connection. The code for that connection is:

shape
{select
[Filing_ID],[Company_Name],[State],[Product_Area],[Line_Of_Business],[Filing_Type],[Delay_Adopt],[File_Date],[Effective_Date],[Filing_Method],[Filing_Description],[Disposition_Type],[Disposition_Date],[Disposition_Description]
from [T_Filing] as [T_Filing]} as [T_Filing]
append
({select
[Action_Key],[Filing_ID],[Action_Date],[Action_Initiated_By],[Action_Type],[Action_Description] from [T_Action_Items]}
relate [Filing_ID] TO [Filing_ID]) as [T_Action_Items]

It's important that I understand any changes to the above statement because
I also have some VBScript code related to a button that queries the database
for a specific filing.

Basically the first page of my form is a search view that returns a couple
fields regarding each Filing. From here a user selects a Details button - to
see all the Filing details for the selected Filing. In order to do that I had
to write my own SQL. Same statement as above but with an additional Where
clause.

I received the Tree View message when I went into my main connection
(Tools/Data Connections/Main Connection/Modify/Edit SQL). After I edited my
SQL statement - changed [File_Date] to Format(T_Filing.File_Date,
"yyyy-mm-dd") As File_Date. I hit the Test Sql button and received the dialog
box stating the test was successful. When I clicked the Ok button I received
the Tree View warning message then in the Data Source view on the Design
Tasks pane all my query fields disappeared. Then I clicked Next to finish up
the data connection changes and received a warning dialog box stating Access
can no longer determine if this query is safe - click Yes to continue.

Thanks for the help!

 

[S.Y.M. Wong-A-Ton]

More questions: Do you use the form to submit changes back to the database?
And when does the date comparison take place? You said previously that you
created a rule? Where did you do that?

I thought you made the changes in the database (which what my suggestion was
all about) and then created the form again. Obviously, you made the changes
within InfoPath, so I'm not sure what's going wrong right now. It is best to
revert back to your original SQL statement for now.

Give me a day or two to think about your scenario. I might come up with a
way to solve your problem without any major changes.
---
S.Y.M. Wong-A-Ton

Ok, here goes.

I have an Access database with several tables. A couple of the tables are
used as lookup tables (secondary data sources) for some dropdown fields. I
have 2 main tables that are related (T_Filing and T_Action_Items) -
one-to-many relationship.

When I created my form I did it by going through the create InfoPath form
based on data source. The data source being an Access database - the main
table is T_Filing and using the add table button I added the T_Action_Items
table. This is my main connection. The code for that connection is:

shape
{select
[Filing_ID],[Company_Name],[State],[Product_Area],[Line_Of_Business],[Filing_Type],[Delay_Adopt],[File_Date],[Effective_Date],[Filing_Method],[Filing_Description],[Disposition_Type],[Disposition_Date],[Disposition_Description]
from [T_Filing] as [T_Filing]} as [T_Filing]
append
({select
[Action_Key],[Filing_ID],[Action_Date],[Action_Initiated_By],[Action_Type],[Action_Description] from [T_Action_Items]}
relate [Filing_ID] TO [Filing_ID]) as [T_Action_Items]

It's important that I understand any changes to the above statement because
I also have some VBScript code related to a button that queries the database
for a specific filing.

Basically the first page of my form is a search view that returns a couple
fields regarding each Filing. From here a user selects a Details button - to
see all the Filing details for the selected Filing. In order to do that I had
to write my own SQL. Same statement as above but with an additional Where
clause.

I received the Tree View message when I went into my main connection
(Tools/Data Connections/Main Connection/Modify/Edit SQL). After I edited my
SQL statement - changed [File_Date] to Format(T_Filing.File_Date,
"yyyy-mm-dd") As File_Date. I hit the Test Sql button and received the dialog
box stating the test was successful. When I clicked the Ok button I received
the Tree View warning message then in the Data Source view on the Design
Tasks pane all my query fields disappeared. Then I clicked Next to finish up
the data connection changes and received a warning dialog box stating Access
can no longer determine if this query is safe - click Yes to continue.

Thanks for the help!
--
-ridawg

The plot thickens...

Let's back up for a while, shall we? Can you explain from the start - in as
few words as you can - how you set up your form? You are using MS Access,
right? And when do you get that "tree view" error? In InfoPath or elsewhere?
Perhaps you can post the shape SQL statement (and mask/change sensitive field
names), so that I can try to reconstruct your query and form?

Sorry I cannot offer more help/advice at this time, but my hands are pretty
much tied until I fully understand your scenario...

 

[ridawg]

I use the form to submit changes back to the database and to enter new
filings. Three views - Search, Details (by default all fields are read only
but user can click Edit button to edit fields and submit changes) and New
Filing (enter required fields for a new filing and submit).

The comparison takes place after a user edits/enters the Action_Date or
Disposition Date field (in view Details or New Filing). For both of these
fields I added a validation rule - Action_Date < File_Date - display dialog
alert box. For Disposition_Date - same thing. I just created these by right
clicking on the field - Date Picker Properties/Data Validation. I have two
validation rules on these fields. The first one is that the field cannot be
blank and the second one is the compare one I'm having problems with.

I did go into my Access database and edited the format of my date fields to
yyyy-mm-dd. Just to see what would happen. Didn't change anything. Still
having the same problem when the dates are equal to each other. Also the data
type in InfoPath is still optional_dateTime. I thought it might change to
just date.

Also just for fun I wrote a straight up SQL statement using select and join.
Essentially the same thing as the InfoPath statement but without the shape
stuff. Once again when I tested the statement I received a success message
then the Tree View warning message. Also, the following is the second warning
message I received when I edited the sql statement (had it incorrectly in my
last post).

"To retrieve information about this query, InfoPath must execute it against
the database. However, InfoPath cannot determine whether the query is safe.
Unsafe queries can delete or modify data in the database in unexpected ways."

Thanks for the help!
--
-ridawg

More questions: Do you use the form to submit changes back to the database?
And when does the date comparison take place? You said previously that you
created a rule? Where did you do that?

I thought you made the changes in the database (which what my suggestion was
all about) and then created the form again. Obviously, you made the changes
within InfoPath, so I'm not sure what's going wrong right now. It is best to
revert back to your original SQL statement for now.

Give me a day or two to think about your scenario. I might come up with a
way to solve your problem without any major changes.
---
S.Y.M. Wong-A-Ton

Ok, here goes.

I have an Access database with several tables. A couple of the tables are
used as lookup tables (secondary data sources) for some dropdown fields. I
have 2 main tables that are related (T_Filing and T_Action_Items) -
one-to-many relationship.

When I created my form I did it by going through the create InfoPath form
based on data source. The data source being an Access database - the main
table is T_Filing and using the add table button I added the T_Action_Items
table. This is my main connection. The code for that connection is:

shape
{select
[Filing_ID],[Company_Name],[State],[Product_Area],[Line_Of_Business],[Filing_Type],[Delay_Adopt],[File_Date],[Effective_Date],[Filing_Method],[Filing_Description],[Disposition_Type],[Disposition_Date],[Disposition_Description]
from [T_Filing] as [T_Filing]} as [T_Filing]
append
({select
[Action_Key],[Filing_ID],[Action_Date],[Action_Initiated_By],[Action_Type],[Action_Description] from [T_Action_Items]}
relate [Filing_ID] TO [Filing_ID]) as [T_Action_Items]

It's important that I understand any changes to the above statement because
I also have some VBScript code related to a button that queries the database
for a specific filing.

Basically the first page of my form is a search view that returns a couple
fields regarding each Filing. From here a user selects a Details button - to
see all the Filing details for the selected Filing. In order to do that I had
to write my own SQL. Same statement as above but with an additional Where
clause.

I received the Tree View message when I went into my main connection
(Tools/Data Connections/Main Connection/Modify/Edit SQL). After I edited my
SQL statement - changed [File_Date] to Format(T_Filing.File_Date,
"yyyy-mm-dd") As File_Date. I hit the Test Sql button and received the dialog
box stating the test was successful. When I clicked the Ok button I received
the Tree View warning message then in the Data Source view on the Design
Tasks pane all my query fields disappeared. Then I clicked Next to finish up
the data connection changes and received a warning dialog box stating Access
can no longer determine if this query is safe - click Yes to continue.

Thanks for the help!
--
-ridawg

The plot thickens...

Let's back up for a while, shall we? Can you explain from the start - in as
few words as you can - how you set up your form? You are using MS Access,
right? And when do you get that "tree view" error? In InfoPath or elsewhere?
Perhaps you can post the shape SQL statement (and mask/change sensitive field
names), so that I can try to reconstruct your query and form?

Sorry I cannot offer more help/advice at this time, but my hands are pretty
much tied until I fully understand your scenario...
---
S.Y.M. Wong-A-Ton


:

I gave this a try but I'm running into problems. Here's what happening:

When I edit my query to add the formatting and test the SQL - no problems.
But the next message I get after editing the SQL states that it can't
represent the SQL in the tree view. Not a big deal but after clicking ok all
my query fields disappear. So I can see my data fields (the date fields are
converted into simpletype data types and I thought they would be converted
into date datatypes).

Not having the query fields essentially makes the form useless.

So do I need to completely re-write the SQL statement? Something to be aware
of is the database I'm connected to consists of 2 tables so my SQL statement
also includes the shape command. Not sure if that might be throwing things
off.

--
-ridawg


:

Yes, so far so good. No need to implement the comparison like on my site,
since your method should work. Try it out and let me know.
---
S.Y.M. Wong-A-Ton


:

I think I understand what you're getting at but let me run this by you.

My database date fields are all setup as date/time datatypes and as a result
my InfoPath form fields are setup as optional_dateTime datatypes. But
InfoPath stores the date differently internally so this is likely why my
comparison validation isn't working. Plus I'm not actually using time.

So in order to fix this I think all I need to do is edit my SQL statements
to correct the format. For example, Format(MyTable.File_Date, "yyyy-mm-dd")
As File_Date, Format(MyTable.Action_Date, "yyyy-mm-dd") As Action_Date.

From there I should be able to use the validation rule that I've already
created (Action Date < File Date) because I'm assuming both fields will come
through as date datatypes that InfoPath can work with or I may need to use an
expression like you outline on your site.

Does my thought process make sense on this?

--
-ridawg


:

I think the problem is in the database field's date/time format you are
using. You must make sure that the date format in your database is the same
as that used by InfoPath in the background, otherwise you'll get
unpredictable results like you are getting right now when trying to compare
dates.

Refer to this article
http://enterprise-solutions.swits.net/infopath/date-time-basics-infopath.htm
to find out how InfoPath uses dates behind the scenes.

Refer to this article
http://enterprise-solutions.swits.net/infopath/filter-repeating-table-date-range-rules.htm
to find out how you can change the format of the date field in your database
to suit InfoPath for date comparison.
---
S.Y.M. Wong-A-Ton


:

Hey,

I have a form with two date fields. The first field is called File Date and
the second field is called Action Date. I tried to create a Data Validation
rule on the Action Date field that states Action Date < File Date.
Esssentially, saying pop up a an error message - Action Date can't be less
than File Date.

The problem I'm having is when the Action Date and File Date have the same
date the error message pops up. This isn't correct - it should only pop up
when it is less than the File Date. If they are equal that's fine.

Not sure why this is happening. Both dates are entered in the form by the
user. The form is tied to a backend database. Both fields are setup as
optional_dateTime types. The database fields are using the Date/Time datatype
and short date as the format.

Thanks!

 

[ridawg]

Ok - just for fun I created two fields in my InfoPath form - one called
testdate1 and another called testdate2. I set the data type for both fields
to date. I created my validation rule that states testdate2 < testdate1.
Everything worked great. If the testdate2 date was less than testdate1 my
error message was displayed and if they dates were the same the error message
didn't display.

Doesn't exactly help me solve my problem.

--
-ridawg

I use the form to submit changes back to the database and to enter new
filings. Three views - Search, Details (by default all fields are read only
but user can click Edit button to edit fields and submit changes) and New
Filing (enter required fields for a new filing and submit).

The comparison takes place after a user edits/enters the Action_Date or
Disposition Date field (in view Details or New Filing). For both of these
fields I added a validation rule - Action_Date < File_Date - display dialog
alert box. For Disposition_Date - same thing. I just created these by right
clicking on the field - Date Picker Properties/Data Validation. I have two
validation rules on these fields. The first one is that the field cannot be
blank and the second one is the compare one I'm having problems with.

I did go into my Access database and edited the format of my date fields to
yyyy-mm-dd. Just to see what would happen. Didn't change anything. Still
having the same problem when the dates are equal to each other. Also the data
type in InfoPath is still optional_dateTime. I thought it might change to
just date.

Also just for fun I wrote a straight up SQL statement using select and join.
Essentially the same thing as the InfoPath statement but without the shape
stuff. Once again when I tested the statement I received a success message
then the Tree View warning message. Also, the following is the second warning
message I received when I edited the sql statement (had it incorrectly in my
last post).

"To retrieve information about this query, InfoPath must execute it against
the database. However, InfoPath cannot determine whether the query is safe.
Unsafe queries can delete or modify data in the database in unexpected ways."

Thanks for the help!
--
-ridawg

More questions: Do you use the form to submit changes back to the database?
And when does the date comparison take place? You said previously that you
created a rule? Where did you do that?

I thought you made the changes in the database (which what my suggestion was
all about) and then created the form again. Obviously, you made the changes
within InfoPath, so I'm not sure what's going wrong right now. It is best to
revert back to your original SQL statement for now.

Give me a day or two to think about your scenario. I might come up with a
way to solve your problem without any major changes.
---
S.Y.M. Wong-A-Ton

Ok, here goes.

I have an Access database with several tables. A couple of the tables are
used as lookup tables (secondary data sources) for some dropdown fields. I
have 2 main tables that are related (T_Filing and T_Action_Items) -
one-to-many relationship.

When I created my form I did it by going through the create InfoPath form
based on data source. The data source being an Access database - the main
table is T_Filing and using the add table button I added the T_Action_Items
table. This is my main connection. The code for that connection is:

shape
{select
[Filing_ID],[Company_Name],[State],[Product_Area],[Line_Of_Business],[Filing_Type],[Delay_Adopt],[File_Date],[Effective_Date],[Filing_Method],[Filing_Description],[Disposition_Type],[Disposition_Date],[Disposition_Description]
from [T_Filing] as [T_Filing]} as [T_Filing]
append
({select
[Action_Key],[Filing_ID],[Action_Date],[Action_Initiated_By],[Action_Type],[Action_Description] from [T_Action_Items]}
relate [Filing_ID] TO [Filing_ID]) as [T_Action_Items]

It's important that I understand any changes to the above statement because
I also have some VBScript code related to a button that queries the database
for a specific filing.

Basically the first page of my form is a search view that returns a couple
fields regarding each Filing. From here a user selects a Details button - to
see all the Filing details for the selected Filing. In order to do that I had
to write my own SQL. Same statement as above but with an additional Where
clause.

I received the Tree View message when I went into my main connection
(Tools/Data Connections/Main Connection/Modify/Edit SQL). After I edited my
SQL statement - changed [File_Date] to Format(T_Filing.File_Date,
"yyyy-mm-dd") As File_Date. I hit the Test Sql button and received the dialog
box stating the test was successful. When I clicked the Ok button I received
the Tree View warning message then in the Data Source view on the Design
Tasks pane all my query fields disappeared. Then I clicked Next to finish up
the data connection changes and received a warning dialog box stating Access
can no longer determine if this query is safe - click Yes to continue.

Thanks for the help!
--
-ridawg


:

The plot thickens...

Let's back up for a while, shall we? Can you explain from the start - in as
few words as you can - how you set up your form? You are using MS Access,
right? And when do you get that "tree view" error? In InfoPath or elsewhere?
Perhaps you can post the shape SQL statement (and mask/change sensitive field
names), so that I can try to reconstruct your query and form?

Sorry I cannot offer more help/advice at this time, but my hands are pretty
much tied until I fully understand your scenario...
---
S.Y.M. Wong-A-Ton


:

I gave this a try but I'm running into problems. Here's what happening:

When I edit my query to add the formatting and test the SQL - no problems.
But the next message I get after editing the SQL states that it can't
represent the SQL in the tree view. Not a big deal but after clicking ok all
my query fields disappear. So I can see my data fields (the date fields are
converted into simpletype data types and I thought they would be converted
into date datatypes).

Not having the query fields essentially makes the form useless.

So do I need to completely re-write the SQL statement? Something to be aware
of is the database I'm connected to consists of 2 tables so my SQL statement
also includes the shape command. Not sure if that might be throwing things
off.

--
-ridawg


:

Yes, so far so good. No need to implement the comparison like on my site,
since your method should work. Try it out and let me know.
---
S.Y.M. Wong-A-Ton


:

I think I understand what you're getting at but let me run this by you.

My database date fields are all setup as date/time datatypes and as a result
my InfoPath form fields are setup as optional_dateTime datatypes. But
InfoPath stores the date differently internally so this is likely why my
comparison validation isn't working. Plus I'm not actually using time.

So in order to fix this I think all I need to do is edit my SQL statements
to correct the format. For example, Format(MyTable.File_Date, "yyyy-mm-dd")
As File_Date, Format(MyTable.Action_Date, "yyyy-mm-dd") As Action_Date.

From there I should be able to use the validation rule that I've already
created (Action Date < File Date) because I'm assuming both fields will come
through as date datatypes that InfoPath can work with or I may need to use an
expression like you outline on your site.

Does my thought process make sense on this?

--
-ridawg


:

I think the problem is in the database field's date/time format you are
using. You must make sure that the date format in your database is the same
as that used by InfoPath in the background, otherwise you'll get
unpredictable results like you are getting right now when trying to compare
dates.

Refer to this article
http://enterprise-solutions.swits.net/infopath/date-time-basics-infopath.htm
to find out how InfoPath uses dates behind the scenes.

Refer to this article
http://enterprise-solutions.swits.net/infopath/filter-repeating-table-date-range-rules.htm
to find out how you can change the format of the date field in your database
to suit InfoPath for date comparison.
---
S.Y.M. Wong-A-Ton


:

Hey,

I have a form with two date fields. The first field is called File Date and
the second field is called Action Date. I tried to create a Data Validation
rule on the Action Date field that states Action Date < File Date.
Esssentially, saying pop up a an error message - Action Date can't be less
than File Date.

The problem I'm having is when the Action Date and File Date have the same
date the error message pops up. This isn't correct - it should only pop up
when it is less than the File Date. If they are equal that's fine.

Not sure why this is happening. Both dates are entered in the form by the
user. The form is tied to a backend database. Both fields are setup as
optional_dateTime types. The database fields are using the Date/Time datatype
and short date as the format.

Thanks!

 

[S.Y.M. Wong-A-Ton]

Yes, I checked that yesterday too! The difference should be somewhere in the
fact that you are using a form connected to a database.

Thank you for the ample information on the scenario. I'll work with it and
see if I can figure out what the problem is.
---
S.Y.M. Wong-A-Ton

Ok - just for fun I created two fields in my InfoPath form - one called
testdate1 and another called testdate2. I set the data type for both fields
to date. I created my validation rule that states testdate2 < testdate1.
Everything worked great. If the testdate2 date was less than testdate1 my
error message was displayed and if they dates were the same the error message
didn't display.

Doesn't exactly help me solve my problem.

--
-ridawg

I use the form to submit changes back to the database and to enter new
filings. Three views - Search, Details (by default all fields are read only
but user can click Edit button to edit fields and submit changes) and New
Filing (enter required fields for a new filing and submit).

The comparison takes place after a user edits/enters the Action_Date or
Disposition Date field (in view Details or New Filing). For both of these
fields I added a validation rule - Action_Date < File_Date - display dialog
alert box. For Disposition_Date - same thing. I just created these by right
clicking on the field - Date Picker Properties/Data Validation. I have two
validation rules on these fields. The first one is that the field cannot be
blank and the second one is the compare one I'm having problems with.

I did go into my Access database and edited the format of my date fields to
yyyy-mm-dd. Just to see what would happen. Didn't change anything. Still
having the same problem when the dates are equal to each other. Also the data
type in InfoPath is still optional_dateTime. I thought it might change to
just date.

Also just for fun I wrote a straight up SQL statement using select and join.
Essentially the same thing as the InfoPath statement but without the shape
stuff. Once again when I tested the statement I received a success message
then the Tree View warning message. Also, the following is the second warning
message I received when I edited the sql statement (had it incorrectly in my
last post).

"To retrieve information about this query, InfoPath must execute it against
the database. However, InfoPath cannot determine whether the query is safe.
Unsafe queries can delete or modify data in the database in unexpected ways."

Thanks for the help!
--
-ridawg

More questions: Do you use the form to submit changes back to the database?
And when does the date comparison take place? You said previously that you
created a rule? Where did you do that?

I thought you made the changes in the database (which what my suggestion was
all about) and then created the form again. Obviously, you made the changes
within InfoPath, so I'm not sure what's going wrong right now. It is best to
revert back to your original SQL statement for now.

Give me a day or two to think about your scenario. I might come up with a
way to solve your problem without any major changes.
---
S.Y.M. Wong-A-Ton


:

Ok, here goes.

I have an Access database with several tables. A couple of the tables are
used as lookup tables (secondary data sources) for some dropdown fields. I
have 2 main tables that are related (T_Filing and T_Action_Items) -
one-to-many relationship.

When I created my form I did it by going through the create InfoPath form
based on data source. The data source being an Access database - the main
table is T_Filing and using the add table button I added the T_Action_Items
table. This is my main connection. The code for that connection is:

shape
{select
[Filing_ID],[Company_Name],[State],[Product_Area],[Line_Of_Business],[Filing_Type],[Delay_Adopt],[File_Date],[Effective_Date],[Filing_Method],[Filing_Description],[Disposition_Type],[Disposition_Date],[Disposition_Description]
from [T_Filing] as [T_Filing]} as [T_Filing]
append
({select
[Action_Key],[Filing_ID],[Action_Date],[Action_Initiated_By],[Action_Type],[Action_Description] from [T_Action_Items]}
relate [Filing_ID] TO [Filing_ID]) as [T_Action_Items]

It's important that I understand any changes to the above statement because
I also have some VBScript code related to a button that queries the database
for a specific filing.

Basically the first page of my form is a search view that returns a couple
fields regarding each Filing. From here a user selects a Details button - to
see all the Filing details for the selected Filing. In order to do that I had
to write my own SQL. Same statement as above but with an additional Where
clause.

I received the Tree View message when I went into my main connection
(Tools/Data Connections/Main Connection/Modify/Edit SQL). After I edited my
SQL statement - changed [File_Date] to Format(T_Filing.File_Date,
"yyyy-mm-dd") As File_Date. I hit the Test Sql button and received the dialog
box stating the test was successful. When I clicked the Ok button I received
the Tree View warning message then in the Data Source view on the Design
Tasks pane all my query fields disappeared. Then I clicked Next to finish up
the data connection changes and received a warning dialog box stating Access
can no longer determine if this query is safe - click Yes to continue.

Thanks for the help!
--
-ridawg


:

The plot thickens...

Let's back up for a while, shall we? Can you explain from the start - in as
few words as you can - how you set up your form? You are using MS Access,
right? And when do you get that "tree view" error? In InfoPath or elsewhere?
Perhaps you can post the shape SQL statement (and mask/change sensitive field
names), so that I can try to reconstruct your query and form?

Sorry I cannot offer more help/advice at this time, but my hands are pretty
much tied until I fully understand your scenario...
---
S.Y.M. Wong-A-Ton


:

I gave this a try but I'm running into problems. Here's what happening:

When I edit my query to add the formatting and test the SQL - no problems.
But the next message I get after editing the SQL states that it can't
represent the SQL in the tree view. Not a big deal but after clicking ok all
my query fields disappear. So I can see my data fields (the date fields are
converted into simpletype data types and I thought they would be converted
into date datatypes).

Not having the query fields essentially makes the form useless.

So do I need to completely re-write the SQL statement? Something to be aware
of is the database I'm connected to consists of 2 tables so my SQL statement
also includes the shape command. Not sure if that might be throwing things
off.

--
-ridawg


:

Yes, so far so good. No need to implement the comparison like on my site,
since your method should work. Try it out and let me know.
---
S.Y.M. Wong-A-Ton


:

I think I understand what you're getting at but let me run this by you.

My database date fields are all setup as date/time datatypes and as a result
my InfoPath form fields are setup as optional_dateTime datatypes. But
InfoPath stores the date differently internally so this is likely why my
comparison validation isn't working. Plus I'm not actually using time.

So in order to fix this I think all I need to do is edit my SQL statements
to correct the format. For example, Format(MyTable.File_Date, "yyyy-mm-dd")
As File_Date, Format(MyTable.Action_Date, "yyyy-mm-dd") As Action_Date.

From there I should be able to use the validation rule that I've already
created (Action Date < File Date) because I'm assuming both fields will come
through as date datatypes that InfoPath can work with or I may need to use an
expression like you outline on your site.

Does my thought process make sense on this?

--
-ridawg


:

I think the problem is in the database field's date/time format you are
using. You must make sure that the date format in your database is the same
as that used by InfoPath in the background, otherwise you'll get
unpredictable results like you are getting right now when trying to compare
dates.

Refer to this article
http://enterprise-solutions.swits.net/infopath/date-time-basics-infopath.htm
to find out how InfoPath uses dates behind the scenes.

Refer to this article
http://enterprise-solutions.swits.net/infopath/filter-repeating-table-date-range-rules.htm
to find out how you can change the format of the date field in your database
to suit InfoPath for date comparison.
---
S.Y.M. Wong-A-Ton


:

Hey,

I have a form with two date fields. The first field is called File Date and
the second field is called Action Date. I tried to create a Data Validation
rule on the Action Date field that states Action Date < File Date.
Esssentially, saying pop up a an error message - Action Date can't be less
than File Date.

The problem I'm having is when the Action Date and File Date have the same
date the error message pops up. This isn't correct - it should only pop up
when it is less than the File Date. If they are equal that's fine.

Not sure why this is happening. Both dates are entered in the form by the
user. The form is tied to a backend database. Both fields are setup as
optional_dateTime types. The database fields are using the Date/Time datatype
and short date as the format.

Thanks!

 

[S.Y.M. Wong-A-Ton]

I was able to reproduce your problem. The use of the format in the database
does not matter in your case; InfoPath still uses the correct format behind
the scenes. I think the problem might be in the addition of the time part to
the date, which would explain not getting the desired results when the dates
are equal to each other, since the time might still differ between the dates.

The way I solved the problem was to use the same technique I used in my
article
http://enterprise-solutions.swits.net/infopath/filter-repeating-table-date-range-rules.htm
which is to compare the date parts (excluding time) of two dates. So if you
copy this technique, you should be fine: Go to the data validation rule you
added, with the old date comparison expression set in the Data Validation
dialog box, select "The expression" from the first drop-down list box, and
then modify the part of the expression within msxml:string-compare() to
include the concat() and substring() expressions like demonstrated in step 18
of my article but use your own fields. What this does is just reformat the
dates to a format that does not include the time part and lends itself well
for doing date comparisons.

Hope this helps.
---
S.Y.M. Wong-A-Ton

I use the form to submit changes back to the database and to enter new
filings. Three views - Search, Details (by default all fields are read only
but user can click Edit button to edit fields and submit changes) and New
Filing (enter required fields for a new filing and submit).

The comparison takes place after a user edits/enters the Action_Date or
Disposition Date field (in view Details or New Filing). For both of these
fields I added a validation rule - Action_Date < File_Date - display dialog
alert box. For Disposition_Date - same thing. I just created these by right
clicking on the field - Date Picker Properties/Data Validation. I have two
validation rules on these fields. The first one is that the field cannot be
blank and the second one is the compare one I'm having problems with.

I did go into my Access database and edited the format of my date fields to
yyyy-mm-dd. Just to see what would happen. Didn't change anything. Still
having the same problem when the dates are equal to each other. Also the data
type in InfoPath is still optional_dateTime. I thought it might change to
just date.

Also just for fun I wrote a straight up SQL statement using select and join.
Essentially the same thing as the InfoPath statement but without the shape
stuff. Once again when I tested the statement I received a success message
then the Tree View warning message. Also, the following is the second warning
message I received when I edited the sql statement (had it incorrectly in my
last post).

"To retrieve information about this query, InfoPath must execute it against
the database. However, InfoPath cannot determine whether the query is safe.
Unsafe queries can delete or modify data in the database in unexpected ways."

Thanks for the help!
--
-ridawg

More questions: Do you use the form to submit changes back to the database?
And when does the date comparison take place? You said previously that you
created a rule? Where did you do that?

I thought you made the changes in the database (which what my suggestion was
all about) and then created the form again. Obviously, you made the changes
within InfoPath, so I'm not sure what's going wrong right now. It is best to
revert back to your original SQL statement for now.

Give me a day or two to think about your scenario. I might come up with a
way to solve your problem without any major changes.
---
S.Y.M. Wong-A-Ton

Ok, here goes.

I have an Access database with several tables. A couple of the tables are
used as lookup tables (secondary data sources) for some dropdown fields. I
have 2 main tables that are related (T_Filing and T_Action_Items) -
one-to-many relationship.

When I created my form I did it by going through the create InfoPath form
based on data source. The data source being an Access database - the main
table is T_Filing and using the add table button I added the T_Action_Items
table. This is my main connection. The code for that connection is:

shape
{select
[Filing_ID],[Company_Name],[State],[Product_Area],[Line_Of_Business],[Filing_Type],[Delay_Adopt],[File_Date],[Effective_Date],[Filing_Method],[Filing_Description],[Disposition_Type],[Disposition_Date],[Disposition_Description]
from [T_Filing] as [T_Filing]} as [T_Filing]
append
({select
[Action_Key],[Filing_ID],[Action_Date],[Action_Initiated_By],[Action_Type],[Action_Description] from [T_Action_Items]}
relate [Filing_ID] TO [Filing_ID]) as [T_Action_Items]

It's important that I understand any changes to the above statement because
I also have some VBScript code related to a button that queries the database
for a specific filing.

Basically the first page of my form is a search view that returns a couple
fields regarding each Filing. From here a user selects a Details button - to
see all the Filing details for the selected Filing. In order to do that I had
to write my own SQL. Same statement as above but with an additional Where
clause.

I received the Tree View message when I went into my main connection
(Tools/Data Connections/Main Connection/Modify/Edit SQL). After I edited my
SQL statement - changed [File_Date] to Format(T_Filing.File_Date,
"yyyy-mm-dd") As File_Date. I hit the Test Sql button and received the dialog
box stating the test was successful. When I clicked the Ok button I received
the Tree View warning message then in the Data Source view on the Design
Tasks pane all my query fields disappeared. Then I clicked Next to finish up
the data connection changes and received a warning dialog box stating Access
can no longer determine if this query is safe - click Yes to continue.

Thanks for the help!
--
-ridawg


:

The plot thickens...

Let's back up for a while, shall we? Can you explain from the start - in as
few words as you can - how you set up your form? You are using MS Access,
right? And when do you get that "tree view" error? In InfoPath or elsewhere?
Perhaps you can post the shape SQL statement (and mask/change sensitive field
names), so that I can try to reconstruct your query and form?

Sorry I cannot offer more help/advice at this time, but my hands are pretty
much tied until I fully understand your scenario...
---
S.Y.M. Wong-A-Ton


:

I gave this a try but I'm running into problems. Here's what happening:

When I edit my query to add the formatting and test the SQL - no problems.
But the next message I get after editing the SQL states that it can't
represent the SQL in the tree view. Not a big deal but after clicking ok all
my query fields disappear. So I can see my data fields (the date fields are
converted into simpletype data types and I thought they would be converted
into date datatypes).

Not having the query fields essentially makes the form useless.

So do I need to completely re-write the SQL statement? Something to be aware
of is the database I'm connected to consists of 2 tables so my SQL statement
also includes the shape command. Not sure if that might be throwing things
off.

--
-ridawg


:

Yes, so far so good. No need to implement the comparison like on my site,
since your method should work. Try it out and let me know.
---
S.Y.M. Wong-A-Ton


:

I think I understand what you're getting at but let me run this by you.

My database date fields are all setup as date/time datatypes and as a result
my InfoPath form fields are setup as optional_dateTime datatypes. But
InfoPath stores the date differently internally so this is likely why my
comparison validation isn't working. Plus I'm not actually using time.

So in order to fix this I think all I need to do is edit my SQL statements
to correct the format. For example, Format(MyTable.File_Date, "yyyy-mm-dd")
As File_Date, Format(MyTable.Action_Date, "yyyy-mm-dd") As Action_Date.

From there I should be able to use the validation rule that I've already
created (Action Date < File Date) because I'm assuming both fields will come
through as date datatypes that InfoPath can work with or I may need to use an
expression like you outline on your site.

Does my thought process make sense on this?

--
-ridawg


:

I think the problem is in the database field's date/time format you are
using. You must make sure that the date format in your database is the same
as that used by InfoPath in the background, otherwise you'll get
unpredictable results like you are getting right now when trying to compare
dates.

Refer to this article
http://enterprise-solutions.swits.net/infopath/date-time-basics-infopath.htm
to find out how InfoPath uses dates behind the scenes.

Refer to this article
http://enterprise-solutions.swits.net/infopath/filter-repeating-table-date-range-rules.htm
to find out how you can change the format of the date field in your database
to suit InfoPath for date comparison.
---
S.Y.M. Wong-A-Ton


:

Hey,

I have a form with two date fields. The first field is called File Date and
the second field is called Action Date. I tried to create a Data Validation
rule on the Action Date field that states Action Date < File Date.
Esssentially, saying pop up a an error message - Action Date can't be less
than File Date.

The problem I'm having is when the Action Date and File Date have the same
date the error message pops up. This isn't correct - it should only pop up
when it is less than the File Date. If they are equal that's fine.

Not sure why this is happening. Both dates are entered in the form by the
user. The form is tied to a backend database. Both fields are setup as
optional_dateTime types. The database fields are using the Date/Time datatype
and short date as the format.

Thanks!