Select statement

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chris - 13 Jun 2007 15:48 GMT

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

Reply to this Message

Klatuu - 13 Jun 2007 16:31 GMT

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.

Signature

Dave Hargis, Microsoft Access MVP

> 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?
[quoted text clipped - 60 lines]
>
> End If

Reply to this Message

Marshall Barton - 13 Jun 2007 16:38 GMT

>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?
[quoted text clipped - 48 lines]
>
> 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

Signature

Marsh
MVP [MS Access]

Reply to this Message