Re: Using SQL in Sub to fill box on mouseclick

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Thanks Graham,

I did see that and changed it to "" and ', still giving me an error. I will
try the listbox.

Jay

"Graham Mandeno" <Graham.Mandeno@xxxxxxxxxxxxx> wrote in message
news:eCbBK1aVFHA.3868@xxxxxxxxxxxxxxxxxxxxxxx
> In addition to Doug's point on the SQL syntax, your VB syntax is
> incorrect. This is because the first " character in your SQL string will
> be seen by VBA as the termination of the string, and everything after it
> will be junk.
>
> To put a " character inside a string in VB(A) you must double it. This
> has a special meaning and is not seen as the end of one string and the
> start of a new one. For example:
>
> strSQL = "SELECT tblAvailabilities.Date, [First]+"" ""+[Last] AS ...
>
> Alternatively, SQL understands both single and double quotes, so you could
> use:
>
> strSQL = "SELECT tblAvailabilities.Date, [First]+' '+[Last] AS ...
>
> However, the whole thing is flawed because what you will see in your
> textbox is not a list of names, but your SQL statement (SELECT ...).
>
> I think what you really want to do is use a listbox, not a textbox, and
> set its RowSource to the SQL statement. Set the listbox's Columns
> property to the number of visible columns in your query.
> --
> Good Luck!
>
> Graham Mandeno [Access MVP]
> Auckland, New Zealand
>
> "JayVZ" <jayvz@xxxxxxxxxxxxxxx> wrote in message
> news:qJ9ge.46713$PA4.21447@xxxxxxxxxxxxxxxxxxxxxxxx
>> In the form that I am working on, I have a number of text boxes that are
>> filled with a query telling me which people are scheduled to work a
>> shift. I also have a text box (I may need to make this a sub form, but
>> would prefer not) where I want a list of all available employees for the
>> specific shift to show. When I click, I want to see the list, so that I
>> can make changes if needed and only chose those employees that are
>> available for the shift.
>>
>> I have:
>> Private Sub Ctl8a_4pb_Click()
>> Me!txtAvailEmployees = "SELECT tblAvailabilities.Date, [First]+" "+[Last]
>> AS [Full Name]
>> FROM tblAvailabilities INNER JOIN tblStaff ON
>> tblAvailabilities.EmployeeNum = tblStaff.EmployeeID
>> GROUP BY tblAvailabilities.Date, [First]+" "+[Last],
>> tblAvailabilities.Shift1
>> HAVING (((tblAvailabilities.Shift1)=-1))
>> WHERE tblAvailabilities.Date=[form]![frmScheduleEdit]![CalDate];"
>> End Sub
>>
>> I get a syntax error. Is is even possible to pass SQL in a manner as
>> this?
>> If it is, where would I be messing up?
>>
>
>


.

Relevant Pages

  • Re: List box questions...
    ... The most simply way is to prove the sql. ... directly for the listbox. ... You can also stuff in a delimited string directly into the listbox. ... approach is only good for small lists ...
    (microsoft.public.access.forms)
  • Re: Export recordset from ADP project
    ... You must dynamically built the whole string: ... Dim sql as string ... For your ListBox, you can do the same thing and use a sql string for the ... DoCmd.OutputTo acOutputStoredProcedure, "EXEC MySP? ...
    (microsoft.public.access.adp.sqlserver)
  • Re: List box column limit?
    ... If you base a listbox on a string only value, it has a max of about 4000 ... rows of data possbile. ... However, if you base the listbox on table, you don't have that limitation. ... You can also just "stuff" the sql right into the listbox. ...
    (microsoft.public.access.forms)
  • Re: Problems with Delete Command
    ... The SQL could get fairly messy if you need to construct it in code, ... ContactID, and WebComID, and create your on-the-fly SQL on that saved query, ... to find the list of ContactIDs from the junction table, ... This is a style/readability thing: if you are going to use string ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Need help with Code Please!!!
    ... posted in response to my last post was some SQL, but this is not the SQL that ... the actual string that gets built at the end of the SQ1 build process. ... The message says Syntax error in query expression ... Dim rs As Recordset 'object ref to qryCompany\USFNumber ...
    (microsoft.public.access.formscoding)