Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2002
    Location
    UK
    Posts
    3

    Unhappy Unanswered: Queries with missing data

    I am setting up a database which, for some of the records, the fields will be blank.
    When cusomers search the database, via a web form, I want them to be able to select search criteria from drop-down lists, but they don't have to fill all of the details in. They do, however, have to fill in at least four of the six possible criteria.
    i.e. partial records and partial searches
    Do I use zeros in the database table where there are missing data (they are number fields) and search on exact matches and zeros, or nulls? How do I tell the query to only run if there are at leat four of the six criteria filled in by the customer?
    I have put this onto a webpage, http://www.geocities.com/alskilini/ which will hopefully explain this better - please let me know if anything needs clarifying

    Kind Regards
    Al

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    You can use client-side scripting to determine how many listboxes have been selected - then enable the search button. Why not create your where clause dynamically based on what the user selected ?

  3. #3
    Join Date
    Aug 2002
    Location
    UK
    Posts
    3

    Re: Queries with missing data

    I've tried, but I can't seem to work out the main problem of displaying records even if some of the info is missing, i.e. partial records and partial searches. (I can't create'on the fly' SQL either)

    My query has the following statements:
    [Forms]![Search Form]![X] Or Is Null
    where X = A-G

    This will return records which have the exact fields as the search criteria or have nothing entered i.e. 1-5 or null

    For each particular search criteria, the Search Form VBA code looks like this:
    Private Sub A_AfterUpdate()
    If Me![A] = Null Then
    [Forms]![Search Form]![A] = [##]
    Else: [Forms]![Search Form]![A] = [A]
    End If
    End Sub

    but it seems to work the other way round to what I'd like.

    Basically, for two records:
    Brown, A=blank, B=3, C=5
    Smith, A=4, B=3, C=5

    I would like it to search on the criteria, A=4, B=3, C=5 and return the BOTH records


    I'll try the count function when I get this working!
    Thankyou for helping
    Ali
    (Access user for about 3 days now!!)

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    When would me![a] be null ? Also, how are you constructing the where clause for [search form] ? What does your query look like after all the selections have been made ? The query should work with a where clause including colx = [Forms]![Search Form]![X] Or colx Is Null. Can you attach your mdb ?

  5. #5
    Join Date
    Aug 2002
    Location
    UK
    Posts
    3

    Re:Queries with missing data

    Originally posted by rnealejr
    When would me![a] be null ? Also, how are you constructing the where clause for [search form] ? What does your query look like after all the selections have been made ? The query should work with a where clause including colx = [Forms]![Search Form]![X] Or colx Is Null. Can you attach your mdb ?
    I've tried the above syntax but get returned the exact opposite records that I would like. I've updated the descriptive webpage at www.geocities.com/alskilini/ and to correspond to the example database (attached).
    I'll post any answers on the webpage also (and hopefully the solution!)
    Cheers Al
    Attached Files Attached Files

Posting Permissions

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