Update query with DatePart() problem

M

MarkB

I have created a query that is supposed to update a field (current period) to
a value based on the current date and the (Day span) field which contains
non-zero values. The problem is that all results are zero.

UPDATE [Task frequencies] SET [Task frequencies].[Current period] =
DatePart(yyyy,Date())*1000+DatePart(y,Date())*[Task frequencies].[Day span];

What am I doing wrong?
 
K

Ken Snell [MVP]

You've left out the " characters to delimit the strings in the first
argument for the DatePart function:

UPDATE [Task frequencies] SET [Task frequencies].[Current period] =
DatePart("yyyy",Date())*1000+DatePart("y",Date())*[Task frequencies].[Day
span];
 
M

MarkB

Ken,

I have tried the statement w/ quotes around the return type and w/ brackets
around the date function. All permutations (4 of them) yield the same results.

Ken Snell said:
You've left out the " characters to delimit the strings in the first
argument for the DatePart function:

UPDATE [Task frequencies] SET [Task frequencies].[Current period] =
DatePart("yyyy",Date())*1000+DatePart("y",Date())*[Task frequencies].[Day
span];

--

Ken Snell
<MS ACCESS MVP>


MarkB said:
I have created a query that is supposed to update a field (current period)
to
a value based on the current date and the (Day span) field which contains
non-zero values. The problem is that all results are zero.

UPDATE [Task frequencies] SET [Task frequencies].[Current period] =
DatePart(yyyy,Date())*1000+DatePart(y,Date())*[Task frequencies].[Day
span];

What am I doing wrong?
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

What is the data type of Current period field?

If you run a select query similar to that shown below, do the actual and
calculated fields return the correct/expected results?

SELECT [Task frequencies].[Current period],
[Task frequencies].[Day span]
DatePart("yyyy",Date()) AS YYYYvalue,
DatePart("y",Date()) AS Yvalue,
DatePart("yyyy",Date())*1000+
DatePart("y",Date())*[Task frequencies].[Day span]
AS CalculatedField
FROM [Task frequencies];

--

Ken Snell
<MS ACCESS MVP>

MarkB said:
Ken,

I have tried the statement w/ quotes around the return type and w/
brackets
around the date function. All permutations (4 of them) yield the same
results.

Ken Snell said:
You've left out the " characters to delimit the strings in the first
argument for the DatePart function:

UPDATE [Task frequencies] SET [Task frequencies].[Current period] =
DatePart("yyyy",Date())*1000+DatePart("y",Date())*[Task frequencies].[Day
span];

--

Ken Snell
<MS ACCESS MVP>


MarkB said:
I have created a query that is supposed to update a field (current
period)
to
a value based on the current date and the (Day span) field which
contains
non-zero values. The problem is that all results are zero.

UPDATE [Task frequencies] SET [Task frequencies].[Current period] =
DatePart(yyyy,Date())*1000+DatePart(y,Date())*[Task frequencies].[Day
span];

What am I doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
M

MarkB

Ken,

Your SELECT statement (save a missing comma) worked.

Here is the final version of the select statement:

SELECT [Task frequencies].Frequency, [Task frequencies].[Current period],
[Task frequencies].[Day span], [Task frequencies].[Week span], [Task
frequencies].[Month span], [Task frequencies].[Quarter span],
DatePart("yyyy",Date())*1000 +(DatePart("y",Date())*[Task frequencies].[Day
span]+0.1) +(DatePart("ww",Date())*[Task frequencies].[Week span]+0.1)
+(DatePart("m",Date())*[Task frequencies].[Month span]+0.1)
+(DatePart("q",Date())*[Task frequencies].[Quarter span]+0.1) AS
CalculatedField4
FROM [Task frequencies];

The problem remains for the UPDATE statement shown here:

UPDATE [Task frequencies] SET [Task frequencies].[Current period] =
DatePart("yyyy",Date())*1000 +(DatePart("y",Date())*[Task frequencies].[Day
span]+0.1) +(DatePart("ww",Date())*[Task frequencies].[Week span]+0.1)
+(DatePart("m",Date())*[Task frequencies].[Month span]+0.1)
+(DatePart("q",Date())*[Task frequencies].[Quarter span]+0.1);

The result in [Task frequencies].[Current period] is still zero.

I appreciate any help you might provide.

Ken Snell said:
What is the data type of Current period field?

If you run a select query similar to that shown below, do the actual and
calculated fields return the correct/expected results?

SELECT [Task frequencies].[Current period],
[Task frequencies].[Day span]
DatePart("yyyy",Date()) AS YYYYvalue,
DatePart("y",Date()) AS Yvalue,
DatePart("yyyy",Date())*1000+
DatePart("y",Date())*[Task frequencies].[Day span]
AS CalculatedField
FROM [Task frequencies];

--

Ken Snell
<MS ACCESS MVP>

MarkB said:
Ken,

I have tried the statement w/ quotes around the return type and w/
brackets
around the date function. All permutations (4 of them) yield the same
results.

Ken Snell said:
You've left out the " characters to delimit the strings in the first
argument for the DatePart function:

UPDATE [Task frequencies] SET [Task frequencies].[Current period] =
DatePart("yyyy",Date())*1000+DatePart("y",Date())*[Task frequencies].[Day
span];

--

Ken Snell
<MS ACCESS MVP>


I have created a query that is supposed to update a field (current
period)
to
a value based on the current date and the (Day span) field which
contains
non-zero values. The problem is that all results are zero.

UPDATE [Task frequencies] SET [Task frequencies].[Current period] =
DatePart(yyyy,Date())*1000+DatePart(y,Date())*[Task frequencies].[Day
span];

What am I doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
M

MarkB

Ken,

Sorry about that. The update query preview does not show the calculated
values but when the query is actually run the appropriate values are updated.

Mark

Ken Snell said:
What is the data type of Current period field?

If you run a select query similar to that shown below, do the actual and
calculated fields return the correct/expected results?

SELECT [Task frequencies].[Current period],
[Task frequencies].[Day span]
DatePart("yyyy",Date()) AS YYYYvalue,
DatePart("y",Date()) AS Yvalue,
DatePart("yyyy",Date())*1000+
DatePart("y",Date())*[Task frequencies].[Day span]
AS CalculatedField
FROM [Task frequencies];

--

Ken Snell
<MS ACCESS MVP>

MarkB said:
Ken,

I have tried the statement w/ quotes around the return type and w/
brackets
around the date function. All permutations (4 of them) yield the same
results.

Ken Snell said:
You've left out the " characters to delimit the strings in the first
argument for the DatePart function:

UPDATE [Task frequencies] SET [Task frequencies].[Current period] =
DatePart("yyyy",Date())*1000+DatePart("y",Date())*[Task frequencies].[Day
span];

--

Ken Snell
<MS ACCESS MVP>


I have created a query that is supposed to update a field (current
period)
to
a value based on the current date and the (Day span) field which
contains
non-zero values. The problem is that all results are zero.

UPDATE [Task frequencies] SET [Task frequencies].[Current period] =
DatePart(yyyy,Date())*1000+DatePart(y,Date())*[Task frequencies].[Day
span];

What am I doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

When you switch to the datasheet view for the update query, it will NOT show
the values that will be written into the records; instead, it shows the
records that will be updated and shows the current data in the fields in
those records.

So I take it that all is working ok now?

--

Ken Snell
<MS ACCESS MVP>

MarkB said:
Ken,

Sorry about that. The update query preview does not show the calculated
values but when the query is actually run the appropriate values are
updated.

Mark

Ken Snell said:
What is the data type of Current period field?

If you run a select query similar to that shown below, do the actual and
calculated fields return the correct/expected results?

SELECT [Task frequencies].[Current period],
[Task frequencies].[Day span]
DatePart("yyyy",Date()) AS YYYYvalue,
DatePart("y",Date()) AS Yvalue,
DatePart("yyyy",Date())*1000+
DatePart("y",Date())*[Task frequencies].[Day span]
AS CalculatedField
FROM [Task frequencies];

--

Ken Snell
<MS ACCESS MVP>

MarkB said:
Ken,

I have tried the statement w/ quotes around the return type and w/
brackets
around the date function. All permutations (4 of them) yield the same
results.

:

You've left out the " characters to delimit the strings in the first
argument for the DatePart function:

UPDATE [Task frequencies] SET [Task frequencies].[Current period] =
DatePart("yyyy",Date())*1000+DatePart("y",Date())*[Task
frequencies].[Day
span];

--

Ken Snell
<MS ACCESS MVP>


I have created a query that is supposed to update a field (current
period)
to
a value based on the current date and the (Day span) field which
contains
non-zero values. The problem is that all results are zero.

UPDATE [Task frequencies] SET [Task frequencies].[Current period] =
DatePart(yyyy,Date())*1000+DatePart(y,Date())*[Task
frequencies].[Day
span];

What am I doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
M

MarkB

Yes. Thanks Ken.

Ken Snell said:
When you switch to the datasheet view for the update query, it will NOT show
the values that will be written into the records; instead, it shows the
records that will be updated and shows the current data in the fields in
those records.

So I take it that all is working ok now?

--

Ken Snell
<MS ACCESS MVP>

MarkB said:
Ken,

Sorry about that. The update query preview does not show the calculated
values but when the query is actually run the appropriate values are
updated.

Mark

Ken Snell said:
What is the data type of Current period field?

If you run a select query similar to that shown below, do the actual and
calculated fields return the correct/expected results?

SELECT [Task frequencies].[Current period],
[Task frequencies].[Day span]
DatePart("yyyy",Date()) AS YYYYvalue,
DatePart("y",Date()) AS Yvalue,
DatePart("yyyy",Date())*1000+
DatePart("y",Date())*[Task frequencies].[Day span]
AS CalculatedField
FROM [Task frequencies];

--

Ken Snell
<MS ACCESS MVP>

Ken,

I have tried the statement w/ quotes around the return type and w/
brackets
around the date function. All permutations (4 of them) yield the same
results.

:

You've left out the " characters to delimit the strings in the first
argument for the DatePart function:

UPDATE [Task frequencies] SET [Task frequencies].[Current period] =
DatePart("yyyy",Date())*1000+DatePart("y",Date())*[Task
frequencies].[Day
span];

--

Ken Snell
<MS ACCESS MVP>


I have created a query that is supposed to update a field (current
period)
to
a value based on the current date and the (Day span) field which
contains
non-zero values. The problem is that all results are zero.

UPDATE [Task frequencies] SET [Task frequencies].[Current period] =
DatePart(yyyy,Date())*1000+DatePart(y,Date())*[Task
frequencies].[Day
span];

What am I doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
Top