Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2014
    Posts
    27

    Unanswered: Search Page help

    I have a search page and whenever I search once it works but then I try to change the criteria and the search doesn't work the second time, what am I doing wrong? Any help would be appreciated.

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    Dont know the context, or what you're using for any of this. Example code/qrys.

  3. #3
    Join Date
    Jun 2014
    Posts
    27
    Here is the SQL view of what I am working with, it searches through criteria and each of the production locations is a checkbox in the form, but it never seems to work after the first time.

    WHERE (((Companies.[MMcf/d]) Between [Forms]![Search page]![MMcf/d High] And [Forms]![Search Page]![MMcf/d low]) AND ((Companies.[Hedging Program])=[Forms]![Search Page]![Hedging]) AND (([S&P].Ordinal) Between [Forms]![Search Page]![SP Rating High] And [Forms]![Search Page]![SP Rating Low]) AND ((Companies.[Market Cap]) Between [Forms]![Search Page]![Market Cap High] And [Forms]![Search Page]![Market Cap low]) AND (([Production Locations].Anadarko)=[Forms]![Search Page]![Anadarko])) OR ((([Production Locations].Bakken)=[Forms]![Search Page]![Bakken])) OR ((([Production Locations].Barnett)=[Forms]![Search Page]![Barnett])) OR ((([Production Locations].[Black Warrior])=[Forms]![Search Page]![Black Warrior])) OR ((([Production Locations].[British Columbia])=[Forms]![Search Page]![BC])) OR ((([Production Locations].[Eagle Ford])=[Forms]![Search Page]![Eagle Ford])) OR ((([Production Locations].Fayetteville)=[Forms]![Search Page]![Fayetteville])) OR ((([Production Locations].Haynesville)=[Forms]![Search Page]![Haynesville])) OR ((([Production Locations].[Horn River])=[Forms]![Search Page]![Horn River])) OR ((([Production Locations].Illinois)=[Forms]![Search Page]![Illinois])) OR ((([Production Locations].Marcellus)=[Forms]![Search Page]![Marcellus])) OR ((([Production Locations].Michigan)=[Forms]![Search Page]![Michigan])) OR ((([Production Locations].Monterey)=[Forms]![Search Page]![Monterey])) OR ((([Production Locations].Niobrara)=[Forms]![Search Page]![Niobrara])) OR ((([Production Locations].Permian)=[Forms]![Search Page]![Permian])) OR ((([Production Locations].Piceance)=[Forms]![Search Page]![Piceance])) OR ((([Production Locations].[San Juan])=[Forms]![Search Page]![SanJuan])) OR ((([Production Locations].Tuscaloosa)=[Forms]![Search Page]![Tuscaloosa])) OR ((([Production Locations].Uinta)=[Forms]![Search Page]![Uinta])) OR ((([Production Locations].Utica)=[Forms]![Search Page]![Utica])) OR ((([Production Locations].[Western Canadian])=[Forms]![Search Page]![Western])) OR ((([Production Locations].Woodford)=[Forms]![Search Page]![Woodford])) OR ((([Production Locations].[Wyoming Basins])=[Forms]![Search Page]![Wyoming]));

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    aaargh.....
    that looks like horribly klunky design...
    dio you really mean you have hard coded all those production locations as columns within one table..... tell me it aint so.....

    Fundamentals of Relational Database Design -- r937.com
    The Relational Data Model, Normalisation and effective Database Design
    http://www.informationqualitysolutio...tionPoster.pdf
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jun 2014
    Posts
    27
    The design isn't ideal and I know that because I just really learned access last week but I didn't hard code anything, I used design view, and I am still trying to play with it, but it was working before I added all of the location check boxes, and I'm not sure how it screwed up the rest of the functionality of my search page.

  6. #6
    Join Date
    Jun 2014
    Posts
    27
    The production locations table is basically just company names and a large bank of check boxes with each column being a location

  7. #7
    Join Date
    Jun 2014
    Posts
    27

    Help

    my form has search text boxes for rangesa of numbers and it has checkboxes to return certain yes/no values, but whenever I try to use the text box searches they don't work, even though they worked before I had the checkboxes. Does anyone know what I am doing wrong?

  8. #8
    Join Date
    Jun 2014
    Posts
    27
    also I want to know how I can turn off certain criteria on my form if someone doesnt want to use it, any and all help is greatly appreciated

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    build the query (or t least the where clause or filter bit) on the fly

    Code:
    strWHERE = "WHERE Companies.[MMcf/d] Between [Forms]![Search page]![MMcf/d High] And [Forms]![Search Page]![MMcf/d low] AND Companies.[Hedging Program])=[Forms]![Search Page]![Hedging] AND [S&P].Ordinal) Between [Forms]![Search Page]![SP Rating High] And [Forms]![Search Page]![SP Rating Low] AND Companies.[Market Cap] Between [Forms]![Search Page]![Market Cap High] And [Forms]![Search Page]![Market Cap low] AND (1=1" '1=1 is a dodge to make certaint he following is always true
    if  not isnull([Forms]![Search Page]![Anadarko]) then
      strWHERE = strWhere & " OR [Production Locations].Anadarko=[Forms]![Search Page]![Anadarko]
    endif
    ...add other locations
    strWHERE = strWHERE & ")" 'close of the openign bracket used to form the locations OR
    then add the where clause to your sql

    or if you are using a filter omit the WHERE clause all together and apply a filter

    but to be honest this design stinks.... seriously consider binning it in favour of a normalised design.
    why?
    what happens if you decide to add a new location
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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