Re: SQL Queries Questions

From: Dirk Goldgar (dg_at_NOdataSPAMgnostics.com)
Date: 01/28/05 

Date: Fri, 28 Jan 2005 00:23:32 -0500

"Seikyo" <Seikyo@discussions.microsoft.com> wrote in message
news:FD61A3F1-F49D-4A50-A825-83197B4212AD@microsoft.com
> 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])),([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; 

-- 
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)

Relevant Pages

  • Re: Please explain this update query?!?
    ... that update everytime a payment is made against an invoice. ... >> This update query works fine as it updates the values that I want it to, ... >> however, I seem to having a problem with multiple transactions, ie. ... > The way to get summations is thru SELECT queries that use the SQL ...
    (microsoft.public.access.queries)
  • Re: Please explain this update query?!?
    ... I have a prob with an update query and I hope someone can explain it to me. ... Apparently when I run the update query, it updates the invoice with the first Receipt transaction, but it seems to ignore the second transaction? ... Therefore when I do a reconciliation, I'm shown that Invoice101 still has $100 outstanding, when it fact it has already been paid. ... The way to get summations is thru SELECT queries that use the SQL ...
    (microsoft.public.access.queries)
  • Re: Best Practise For Updates between Access 2000 and SQL Server
    ... I would run an update query unless the conditions of the update were too ... > frontend that grabs a set of order records from SQL and pulls them into ... > that have been updated in the frontend are sent to the SQL-Server. ...
    (microsoft.public.access.queries)
  • Re: comparing two tables with the same field
    ... I did do the trim on both fields in both tables. ... Here is the SQL of the trim ... I wouldn't need to recreate this update query after trimming would I? ...
    (microsoft.public.access.queries)
  • Re: advice needed - what approach for sql access
    ... One option is to cache the DataSet in the Session or ViewState. ... how much data you're retrieving, but that obviously affects what is the ... > with data from an SQL database, unfortunately I've been advised that this ... then update the SQL tables with an update query afterwards.... ...
    (microsoft.public.dotnet.faqs)