Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2005
    Posts
    121

    Exclamation Unanswered: Query Driving Me Nuts!

    I have a problem and I need some help.

    I have a form that will display a store number (numeric) in a field on the form. From that form, there is a button to open another form displaying information for trailers loaded for that store number. Form uses a query to select records using the store number showing on the first form as criteria.

    Problem is, for some reason, the form shows store 104, the query is finding 104, 1043, 1049 as well. I have tried several approaches on the query to have it just lock in on 104, but with no success.

    Any help? The query criteria is asking for [Forms]![PrintBOL]![Store]. Store is a numeric field.

    Thanks guys!

  2. #2
    Join Date
    Dec 2005
    Posts
    121
    I guess I need to explain further....

    The query is set up to either use the store number field if data is present, or use a load number field. The form is set up that if one is showing, the other field must be null. So if I select a store number, the load number field is blank. If I select a load number, the store number field is blank. Based on Access Help, I set up the criteria in each field as follows

    Store field on query:
    Like [Forms]![PrintBOL]![Store] & "*"

    Load Number field on query:
    Like [Forms]![PrintBOL]![Load#] & "*"

    The load number part works fine, if the associate selects a load number, they get that load number, but if they select the store number, they are getting not only 104, but 1043, 1049 as well as 104. I don't want to create 2 different querys and 2 different load selection forms unless it's absolutly necessary, so I tried to have the query use either field on the selection form. Hope this makes it a little more clear.

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    The Like & "*" is what is giving you problems on the Load Number field. You probably can't change it to =Forms!PrintBOL!Load# because you're using the same query of criteria on 2 fields. A way around this is to:

    In the field row of your query for the column (Load#), put in a statement like this:

    LN: IIf(IsNull(Forms!PrintBOL!Load#),"X",[LoadNumber])

    then in the criteria of this column, put something like this:

    Like [Forms]![PrintBOL]![Load#] & "*" Or Like "X"

    (Note: this column would be unchecked to show on the query, then drop down your Load# field in a separate column)

    What you're doing is basically fooling the query. If the field in the form is left blank, you make the column show an X (or any other character). If it's not blank, show the value of the field. Your criteria then is set on either the Like X or the value of the field.
    Last edited by pkstormy; 05-04-07 at 11:21.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Dec 2005
    Posts
    121
    Paul,
    That sounds like it should work for me based on what I am trying to do. Thanks for the idea, I'll put it to the test.

    Bob

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    It's a neat little trick I picked up through trial and error and having the same need you have. Not sure if it's documented in any books or on the web (if it's on the web, I'd be interested in seeing it if anyone can supply a weblink.) Hope it works for you.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Dec 2005
    Posts
    121
    To all (including Paul)

    I tried your concept on this database but ran into some problems.

    I have a query driven off a form with 2 fields. The query pulls numerous fields. There are two fields we wanted to drive a report off of. One was a DC number field [DCNumber]. The other is a Month field [Month] to indicate the fiscal month the record being entered pointed to.

    I have 3 other fields with set criteria, a [Date] field that is set to query the current fiscal year (>=#02/04/2007# And <=#02/02/2008#), A [Type] field which is set to 1, and a [Decon] field which is set to (Is Not Null and <>"None"). Those will always be constant in the query no matter what.

    I wanted the report from the query to look to a form that shows 2 drop down lists, DC Number and Month and either select a month and DC number from the list to query for that selection, or if the drop down list was left blank in either catagory, it would pull all records.

    I tried using the above method setting up two new columns and entering the information as you have shown. I get my [Type] records with 1, I get the date range I set Ok, The [Decon] fields are ok, no empy or none records, when I leave the fields on the form with the drop down lists blank.

    When I select a DC number from the drop down, I still get all the DC records, not just the one I selected from the drop down list. I removed the DC number and asked for a month (March) and got every month so far this year.

    I'm confused, what am I missing here? Please help!

    Never Mind, I must be low on Caffiene today, I found my problem, point to the wrong field. I'll get this right one of these days!
    Last edited by Bob.Carter.17; 05-11-07 at 14:33.

  7. #7
    Join Date
    Sep 2006
    Posts
    265
    I know this is slightly out of context to the discussion but I would ensure that the length of the store is always four: 104 > 1040.

    Simon

Posting Permissions

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