Search options (Page 32 of 34)

if you use Report SQL or Button action SQL query use

{edit5}

where jobs.id = {edit5}

http://myvisualdatabase.com/help_en/SQLquery.html

in script sql query
use '+jobsfiles.edit5.sqlValue+'  or  '''+jobsfiles.edit5.text+'''

http://myvisualdatabase.com/help_en/script_edit.html

Я прошу прощения, но где у меня в примере такая строчка как у вас ?

TIME((STRFTIME('%s', Время на дистанции) + STRFTIME('%s', Штраф за пропуск) * Пропуск КП), 'unixepoch')

Прежде чем дать вам я проверил работает ли это.

Вот проверка

TIME((STRFTIME('%s', '00:45:00') + STRFTIME('%s', '00:20:00') * 2), 'unixepoch')
01:25:00

Только руками

It is possible in this way for a table column. Then there is no need for a calculated field

TNxNumberColumn(Form1.TableGrid1.Columns[i]).FormatMask := 'hh:nn am/pm';

а что вы хотите сделать ?
Просто умножить Mtime * 2 ?

Тогда

SELECT TIME((STRFTIME('%s', Mtime))*2, 'unixepoch')

Все будет корректно до 23:59:59. 24 часа отобразится снова как  00:00:00.
Обработка в формате более 24 часов потребует изменить решение

Для SQLite

00:20:00 * 5

1.пересчитать время в секунды
  00:20:00 = 1200
2.умножить на 5
  1200 * 5 = 6000
3. в sqlite используем функцию time()
  time(6000, 'unixepoch') = 01:40:00

01:45:00 * 2
6300 * 2
time(12600, 'unixepoch') = 03:30:00

SELECT TIME((STRFTIME('%s', '01:45:00'))*2, 'unixepoch')

Для скрипта:

Form1.Edit1.Text := TimeToStr(StrToTime('01:45:00')*2);
= 03:30:00

Hi,

in the program you can use the advice brian.zaballa but for REPORT :

1. red field "(the red date on the report)" in REPORT write new text

[StrToDateTime(<Report."event.eDateTime">)]

2. in format tab select Date\Time and "format string" type mmmm (or what do yo want... for example hh:mm am/pm )
the same as
  DisplayFormat
    FormatStr: mmmm
    Kind: fkDateTime

OR

1. red field "(the red date on the report)" in REPORT write new text (month or time with am/pm)

[FormatDateTime('mmmm',StrToDateTime(<Report."event.eDateTime">))] 
or
[FormatDateTime('hh:nn:ss am/pm',StrToDateTime(<Report."event.eDateTime">))]
or
[StrToDateTime(<Report."event.eDateTime">) #dmmmm]
or
[StrToDateTime(<Report."event.eDateTime">) #dhh:mm am/pm]

2. in format tab select Text (no formatting).
the same as
  DisplayFormat
    FormatStr:
    Kind: fkText

Thats all

The date and time is displayed in the table and in the program depending on the regional settings in WINDOWS. But if you really need it, you can create a calculated field with something like this(adapted from the internet ). And output it to a table.

CASE WHEN strftime('%H', eDateTime) % 12 = 0 
        THEN 12 
        ELSE strftime('%H', eDateTime) % 12 END 
        || ':' || strftime('%M', eDateTime)
    || ' ' ||
    CASE WHEN
        strftime('%H', eDateTime)-0 >= 12 THEN 'PM'
        ELSE 'AM' END 

Hi,

Yes, your approach is correct. But there is a difference between your case and this one. In your case, this is not enough, since the SUM will not work.
Create event in TextObject OnBeforePrint
For your case in the script

var
 leg_M: integer;    

procedure ReportbLegcfLegMileageOnBeforePrint(Sender: TfrxComponent);                       
begin           
  if not ValidInt(<Report."bLeg.cfLegMileage">) then                                   
    leg_M := 0 else leg_M := StrToInt(<Report."bLeg.cfLegMileage">);                                
end;

begin

end.

in MasterData for TextObject instead of [Report."bLeg.cfLegMileage"] write your variable [<leg_M>].
in GroupFooter1 for TextObject instead of [SUM(<Report."bLeg.cfLegMileage">,MasterData1)] write [SUM(<leg_M>,MasterData1)].
in GroupFooter2 for TextObject instead of [SUM(<Report."bLeg.cfLegMileage">,MasterData1)] write [SUM(<leg_M>,MasterData1)].

Lets you revert to a numeric representation (integer in this case)
It is easier to write IFNULL in the request.

That's all

P.S. For 7410_project and your project.
       If you do not want to display 0. You can enable in TextObject properties - HideZeros: True and the field remains blank.

Hi
These NULLs cause this result

name          OD         start     finish      diff
Kelon Decee    637381    637381    NULL    NULL
Kelon Decee    637500    637500    637381    119
Kelon Decee    637782    637782    637500    282
Noren Boley    635729    635729    NULL    NULL
Noren Boley    635917    635917    635729    188
Noren Boley    636129    636129    635917    212

please modify CALCULATED FIELD  cfLegMileage

IFNULL((select max(x.od) from bleg x where x.od = bleg.od and x.id_atrip = atrip.id)  
-
(select max(x.od) from bleg x where x.od < bleg.od and x.id_atrip = atrip.id),0) 

and simplify , works faster

IFNULL((SELECT bLeg.OD - max(b.OD) FROM bLeg b WHERE bLeg.id_aTrip = b.id_aTrip and b.OD  < bLeg.OD),0)

AFTER

name            OD        start    finish    diff res
Kelon Decee    637381    637381    NULL    0
Kelon Decee    637500    637500    637381    119
Kelon Decee    637782    637782    637500    282
Noren Boley    635729    635729    NULL    0
Noren Boley    635917    635917    635729    188
Noren Boley    636129    636129    635917    212

You need to be careful when you create your databases and queries to it.

If you create related columns in your tables, then do not forget to fill them in these tables.
If you refer to a table in a query and mention a related column in another table,
do not forget about the JOIN (INNER or LEFT) of the other table ON related column.
If you have created a column, think about whether to put a default value (zero or something else).
I drew your attention to these things in the previous topic.

Yes, your project has gone relatively far, but you need to strive to simplify and optimize databases, tables, queries.

To test the correctness of your program, create yourself a database with the most correct data.
Otherwise, you will end up running in circles between your code and the database.

Strive for your program to fill in all the necessary data in the tables and then work with the output forms.
Create a form for yourself with one table and display the data of the tables in it one by one to control the correctness of filling.
To work with the database, you can use SQLlite administration programs.

Everything else works correctly in an MVD program (almost ).

Sorry. Translate by Google.

Yes, you need to use JOIN.

A JOIN clause is used to combine rows from two or more tables, based on a related column between them

The simplest JOIN example:

SELECT EquipName, EqModel, EqPurchDate,
       EqNotes, EqSparePart, EquipType
             
FROM tbl_Equip, tbl_EquipType

WHERE tbl_Equip.RELATED COLUMN = tbl_EquipType.RELATED COLUMN

AND tbl_Equip.EqSparePart LIKE "Installed" 

RELATED COLUMN - replace with your related column.

you can read about JOIN on the Internet.

During my attempt to rewrite the request, I got an unexpected result. After analysis, I can say: Your troubles are the result of a not very good database structure
and the TRIP and TRIPLEG tables do not match in the STATE (id_state) part. There may be other errors.
In the future, you may face unpredictable calculations.
Create the tables in the correct state with your data.

CheckBox component properties:

AllowGrayed -  Determines whether check box can be in a "grayed" state.
http://myvisualdatabase.com/help_en/CheckBox.html - please read.

Put "AllowGrayed" in the FALSE state and you will have only YES and NO.