Update query with DatePart() problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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,

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...
 
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...
 
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...
 
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...
 
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...
 
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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using BETWEEN AND with DatePart 1
query will not work between years 7
Format DatePart 4
Updating field based Between Dates 2
Want 3rd Thursday of each Month 1
Running total in query 2
Another most-recent date query 0
datepart returns erroneous week number? 2

Back
Top