VBA Quotes using IIF

  • Thread starter Thread starter scott
  • Start date Start date
S

scott

In CODE 1 below, I have a working SELECT statement that is the RowSource for
a combo box named cbotrackID. I'm trying to write it out in VBA, but I'm
having a lot of trouble with the quotes. I didn't put my failed code in CODE
2 because I didn't want to confuse anyone.

Can someone help me convert the SQL in CODE 1 into a VBA statement that I
can use in VBA to set the RowSource with a code version of the sql?


CODE 1 **********

SELECT t.trackID, t.trackNum, [trackNum] & ") " & [trackSong] &
IIf(IsNull([trackTime]),""," (" & [trackTime] & ")") AS Song,

t.trackSong, t.trackTime

FROM t_track t



CODE 2 **********

Me.cbotrackID.RowSource = ???
 
Dim strSQL As String
strSQL = "SELECT t.trackID, t.trackNum, [trackNum] & ") " & _
[trackSong] & IIf(IsNull([trackTime]),""," (" & [trackTime] & ")") _
& "AS Song, t.trackSong, t.trackTime FROM t_track t"
 
I'm still getting an error. Can you try to print.debug your statement? Sorry
for delay, i had an emergency come up.


Ken Snell (MVP) said:
Dim strSQL As String
strSQL = "SELECT t.trackID, t.trackNum, [trackNum] & ") " & _
[trackSong] & IIf(IsNull([trackTime]),""," (" & [trackTime] & ")") _
& "AS Song, t.trackSong, t.trackTime FROM t_track t"

--

Ken Snell
<MS ACCESS MVP>



scott said:
In CODE 1 below, I have a working SELECT statement that is the RowSource
for a combo box named cbotrackID. I'm trying to write it out in VBA, but
I'm having a lot of trouble with the quotes. I didn't put my failed code
in CODE 2 because I didn't want to confuse anyone.

Can someone help me convert the SQL in CODE 1 into a VBA statement that I
can use in VBA to set the RowSource with a code version of the sql?


CODE 1 **********

SELECT t.trackID, t.trackNum, [trackNum] & ") " & [trackSong] &
IIf(IsNull([trackTime]),""," (" & [trackTime] & ")") AS Song,

t.trackSong, t.trackTime

FROM t_track t



CODE 2 **********

Me.cbotrackID.RowSource = ???
Click to expand...
Click to expand...
 
Sorry - try this:

Dim strSQL As String
strSQL = "SELECT t.trackID, t.trackNum, [trackNum] & ') '" & _
"[trackSong] & IIf(IsNull([trackTime]),'',' (' & [trackTime] & ')')" _
& "AS Song, t.trackSong, t.trackTime FROM t_track t"

--

Ken Snell
<MS ACCESS MVP>


scott said:
I'm still getting an error. Can you try to print.debug your statement?
Sorry for delay, i had an emergency come up.


Ken Snell (MVP) said:
Dim strSQL As String
strSQL = "SELECT t.trackID, t.trackNum, [trackNum] & ") " & _
[trackSong] & IIf(IsNull([trackTime]),""," (" & [trackTime] & ")") _
& "AS Song, t.trackSong, t.trackTime FROM t_track t"

--

Ken Snell
<MS ACCESS MVP>



scott said:
In CODE 1 below, I have a working SELECT statement that is the RowSource
for a combo box named cbotrackID. I'm trying to write it out in VBA, but
I'm having a lot of trouble with the quotes. I didn't put my failed code
in CODE 2 because I didn't want to confuse anyone.

Can someone help me convert the SQL in CODE 1 into a VBA statement that
I can use in VBA to set the RowSource with a code version of the sql?


CODE 1 **********

SELECT t.trackID, t.trackNum, [trackNum] & ") " & [trackSong] &
IIf(IsNull([trackTime]),""," (" & [trackTime] & ")") AS Song,

t.trackSong, t.trackTime

FROM t_track t



CODE 2 **********

Me.cbotrackID.RowSource = ???
Click to expand...
Click to expand...
Click to expand...
 
I had to modify it a bit, but it works. Thanks.


Ken Snell (MVP) said:
Sorry - try this:

Dim strSQL As String
strSQL = "SELECT t.trackID, t.trackNum, [trackNum] & ') '" & _
"[trackSong] & IIf(IsNull([trackTime]),'',' (' & [trackTime] & ')')" _
& "AS Song, t.trackSong, t.trackTime FROM t_track t"

--

Ken Snell
<MS ACCESS MVP>


scott said:
I'm still getting an error. Can you try to print.debug your statement?
Sorry for delay, i had an emergency come up.


Ken Snell (MVP) said:
Dim strSQL As String
strSQL = "SELECT t.trackID, t.trackNum, [trackNum] & ") " & _
[trackSong] & IIf(IsNull([trackTime]),""," (" & [trackTime] & ")") _
& "AS Song, t.trackSong, t.trackTime FROM t_track t"

--

Ken Snell
<MS ACCESS MVP>



In CODE 1 below, I have a working SELECT statement that is the
RowSource for a combo box named cbotrackID. I'm trying to write it out
in VBA, but I'm having a lot of trouble with the quotes. I didn't put
my failed code in CODE 2 because I didn't want to confuse anyone.

Can someone help me convert the SQL in CODE 1 into a VBA statement that
I can use in VBA to set the RowSource with a code version of the sql?


CODE 1 **********

SELECT t.trackID, t.trackNum, [trackNum] & ") " & [trackSong] &
IIf(IsNull([trackTime]),""," (" & [trackTime] & ")") AS Song,

t.trackSong, t.trackTime

FROM t_track t



CODE 2 **********

Me.cbotrackID.RowSource = ???
Click to expand...
Click to expand...
Click to expand...
 
Back
Top