error in updatequery (1 Viewer)

M

mohamedmatter

Registered User.

Local time

Today, 02:32

Joined

Oct 25, 2015

Messages

112

• Nov 4, 2018

• #1

i create update query to update fieldname DateOfTraining to 1/1/1999 but display error in query Group_A 30/12/1899 where error
i attach simple database

 

Attachments

• Teacher Training.accdb

  960 KB · Views: 43

M

mohamedmatter

Registered User.

Local time

Today, 02:32

Joined

Oct 25, 2015

Messages

112

• Nov 4, 2018

• #2

JHB said:

You can update a table but NOT a query.

Click to expand...

error display in table also i attach image from table

 

Attachments

• 
  2018-11-04_08h21_40.png

  43.7 KB · Views: 33

arnelgp

..forever waiting... waiting for jellybean!

Local time

Today, 17:32

Joined

May 7, 2009

Messages

19,234

• Nov 4, 2018

• #3

replace your Update To field with:

IIf(Nz([DateOfTraining],0)<#1/1/1999#,#1/1/1999#,[DateOfTraining])

 

Gasman

Enthusiastic Amateur

Local time

Today, 10:32

Joined

Sep 21, 2011

Messages

14,267

• Nov 4, 2018

• #4

You update tables, not queries?

 

J

JHB

Have been here a while

Local time

Today, 11:32

Joined

Jun 17, 2012

Messages

7,732

• Nov 4, 2018

• #5

The same was my immediate response, but you can actually update a query, which then writes the values in the table. Therefore I deleted my response, but apparently not fast enough because OP had seen it.

 

J

JHB

Have been here a while

Local time

Today, 11:32

Joined

Jun 17, 2012

Messages

7,732

• Nov 4, 2018

• #6

The problem is that there are some minutes value in the "DateOfTraining" field.
Therefore the update query doesn't update because it has the criteria "IIf([DateOfTraining] Is Null .." and it isn't Null because of the minutes value.

 

Gasman

Enthusiastic Amateur

Local time

Today, 10:32

Joined

Sep 21, 2011

Messages

14,267

• Nov 4, 2018

• #7

That might be as the o/p was placing 1/1999 in the field initially?

JHB said:

The problem is that there are some minutes value in the "DateOfTraining" field.
Therefore the update query doesn't update because it has the criteria "IIf([DateOfTraining] Is Null .." and it isn't Null because of the minutes value.

Click to expand...

 

M

mohamedmatter

Registered User.

Local time

Today, 02:32

Joined

Oct 25, 2015

Messages

112

• Nov 4, 2018

• #8

arnelgp said:

replace your Update To field with:

IIf(Nz([DateOfTraining],0)<#1/1/1999#,#1/1/1999#,[DateOfTraining])

Click to expand...

nots
i set date less then or more then to anther grouptraining