Syntax Error Query - ('Single' "Double" Quotes)

[Ihussein]

Hello,
The following code is showing syntax error, as understand this should be because of the single double quotes, any support please.
Thanks

strDeleteRegData = "DELETE tblDistReg.*, RegisterID FROM tblDistReg " _
& "WHERE (((tblDistReg.RegisterID)=DLookUp('PlanID','tblDistPlan','LocationID_FK=' & Forms!frmDistRegisterHomeAdd!cboLocationName" _
& "' And MonthOfDist =' & Forms!frmDistRegisterHomeAdd!cboMonthOfDist)));"

 

[arnelgp]

strDeleteRegData = "DELETE tblDistReg.*, RegisterID FROM tblDistReg " _
& "WHERE tblDistReg.RegisterID=" & DLookUp("PlanID","[tblDis tPlan]","LocationID_FK=" & Forms!frmDistRegisterHomeAdd!cboLocationName) _
& " And MonthOfDist =" & Forms!frmDistRegisterHomeAdd!cboMonthOfDist;"

 

[Ihussein]

Thanks for the prompt replay, I'm using VBA, it will not accept the double quotes, I have already defined strDeleteRegData as string variable.
Best Regards

 

[arnelgp]

what are the datatype of these controls:

Forms!frmDistRegisterHomeAdd!cboLocationName
Forms!frmDistRegisterHomeAdd!cboMonthOfDist

 

[Ihussein]

Forms!frmDistRegisterHomeAdd!cboLocationName
Integer

Forms!frmDistRegisterHomeAdd!cboMonthOfDist
Date

 

[arnelgp]

Dim lngLocation As Long
lngLocation = Nz(DLookUp("PlanID","[tblDis tPlan]","LocationID_FK=" & Forms!frmDistRegisterHomeAdd!cboLocationName), 0)
strDeleteRegData = "DELETE tblDistReg.*, RegisterID FROM tblDistReg " _
& "WHERE tblDistReg.RegisterID=" lngLocation
& " And MonthOfDist =#" & Format(Forms!frmDistRegisterHomeAdd!cboMonthOfDist,"mm/dd/yyyy") & ";"

 

[Cronk]

cboMonthOfDist - does that hold a date or a month?

The previous air code has a couple of typos

strDeleteRegData = "DELETE tblDistReg.*, RegisterID FROM tblDistReg " _
& "WHERE tblDistReg.RegisterID=" & lngLocation
& " And MonthOfDist =#" & Format(Forms!frmDistRegisterHomeAdd!cboMonthOfDist  ,"mm/dd/yyyy") & "#;

"

 

[Ihussein]

I have tried the above code but it is asking to Enter Parameter Value of MonthOfDist. I might need to check the rowsource of cboMonthOfDist?
Thanks

 

[MarkK]

MonthOfDist is a field name in your SQL.

strDeleteRegData = "DELETE tblDistReg.*, RegisterID FROM tblDistReg " _
& "WHERE tblDistReg.RegisterID=" & lngLocation
& " And [COLOR="Red"]MonthOfDist[/COLOR] =#" & Format(Forms!frmDistRegisterHomeAdd!cboMonthOfDist  ,"mm/dd/yyyy") & "#;

. . . and the prompt means the field doesn't exist in the table tblDistReg.

Also, you don't need fields in a DELETE query . . .

sql = _
   "DELETE FROM tblDistReg " & _
   "WHERE RegisterID = " & lngLocation & " " & _
      "And [COLOR="Red"]MonthOfDist[/COLOR] = #" & Forms!frmDistRegisterHomeAdd!cboMonthOfDist & "#;"

. . . and I don't think formatting the date will make any difference.

 

[Ihussein]

Still asking to Enter Parameter Value of MonthOfDist.

 

[arnelgp]

check your table if you have MonthOfDist field there.

 

[Ihussein]

Mistakenly I was writing my code in wrong way. I suppose to lookup MonthOfDist from another table, then along with lngLocation execute my delete query. I have re-wrote my code and now it's working. Please accept my a apology, I'm really sorry for the inconvenience made and thanks to access-programmers community for there continues support/help.
Have a nice day