Author Topic: Date and Time Formats  (Read 4463 times)

orgtec

  • Newbie
  • *
  • Posts: 15
    • View Profile
Date and Time Formats
« on: April 05, 2014, 04:40:42 PM »
I'm trying to get some Date and Time Formats to be used in a consistent manner and I'm having some trouble.

I have configured in Access 2 fields, VisitDate formatted and fixed like dd-mm-yyyy and VisitTime formatted and fixed like hh:mm.

I have tried several things but nothing seems to work on Android Form, Empty field, Fixed Field etc, it always syncs like dd-mm-yyyy hh:mm:ss for both fields.

I have configured on Form VisitDate as Date only, and it shows only Date as dd-mm-yyyy and also configured VisitTime as Time only and it shows hh:mm:ss.

If I try do configue VisitTime as Fixed it forces me to put in dd-mm-yyyy hh:mm:ss in full.

I also tried to convert VisitTime to String and tried to Concatenate(Hour(VisitTime),":",Minute(VisitTime)) to see  I could get around it, no go, Concatenate does not like the ":".

Do you have a way to correctly use the format that is in Access?

The problem of all this is that when I try to filter on a VisitDate to see all the the VisitTimes, only the one that has the Date and Time correct works. If I have 4 visits only one shows up.

Help

CellicaTeam

  • Administrator
  • Hero Member
  • *****
  • Posts: 693
    • View Profile
Re: Date and Time Formats
« Reply #1 on: April 07, 2014, 01:15:31 AM »
Please find inline answers to your queries,
   
1) I have configured in Access 2 fields, VisitDate formatted and fixed like dd-mm-yyyy and VisitTime formatted and fixed like hh:mm.
I have tried several things but nothing seems to work on Android Form, Empty field, Fixed Field etc, it always syncs like dd-mm-yyyy hh:mm:ss for both fields.

=> In Access date is saved as 00:00:0000 when you set time format.


2) I have configured on Form VisitDate as Date only, and it shows only Date as dd-mm-yyyy and also configured VisitTime as Time only and it shows hh:mm:ss.
 If I try do configue VisitTime as Fixed it forces me to put in dd-mm-yyyy hh:mm:ss in full.

=>  Please open the form in Form Manager module, select the date control and change "Format" property to "Time".


3) I also tried to convert VisitTime to String and tried to Concatenate(Hour(VisitTime),":",Minute(VisitTime)) to see  I could get around it, no go, Concatenate does not like the ":".

=> Concatenate function use only two parameter, try following expression in macro.
CONTATINATE(HOUR(VisitTime), CONTATINATE(":", MINUTE(VisitTime) )


4) Do you have a way to correctly use the format that is in Access?

    The problem of all this is that when I try to filter on a VisitDate to see all the the VisitTimes, only the one that has the Date and Time correct works. If I have 4 visits only one shows up.

=> For searching date, time and date time, you need to set date search option of find dialog to TRUE. Now long press on search textbox control and select any one option from the list presented (Date, Time and Date Time)


If you have any issues or questions please mail us at support@cellica.com

orgtec

  • Newbie
  • *
  • Posts: 15
    • View Profile
Re: Date and Time Formats
« Reply #2 on: April 07, 2014, 02:20:47 AM »
1) It is true that is saved as hh:mm:ssss if you donīt choose any format, but when you select a specific format it stay like the format and displays like the format. The problem appears to be that Cellica always saves like hh:mm:ss regardless of format.

2) The field is already specified as time but it still asks for Date and Time in full. It should be that if you specify Date, the entry of fixed should be dd-mm-yyyy, if you specify Time should be hh:mm, and if you specify Date & Time then it should be dd-mm-yyyy hh:mm. It isn't what is happenning

3)I understand now that it is 2 strings at a time, but the Macro PDF does not say that.

4) I'm not using a Find button, I'm using a Combobox with the VisitDate as Source to filter the Subform that has a Grid from the Visit Records. The Combobox shows all records, repeated VisitDates with Date & Time, where Time now is equal to 00:00. Before it showed Time equal to whatever it was recorded which only filtered one result, It appears that now is recording Date & Time with Time as 00:00 and it filters ok. As long has it stays like that the filter works.

Thanks

CellicaTeam

  • Administrator
  • Hero Member
  • *****
  • Posts: 693
    • View Profile
Re: Date and Time Formats
« Reply #3 on: April 08, 2014, 02:43:45 AM »
 
1) It is true that is saved as hh:mm:ssss if you donīt choose any format, but when you select a specific format it stay like the format and displays like the format. The problem appears to be that Cellica always saves like hh:mm:ss regardless of format.
=> You can also set the format for date time values in Cellica Database software using Date Format and Time Format options in "Preferences" of Cellica Database device application.

2) The field is already specified as time but it still asks for Date and Time in full. It should be that if you specify Date, the entry of fixed should be dd-mm-yyyy, if you specify Time should be hh:mm, and if you specify Date & Time then it should be dd-mm-yyyy hh:mm. It isn't what is happenning
=> Set the "Format" property of DateTime control according to your requirement while designing the form in Cellica Database application.
 
 

orgtec

  • Newbie
  • *
  • Posts: 15
    • View Profile
Re: Date and Time Formats
« Reply #4 on: April 08, 2014, 05:59:06 AM »
1- Between the configuration of Access Format and Cellica Format I got them to save correctly, maybe it had to do with the initial configuration of the Profile Connection to the Database.

2- The problem still exists on the configuration of Exact for Date or Time, it does not work the way you think. I send you an Image thru the support email.

Also when you try to choose Records, using a DatTime field configured as Date only, it shows up with time as 00:00:00.

I have not figured out how to place images here on the KB.

orgtec

  • Newbie
  • *
  • Posts: 15
    • View Profile
Re: Date and Time Formats
« Reply #5 on: April 08, 2014, 07:59:44 AM »
Just for an update and a way around the problem.

Since the Exact Time does not work properly, it always shows as Date & Time, in the Forms and in the Access.

I used ADDTIME(hh:mm:ss, 0,0) as an expression that is Assigned on form Startup. So when you select VisitTime to introduce the correct Visit Time, it shows the value in the Field and not the actual time Now. This way I only have to change the Hour and not the Minutes on the selector.

It becomes a minor issue, but the Mask of the Exact setup should work according to the Format that is set.