Select statement

  • Thread starter Thread starter Chris
  • Start date Start date
C

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
 
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.
 
Chris said:
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
Click to expand...


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
 
Back
Top