Solved Error in sql code (1 Viewer)

[chizzy42]

Hi all, hope all is well. Im putting together a new database to work out passes and fails an hour and resultant percentage yield and i've got a couple of quandaries just now im hoping someone more enlightened can pick up. The code seems to work and produce the desired results, but if i try to edit in sql view i get the error....Syntax error in string in query expression '00\:00'))'. I tried running through a sql validator and sure enough got an error..
You have an error in your SQL syntax; it seems the error is around: '[field1], '00\:00\:00')) AS [Hour], ( SELECT COUNT(*) FROM ' at line 2. The code as said previous works functionally but seems to have an error in formatting. Any help would be appreciated.

SELECT
  Val(Format([field1], '00\:00\:00')) AS [Hour],
  (
    SELECT
      COUNT(*)
    FROM
      Timer1 AS T1
    WHERE
      Val(Format(T1.[field1], '00\:00\:00')) = Val(Format(Timer1.[field1], '00\:00\:00'))
      AND T1.Field3 Like 'pass'
      AND T1.datein Between (
        Date() + #8:00:00 AM#) And (Date()+#11:59:00 PM#)) AS Pass,(
          SELECT
            COUNT(*)
          FROM
            Timer1 AS T1
          WHERE
            Val(Format(T1.[field1], '00\:00\:00')) = Val(Format(timer1.[field1], '00\:00\:00'))
            AND T1.Field3 Like 'fail'
            AND T1.datein Between (
              Date() + #8:00:00 AM#) And (Date()+#11:59:00 PM#)) AS fail, IIf(([fail] + [pass]) = 0, 0, Round(([pass] / ([fail] + [pass])) * 100, 2)) AS yield
              FROM
                timer1
              GROUP BY
                Val(Format([field1], '00\:00\:00'));

 

[Minty]

DatePart("h",[field1] ) as HourVal
will return the hour as an integer from a datetime field?

Avoid calling it Hour, it is a reserved name

 

[chizzy42]

Hi minty thanks for the quick response, will this code work for the data in field1 if the text is just unformatted text ie in the format 110845 where 11 is the hr 08 mins 45 secs ?

 

[Minty]

No - it was based on your field being a Date field. (With a time portion that is always there even if you can't see it.)

If it is text and just the time then you will need to do some string formatting.
I just tested this in the immediate window, and it works fine

? Timevalue(Format("110845","00\:00\:00"))
11:08:45

 

[chizzy42]

Thanks for that , it works but doesn't sum the passes fails now for each hour just gives 1 for each timestamp. new code below and ive included before and after pics of the result change . The format just really has to pull the hour from the result and not the full time.

SELECT
Timevalue(Format([field1],"00\:00\:00")) as HourVal,
  (
    SELECT
      COUNT(*)
    FROM
      Timer1 AS T1
    WHERE
      Timevalue(Format(T1.[field1],"00\:00\:00")) = Timevalue(Format(Timer1.[field1],"00\:00\:00"))
      AND T1.Field3 Like 'pass'
      AND T1.datein Between (
        Date() + #8:00:00 AM#) And (Date()+#11:59:00 PM#)) AS Pass,(
          SELECT
            COUNT(*)
          FROM
            Timer1 AS T1
          WHERE
            Timevalue(Format(T1.[field1],"00\:00\:00")) =Timevalue(Format(Timer1.[field1],"00\:00\:00"))
            AND T1.Field3 Like 'fail'
            AND T1.datein Between (
              Date() + #8:00:00 AM#) And (Date()+#11:59:00 PM#)) AS fail, IIf(([fail] + [pass]) = 0, 0, Round(([pass] / ([fail] + [pass])) * 100, 2)) AS yield
              FROM
                timer1
              GROUP BY
               Timevalue(Format([field1],"00\:00\:00"));

 

[Minty]

Try adding the Datepart around the previous bit?

Datepart("h",Timevalue(Format([field1],"00\:00\:00"))) as HourVal,

But looking at your code does the DateIn field not have the time portion you need already present in it???
In which case

DatePart("h",[DateIn]) as HourVal

might well work instead?

 

[chizzy42]

Boom, used the code below and it looks to be coming up with the correct query results. I had written this query a while back and to be fair i was a bit fuzzy how it came together. I was just surprised it worked but couldn't edit it as it just locked up with the error i first stated.

Datepart("h",Timevalue(Format([field1],"00\:00\:00"))) as HourVal,

Thanks very much for your time its much appreciated.

 

[arnelgp]

datepart?

you can simply use:

Hour([field1]) As HourVal

 

[chizzy42]

arnelgp, do you mean replacing

Datepart("h",Timevalue(Format([field1],"00\:00\:00"))) as HourVal

with

Hour([field1]) As HourVal

i thought the field data had to be formatted as the time is in the format 143612