Select Statement Problem

[Laser]

Can anyone see what my problem is with this:

Set MySet4 = MyDB4.OpenRecordset("SELECT * FROM [InvoicePayments] WHERE ((([InvoicePayments.PaymentDate])= ([UnallocatedReceipts].Column(3)))")

Keeps telling me I'm missing something like ] or )

 

[plog]

Pasted it into notepad and counted characters. 6 opening parenthesis, 5 closing. So 3 steps:

1. Add a closing parenthesis at the very end and hope.
2. Add a closing parenthesis just inside the last quote mark and hope.
3. If first 2 don't work, start from scratch and rebuild it clause by clause.

 

[MajP]

I doubt that is even close for several reasons. I am pretty sure you cannot use Column in a sql and becuase this is in code you have to properly format the date.

dim strSql as string
strSql = "SELECT * FROM [InvoicePayments] WHERE PaymentDate = #" & Format(Me.UnallocatedReceipts.Column(3), "MM/DD/YYYY") & "#"
debug.print strSql
MyDB4.OpenRecordset(strSql)

 

[Laser]

Pasted it into notepad and counted characters. 6 opening parenthesis, 5 closing. So 3 steps:

1. Add a closing parenthesis at the very end and hope.
2. Add a closing parenthesis just inside the last quote mark and hope.
3. If first 2 don't work, start from scratch and rebuild it clause by clause.

Thanks for responding.. tried your suggestions but nothing worked

 

[Laser]

I doubt that is even close for several reasons. I am pretty sure you cannot use Column in a sql and becuase this is in code you have to properly format the date.

dim strSql as string
strSql = "SELECT * FROM [InvoicePayments] WHERE PaymentDate = #" & Format(Me.UnallocatedReceipts.Column(3), "MM/DD/YYYY") & "#"
debug.print strSql
MyDB4.OpenRecordset(strSql)

Always had problems with Access and dates.. I suspect you may be right with regard to UnallocatedReceipts.Column(3) but maybe I can pull the date out of the Combo box and place it in a textbox.. maybe it will work then.. fingers crossed

 

[MajP]

Always had problems with Access and dates.. I suspect you may be right with regard to UnallocatedReceipts.Column(3) but maybe I can pull the date out of the Combo box and place it in a textbox.. maybe it will work then.. fingers crossed

Why not just try that code and post the sql string from the debug if it does not work?

 

[Gasman]

Always had problems with Access and dates.. I suspect you may be right with regard to UnallocatedReceipts.Column(3) but maybe I can pull the date out of the Combo box and place it in a textbox.. maybe it will work then.. fingers crossed

I used this constant once and a while when I was suing Access.

'Public Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Needed for dates in queries as Access expects USA format.
Public Const strcJetDate = "\#yyyy-mm-dd\#"  'Needed for dates in queries as Access expects USA but will accept ISO format.

 

[MarkK]

With CurrentDb.CreateQueryDef("", "SELECT * FROM InvoicePayments WHERE PaymentDate = pDate")
    .Parameters("pDate") = Me.UnallocatedReceipts.column(3)
    Set rst = .OpenRecordset
End With

 

[June7]

SQL definitely can't handle the embedded Column property (not in query object or VBA built string).
Use parameters or concatenate control reference.

None of the parenthesis in that original WHERE clause are actually needed.

If system is U.S. date, then formatting date probably not needed but wouldn't hurt.

 

[Pat Hartman]

Thanks for responding.. tried your suggestions but nothing worked

You didn't post the code that didn't work so we don't know if you implemented the suggestion correctly or have more typos. This isn't helping you and it doesn't help us to help you. When you tell us something didn't work - ALWAYS POST YOUR VERSION of the code.