VBA Quotes using IIF

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 = ???




0
scott
3/19/2008 11:33:43 PM
access.formscoding 7493 articles. 0 followers. Follow

4 Replies
740 Views

Similar Articles

[PageSpeed] 18

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" <sbailey@mileslumber.com> wrote in message 
news:uK7ohihiIHA.5780@TK2MSFTNGP06.phx.gbl...
> 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 = ???
>
>
>
> 


0
Ken
3/19/2008 11:42:11 PM
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)" <kthsneisllis9@ncoomcastt.renaetl> wrote in message 
news:%23fifcrhiIHA.1212@TK2MSFTNGP05.phx.gbl...
> 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" <sbailey@mileslumber.com> wrote in message 
> news:uK7ohihiIHA.5780@TK2MSFTNGP06.phx.gbl...
>> 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 = ???
>>
>>
>>
>>
>
> 


0
scott
3/22/2008 2:03:56 AM
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" <sbailey@mileslumber.com> wrote in message 
news:%23xEQU$7iIHA.6084@TK2MSFTNGP06.phx.gbl...
> 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)" <kthsneisllis9@ncoomcastt.renaetl> wrote in message 
> news:%23fifcrhiIHA.1212@TK2MSFTNGP05.phx.gbl...
>> 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" <sbailey@mileslumber.com> wrote in message 
>> news:uK7ohihiIHA.5780@TK2MSFTNGP06.phx.gbl...
>>> 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 = ???
>>>
>>>
>>>
>>>
>>
>>
>
> 


0
Ken
3/22/2008 2:39:24 AM
I had to modify it a bit, but it works. Thanks.


"Ken Snell (MVP)" <kthsneisllis9@ncoomcastt.renaetl> wrote in message 
news:uKSd2W8iIHA.5088@TK2MSFTNGP02.phx.gbl...
> 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" <sbailey@mileslumber.com> wrote in message 
> news:%23xEQU$7iIHA.6084@TK2MSFTNGP06.phx.gbl...
>> 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)" <kthsneisllis9@ncoomcastt.renaetl> wrote in message 
>> news:%23fifcrhiIHA.1212@TK2MSFTNGP05.phx.gbl...
>>> 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" <sbailey@mileslumber.com> wrote in message 
>>> news:uK7ohihiIHA.5780@TK2MSFTNGP06.phx.gbl...
>>>> 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 = ???
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
> 


0
scott
3/22/2008 6:23:39 PM
Reply:

Similar Artilces:

How to use outlook address in Excel
Hello, I have an Excel sheet which I use as an invoicing-application. I would like to retrieve address-data from Outlook where I keep all my contact-data of my customers. So, I want to select a customer from my Outlook contactlist when I am writing a new invoice in Excel. In Word, I have a macro which does this, but unfortunately the Application.GetAddress does not work in Excel. Can somebody help me ? "Henny Slokker" wrote: > Hello, > > I have an Excel sheet which I use as an invoicing-application. I would like > to retrieve address-data from Outlook where I...

impossible to access the "news" from outlook 2003
I wanted to set-up my newsgroups access In outlook 2003. I followed the documented procedure (toolbar/standard/customize) from the help menu to add the "News" selection to the "GO" menu. It worked but when I exited Outlook and got back later the "news" menu disappeared. I tried to reinstate it following the same installation procedure but the News option is no more available in the "toolbar/customize list". Looks like a bug. Has anybody faced the same issue ? Is there a workaround to access the Newsgroup interface ? Thanks, Laurent conflicts ...

IE8 uses 95+% of cpu after update to sp3
I just updated to XP sp3 and did updates after that and IE8 is running so slow. I loaded task manager to see what was happening and IE8 was using up to 99%. My system was a slow XP sp2 but did a disk clean and defrag and it was running a lot faster. Apps load and run faster after sp3 but not IE8. Thanks for any suggestions mx5 wrote: > I just updated to XP sp3 and did updates after that and IE8 is > running so slow. I loaded task manager to see what was happening > and IE8 was using up to 99%. > > My system was a slow XP sp2 but did a disk clean and defrag and...

"Print Quote For Customer"
Using the Outlook client this option is under the file menu when you have a quote open. What template does this use and where do I download it from? ...

"Transfer Out" Call Option
I manually entered into Money 2007 Deluxe a covered call (buy stock, sell to open for a call option) that I made in September 2007. In November 2007 I transferred all of the assets (including the stock and short call) to another account. I was able to "transfer out" all of the equities to the new account. When I try to transfer the call MS Money says that "The activity 'transfer out' is unavailable for the investment type you have selected." In reality the calls were transferred from brokerage to brokerage. Any ideas how to make this happen in MS Money 2007? Thank...

GUID/Outlook format link giving error "Unable to open the seleted folder or item"
Hi, I have created custom forms in multiple public Outlook folders and when I send the GUID type link to the user, they receive an error: "Unable to open the seleted folder or item". The same happens if I send the Outlook type link. However, if I ask the user to go to the folder and find the appropriate posting, then the form opens and after that the link works, too. Is this due to some Exchange server setting? My clients are not the type that tolerate opening the form the long way, they want a one click solution. Please help, this is driving my team crazy. Any ideas are welcome...

VB: using a string to set a range object?
I'm a bit new to the excel "range" object type. I was suprised to see that while I can do: dim chunk as Range chunk = .Range(A5:B6) I apparently cannot do: dim chunk as Range dim stuff as string string = "A5:B6" chunk= .Range(string) How can I concatenate up a string describing a range, and then use it to define a range object's target cells? - Ross. Oops, I meant chunk = .Range("A5:B6") in the first example - I forgot the quotes. R. "RGK" <nothanks@nospam.go> wrote in message news:RqydnSWzbu_OEZbeRVn-2A@...

How to use CSplitterWnd for Dynamic Nested Splitter window?
Using VC++ (.NET) I am trying to implement a Dynamic Nested Splitter window using CSplitterWnd. I haven't found any documentation that explains how to get this to work properly and the only examples I have found also do not work properly. There are plenty of examples of Static Nested Splitter windows, but I need an example of a Dynamic one. The static splitter windows won't work for my requirements. Are there any examples or documentation that explains how to properly implement a Dynamic Nested Splitter Window using CSplitterWnd? Thanks, Dave Hi Dave, > Using VC++ (.NE...

"X-axis crosses at..."
You can make the x-axis cross at the maximum Y-value, but is it possible (without macro) to make it cross at the minimum value? I should find that much more useful. NSV -- nsv ------------------------------------------------------------------------ nsv's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26500 View this thread: http://www.excelforum.com/showthread.php?threadid=526722 When this is an issue for me, I just use some value <= the minimum Y axis value that the data will ever see. So, if my Y data will always be positive, I set the X axis crosses...

Add phone number to quote report.
Hey guys, My retail staff asked me if it is possible to add the customer phone number to the Quote summary or Quote detailed report. Does anyone know what changes need to be made to the report to make this happen. I know I will have to change the query and add a join statement to get it from the customer record but am unable to figure out how to do it. Any ideas? Thanks, Nevermind, I figured it out myself. Thanks anyway. <nick@realkiteboarding.com> wrote in message news:BhKXd.15130$GO5.11570@fe07.lga... > Hey guys, > > My retail staff asked me if it is possible to a...

Coding Convention for using Binary FlagWords?
Hi all --- I'm thinking of putting the .Tag property to use by using a binary ("bitwise") encoded flag word. Over time I've come across different possible uses for .Tag but have never standardized how I use it. Has someone developed a convention for "parsing" binary flag words? A simple IF works if I only need to test for one flag, but if I need to check for the presence of multiple bits the only construct that comes to mind is a series of IF statements .... something along the lines of: Enum FlagWord FlagA = 1 FlagB = 2 FlagC = 4 .......

Using Outlook client for CRM 3.0 in a remote office
I need to set up access to the corporate CRM from several remote offices. All of them have VPN connections (Windows or Checkpoint). What are the steps required to configure remote clients to be able to use CRM features in Outlook. Remote users can connect to CRM through Internet Expolorer. Thanks. Assuming they are connected, the isntallation over a VPN connection should not be any different than a typicaly installation. If the connection is slow though, the first synch & Go Offline process will be noticiably slower though. -- Matt Parks MVP - Microsoft CRM "mkatsev"...

using std::deque in multiple threads
I have a thread that listens on a message queue and populates a std::deque with events from that message queue, but only holds the latest 100. In my main thread, I want to populate a listbox with the information from that deque. So, it ends up being that one thread can add/remove items from the deque while another thread is trying to iterate through them. How can I make this thread-safe? Thanks, PaulH The code looks a bit like this: std::deque<MESSAGE_TYPE> m_dequeMessages; CMyClass::MessageThread() { //... while (ReadMsgQueue(hMsgQueue, &msg,...)) { m_dequeMessages.pu...

Wordwrapping a long sentence using F2, how to end the process?
Using Excel 2002...I pasted in a long sentence, and it goes off the right side of the screen. I know that I can make it word wrap by hitting F2. I've done that and it works nicely. But I was told that I could end this process by hitting Alt-Enter, and that doesn't work... all that happens after Alt-Enter is a blank new row opens up below the word-wrapped rows. If I hit Alt-Enter again, another blank row opens up below. How can I end this F2 word-wrap and go on with other business. John alt-enter is used to force a new line in a cell. If you want to wrap the text a...

Use a wildcard within edit/replace
Hello Is there a method of using a wildcard function within edit/replace (in Excel 2003) so that I can tweak the way a formula works? The example I have is the following formula =IF(ISERROR(VLOOKUP($A8,DataImport!$A:$J,5,FALSE)),"",VLOOKUP($A8,DataImport!$A:$J,5,FALSE)) which I would like to change to =IF(ISERROR(VLOOKUP(TEXT($C8,"0000),DataImport!$D:$E,2,FALSE)),"",VLOOKUP(TEXT($C8,"0000"),DataImport!$D:$E,2,FALSE)) The issue is (I think) that this formula is repeated many times over in one column over a number of worksheets - therefo...

Allow Account Description changes in "Mass Modify" screen
In the "Mass Modify" screen of Account Maintenance "Cards", there should be a selection for Description changes. Some of our Accounts have 45-50 account combinations. If an account needs to be renamed, I have to go into each card to update. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click ...

Outlook 2003 "Full Name..." field in contact screen reversed.
Outlook 2003 "Full Name..." field in contact screen reversed. I am using XP, with Outlook 2003. For some reason, all my 800 contacts are having a problem. When I open the contact screen the "Full Name... " field is displaying names backwards. "Mr. John Smith" displays as "Mr. Smith John" I have checked my "Contacts Options" selection for default Full Name order and it correctly displays "First (Middle) Last"... I have noticed that if I delete the name and re-enter it, it displays correctly, however I cannot spend the time to d...

excel 2000 message
excel 2000 message - 'cannot use object linking and embedding' Were they hit by the MSBlast worm? One poster (Lutz Meyer) guessed that this was the cause of his problems. I haven't seen any confirmation/denial, but you may want to read his post: http://groups.google.com/groups?threadm=3F3971AF.FA4490F5%40msn.com Post back with your results. I'm curious if that was the problem. (It's come up quite a few times since MSBlast hit.) bill bootle wrote: > > excel 2000 message - 'cannot use object linking and > embedding' -- Dave Peterson ec35720@msn.c...

help
Hi, I'm trying to turn the warning message off when I use: Cells.Replace What:="Z Divisional Totals", Replacement:="Divisional Totals", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Second part is: Is there a way to check either the column and rows filled to create border where is pointed to "APPLYBORDER". Your help would be much apprecated. r = Range("A10").End(xlDown).Row ApplyBorder Range("A10", "A" & r) ApplyBorder Range("B10", "B" & r) ApplyBorder Range("C10", "C&qu...

"Customize Outlook Today
I just purchased a new computer with Windows XP Professional installed (replacing my old one, with Windows 95). After installing my Office 2000 suite onto the new computer, I find that in Outlook, when I click on the "Customize Outlook Today" button, nothing happens. Acordingly I am unable to configure the display to suit my needs. For example, I need to have the days tasks only, rather than the full list you get when you cannot customize the display. Any suggestions? The answer may be found in MS Knowledge Base article 820575, "You Cannot Customize Outlook Today After...

Mobile using up CPU
Hi there Since we installed CRMMobile our (new) server has been SO SLOW! SqlServer is taking anywhere from 69 - 99% cpu time (mostly around 99) I have run sql profiler and discovered that many times a second the following transactions are running: exec sp_executesql N'SELECT recipientAddr,recipientSrvcPort,sourceAddr,sourceSrvcPort, MIN(arrivalDate) as minArrivalDate, count(*) as messageCount FROM vwInBoundQueue WHERE recipientAddr=@recipientAddr AND recipientSrvcPort=@recipientSrvcPort GROUP BY sourceAddr,sourceSrvcPort,recipientAddr,recipientSrvcPort ORDER BY minArrivalDat...

how can i start using excel for the first time?
i cant figure out how to get excel to work for me and im a first time user of it? i Cant get nothing to work on it? can anyone please help me here Paul Can you get Excel to start up? Can you get a blank workbook to open via File>New? For basics on Excel see.......... http://www.usd.edu/trio/tut/excel/index.html http://www.baycongroup.com/el0.htm Microsoft Training Courses. http://office.microsoft.com/en-us/training/CR061831141033.aspx Gord Dibben Excel MVP On Wed, 15 Dec 2004 15:39:02 -0800, "Paul Scheffer" <Paul Scheffer@discussions.microsoft.com> wrote: >i ...

File in use .... is locked for editing
Please help. In Microsoft Excel 2000, we seem to be getting the above error message frequently. No other users are in the file, and there aren't any temporary files relating to the file in question. Any ideas on what is causing this? We are running a mixture of Windows 2000 & XP machines on to a Small Business Server 2000. Hope to hear from someone soon Regards Simon I think you killed the usual answer of cleaning the temp folder. But have you rebooted? Maybe there's a hidden instance of excel running that "owns" the file. And if the file/workbook is on a net...

Can I change default workplace "All" filter to "Appointments"??
Hi all, We heavily use the Appointments section of the CRM Workplace, and have defined a number of important Appointment views (Today's Appointments, Tomorrow's Appointments, To Be Scheduled Appointments, Waiting for Callback), etc. (We heaviy customized Appointments to add a status value, and links to our custom entities. Very useful!) The views all work fine, except they don't show up in the main set of activity views. They DO show up when the Type = "Appointment", so using these views require the user to: a) Click on Workplace b) Change the Type from All to Appoin...

clear historical quotes
I just upgraded to 2007 trial version (from 2003) and I find what appears to be an undocumented item, so I'm wondering what it does. I'm on the Portfolio Manager screen, and on the left side, I click on Other Tasks->More-> and there's a "Clear historical quotes" menu item. I don't find it documented in the help file, nor in the FAQ, nor on this newsgroup, yet. I'm wondering if anyone's been brave enough to click on it and could tell us what it does? Does it reduce the size of your file? How much? I would hope it does NOT just do a wholesale de...