syntax

[Guest]

What would be the proper syntax for the following select statement using VBA
behind a form?

SELECT proddta_F4311.PDPDDJ, proddta_F4311.PDUOPN AS [Units Open],
proddta_F4311.PDITM, proddta_F4311.PDLITM AS Item INTO tblPOReceipt
FROM proddta_F4311
WHERE (((proddta_F4311.PDPDDJ)="1" & Format([Forms]![frmPO
Receipt]![txtDate],"yy") & Format(DatePart("y",[Forms]![frmPO
Receipt]![txtDate]),"000")));

This works fine in a traditional query but VBA doesn’t like the syntax.

 

[John W. Vinson]

What would be the proper syntax for the following select statement using VBA
behind a form?

SELECT proddta_F4311.PDPDDJ, proddta_F4311.PDUOPN AS [Units Open],
proddta_F4311.PDITM, proddta_F4311.PDLITM AS Item INTO tblPOReceipt
FROM proddta_F4311
WHERE (((proddta_F4311.PDPDDJ)="1" & Format([Forms]![frmPO
Receipt]![txtDate],"yy") & Format(DatePart("y",[Forms]![frmPO
Receipt]![txtDate]),"000")));

This works fine in a traditional query but VBA doesn’t like the syntax.

Please post the VBA. Bear in mind that VBA and SQL are different languages -
while this is valid SQL, it won't work (as is) in VBA.

John W. Vinson [MVP]

 

[Guest]

this is my select John. I'm just trying to code behind a form in access. I
have done many select statements behind forms in the past but I'm struggling
with this date format. Like I said, it works fine on the query level.

sSQL = "SELECT proddta_F4311.PDDOCO, proddta_F4311.PDPDDJ,
proddta_F4311.PDMCU, proddta_F4311.PDUOPN, proddta_F4311.PDITM,
proddta_F4311.PDLITM"
sSQL = sSQL & " FROM proddta_F4311"
sSQL = sSQL & " WHERE (((proddta_F4311.PDDOCO)= " & Me.txtPOnum & "))"
sSQL = sSQL & " AND ((proddta_F4311.PDPDDJ)= '1' & Format([Forms]![frmPO
Receipt]![txtDate],'yy') & Format(DatePart('y',[Forms]![frmPO
Receipt]![txtDate]),'000')) "
sSQL = sSQL & " AND ((proddta_F4311.PDMCU)=' 110') "
sSQL = sSQL & " AND ((proddta_F4311.PDNXTR)<'999'))"

What would be the proper syntax for the following select statement using VBA
behind a form?

SELECT proddta_F4311.PDPDDJ, proddta_F4311.PDUOPN AS [Units Open],
proddta_F4311.PDITM, proddta_F4311.PDLITM AS Item INTO tblPOReceipt
FROM proddta_F4311
WHERE (((proddta_F4311.PDPDDJ)="1" & Format([Forms]![frmPO
Receipt]![txtDate],"yy") & Format(DatePart("y",[Forms]![frmPO
Receipt]![txtDate]),"000")));

This works fine in a traditional query but VBA doesn’t like the syntax.

Please post the VBA. Bear in mind that VBA and SQL are different languages -
while this is valid SQL, it won't work (as is) in VBA.

John W. Vinson [MVP]

 

[John W. Vinson]

What would be the proper syntax for the following select statement using VBA
behind a form?

SELECT proddta_F4311.PDPDDJ, proddta_F4311.PDUOPN AS [Units Open],
proddta_F4311.PDITM, proddta_F4311.PDLITM AS Item INTO tblPOReceipt
FROM proddta_F4311
WHERE (((proddta_F4311.PDPDDJ)="1" & Format([Forms]![frmPO
Receipt]![txtDate],"yy") & Format(DatePart("y",[Forms]![frmPO
Receipt]![txtDate]),"000")));

This works fine in a traditional query but VBA doesn’t like the syntax.

I am not sure what you want. What's in txtDate, and what is the format of
PDPDDJ? Could you give a couple of examples?

John W. Vinson [MVP]

 

[John W. Vinson]

this is my select John. I'm just trying to code behind a form in access. I
have done many select statements behind forms in the past but I'm struggling
with this date format. Like I said, it works fine on the query level.

sSQL = "SELECT proddta_F4311.PDDOCO, proddta_F4311.PDPDDJ,
proddta_F4311.PDMCU, proddta_F4311.PDUOPN, proddta_F4311.PDITM,
proddta_F4311.PDLITM"
sSQL = sSQL & " FROM proddta_F4311"
sSQL = sSQL & " WHERE (((proddta_F4311.PDDOCO)= " & Me.txtPOnum & "))"
sSQL = sSQL & " AND ((proddta_F4311.PDPDDJ)= '1' & Format([Forms]![frmPO
Receipt]![txtDate],'yy') & Format(DatePart('y',[Forms]![frmPO
Receipt]![txtDate]),'000')) "
sSQL = sSQL & " AND ((proddta_F4311.PDMCU)=' 110') "
sSQL = sSQL & " AND ((proddta_F4311.PDNXTR)<'999'))"

Another thought: what's the datatpye of PDDOCO? If it's Text, you need quote
delimiters on it too. What does sSQL contain when you actually execute the
code?

John W. Vinson [MVP]