Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Best Way to update Date Billed (2000)

    I am looking for the best way to fill in the DateBilled on a batch of invoices. I'm assuming I create a query to first pull out the invoices to be billed. The SQL I have for the query is:

    SELECT tblBilling.Autonumber, tblBilling.AccountID, tblBilling.DateBiled, tblBilling.PaidInFull
    FROM tblBilling
    WHERE (((tblBilling.PaidInFull)=No));

    This is where I get stuck. I want to now update the DateBilled fields which are now blank to indicate the date billed as being =Now() in short date format. I tried creating an append query, but can't seem to get it to fill in the dates.

    Thanks,
    Leesha

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Best Way to update Date Billed (2000)

    An append query is used for adding new records to a table. What you need is an update query. If I understand your intention correctly, the SQL for it should be like this:

    UPDATE tblBilling SET DateBilled = Date() WHERE PaidInFull = False

    Remarks:
    <LI>When you switch an update query to datasheet view, you will only see the field(s) that will be updated, and you see the current value (before updating).
    <LI>To execute an update query from design mode, select Query | Run or click the Run button (the red exclamation mark); to run it from the database window, double click the query.
    <LI>The SQL above will set DateBilled to today (just the date part, without the time); if you want to record the date and the time, use Now() instead of Date().
    <LI>If you enter the above SQL into SQL view, the switch to design view and back, Access will probably modify the SQL slightly. This is nothing to worry about.
    <LI>To set a specific date format for DateBilled, click in this column, then activate the Properties window and set the Format property.[/list]Oh, and try this on a copy first, to test that it does what you want!

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Best Way to update Date Billed (2000)

    Ah!!! Thanks for the rationale!! It worked beautifully. I had tried the update query but was trying to enter the name of the field in the "update to" section of the query as I had interpretted that to be = to "where" I wanted the data updated vs "what" data I wanted stored! I will simply never think the way the Access does, but I'll also not let it beat me!

    Thanks,
    Leesha

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •