asked on

update query

I have two tables: concediu_zile and V03. I want a query to update table V03 (please see the example). Example: For CNP 112 because I have in zile concediu(table: concediu_zile) the valu1-jul-2009 the query must put "1" in the column 1_jul(table V03) for CNP 112 and so on. Please see the example:

example

concediu_zile V03
CNP zileconcediu CNP 1_jul 2_jul 3_jul
112 1-jul-2009 112
112 2-jul-2009 114
114 2-jul-2009 115
114 3-jul-2009
115 2-jul-2009

V03 (after I run the query)
CNP 1_jul 2_jul 3_jul
112 1 1
114 1 1
115 1

Thank you

Guy Hengel [angelIII / a3]

what about this:

UPDATE V03
   SET [1_jul] = DMAX(1, "concediu_zile", "CNP=" & CNP & " AND zileconcediu = '1-jul-2009' " )
     , [2_jul] = DMAX(1, "concediu_zile", "CNP=" & CNP & " AND zileconcediu = '2-jul-2009' " )
     , [3_jul] = DMAX(1, "concediu_zile", "CNP=" & CNP & " AND zileconcediu = '3-jul-2009' " )

Open in new window

Qw M

ASKER

When I run the query I get .... didn't update the fils due to a type conversion failture... 1_jul, 2_jul, 3_jul must be text or numeric filds. The filds were text but now I changed the filds and are number with filds size integer.
Please help me...

Qw M

ASKER

I think it is the CNP because the CNP fild is text.

Qw M

ASKER

no... is it not the CNP fild..... Please help me....

Guy Hengel [angelIII / a3]

ok, let's see:

UPDATE V03
   SET [1_jul] = DMAX(1, "concediu_zile", "CNP=""" & CNP & """ AND zileconcediu = ""1-jul-2009"" " )
     , [2_jul] = DMAX(1, "concediu_zile", "CNP=""" & CNP & """ AND zileconcediu = ""2-jul-2009"" " )
     , [3_jul] = DMAX(1, "concediu_zile", "CNP=""" & CNP & """ AND zileconcediu = ""3-jul-2009"" " )

Open in new window

Qw M

ASKER

I get the same error .....didn't update the fils due to a type conversion failture... 1_jul, 2_jul, 3_jul must be text or numeric filds? I changed form number to text and I get the same mesage........ didn't update the fils due to a type conversion failture... And the CNP must be a fild text....

Please hepl me.
Thank you

Guy Hengel [angelIII / a3]

what data type is zileconcediu ?

Qw M

ASKER

date/time with format medium date

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Qw M

ASKER

It's working. Thank you so much!