This code won't execute without a syntax error error. What am I doing wrong?
Private Sub cboInvFindVendorName_AfterUpdate()
Me.cboInvFindDate.RowSource = "SELECT [InvoiceDate] FROM tblInvoices " & _
WHERE [vendorID] = " & Nz(me.cboInvFindVendorname) ORDER by [InvoiceDate]"
End Sub
I think that the previous answers have incorrect quotations around the nz() function. also, it appears by the name, that the field is combobox, and it may be more appropriate to refer to its exact column, I went with column zero as this is usually the bound column of the key. I don't think referring to the column is a requirement but I think just makes sure.
Me.cboInvFindDate.RowSource = "SELECT [InvoiceDate] FROM tblInvoices " & _
"WHERE [vendorID] = " & Nz(Me.cboInvFindVendorname.column(0), 0) & " ORDER BY [InvoiceDate]"
Try without the '&' after the 'FROM tblInvoices', like so:
Private Sub cboInvFindVendorName_AfterUpdate()
Me.cboInvFindDate.RowSource = "SELECT [InvoiceDate] FROM tblInvoices
WHERE [vendorID] = " & Nz(me.cboInvFindVendorname) ORDER by [InvoiceDate]"
End Sub
Here's an example of VBA Access SQL syntax I found:
SELECT tblStaff.[Firstname], tblStaff.[Lastname] FROM tblStaff WHERE tblStaff.[Office]="Paris”;
Final Code
Private Sub cboInvFindVendorName_AfterUpdate()
Me.cboInvFindDate.RowSource = "SELECT tblInvoices.[InvoiceDate] FROM tblInvoices
WHERE tblInvoices.[vendorID] = " & Nz(me.cboInvFindVendorname) ORDER by [InvoiceDate]"
End Sub
Hope this helps,
Sohail
You missed a quote and a zero:
Me.cboInvFindDate.RowSource = "SELECT [InvoiceDate] FROM tblInvoices " & _
"WHERE [vendorID] = " & Nz(Me.cboInvFindVendorname, 0) & " ORDER BY [InvoiceDate]"
