Re: Using SQL in Sub to fill box on mouseclick

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

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: 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)
  • RE: Web Part and Access database
    ... I dont know if it is possible to connect to access,but you can download SQL ... Server Error in '/Webparts' Application. ... The connection string specifies a local Sql Server Express instance ... String user, String password, Boolean trusted, String connectionString) ...
    (microsoft.public.sharepoint.portalserver.development)
  • Re: INDEXES: BTRIEVE vs EXTFH (cobol)
    ... Bill Bach wrote: ... In SQL, you can specify just about anything, but at the lower MKDE ... the engine has to pick the right Btrieve ... When COBOL needs to find this value, it knows that the key is a string. ...
    (comp.databases.btrieve)
  • Re: INDEXES: BTRIEVE vs EXTFH (cobol)
    ... In SQL, you can specify just about anything, but at the lower MKDE ... the engine has to pick the right Btrieve ... When COBOL needs to find this value, it knows that the key is a string. ...
    (comp.databases.btrieve)