Select statement

[Chris]

I'm having difficulty executing the following sql statement. Can someone
tell me if my syntax is correct? Do I have missing double or single quotes?
The date function I'm using takes what the user enters on the form as lets
say 06/26/2006 and converts it to a Julian date like 107177 that is held in
one of our peoplesoft tables. I've used this line of code many times in the
criteria line of a traditional query but never in a select statement behind
a form. Can someone scan my code real quick and let me know if they see
syntax errors?



PDDOCO is a number field

PDPDDJ is a number field

PDMCU is a text field

PDNXTR is a text field



Thanks,

Chris





Dim sSQL As String

Dim rsPOORDER As DAO.Recordset

Dim DB As Database

Set DB = CurrentDb



sSQL = "SELECT proddta_F4311.PDDOCO, proddta_F4311.PDPDDJ,
proddta_F4311.PDMCU, proddta_F4311.PDNXTR"

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')"



If rsPOORDER.EOF Then

MsgBox "This is not a valid PO Number. Please try again",
vbCritical, "Legend Valve Error Log"

Me.txtDate = Null

Me.txtPOnum = Null

Me.LstBP = Null



Me.txtPOnum.SetFocus

Exit Sub

Else

" This will kick off a query"



End If

 

[Guest]

There are two issues here. First, the references to the form controls have
to be outside the qoutes. Next, the syntax doesn't line up. You say the
PDPDDJ is a numeric field, but you are trying to format it as a date.

sSQL = sSQL & " AND ((proddta_F4311.PDPDDJ)= # & '1' &
Format([Forms]![frmPO Receipt]![txtDate],'yy') &
Format(DatePart('y',[Forms]![frmPO Receipt]![txtDate]),'000') & #"

Assuming the date is today, the result will be: #1164164# which is neither
a number nor a date. These two parts:
Format([Forms]![frmPO Receipt]![txtDate],'yy')
Format(DatePart('y',[Forms]![frmPO Receipt]![txtDate]),'000')
return the exact same value.

What is it you are trying to accomplish? Maybe we can help with that part
as well.

 

[Marshall Barton]

I'm having difficulty executing the following sql statement. Can someone
tell me if my syntax is correct? Do I have missing double or single quotes?
The date function I'm using takes what the user enters on the form as lets
say 06/26/2006 and converts it to a Julian date like 107177 that is held in
one of our peoplesoft tables. I've used this line of code many times in the
criteria line of a traditional query but never in a select statement behind
a form. Can someone scan my code real quick and let me know if they see
syntax errors?

PDDOCO is a number field
PDPDDJ is a number field
PDMCU is a text field
PDNXTR is a text field

Dim sSQL As String
Dim rsPOORDER As DAO.Recordset
Dim DB As Database
Set DB = CurrentDb

sSQL = "SELECT proddta_F4311.PDDOCO, proddta_F4311.PDPDDJ,
proddta_F4311.PDMCU, proddta_F4311.PDNXTR"

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')"


If rsPOORDER.EOF Then

MsgBox "This is not a valid PO Number. Please try again",
vbCritical, "Legend Valve Error Log"

Me.txtDate = Null
Me.txtPOnum = Null
Me.LstBP = Null

Me.txtPOnum.SetFocus

Exit Sub

Else

" This will kick off a query"

End If

You must open the recordset before trying to use the query's
data.

Set DB = CurrentDb()
Set rsPOORDER = OpenRecordset(sSQL)

Then you can use your
If rsPOORDER.EOF Then