A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Updating Date with Update Query



 
 
Thread Tools Display Modes
  #1  
Old March 8th, 2010, 07:22 AM posted to microsoft.public.access.gettingstarted
ialvi
external usenet poster
 
Posts: 1
Default Updating Date with Update Query

I have inherited an Access 2003 database that has a table that stores Invoice
Number, Invoice Month, Quantities, etc. A user in my team has pointed out
that while each record has the Invoice Number filled (because it is part of
the primary key), many records are missing the Invoice Month (an optional
field.) This user wants the Invoice Month filled. I created an update query
to fill in the blanks in the Invoice Month field. In the design of this
query, in the "Update to" field I am using an expression like this: IIf([Inv
Number]=2,1/1/2010,[Inv Month]) I was planning to run this query for each
Invoice Number once, but the problem is that no matter what date is in the
expression, when the query is run, the date written in the table is always
12/30/1899. Why is this query converting all of my dates into 12/30/1899? How
can I control it?
  #2  
Old March 8th, 2010, 03:17 PM posted to microsoft.public.access.gettingstarted
John Spencer
external usenet poster
 
Posts: 7,815
Default Updating Date with Update Query

You need to delimit the date with # marks. Right now the query is updating
the date to the arithmetic result of 1 divided by 1 divided by 2010. Which is
a time on December 30, 1899.

I would change the update to expression to
#1/1/2010#
and add criteria to filter the records to be updated

UPDATE InvoiceTable
SET [Inv Month] = #1/1/2010#
WHERE [Inv Month] is NULL
AND [Inv Number] in (2,5,15,22)

Or if there is a range of invoices to be updated use something like the
following. This would set every Inv Month in the range to the specified date
if the Inv Month was null.

UPDATE InvoiceTable
SET [Inv Month] = #1/1/2010#
WHERE [Inv Month] is NULL
AND [Inv Number] Between 2 and 123



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

ialvi wrote:
I have inherited an Access 2003 database that has a table that stores Invoice
Number, Invoice Month, Quantities, etc. A user in my team has pointed out
that while each record has the Invoice Number filled (because it is part of
the primary key), many records are missing the Invoice Month (an optional
field.) This user wants the Invoice Month filled. I created an update query
to fill in the blanks in the Invoice Month field. In the design of this
query, in the "Update to" field I am using an expression like this: IIf([Inv
Number]=2,1/1/2010,[Inv Month]) I was planning to run this query for each
Invoice Number once, but the problem is that no matter what date is in the
expression, when the query is run, the date written in the table is always
12/30/1899. Why is this query converting all of my dates into 12/30/1899? How
can I control it?

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:07 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.