Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2003
    Posts
    5

    Unanswered: Question with building a form and a query

    I have attached a database I am working on. It is one of the first ones I have created for my own personel use. It is a Journal database. I am having a problem with my query and was hoping someone could give me some pointers.

    I have created a form with all of my search criteria and pass this on to the query. But it does not work correctly, I suspect because the form is passing a "blank" on if the field wasn't filled out.

    For example, you choose a date and a jeyword to search by, but you leave the time field empty. Does it still pass on Zeros if a text field or combo box is blank?

    Is there a way to disable a combo box if no selection is made?

    Any help is appreciated. Take a look at the attachment and feel free to give me some constructive critisism.

    -coty

  2. #2
    Join Date
    Nov 2003
    Posts
    5

    followup

    Sorry about the attachment. It didn't save it when I chose to preview the message. Here it is.
    Attached Files Attached Files

  3. #3
    Join Date
    Nov 2003
    Posts
    5
    ok, I tested that attachment. it's good to download.

  4. #4
    Join Date
    Oct 2003
    Location
    São Paulo - Brazil
    Posts
    91
    Have you tried to add this instruction in your querie:

    [forms]![Search]![Year] or isnull( [forms]![Search]![Year] ) = true

    I didn´t test but, at least, null values will be added.
    My environment: Windows XP/ Access 2000 - Using Microsoft DAO 3.6 Library

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Create the query dynamically, then dump it in as the record source during the OnOpen event for the report. I think the problem you're running into here is you're asking the query to return records according to the value of the fields on the form. If the value of the field on the form is null, then it's looking only for records with a null value in whatever field you're searching. Here's an example one way to do it:


    Private Sub Report_Open(Cancel As Integer)
    Dim frm As Form
    Dim SQL As String

    Set frm = Forms!Search

    SQL = "SELECT [Entries Table].[Entry Date], [Entries Table].[Mood Key], [Entries Table].[Journal Entry] " & _
    "FROM [Entries Table] WHERE ([Entries Table].[Entry Date] BETWEEN #" & frm!Date1.Value & "# And #" & frm!Date2 & "#)"

    If IsNull(frm!Mood.Value) = False Then SQL = SQL & "AND ([Entries Table].[Mood Key] = " & frm!Mood.Value & ") "
    If IsNull(frm!Keyword1) = False Then SQL = SQL & "AND ([Entries Table].[Journal Entry] Like '*" & frm!Keyword1 & "*')"

    Me.RecordSource = SQL

    End Sub

    The "If IsNull(field) = False Then" statement will simply leave that criteria off all-together as opposed to searching for a null record in your table.
    Last edited by Teddy; 11-05-03 at 13:28.

  6. #6
    Join Date
    Nov 2003
    Posts
    5
    Originally posted by Mixirica

    [forms]![Search]![Year] or isnull( [forms]![Search]![Year] ) = true


    I used this code in the Criteria fields and it worked perfectly on all except the date field. Here is where I ran into trouble...

    I have a criteria setup as so:
    Between [forms]![Search]![Date1] And [forms]![Search]![Date2]

    I added your code to the end like this:
    Between [forms]![Search]![Date1] And [forms]![Search]![Date2] or isnull( [forms]![Search]![Date1] ) = true And ( [forms]![Search]![Date2] ) = true


    When I ran the first search it created the query matrix just fine, but I still am not getting the results I need. It still requires an entry in the date field otherwise it is searching for a null entry.

    I was going to try Teddy's code, but I admit I don't know enough to set this up. Is the code going to be in the onload event of the report? Trigger it with a button from the search form? I am afraid I don't understand it enough to get it working.

    I appreciate both of your responses though. Thank you.

  7. #7
    Join Date
    Oct 2003
    Location
    São Paulo - Brazil
    Posts
    91
    No, the code is going to be in the onload REPORT event.

    Another advice: Try to learn Teddy´s way. It´s better.
    My environment: Windows XP/ Access 2000 - Using Microsoft DAO 3.6 Library

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I'm quite a novice at VB myself. However, this little bit of code is fairly straightforward. If you would like, feel free to pm me and I would be more then happy to walk you through it.

    **One thing I like to do is create a form called "debug" with a single text box called "txtDebug". Then if there's ever a question as to what the final query being returned is, open the "debug" form and put "forms!debug!txtDebug.Value = Me.recordsource" in the onload event of a report. It's a quick way to see if there's any odd syntax errors popping up.
    Last edited by Teddy; 11-05-03 at 13:31.

Posting Permissions

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