access guru wanted

Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: access guru wanted

  1. #1
    jaybone Guest

    Default access guru wanted

    Posted this yesterday on ASP Board with no luck. Database gurus help!!!!!!<BR>access database.When i insert a single zipcode it returns correct recordset but i want user to be able to select multiple zips from a listbox. when more then one zip is selected it returns no records found. I am using the IN operator in my sql statement and its not working. The SQL Statement is a stored query in access. Can this not be done or is there a better way to do this. Any tutorials or psuedo code is appreciated. I have even tried to reformat data to include single quotes around zipcodes as suggested by access but then i don&#039;t get any records returned.<BR><BR> i.e.21201, 21202 <BR>WHERE PropertyInformation.ZipCode IN ([(@ZipCode)]) AND <BR>PropertyInformation.ListPrice &#062;= [(@MinimumPrice)] AND <BR>PropertyInformation.ListPrice &#060;= [(@MaximumPrice)] AND <BR>PropertyInformation.Bedrooms &#062;= [(@Bedrooms)] AND <BR>PropertyInformation.Baths &#062;= [(@Baths)]; <BR><BR>I did a response.write just to make sure the list box was putting out the correct dynamic results(if one is selected = 21201/ if two are selected = 21201, 21202)and it is. I thought I should be able to insert these results into the IN operator and recieve matching records from either zip. Sql statement is stored query in access. Just for the fun of it. here is the actual documentation from access. Compare their example to my WHERE clause and then tell me why mine is not working. I just don&#039;t get it. <BR><BR>IN operator: <BR>Determines whether the value of an expression is equal to any of several values in a specified list. <BR><BR>Syntax <BR><BR>expr [Not] In(value1, value2, . . .) <BR><BR>Remarks <BR><BR>The In operator syntax has these parts: <BR><BR>Part Description <BR>expr Expression identifying the field that contains the data you want to evaluate. <BR>value1, value2 Expression or list of expressions against which you want to evaluate expr. <BR>If expr is found in the list of values, the In operator returns True; otherwise, it returns False. You can include the Not logical operator to evaluate the opposite condition (that is, whether expr is not in the list of values). <BR>For example, you can use In to determine which orders are shipped to a set of specified regions: <BR><BR>SELECT * <BR>FROM Orders <BR>WHERE ShipRegion In (&#039;Avon&#039;,&#039;Glos&#039;,&#039;Som&#039; )

  2. #2
    Tim Snyder Guest

    Default RE: access guru wanted

    Is Zip a char or int?

  3. #3
    jaybone Guest

    Default RE: access guru wanted

    I checked design view and the zipcode field is text.

  4. #4
    Tim Snyder Guest

    Default RE: access guru wanted

    What does the content of the parameter look like<BR>ie &#039;2001&#039;, 2002&#039; etc<BR>or 2001, 2002 <BR><BR>It should be the first. Access is fussy

  5. #5
    jaybone Guest

    Default RE: access guru wanted

    yes i convert the zipcode parameter output to &#039;21201&#039; with<BR>Zip = Request.Form("Areas")<BR>Zip = replace(Zip,",","&#039;,&#039;")<BR>Zip = "&#039;" & Zip & "&#039;"<BR>This is the response.write &#039;21201&#039;,&#039; 21202&#039;<BR>Is this correct. What about the space between the &#039; and the 2 in the second zip could that space have anything to do with it. if i choose 2 choices it returns no recordset.

  6. #6
    Tim Snyder Guest

    Default RE: access guru wanted

    &#039;2001&#039;, &#039;2002&#039; etc <BR>

  7. #7
    jaybone Guest

    Default RE: access guru wanted

    I&#039;m sorry Tim I don&#039;t understand your response. If you are showing me that the space should not be in the second zipcode the way it is how can I fix that. I appreciate you trying to help me out. I&#039;m at wits end here. My conversion code is<BR><BR>Zip = Request.Form("Areas")<BR>Zip = replace(Zip,",","&#039;,&#039;")<BR>Zip = "&#039;" & Zip & "&#039;"<BR>

  8. #8
    Tim Snyder Guest

    Default I think I have it for you

    Okay the format you should have If memory serves correctly is<BR><BR>&#039;2001&#039;, &#039;2002&#039;, &#039;2003&#039;<BR><BR>Note: &#039;is tight up to the data , tight up to the previous string and a space before the next one....<BR>Also No comma at the end!!

  9. #9
    m. callarse Guest

    Default RE: I think I have it for you

    I was able to duplicate the problem in Access, and have not found the syntax around it. An out is to build the query in your page so that instead of passing a parameter, the IN value(s) are hard-coded into the query at runtime. If I find anything, I&#039;ll let you know.

  10. #10
    DRY_GIN Guest

    Default RE: access guru wanted

    in what format data is coming from form ?<BR>in format or "2001,2003" or "2001, 2003" ??<BR>if where is space after each comma - you should remove it<BR>assuming you use post method in form,<BR>final VB string will look like this :<BR>if Request.form("zip") &#060;&#062; "" then "select * from my_table where zip IN(&#039;" & replace(Request.form("zip"),", ","&#039;,&#039;") &"&#039;)", conn, 3<BR> conn - is your db connection

Posting Permissions

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