How do I include a space into a select query?

Results 1 to 2 of 2

Thread: How do I include a space into a select query?

  1. #1
    TomC Guest

    Default How do I include a space into a select query?

    I need to retrieve the last name of a person from an Access database field [jname] containing both first and last name info which is in the form of A Smith. A A Smith or Ms A Smith. The following select query works in Access but does not translate to a select query in an ASP file because of the " " representing a space.<BR><BR>I have tried replacing the " " with space(1), chr(32), &#039" & space(1) & "&#039 and also &#039" & chr(32) & "&#039 in the sql query but while the query seems to run OK it does not return any records. Can anybody help?<BR><BR><BR>sql="SELECT data.jname, Sum(IIf([data]![finish]=1,1,0)) AS expr2, IIf(InStr(InStr([data]![jname]," ")+1,[data]![jname]," ")&#060;&#062;0,Right([data]![jname],Len([data]![jname])-InStr(InStr([data]![jname]," ")+1,[data]![jname]," ")),Right([data]![jname],Len([data]![jname])-InStr([data]![jname]," "))) AS Expr FROM data GROUP BY data.jname HAVING (((Sum(IIf([data]![finish]=1,1,0)))&#062;10)) ORDER BY IIf(InStr(InStr([data]![jname]," ")+1,[data]![jname]," ")&#060;&#062;0,Right([data]![jname],Len([data]![jname])-InStr(InStr([data]![jname]," ")+1,[data]![jname]," ")),Right([data]![jname],Len([data]![jname])-InStr([data]![jname]," ")));"

  2. #2
    Join Date
    Dec 1969

    Default Replace dbl quote with apostrophe...

    SQL normally uses apostrophes, anyway, so it&#039s more natural to do it this way!<BR><BR>Partial replacement of your code:<BR><BR>sql="SELECT data.jname, Sum(IIf([data]![finish]=1,1,0)) AS expr2, IIf(InStr(InStr([data]![jname],&#039 &#039)+1,[data]![jname],&#039 &#039")&#060;&#062;0,Right([data]![jname],Len([data]![jname])-InStr(InStr([data]![jname],&#039 &#039)+1,[data]![jname],&#039 &#039)),Right([data]![jname],Len([data]![jname])-InStr([data]![jname],&#039 &#039))) AS ...<BR><BR>What in the world does that query *DO*??? Please satisfy my curiosity!<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts