SQL INSERT AND WHERE Problems (1 Viewer)

[Pete64]

Here my statement:

DoCmd.RunSQL "INSERT INTO [timesheet header](flag) VALUES(-1) WHERE [timesheet header].SERVICE_COMPANY_NO = serviceno AND [timesheet header].PERIOD_STARTING = start;

I know the syntax of this querys is probly well off but i have tried so many different ways but can't get it workin please help ; )

I get runtime errors of missing semi colon at end of statement, but I already have one.

basically with this statement I want to append a value in a table called timesheet header and the field I want to append is called flag -its set to Yes/No in table propertys. But I want to use a where clause to narrow this down to one record basically WHERE [timesheet header].SERVICE_COMPANY_NO = serviceno AND [timesheet header].PERIOD_STARTING = start;

serviceno and start are set values in my VBA code.

 

[Jon K]

If serviceno and start are numeric:-

DoCmd.RunSQL "INSERT INTO [timesheet header] (flag) VALUES (-1) WHERE [timesheet header].SERVICE_COMPANY_NO = " & serviceno & " AND [timesheet header].PERIOD_STARTING = " & start & ";"


If serviceno and start are text:-

DoCmd.RunSQL "INSERT INTO [timesheet header] (flag) VALUES (-1) WHERE [timesheet header].SERVICE_COMPANY_NO = '" & serviceno & "' AND [timesheet header].PERIOD_STARTING = '" & start & "';"

 

[Pete64]

thanks

would it matter if I used a date field start is a date would this make any difference to my statement structure.

am still gettin the 3137,"Missing semicolon ( at end of SQL error
I ave no idea.

 

[SilentBreaker]

If the Value type is Date, use this one:

DoCmd.RunSQL = "Select..........WHERE.....DateField =" & Chr(35) & dtmDate & Chr(35)

If the Value type is String, use this one:

DoCmd.RunSQL = "Select..........WHERE.....StringField=" & Chr(34) & strString & Chr(34)

If the Value type is Numeric, use this one:

DoCmd.RunSQL = "Select..........WHERE.....NumericField=" & strString

Cheers

 

[Jon K]

The delimiter for a date is the #:-

... [timesheet header].PERIOD_STARTING = #" & start & "#;"


I have overlooked your SQL statement. Since you are adding NEW records to the table using Insert Into, I don't think you can use the Where Clause. Probably this is why you got the Missing Semicolon error.

A Where clause can apply only to existing records, for example, when you are updating specific records using an Update statement.

 

[Pete64]

OK! this is wot I've got

thanks for all your help but look:

DoCmd.RunSQL "INSERT INTO [Timesheet Header](flag) VALUES (-1) WHERE [Timesheet Header]!SERVICE_COMPANY_NO = " & Chr(34) & serviceno & Chr(34) And [timesheet header]!PERIOD_STARTING = " & Chr(35) & start & Chr(35);"

Run time error 2465
but i get a new error now can't find the field timesheet header refered to in your expression

 

[namliam]

Your qouting is off:

DoCmd.RunSQL "INSERT INTO [Timesheet Header](flag) VALUES (-1) WHERE [Timesheet Header]!SERVICE_COMPANY_NO = " & Chr(34) & serviceno & Chr(34) & " And [timesheet header]!PERIOD_STARTING = " & Chr(35) & start & Chr(35) & ";"

Regards

 

[Pete64]

yeah i thought what your saying might be true but how would I phare an update sql statement for what I what to do am stuck.

 

[Pete64]

As Jon K suggested it wont work

I tried again but it doesn't work so am trying to write an update query to achieve the result.

this is what i have so far:

DoCmd.RunSQL "UPDATE tables![timesheet header].flag SET flag = -1 WHERE [Timesheet Header]!SERVICE_COMPANY_NO = serviceno And [timesheet header]!PERIOD_STARTING = start;"

i get a syntax error in statement, can anyone help
cheers ;-)

 

[namliam]

Do you want to append (insert) or update 1 record?

For update your syntax is about correct but change insert to update.

To insert 1 record its totaly off...

Post a sample db (with the empty table in it, with the form and or coding needed) and tell me EXACTLY what you want to do. And i will probably be able to do it.

Regards

The Mailman

 

[Pete64]

nice one

OK heres an example and I'll tell you what I want

basically on the click of the OK button on the frmflag I want the SQL statement to run to find a record with with values predetermined in the code i've set and I want the flag to be set as a tick in the table. with values being set and used in the WHERE clause of the SQL statement

cheers for the help

 

[WayneRyan]

Pete,

DoCmd.RunSQL "UPDATE [timesheet header] " & _
             "SET flag = -1 " & _
             "WHERE serviceno = " & Forms![Timesheet Header]!SERVICE_COMPANY_NO And " & _
             "      start = #" & Forms![timesheet header]!PERIOD_STARTING & "#"

Wayne

 

[Pete64]

to get the example to work you have to press on both the select visible only and turn flag on button then press OK

just a very quick example

 

[namliam]

This is the modified code for your OK button

Private Sub cmdContinue_Click()

    Dim serviceno As String
    Dim start As Date
    serviceno = "7865"
    start = "02/02/98"
    
    If checks = 1 And checks2 = 1 Or checks2 = 2 Then
     If checks2 = 1 Then
          CurrentDb.Execute "UPDATE [Timesheet Header] SET [Timesheet Header].Flag = True " & _
                            "WHERE [Timesheet Header].SERVICE_COMPANY_NO='" & serviceno & "' " & _
                            "AND [Timesheet Header].PERIOD_STARTING=#" & start & "#;"
     Else
          CurrentDb.Execute "UPDATE [Timesheet Header] SET [Timesheet Header].Flag = True " & _
                            "WHERE [Timesheet Header].SERVICE_COMPANY_NO='" & serviceno & "' " & _
                            "AND [Timesheet Header].PERIOD_STARTING=#" & start & "#;"
     End If
    End If

    Me.Visible = False
    
End Sub

You might have to switch the queries

Regards

The Mailman

 

[Pete64]

U R A LEGEND BETTER THAN PAT

cheers for all your help it works like a dream..
UR THE MAILMAN

; ) until next time

ciao
-----
Pete
-----