Problem in Access Update Query

[kashif.special2005]

Hi,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
I have a update query named “Update_Table_2” and I assigned this query with a button click, when I click first time on butting it tell me an error message that can’t update due to data type conversion or etc… now when I click second time on that button that update query “Update_Table_2” run fine.
<o> </o>
I am not able to understand what is the problem.
<o> </o>
Please help.
<o> </o>
Thanks,
Kashif
<o> </o>
<o> </o>

 

[CharlesChuckieCharles]

you should post your code and your query SQL!

 

[kashif.special2005]

Unique_ID = [SR Domain] & [SR Process] & [SR Sub-process]

TCO_FCO_Case = DLookUp("[TCO_FCO_Count]","[Combo]","[Combo].[Unique_ID]='" & Main_Table_CSV_WLM103.Unique_ID & "'")

Business_Days = GetNetWorkDays([SR Created Date],[SR Closed Date])-"1"

Closed_in_5 = IIf(CInt([Business_Days])<=5,1,0)

Closed_in_20 = IIf(CInt([Business_Days])<=20,1,0)

Closed_Date = DateSerial(Year([SR Closed Date]),Month([SR Closed Date]),Day([SR Closed Date]))

Day = IIf([Closed_Date]=Forms!Main_Form.Text14,1,0)

WTD = IIf(CDate([Closed_Date])>=Forms!Main_Form.Text14-6 And CDate([Closed_Date])<=Forms!Main_Form.Text14,1,0)

MTD = IIf(CDate([Closed_Date])>=DLookUp("[Month_Start]","[Period_Select]","[Period_Select].[Day]= [Forms]![Main_Form]![Text14] ") And CDate([Closed_Date])<=Forms!Main_Form!Text14,1,0)

QTD = IIf(CDate([Closed_Date])>=DLookUp("[Quarter_Start]","[Period_Select]","[Period_Select].[Day]=[Forms]![Main_Form]![Text14]") And CDate([Closed_Date])<=Forms!Main_Form!Text14,1,0)

YTD = IIf(CDate([Closed_Date])>=DLookUp("[Year_Start]","[Period_Select]","[Period_Select].[Day]=[Forms]![Main_Form]![Text14] ") And CDate([Closed_Date])<=Forms!Main_Form!Text14,1,0)

Where Unique_Id and Business_Day column updated correctely, other column not updating and give me error message.

Private Sub Cmd1_Click()
Docmd.openquery "Update_Table_2"
End Sub

Kashif.

 

[CharlesChuckieCharles]

where's the SQL of Update_Table_2?

 

[kashif.special2005]

UPDATE Main_Table_CSV_WLM103 SET Main_Table_CSV_WLM103.Unique_ID = [SR Domain] & [SR Process] & [SR Sub-process], Main_Table_CSV_WLM103.TCO_FCO_Case = DLookUp("[TCO_FCO_Count]","[Combo]","[Combo].[Unique_ID]='" & Main_Table_CSV_WLM103.Unique_ID & "'"), Main_Table_CSV_WLM103.Business_Days = GetNetWorkDays([SR Created Date],[SR Closed Date])-"1", Main_Table_CSV_WLM103.Closed_in_5 = IIf([Business_Days]<="5",1,0), Main_Table_CSV_WLM103.Closed_in_20 = IIf([Business_Days]<="20",1,0), Main_Table_CSV_WLM103.Closed_Date = DateSerial(Year([SR Closed Date]),Month([SR Closed Date]),Day([SR Closed Date])), Main_Table_CSV_WLM103.[Day] = IIf([Closed_Date]=Forms!Main_Form.Text14,1,0), Main_Table_CSV_WLM103.WTD = IIf(CDate([Closed_Date])>=Forms!Main_Form.Text14-6 And CDate([Closed_Date])<=Forms!Main_Form.Text14,1,0), Main_Table_CSV_WLM103.MTD = IIf(CDate([Closed_Date])>=DLookUp("[Month_Start]","[Period_Select]","[Period_Select].[Day]= [Forms]![Main_Form]![Text14] ") And CDate([Closed_Date])<=Forms!Main_Form!Text14,1,0), Main_Table_CSV_WLM103.QTD = IIf(CDate([Closed_Date])>=DLookUp("[Quarter_Start]","[Period_Select]","[Period_Select].[Day]=[Forms]![Main_Form]![Text14]") And CDate([Closed_Date])<=Forms!Main_Form!Text14,1,0), Main_Table_CSV_WLM103.YTD = IIf(CDate([Closed_Date])>=DLookUp("[Year_Start]","[Period_Select]","[Period_Select].[Day]=[Forms]![Main_Form]![Text14] ") And CDate([Closed_Date])<=Forms!Main_Form!Text14,1,0);

I don't know why it is not working at one click, when I do click on second time it is working fine. If it would be data type mismatch error then it would also not work on second click.

Please help.

Kashif.

 

[CharlesChuckieCharles]

it quite complex to look at

But should it be an append query rather than an update??

if its supposed to be an update
shouldn't
Main_Table_CSV_WLM103.Unique_ID = [SR Domain] & [SR Process] & [SR Sub-process]

be in a Where clause ??

 

[kashif.special2005]

Hi,

But it working when I manually run this query rather that on button.

Thanks,
Kashif.

 

[CharlesChuckieCharles]

so if its an UPDATE which record are you updating?

 

[CharlesChuckieCharles]

INSERT INTO Table1 ( ID )
SELECT Table1.ID
FROM Table1;

this is the syntax for adding records to a table


UPDATE Table1 SET Table1.[Zone] = "Newzone"
WHERE (((Table1.ID)=1));

This is the update syntax where you specify which record to update

This is all Acess/SQL Standard.