SQL Queries Questions

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

All the field below are in Date/Time Field. Is there any problem with the SQL
statement below as I got no result printed even though [BEG_WORK] is more
than [SCHD_START]? I am trying to use Update SQL Queries.

I am trying to display the duration of [LATE_HRS] if [BEG_WORK] is more than
[SCHD_START]. If [BEG_WORK] <=[SCHD_START] Then 0:00 will be show.

IIf(([BEG_WORK]>[SCHD_START]),([LATE_HRS]=([BEG_WORK]-[SCHD_START])),([LATE_HRS]="#0:00#"))
 
Seikyo said:
All the field below are in Date/Time Field. Is there any problem with
the SQL
statement below as I got no result printed even though [BEG_WORK] is
more
than [SCHD_START]? I am trying to use Update SQL Queries.

I am trying to display the duration of [LATE_HRS] if [BEG_WORK] is
more than [SCHD_START]. If [BEG_WORK] <=[SCHD_START] Then 0:00 will
be show.
Click to expand...
IIf(([BEG_WORK]>[SCHD_START]),([LATE_HRS]=([BEG_WORK]-[SCHD_START])),([L
ATE_HRS]="#0:00#"))

You can't perform an assignment like that in SQL. Your expression would
be better framed as:

IIf([BEG_WORK]>[SCHD_START],
([BEG_WORK]-[SCHD_START]),
#0:00#)

Is this expression to be used in an update query? Then the query's full
SQL might look something like this:

UPDATE YourTable SET [LATE_HRS] =
IIf([BEG_WORK]>[SCHD_START],
([BEG_WORK]-[SCHD_START]),
#0:00#);

If you're trying to do it in a SELECT query instead, with [LATE_HRS] as
a calculated field, it might look something like this:

SELECT
*,
IIf([BEG_WORK]>[SCHD_START],
([BEG_WORK]-[SCHD_START]),
#0:00#) AS [LATE_HRS]
FROM YourTable;
 
All the field below are in Date/Time Field. Is there any problem with the SQL
statement below as I got no result printed even though [BEG_WORK] is more
than [SCHD_START]? I am trying to use Update SQL Queries.

I am trying to display the duration of [LATE_HRS] if [BEG_WORK] is more than
[SCHD_START]. If [BEG_WORK] <=[SCHD_START] Then 0:00 will be show.

IIf(([BEG_WORK]>[SCHD_START]),([LATE_HRS]=([BEG_WORK]-[SCHD_START])),([LATE_HRS]="#0:00#"))
Click to expand...

What's the context of this IIF function? It almost certainly will NOT
work as you intend. A parameter can contain a value but it cannot
contain an operator or an operation. And you're mixing levels here -
an Update query will not display *anything*, it will simply update
data in a table.

Please post the entire SQL of the query, and describe what you want it
to accomplish.

John W. Vinson[MVP]
 
I suddenly found out the solution for the problem I post but I think is very
lengthy.
I use several SQL to do the stuff and Macro to link them I think.

1> I would update all the Hours by having [END_WORK] - [SCHD_START] (Both in
time field)

[END_WORK]-[SCHD_START]

2> I would filter those Queries with result more than zero so only the
OverTime Hours will be found.

[OT_HRS]-[BASE_HRS]>0

3> I would find those BASE_HRS>0 to deduct 1 Hours from the OT, only those
OT >0 will be show

BASE_HRS>0, [OT_HRS]-#1:00:00 AM#

But now I do find some problem with my system. If I run the whole series of
SQL , I will end up doing the record again and again. This will be a big
trouble when the database get larger. Is there any way in which I can cut
down on the search like an expression?
 
Seikyo said:
I suddenly found out the solution for the problem I post but I think
is very lengthy.
I use several SQL to do the stuff and Macro to link them I think.

1> I would update all the Hours by having [END_WORK] - [SCHD_START]
(Both in time field)

[END_WORK]-[SCHD_START]

2> I would filter those Queries with result more than zero so only the
OverTime Hours will be found.

[OT_HRS]-[BASE_HRS]>0

3> I would find those BASE_HRS>0 to deduct 1 Hours from the OT, only
those OT >0 will be show

BASE_HRS>0, [OT_HRS]-#1:00:00 AM#

But now I do find some problem with my system. If I run the whole
series of SQL , I will end up doing the record again and again. This
will be a big trouble when the database get larger. Is there any way
in which I can cut down on the search like an expression?
Click to expand...

Almost certainly there is a simpler way to accomplish what you want ...
but I don't understand what it is that you want to accomplish. What you
say in this last message doesn't seem to fit with your original question
in this thread; maybe you have multiple discussion threads going at
once? If so, I don't know what you may have said in other threads.

I suggest you start with a description of the table or tables involved,
and then state what it is you want to do..
 
Back
Top