access 2007 and update table

[Marco]

In access 2003 I use a update query for update a table.
This works perfectly:
UPDATE tblname INNER JOIN qryname ON tblname.ID = qryname.ID SET
tblname.field1 = [field1], tblname.field2 = [field2], tblname.field3 =
[field3]
WHERE (((tblname.aDate)>=Date()-50));
In access 2007 doesn 't show new values and it doesn 't work as it would
have. Why?


thx

 

[strive4peace]

Hi Marco,

"doesn 't show new values"

perhaps no records meet the criteria...

WHERE (((tblname.aDate)>=Date()-50))

"it doesn 't work as it would have"

what do you mean by this?

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Marco]

Hi strive4peace,

"doesn 't show new values"

perhaps no records meet the criteria...

WHERE (((tblname.aDate)>=Date()-50))

I have new values in qryname and I have cancelled some in the table. ;-)
I have tried also in the command button
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdRefresh
CurrentDb.Execute ("UPDATE tblname INNER JOIN qryname ON tblname.ID =
qryname.ID SET tblname.field1 = [field1], tblname.field2 = [field2],
tblname.field3 = [field3] WHERE tblname.aDate >=Date()-50")
DoCmd.SetWarnings True

"it doesn 't work as it would have"

what do you mean by this?

If i switch in access 2003 works well:
1) the update query
2) the command button
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdRefresh
DoCmd.OpenQuery "qryname", acViewNormal, acEdit
DoCmd.SetWarnings True
3) the command button2
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdRefresh
CurrentDb.Execute ("UPDATE tblname INNER JOIN qryname ON tblname.ID =
qryname.ID SET tblname.field1 = [field1], tblname.field2 = [field2],
tblname.field3 = [field3] WHERE tblname.aDate >=Date()-50")
DoCmd.SetWarnings True
4) the update query design view, datasheet view

Is it a bug ?

Note:
beta2tr
in qryname the field1-3 are calculated field

Regards

Warm Regards,
Crystal
*
have an awesome day
*

Also to You

MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

In access 2003 I use a update query for update a table.
This works perfectly:
UPDATE tblname INNER JOIN qryname ON tblname.ID = qryname.ID SET
tblname.field1 = [field1], tblname.field2 = [field2], tblname.field3 =
[field3]
WHERE (((tblname.aDate)>=Date()-50));
In access 2007 doesn 't show new values and it doesn 't work as it would
have. Why?


thx

 

[strive4peace]

Hi Marco,

after you make changes to a table using SQL, you need to refresh the
table definitions so that other processes (ie: your form) sees the changes

currentdb.tabledefs.refresh
DoEvents

*** DoEvents ***

DoEvents is used to make VBA pay attention to what is currently
happening and look to see if the OS (Operating System) has any requests.

ie: if you have a loop and want to be able to BREAK it with CTRL-BREAK,
put DoEvents into the loop

DoEvents will also update values written to a form by a general
procedure or code behind another form or report

A DoEvents is done when you use MsgBox, or are stepping through code
(since it has to pay attention to the keyboard)

It is a good way to say, "Wake Up!"

***
before you update your table using SQL, you should save changes to the
form...

if me.dirty then me.dirty = false

***

since you are using CurrentDb.Execute,
you don't need
DoCmd.SetWarnings False

***

it is also a good idea to assign the SQL to a string so that you can
examine it -- you also need # delimiters around the date to compare

you also need to qualify field1 with qryname.field1 so Access knows
where it comes from (field1 is in both tables and Access needs to know
which to get it from)

~~~~~~~~~~~~~~~~~
Dim strSql As String

'create SQL to update records
strSql = ""UPDATE tblname " _
& " INNER JOIN qryname " _
& " ON tblname.ID = qryname.ID " _
& " SET tblname.field1 = qryname.[field1]" _
& " , tblname.field2 = qryname.[field2]" _
& " , tblname.field3 = qryname.[field3] " _
& " WHERE tblname.aDate >= #" & Date()-50 & "#;"

'comment this out after debugged
'Debug.Print strSql

'run the SQL statement
CurrentDb.Execute strSql

'refresh the table definitions with records
'added/modified by other persons/processes
CurrentDb.TableDefs.Refresh

'wake-up call for Access
DoEvents

'----------------- keep and customize what is relevant
'requery a subform
Me.subform_controlname.Requery

'requery the form you are behind
Me.FrmSubItinerary2.Requery
'-----------------

~~~~~~~~~~~~~~~~~~~````

** debug.print ***

debug.print strSQL

--> this prints a copy of the SQL statement to the debug window (CTRL-G)

After you execute your code, open the Debug window
CTRL-G to Goto the debuG window -- look at the SQL statement

If the SQL statement has an error

1. Make a new query (design view)

2. choose View, SQL from the menu
(or SQL from the toolbar, first icon)

3. cut the SQL statement from the debug window
(select, CTRL-X)

4. paste into the SQL window of the Query
(CTRL-V)

5. run ! from the SQL window
-- Access will tell you where the problem is in the SQL

'~~~~~~~~~~~~~~

"in qryname the field1-3 are calculated field"

what kind of calculation? If it uses dSum, dCount, or another domain
aggregate function, this won't work...

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hi strive4peace,

"doesn 't show new values"

perhaps no records meet the criteria...

WHERE (((tblname.aDate)>=Date()-50))

I have new values in qryname and I have cancelled some in the table. ;-)
I have tried also in the command button
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdRefresh
CurrentDb.Execute ("UPDATE tblname INNER JOIN qryname ON tblname.ID =
qryname.ID SET tblname.field1 = [field1], tblname.field2 = [field2],
tblname.field3 = [field3] WHERE tblname.aDate >=Date()-50")
DoCmd.SetWarnings True

"it doesn 't work as it would have"

what do you mean by this?

If i switch in access 2003 works well:
1) the update query
2) the command button
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdRefresh
DoCmd.OpenQuery "qryname", acViewNormal, acEdit
DoCmd.SetWarnings True
3) the command button2
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdRefresh
CurrentDb.Execute ("UPDATE tblname INNER JOIN qryname ON tblname.ID =
qryname.ID SET tblname.field1 = [field1], tblname.field2 = [field2],
tblname.field3 = [field3] WHERE tblname.aDate >=Date()-50")
DoCmd.SetWarnings True
4) the update query design view, datasheet view

Is it a bug ?

Note:
beta2tr
in qryname the field1-3 are calculated field

Regards

Warm Regards,
Crystal
*
have an awesome day
*

Also to You

MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

In access 2003 I use a update query for update a table.
This works perfectly:
UPDATE tblname INNER JOIN qryname ON tblname.ID = qryname.ID SET
tblname.field1 = [field1], tblname.field2 = [field2], tblname.field3 =
[field3]
WHERE (((tblname.aDate)>=Date()-50));
In access 2007 doesn 't show new values and it doesn 't work as it would
have. Why?


thx

 

[aaron.kempf]

comical stuff

does it make you do this when you're usign Access Data Projects?

ROFL

Hi Marco,

after you make changes to a table using SQL, you need to refresh the
table definitions so that other processes (ie: your form) sees the changes

currentdb.tabledefs.refresh
DoEvents

*** DoEvents ***

DoEvents is used to make VBA pay attention to what is currently
happening and look to see if the OS (Operating System) has any requests.

ie: if you have a loop and want to be able to BREAK it with CTRL-BREAK,
put DoEvents into the loop

DoEvents will also update values written to a form by a general
procedure or code behind another form or report

A DoEvents is done when you use MsgBox, or are stepping through code
(since it has to pay attention to the keyboard)

It is a good way to say, "Wake Up!"

***
before you update your table using SQL, you should save changes to the
form...

if me.dirty then me.dirty = false

***

since you are using CurrentDb.Execute,
you don't need
DoCmd.SetWarnings False

***

it is also a good idea to assign the SQL to a string so that you can
examine it -- you also need # delimiters around the date to compare

you also need to qualify field1 with qryname.field1 so Access knows
where it comes from (field1 is in both tables and Access needs to know
which to get it from)

~~~~~~~~~~~~~~~~~
Dim strSql As String

'create SQL to update records
strSql = ""UPDATE tblname " _
& " INNER JOIN qryname " _
& " ON tblname.ID = qryname.ID " _
& " SET tblname.field1 = qryname.[field1]" _
& " , tblname.field2 = qryname.[field2]" _
& " , tblname.field3 = qryname.[field3] " _
& " WHERE tblname.aDate >= #" & Date()-50 & "#;"

'comment this out after debugged
'Debug.Print strSql

'run the SQL statement
CurrentDb.Execute strSql

'refresh the table definitions with records
'added/modified by other persons/processes
CurrentDb.TableDefs.Refresh

'wake-up call for Access
DoEvents

'----------------- keep and customize what is relevant
'requery a subform
Me.subform_controlname.Requery

'requery the form you are behind
Me.FrmSubItinerary2.Requery
'-----------------

~~~~~~~~~~~~~~~~~~~````

** debug.print ***

debug.print strSQL

--> this prints a copy of the SQL statement to the debug window (CTRL-G)

After you execute your code, open the Debug window
CTRL-G to Goto the debuG window -- look at the SQL statement

If the SQL statement has an error

1. Make a new query (design view)

2. choose View, SQL from the menu
(or SQL from the toolbar, first icon)

3. cut the SQL statement from the debug window
(select, CTRL-X)

4. paste into the SQL window of the Query
(CTRL-V)

5. run ! from the SQL window
-- Access will tell you where the problem is in the SQL

'~~~~~~~~~~~~~~

"in qryname the field1-3 are calculated field"

what kind of calculation? If it uses dSum, dCount, or another domain
aggregate function, this won't work...

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hi strive4peace,

"doesn 't show new values"

perhaps no records meet the criteria...

WHERE (((tblname.aDate)>=Date()-50))

I have new values in qryname and I have cancelled some in the table. ;-)
I have tried also in the command button
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdRefresh
CurrentDb.Execute ("UPDATE tblname INNER JOIN qryname ON tblname.ID =
qryname.ID SET tblname.field1 = [field1], tblname.field2 = [field2],
tblname.field3 = [field3] WHERE tblname.aDate >=Date()-50")
DoCmd.SetWarnings True

"it doesn 't work as it would have"

what do you mean by this?

If i switch in access 2003 works well:
1) the update query
2) the command button
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdRefresh
DoCmd.OpenQuery "qryname", acViewNormal, acEdit
DoCmd.SetWarnings True
3) the command button2
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdRefresh
CurrentDb.Execute ("UPDATE tblname INNER JOIN qryname ON tblname.ID =
qryname.ID SET tblname.field1 = [field1], tblname.field2 = [field2],
tblname.field3 = [field3] WHERE tblname.aDate >=Date()-50")
DoCmd.SetWarnings True
4) the update query design view, datasheet view

Is it a bug ?

Note:
beta2tr
in qryname the field1-3 are calculated field

Regards

Warm Regards,
Crystal
*
have an awesome day
*

Also to You

MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Marco wrote:
In access 2003 I use a update query for update a table.
This works perfectly:
UPDATE tblname INNER JOIN qryname ON tblname.ID = qryname.ID SET
tblname.field1 = [field1], tblname.field2 = [field2], tblname.field3 =
[field3]
WHERE (((tblname.aDate)>=Date()-50));
In access 2007 doesn 't show new values and it doesn 't work as it would
have. Why?


thx

 

[Marco]

strive4peace wrote

after you make changes to a table using SQL, you need to refresh the table
definitions so that other processes (ie: your form) sees the changes

currentdb.tabledefs.refresh
DoEvents

*** DoEvents ***

DoEvents is used to make VBA pay attention to what is currently happening
and look to see if the OS (Operating System) has any requests.

ie: if you have a loop and want to be able to BREAK it with CTRL-BREAK,
put DoEvents into the loop

DoEvents will also update values written to a form by a general procedure
or code behind another form or report

A DoEvents is done when you use MsgBox, or are stepping through code
(since it has to pay attention to the keyboard)

It is a good way to say, "Wake Up!"

***
before you update your table using SQL, you should save changes to the
form...

if me.dirty then me.dirty = false

***

since you are using CurrentDb.Execute,
you don't need
DoCmd.SetWarnings False

***

it is also a good idea to assign the SQL to a string so that you can
examine it -- you also need # delimiters around the date to compare

you also need to qualify field1 with qryname.field1 so Access knows where
it comes from (field1 is in both tables and Access needs to know which to
get it from)

~~~~~~~~~~~~~~~~~
Dim strSql As String

'create SQL to update records
strSql = ""UPDATE tblname " _
& " INNER JOIN qryname " _
& " ON tblname.ID = qryname.ID " _
& " SET tblname.field1 = qryname.[field1]" _
& " , tblname.field2 = qryname.[field2]" _
& " , tblname.field3 = qryname.[field3] " _
& " WHERE tblname.aDate >= #" & Date()-50 & "#;"

'comment this out after debugged
'Debug.Print strSql

'run the SQL statement
CurrentDb.Execute strSql

'refresh the table definitions with records
'added/modified by other persons/processes
CurrentDb.TableDefs.Refresh

'wake-up call for Access
DoEvents

'----------------- keep and customize what is relevant
'requery a subform
Me.subform_controlname.Requery

'requery the form you are behind
Me.FrmSubItinerary2.Requery
'-----------------

~~~~~~~~~~~~~~~~~~~````

** debug.print ***

debug.print strSQL

--> this prints a copy of the SQL statement to the debug window (CTRL-G)

After you execute your code, open the Debug window
CTRL-G to Goto the debuG window -- look at the SQL statement

If the SQL statement has an error

1. Make a new query (design view)

2. choose View, SQL from the menu
(or SQL from the toolbar, first icon)

3. cut the SQL statement from the debug window
(select, CTRL-X)

4. paste into the SQL window of the Query
(CTRL-V)

5. run ! from the SQL window
-- Access will tell you where the problem is in the SQL

'~~~~~~~~~~~~~~

Thanks for the magnificent explanation, but also the sql query it does not
work in access 2007 and I do not have no type of error.
I have created a sample new db in access 2007 and converted in access 2003.
The .mdb works perfectly under access 2003, but no under access 2007 and
..accdb no works too.
No works = the field table are always without new value.

"in qryname the field1-3 are calculated field"

what kind of calculation? If it uses dSum, dCount, or another domain
aggregate function, this won't work...

I have dsum too in mine query "qryname" but even if I remove it does not
work the same in access 2007.

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

regards

 

[strive4peace]

Hi Marco,

"No works = the field table are always without new value"

are you requering the information on the form? If the form SourceObject
is a query, sometimes you have to remove it and put it back again to get
the query to display values changed by running SQL statements


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

strive4peace wrote

after you make changes to a table using SQL, you need to refresh the table
definitions so that other processes (ie: your form) sees the changes

currentdb.tabledefs.refresh
DoEvents

*** DoEvents ***

DoEvents is used to make VBA pay attention to what is currently happening
and look to see if the OS (Operating System) has any requests.

ie: if you have a loop and want to be able to BREAK it with CTRL-BREAK,
put DoEvents into the loop

DoEvents will also update values written to a form by a general procedure
or code behind another form or report

A DoEvents is done when you use MsgBox, or are stepping through code
(since it has to pay attention to the keyboard)

It is a good way to say, "Wake Up!"

***
before you update your table using SQL, you should save changes to the
form...

if me.dirty then me.dirty = false

***

since you are using CurrentDb.Execute,
you don't need
DoCmd.SetWarnings False

***

it is also a good idea to assign the SQL to a string so that you can
examine it -- you also need # delimiters around the date to compare

you also need to qualify field1 with qryname.field1 so Access knows where
it comes from (field1 is in both tables and Access needs to know which to
get it from)

~~~~~~~~~~~~~~~~~
Dim strSql As String

'create SQL to update records
strSql = ""UPDATE tblname " _
& " INNER JOIN qryname " _
& " ON tblname.ID = qryname.ID " _
& " SET tblname.field1 = qryname.[field1]" _
& " , tblname.field2 = qryname.[field2]" _
& " , tblname.field3 = qryname.[field3] " _
& " WHERE tblname.aDate >= #" & Date()-50 & "#;"

'comment this out after debugged
'Debug.Print strSql

'run the SQL statement
CurrentDb.Execute strSql

'refresh the table definitions with records
'added/modified by other persons/processes
CurrentDb.TableDefs.Refresh

'wake-up call for Access
DoEvents

'----------------- keep and customize what is relevant
'requery a subform
Me.subform_controlname.Requery

'requery the form you are behind
Me.FrmSubItinerary2.Requery
'-----------------

~~~~~~~~~~~~~~~~~~~````

** debug.print ***

debug.print strSQL

--> this prints a copy of the SQL statement to the debug window (CTRL-G)

After you execute your code, open the Debug window
CTRL-G to Goto the debuG window -- look at the SQL statement

If the SQL statement has an error

1. Make a new query (design view)

2. choose View, SQL from the menu
(or SQL from the toolbar, first icon)

3. cut the SQL statement from the debug window
(select, CTRL-X)

4. paste into the SQL window of the Query
(CTRL-V)

5. run ! from the SQL window
-- Access will tell you where the problem is in the SQL

'~~~~~~~~~~~~~~

Thanks for the magnificent explanation, but also the sql query it does not
work in access 2007 and I do not have no type of error.
I have created a sample new db in access 2007 and converted in access 2003.
The .mdb works perfectly under access 2003, but no under access 2007 and
.accdb no works too.
No works = the field table are always without new value.

"in qryname the field1-3 are calculated field"

what kind of calculation? If it uses dSum, dCount, or another domain
aggregate function, this won't work...

I have dsum too in mine query "qryname" but even if I remove it does not
work the same in access 2007.

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

regards

 

[Marco]

Hi Crystal,

are you requering the information on the form? Yes

If the form SourceObject
is a query, sometimes you have to remove it and put it back again to get
the query to display values changed by running SQL statements

my source is a table ( like in sample)

what I am trying to explain (and undestand) is that all the code, query,
written works perfectly with office 2003, while not there is compatibility
with office 2007 without to receive any type of error.
The update query don't update the table: it is absurd.

1) it's office 2007 bug.
2) it's my setting problem.

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

regards

 

[strive4peace]

Hi Marco,

can you post the SQL you are using for your update query? Have you
checked the data types on your linked fields to make sure they are the
same in 2007? Perhaps something changed in the structure.


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Marco]

strive4peace wrote

can you post the SQL you are using for your update query? Have you
checked the data types on your linked fields to make sure they are the
same in 2007? Perhaps something changed in the structure.

yes , i have created too a litte new db with acc2007 and converted in
acc2003 format.
With office 2003 no problem, the table is updated.UPDATE tblname INNER JOIN qryname ON tblname.ID = qryname.ID SET
tblname.name1 = [qryname].[name1], tblname.name2 = [qryname ].[name2],
tblname.name3 = [qryname].[name3], tblname.name4 = [qryname].[name4],
tblname.name5 = [qryname].[name5]
WHERE (((tblname .adate)>=Date()-20));

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Regards

 

[strive4peace]

Hi Marco,

What if something isn't filled out?

also, as I said in an earlier post, you need to delimit the date you are
comparing with # -- or use DateValue to convert it so it can be compared

Are you running this as a query or putting the SQL in code?


try this:
UPDATE tblname
INNER JOIN qryname
ON tblname.ID = qryname.ID
SET tblname.name1 = [qryname].[name1]
, tblname.name2 = nz([qryname ].[name2],"")
, tblname.name3 = nz([qryname].[name3], "")
, tblname.name4 = nz([qryname].[name4], "")
, tblname.name5 = nz([qryname].[name5] , "")
WHERE (tblname .adate >= DateValue (Date()-20) );

Do you have AllowZeroLength set to Yes in the table design for your text
fields in Tblname?

If you do a SELECT statement and just show data from both table/queries,
does the tblname.ID = qryname.ID link work?

What is the SQL for your query?


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

strive4peace wrote

can you post the SQL you are using for your update query? Have you
checked the data types on your linked fields to make sure they are the
same in 2007? Perhaps something changed in the structure.

yes , i have created too a litte new db with acc2007 and converted in
acc2003 format.
With office 2003 no problem, the table is updated.UPDATE tblname INNER JOIN qryname ON tblname.ID = qryname.ID SET
tblname.name1 = [qryname].[name1], tblname.name2 = [qryname ].[name2],
tblname.name3 = [qryname].[name3], tblname.name4 = [qryname].[name4],
tblname.name5 = [qryname].[name5]
WHERE (((tblname .adate)>=Date()-20));

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Regards

 

[Marco]

strive4peace wrote

What if something isn't filled out?

also, as I said in an earlier post, you need to delimit the date you are
comparing with # -- or use DateValue to convert it so it can be compared

Are you running this as a query or putting the SQL in code?


try this:
UPDATE tblname
INNER JOIN qryname
ON tblname.ID = qryname.ID
SET tblname.name1 = [qryname].[name1]
, tblname.name2 = nz([qryname ].[name2],"")
, tblname.name3 = nz([qryname].[name3], "")
, tblname.name4 = nz([qryname].[name4], "")
, tblname.name5 = nz([qryname].[name5] , "")
WHERE (tblname .adate >= DateValue (Date()-20) );

nothing is changed

Do you have AllowZeroLength set to Yes in the table design for your text
fields in Tblname?

In sample i haven't text fields

If you do a SELECT statement and just show data from both table/queries,
does the tblname.ID = qryname.ID link work?

SELECT tblname.ID, tblname.Activity, tblname.aDate, tblname.Field1,
tblname.Time1, tblname.Zone1, tblname.Zone2, tblname.Zone3, tblname.Zone4,
tblname.Zone5, tblname.MiField1, tblname.MeField1h, tblname.Field1St,
tblname.Field1Ass, tblname.IndSf, qryname.ID, qryname.Activity,
qryname.aDate, qryname.Field1, qryname.Time1, qryname.Zone1, qryname.Zone2,
qryname.Zone3, qryname.Zone4, qryname.Zone5, qryname.MiField1,
qryname.MeField1h, qryname.Field1St, qryname.Field1Ass, qryname.IndSf

FROM tblname INNER JOIN qryname ON tblname.ID = qryname.ID;

Is it this? yes i have the 3 record (date-20) with tblname.ID = qryname.ID

What is the SQL for your query?

SELECT tblname.ID, tblname.Activity, tblname.aDate, tblname.Field1,
tblname.Time1, tblname.Zone1, tblname.Zone2, tblname.Zone3, tblname.Zone4,
tblname.Zone5, ([Time1]/[Field1]) AS MiField1, [Field1]/([Time1]*24) AS
MeField1h, DSum("[Field1]","tblname","[ID] <=" & Nz([ID],0) & "And
[Activity]=6" & "And [aDate]>= #01/01/06#") AS Field1St,
DSum("[Field1]","tblname","[ID]<=" & Nz([ID],0) & "And [Activity]=6") AS
Field1Ass,
((Nz([Zone1],0)*1440)+(Nz([Zone2],0)*1440*1.5)+(Nz([Zone3],0)*1440*3)+(Nz([Zone4],0)*1440*6)+(Nz([Zone5],0)*1440*10))
AS IndSf

FROM tblname

WHERE (((tblname.Activity)=6) AND ((tblname.aDate)>=#1/1/2006#));

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

regards

 

[strive4peace]

Hi Marco,

Can you email me the database so I can have a look? Please put your
name in the subject line so I know it belongs to this thread -- I will
comment back to the thread.

Specify the name of the update query that is not working.

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

strive4peace wrote

What if something isn't filled out?

also, as I said in an earlier post, you need to delimit the date you
are comparing with # -- or use DateValue to convert it so it can be
compared

Are you running this as a query or putting the SQL in code?


try this:
UPDATE tblname
INNER JOIN qryname
ON tblname.ID = qryname.ID
SET tblname.name1 = [qryname].[name1]
, tblname.name2 = nz([qryname ].[name2],"")
, tblname.name3 = nz([qryname].[name3], "")
, tblname.name4 = nz([qryname].[name4], "")
, tblname.name5 = nz([qryname].[name5] , "")
WHERE (tblname .adate >= DateValue (Date()-20) );

nothing is changed

Do you have AllowZeroLength set to Yes in the table design for your
text fields in Tblname?

In sample i haven't text fields

If you do a SELECT statement and just show data from both
table/queries, does the tblname.ID = qryname.ID link work?

SELECT tblname.ID, tblname.Activity, tblname.aDate, tblname.Field1,
tblname.Time1, tblname.Zone1, tblname.Zone2, tblname.Zone3,
tblname.Zone4, tblname.Zone5, tblname.MiField1, tblname.MeField1h,
tblname.Field1St, tblname.Field1Ass, tblname.IndSf, qryname.ID,
qryname.Activity, qryname.aDate, qryname.Field1, qryname.Time1,
qryname.Zone1, qryname.Zone2, qryname.Zone3, qryname.Zone4,
qryname.Zone5, qryname.MiField1, qryname.MeField1h, qryname.Field1St,
qryname.Field1Ass, qryname.IndSf

FROM tblname INNER JOIN qryname ON tblname.ID = qryname.ID;

Is it this? yes i have the 3 record (date-20) with tblname.ID = qryname.ID

What is the SQL for your query?

SELECT tblname.ID, tblname.Activity, tblname.aDate, tblname.Field1,
tblname.Time1, tblname.Zone1, tblname.Zone2, tblname.Zone3,
tblname.Zone4, tblname.Zone5, ([Time1]/[Field1]) AS MiField1,
[Field1]/([Time1]*24) AS MeField1h, DSum("[Field1]","tblname","[ID] <="
& Nz([ID],0) & "And [Activity]=6" & "And [aDate]>= #01/01/06#") AS
Field1St, DSum("[Field1]","tblname","[ID]<=" & Nz([ID],0) & "And
[Activity]=6") AS Field1Ass,
((Nz([Zone1],0)*1440)+(Nz([Zone2],0)*1440*1.5)+(Nz([Zone3],0)*1440*3)+(Nz([Zone4],0)*1440*6)+(Nz([Zone5],0)*1440*10))
AS IndSf

FROM tblname

WHERE (((tblname.Activity)=6) AND ((tblname.aDate)>=#1/1/2006#));

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

regards

 

[strive4peace]

Hi Marco,

Got your database and took a look...

here is the SQL for the update query you are running successfully in one
environment but not in others:

Name: qupdRunning

UPDATE tblSports
INNER JOIN qryRunning
ON tblSports.IDSports = qryRunning.IDSports
SET tblSports.MinutiKm = [qryRunning].[MinutiKm]
, tblSports.MediaKmh = [qryRunning].[MediaKmh]
, tblSports.KmStagione = [qryRunning].[KmStagione]
, tblSports.KmAssoluti = [qryRunning].[KmAssoluti]
, tblSports.IndiceSforzo = [qryRunning].[IndiceSforzo]
WHERE (((tblSports.Giorno)>=Date()-20));

here is the SQL for the query it references:

'~~~~~~~~~~~~~~~~~~~~~~
Name: qryRunning

SELECT tblSports.IDSports
, tblSports.IDAttività
, tblSports.Giorno
, tblSports.Km
, tblSports.Tempo
, tblSports.Zona1
, tblSports.Zona2
, tblSports.Zona3
, tblSports.Zona4
, tblSports.Zona5
, ([Tempo]/[Km]) AS MinutiKm
, [Km]/([Tempo]*24) AS MediaKmh
, DSum(
"[Km]"
,"tblSports"
,"[IDSports] <=" & Nz([IDSports],0) & "And [IDAttività]=6"
& "And [Giorno]>= #01/01/06#"
) AS KmStagione
, DSum(
"[Km]"
,"tblSports"
,"[IDSports]<=" & Nz([IDSports],0) & "And [IDAttività]=6"
) AS KmAssoluti
, (
(Nz(
[Zona1]
,0
)*1440)
+ (Nz(
[Zona2]
,0
)*1440*1.5)
+ (Nz(
[Zona3]
,0
)*1440*3)
+ (Nz(
[Zona4]
,0
)*1440*6)
+ (Nz(
[Zona5]
,0
)*1440*10)
) AS IndiceSforzo
FROM tblSports
WHERE (((tblSports.IDAttività)=6)
AND ((tblSports.Giorno)>=#1/1/2006#));
'~~~~~~~~~~~~~~~~~~~~~~

both of these queries are based on the same table. The SQL for
qupdRunning can be rewritten to eliminate qryRunning

'~~~~~~~~~~~~~~~~~~~~~~
UPDATE tblSports
SET
tblSports.MinutiKm = ([Tempo]/[Km])

, tblSports.MediaKmh = [Km]/([Tempo]*24)
, tblSports.KmStagione = nz(DSum("[Km]"
,"tblSports"
,"[IDSports] <=" & Nz([IDSports],0) & "And [IDAttività]=6" & "And
[Giorno]>= #01/01/06#"),0)

, tblSports.KmAssoluti = nz(DSum("[Km]"
,"tblSports"
,"[IDSports]<=" & Nz([IDSports],0) & "And [IDAttività]=6"),0)

, tblSports.IndiceSforzo =
((Nz([Zona1],0)*1440)
+ (Nz([Zona2],0)*1440*1.5)
+ (Nz([Zona3],0)*1440*3)
+ (Nz([Zona4],0)*1440*6)
+ (Nz([Zona5],0)*1440*10))

, tblSports.DateModified = Now()

WHERE (((tblSports.IDAttività)=6)
AND ((tblSports.Giorno)>=Date()-200));
'~~~~~~~~~~~~~~~~~~~~~~

Notes:

this simplifies things by removing an unnecessary layer

changed the date criteria to be Date()-200 so it would find some records...

added DateModified to your sports table so you can see what was changed
by the query...

wrapped your dSum results in NZ (always a good idea to use NZ with the
domain aggregate functions)

let me know if this takes care of the problem... if not, we will
explore other options

'~~~~~~~~~~~~~~~~~~~~~~
Design Notes:

I would like to caution you against storing calculations in your table.
Each of these values that are being updated can be calculated at any
time on a form or report or for ranking -- there is really no need to
store this information

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hi Marco,

Can you email me the database so I can have a look? Please put your
name in the subject line so I know it belongs to this thread -- I will
comment back to the thread.

Specify the name of the update query that is not working.

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

strive4peace wrote

What if something isn't filled out?

also, as I said in an earlier post, you need to delimit the date you
are comparing with # -- or use DateValue to convert it so it can be
compared

Are you running this as a query or putting the SQL in code?


try this:
UPDATE tblname
INNER JOIN qryname
ON tblname.ID = qryname.ID
SET tblname.name1 = [qryname].[name1]
, tblname.name2 = nz([qryname ].[name2],"")
, tblname.name3 = nz([qryname].[name3], "")
, tblname.name4 = nz([qryname].[name4], "")
, tblname.name5 = nz([qryname].[name5] , "")
WHERE (tblname .adate >= DateValue (Date()-20) );

nothing is changed

Do you have AllowZeroLength set to Yes in the table design for your
text fields in Tblname?

In sample i haven't text fields

If you do a SELECT statement and just show data from both
table/queries, does the tblname.ID = qryname.ID link work?

SELECT tblname.ID, tblname.Activity, tblname.aDate, tblname.Field1,
tblname.Time1, tblname.Zone1, tblname.Zone2, tblname.Zone3,
tblname.Zone4, tblname.Zone5, tblname.MiField1, tblname.MeField1h,
tblname.Field1St, tblname.Field1Ass, tblname.IndSf, qryname.ID,
qryname.Activity, qryname.aDate, qryname.Field1, qryname.Time1,
qryname.Zone1, qryname.Zone2, qryname.Zone3, qryname.Zone4,
qryname.Zone5, qryname.MiField1, qryname.MeField1h, qryname.Field1St,
qryname.Field1Ass, qryname.IndSf

FROM tblname INNER JOIN qryname ON tblname.ID = qryname.ID;

Is it this? yes i have the 3 record (date-20) with tblname.ID =
qryname.ID

What is the SQL for your query?

SELECT tblname.ID, tblname.Activity, tblname.aDate, tblname.Field1,
tblname.Time1, tblname.Zone1, tblname.Zone2, tblname.Zone3,
tblname.Zone4, tblname.Zone5, ([Time1]/[Field1]) AS MiField1,
[Field1]/([Time1]*24) AS MeField1h, DSum("[Field1]","tblname","[ID]
<=" & Nz([ID],0) & "And [Activity]=6" & "And [aDate]>= #01/01/06#") AS
Field1St, DSum("[Field1]","tblname","[ID]<=" & Nz([ID],0) & "And
[Activity]=6") AS Field1Ass,
((Nz([Zone1],0)*1440)+(Nz([Zone2],0)*1440*1.5)+(Nz([Zone3],0)*1440*3)+(Nz([Zone4],0)*1440*6)+(Nz([Zone5],0)*1440*10))
AS IndSf

FROM tblname

WHERE (((tblname.Activity)=6) AND ((tblname.aDate)>=#1/1/2006#));

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

regards

 

[Marco]

Hi Crystal,

Got your database and took a look...

The SQL for qupdRunning can be rewritten to eliminate qryRunning

'~~~~~~~~~~~~~~~~~~~~~~
UPDATE tblSports
SET
tblSports.MinutiKm = ([Tempo]/[Km])

, tblSports.MediaKmh = [Km]/([Tempo]*24)
, tblSports.KmStagione = nz(DSum("[Km]"
,"tblSports"
,"[IDSports] <=" & Nz([IDSports],0) & "And [IDAttività]=6" & "And
[Giorno]>= #01/01/06#"),0)

, tblSports.KmAssoluti = nz(DSum("[Km]"
,"tblSports"
,"[IDSports]<=" & Nz([IDSports],0) & "And [IDAttività]=6"),0)

, tblSports.IndiceSforzo =
((Nz([Zona1],0)*1440)
+ (Nz([Zona2],0)*1440*1.5)
+ (Nz([Zona3],0)*1440*3)
+ (Nz([Zona4],0)*1440*6)
+ (Nz([Zona5],0)*1440*10))

, tblSports.DateModified = Now()

WHERE (((tblSports.IDAttività)=6)
AND ((tblSports.Giorno)>=Date()-200));
'~~~~~~~~~~~~~~~~~~~~~~

Notes:

this simplifies things by removing an unnecessary layer

changed the date criteria to be Date()-200 so it would find some
records...

added DateModified to your sports table so you can see what was changed by
the query...

This is a good idea.

wrapped your dSum results in NZ (always a good idea to use NZ with the
domain aggregate functions)

let me know if this takes care of the problem... if not, we will explore
other options

Yes, it works under access 2007 (beta)

'~~~~~~~~~~~~~~~~~~~~~~
Design Notes:

I would like to caution you against storing calculations in your table.
Each of these values that are being updated can be calculated at any time
on a form or report or for ranking -- there is really no need to store
this information

I knew , but for a fast comparison between the current performance and the
old one, I have decided to save some data calculates.
I could send you the full database for an analysis and I am sure that you
will know give me some useful design advice.

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Best Regards

 

[strive4peace]

you're welcome, Marco happy to help

if you want feedback on your table design, make a post in the table
design forum, I will look out for you if you do it soon

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hi Crystal,

Got your database and took a look...

The SQL for qupdRunning can be rewritten to eliminate qryRunning

'~~~~~~~~~~~~~~~~~~~~~~
UPDATE tblSports
SET
tblSports.MinutiKm = ([Tempo]/[Km])

, tblSports.MediaKmh = [Km]/([Tempo]*24)
, tblSports.KmStagione = nz(DSum("[Km]"
,"tblSports"
,"[IDSports] <=" & Nz([IDSports],0) & "And [IDAttività]=6" & "And
[Giorno]>= #01/01/06#"),0)

, tblSports.KmAssoluti = nz(DSum("[Km]"
,"tblSports"
,"[IDSports]<=" & Nz([IDSports],0) & "And [IDAttività]=6"),0)

, tblSports.IndiceSforzo =
((Nz([Zona1],0)*1440)
+ (Nz([Zona2],0)*1440*1.5)
+ (Nz([Zona3],0)*1440*3)
+ (Nz([Zona4],0)*1440*6)
+ (Nz([Zona5],0)*1440*10))

, tblSports.DateModified = Now()

WHERE (((tblSports.IDAttività)=6)
AND ((tblSports.Giorno)>=Date()-200));
'~~~~~~~~~~~~~~~~~~~~~~

Notes:

this simplifies things by removing an unnecessary layer

changed the date criteria to be Date()-200 so it would find some
records...

added DateModified to your sports table so you can see what was
changed by the query...

This is a good idea.

wrapped your dSum results in NZ (always a good idea to use NZ with the
domain aggregate functions)

let me know if this takes care of the problem... if not, we will
explore other options

Yes, it works under access 2007 (beta)

'~~~~~~~~~~~~~~~~~~~~~~
Design Notes:

I would like to caution you against storing calculations in your
table. Each of these values that are being updated can be calculated
at any time on a form or report or for ranking -- there is really no
need to store this information

I knew , but for a fast comparison between the current performance and
the old one, I have decided to save some data calculates.
I could send you the full database for an analysis and I am sure that
you will know give me some useful design advice.

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Best Regards