updateqry

[allj]

I have what is a complicated update qry I haven't been able to make work. I have a form that is filtered by the following qry. Me.Filter = "[dateofsession] Between forms![billingtransmitted]![beginningdate]And forms![billingtransmitted]![EndingDate] and [selected] = true"
FilterOn = True
Selected are claims between certain dates to be sent to various insurance companies..
Ok now I want to update all the selected records by changing the field billingselected to true. I tried this but it doesn't work

CurrentDb.Execute "UPDATE insurance INNER JOIN (Clients INNER JOIN Billing ON Clients.NameID = Billing.LookupClientName) ON insurance.insurance = Clients.Insurance SET Billing.BillingSelected = True WHERE (Billing.DateOfSession Between " & Me![BeginningDate] & " And " & Me![EndingDate] & " ) AND ([insurance].[selected]=True);"

Any suggestions or easier solutions are much appreciated. Alan

[FishVal]

Hi, allj.

Two points.

• date constant in SQL expression has to be enclosed in # marks
  e.g.
  

  WHERE (Billing.DateOfSession Between #" & Me![BeginningDate] & "# And #" & Me![EndingDate] & "#)"
   

• date must be in m/d/y format; if your form control has other format, then it's content has to be formatted properly before passing to SQL statement
  e.g.
  

  WHERE (Billing.DateOfSession Between #" & Format(Me![BeginningDate], "mm/dd/yyyy") & "# And #" & Format(Me![EndingDate], "mm/dd/yyyy") & "#)"
   

Regards,
Fish.

[allj]

Thank you.
I continued to have syntax problems but solved the issue with the following code:

Dim ck As checkbox
Set ck = Me!BillingSelected

If ck = "0" Then
CurrentDb.Execute "UPDATE billing SET [billingselected] = true WHERE [dateofsession] like( #" & Me![DateOfSession] & "#);"
Me.Recalc
CurrentDb.Execute "UPDATE billing SET [billingselected] = true WHERE [billingselected]=true"
Me.Recalc
Else
CurrentDb.Execute "UPDATE billing SET [billingselected] = false WHERE [billingselected]=true"
Me.Recalc
End If

Thanks again