2. ASPRunnerPro General questions
  3. [SOLVED]   Excel Date Field Issue
This topic is locked
[SOLVED]

  Excel Date Field Issue

 		1/17/2013 2:06:09 AM
ASPRunnerPro General questions
R
 rob_by68 author

Hi to everybody.
I am using ASP Runner PRO 6.2 and i am havin issuus with fields formatted as dates in Excel 2010.
Basically i have an Excel spreadsheet database in which i have some data fields. What i need to do

is insert directly in Excel 2010 and present the data (only for List, Search, no Add) via ASP Runner Pro.
Whenever i input some date in some date field, ASP Runner PRO Displays correctly, but when i do search based

on date fields the system returns an error ("Microsoft Access Database Engine error '80040e07'Data type mismatch in criteria expression).
What i have found in sql query Results is that fields formatted as Dates in Excel are "seen" by ASP Runner PRO with a format of

"yyyy-mm-dd 00:00:00" while the original field has been filled with a format of "dd/mm/yyyy" (Italian Standard). In the list page records and dates

are displayed correctly, the probem arises when i try to do searches based on date fields.
Have you any suggestion on how resolve this issue? All System parameters (Date, Language, Time Zone) are correct.
Any help woul be very appreciated.
Thank you and regards from Italy.
Roberto

Bologna (I)
G
 		gdmacdo 1/17/2013

Have you checked your search page? Double-click on the date field and click the View as tab. Is it set to short date?
If it is and is still not working, maybe try to set it as "Custom" and use this formula:
strValue = format_shortdate(db2time(data("Your_Date_Field_Name")))



Hi to everybody.
I am using ASP Runner PRO 6.2 and i am havin issuus with fields formatted as dates in Excel 2010.
Basically i have an Excel spreadsheet database in which i have some data fields. What i need to do

is insert directly in Excel 2010 and present the data (only for List, Search, no Add) via ASP Runner Pro.
Whenever i input some date in some date field, ASP Runner PRO Displays correctly, but when i do search based

on date fields the system returns an error ("Microsoft Access Database Engine error '80040e07'Data type mismatch in criteria expression).
What i have found in sql query Results is that fields formatted as Dates in Excel are "seen" by ASP Runner PRO with a format of

"yyyy-mm-dd 00:00:00" while the original field has been filled with a format of "dd/mm/yyyy" (Italian Standard). In the list page records and dates

are displayed correctly, the probem arises when i try to do searches based on date fields.
Have you any suggestion on how resolve this issue? All System parameters (Date, Language, Time Zone) are correct.
Any help woul be very appreciated.
Thank you and regards from Italy.
Roberto

Bologna (I)
R
 		rob_by68 author 1/17/2013

Thank you Greg Mac.
Field was set as Short Date.
I had no success changing the "View As" property as "Custom" and inserting

strValue = format_shortdate(db2time(data("Data Ingresso")))

where "Data Ingresso" is the field name.

I keep on receiving exactly the same error.
I am bangin' my head. Can you suggest somethint else?
Thanks a lot again.
Sergey Kornilov admin 1/17/2013

My suggestion is to switch from Excel to real database like MS Access or MySQL. In Excel you cannot actually set the data type of the field.
R
 		rob_by68 author 1/18/2013

Thanks to everybody.
At this point, i'll do what Sergey suggested, i'll migrate my data to Access.
Thanks again.
Roberto