Rowsource Question 1

[georgp]

Hi,

I have a listbox with the RowSource:

utbx_list.RowSource = "qry_PSynth".

The stored query behind is as follows:

SELECT tbl_PSynth.ID,
tbl_PSynth.Name1 & IIf(IsNull(tbl_PSynth.Name2),"","/" & tbl_PSynth.Name2) AS [Poly Comp]
FROM tbl_PSynth;

This works fine.

However, if I use the (same) VBA phrase

utbx_list.Rowsource = "SELECT tbl_PSynth.ID, tbl_PSynth.Name1 & IIf(IsNull(tbl_PSynth.Name2),"","/" & tbl_PSynth.Name2) AS [Poly Comp]
FROM tbl_PSynth"
(this is in one line)

I get an error message -> TYPE MISMATCH.

I need this because I want to make some variable WHERE/ORDER clause additions.
I do not understand what I am doing wrong.
Any help?

Thanks, georgp

 

[NorthNone]

The double-quotes will trip you up every time when taking SQL from a query and plopping it over in VBA. Try this
tbx_list.Rowsource = "SELECT tbl_PSynth.ID, tbl_PSynth.Name1 & IIf(IsNull(tbl_PSynth.Name2),'',' / ' & tbl_PSynth.Name2) AS [Poly Comp] FROM tbl_PSynth"
HTH
JeanS

---------------------------------------
Remember, the customer may not always be right, but the customer is always the customer.

 

[georgp]

Thanks a lot - that did the trick.
georgp