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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|