Results 1 to 13 of 13
  1. #1
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Parameter Query (97 VR1)

    Sorry, but you'll have to post the SQL for the query before anyone can figure out why you get an error.
    Charlotte

  2. #2
    PFI
    Guest

    Re: Parameter Query (97 VR1)

    I'm sorry, here is my query SQL:
    SELECT [QryCustomerName&Address].Status, QryBatteryPlan1.[1 Cycle], QryBatteryPlan1.[Batt Date], [QryCustomerName&Address].[Cust #], QryBatteryPlan1.[Batt Prepay], QryBatteryPlan1.BattType, QryBatteryPlan1.NumberofBatteries, [Batt Prepay]-[NumberofBatteries] AS Remaining, FirstOfMonth([Batt Date],[1 Cycle]) AS BatteryDueDate
    FROM [QryCustomerName&Address] INNER JOIN QryBatteryPlan1 ON [QryCustomerName&Address].[Cust #] = QryBatteryPlan1.[Cust #]
    WHERE ((([QryCustomerName&Address].Status) Like "Current" Or ([QryCustomerName&Address].Status)="REFERRAL" Or ([QryCustomerName&Address].Status)="VET" Or ([QryCustomerName&Address].Status)="Vet Staff" Or ([QryCustomerName&Address].Status)="REF." Or ([QryCustomerName&Address].Status)="REF.." Or ([QryCustomerName&Address].Status)="PITA") AND (([Batt Prepay]-[NumberofBatteries])>=1) AND ((FirstOfMonth([Batt Date],[1 Cycle]))=[Enter Battery Due Date:]));

  3. #3
    PFI
    Guest

    Parameter Query (97 VR1)

    On the database (my first) I'm working on , I have a query that gives me each customer and the type of battery he uses and the date they purchased the battery. I have created a calulated field to tell me when the next battery is due. I used a function to do this:
    <pre> Function FirstOfMonth(InputDate As Date, intCycle As Integer)
    Dim D As Integer, M As Integer, Y As Integer


    D = Day(InputDate)
    M = Month(InputDate)
    Y = Year(InputDate)
    If D > 20 Then ' If day > 20th then month is advanced by one.
    M = M + 1

    FirstOfMonth = DateSerial(Y, M + intCycle, 1)
    Else
    FirstOfMonth = DateSerial(Y, M + intCycle, 1)
    End If

    End Function</pre>

    This works just fine, but the trouble happens when I want to run the query for a specified "Battery Due Date" If I type that request in the criteria line I get Error 3071 Expression is typed incorectly or is too complex to be evaluated. I've tried many different ways, but I know dates are tricky to work with. I would greatly appreciate any help I can get.
    Thanks!
    PFI

    Edited by Charlotte to improve code readability

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Parameter Query (97 VR1)

    Try this instead. I aliased to two tables to simplify reading the code and to eliminate the need for all those brackets around the table names. Plus alias seem to make the query more efficient. The In operator is also a lot faster than the individual conditions joined by Or.

    I've seen the "too complex" error before when you use a calculated expression in a where clause without referencing the specific table the field belongs to.

    SELECT C.Status, B.[1 Cycle], B.[Batt Date], C.[Cust #], B.[Batt Prepay],
    B.BattType, B.NumberofBatteries, [Batt Prepay]-[NumberofBatteries] AS Remaining,
    FirstOfMonth([Batt Date],[1 Cycle]) AS BatteryDueDate
    FROM [QryCustomerName&Address] AS C
    INNER JOIN QryBatteryPlan1 AS P
    ON C.[Cust #] = P.[Cust #]
    WHERE C.Status In ("Current" , "REFERRAL", "VET", "Vet Staff", "REF.", "REF..", "PITA")
    AND B.[Batt Prepay]-B.[NumberofBatteries]>=1
    AND FirstOfMonth(B.[Batt Date],[1 Cycle])=[Enter Battery Due Date:];
    Charlotte

  5. #5
    PFI
    Guest

    Re: Parameter Query (97 VR1)

    I'm not quite sure I understand "aliasing" or how to plug that into a query.
    Should I replace the sql statement I'm now using with the one you are suggesting or isn't it that simple?

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Parameter Query (97 VR1)

    Aliasing of tables in SQL is just like aliasing a field or an expression. When you have "[Batt Prepay]-[NumberofBatteries] AS Remaining" in your SQL, you're creating an alias, "Remaining" for the expression "[Batt Prepay]-[NumberofBatteries] ". You alias tables the same way. The alias gives you a handy label to use when referring to the table or expression.

    You can do this easily in the query grid itself by opening the properties sheet for one of the tables and changing the alias from the actual table or query name to whatever you want to call it. I usually use 1-letter aliases to keep the SQL more readable. When you do it this way, the query engine will change the SQL to use the new alias instead of the actual table name except in the FROM clause where it *must* know the actual name of the table or query that's being used.

    Try creating a new query using the SQL I posted and see whether that will run. If it works, then just replace the old query with that one.
    Charlotte

  7. #7
    PFI
    Guest

    Re: Parameter Query (97 VR1)

    I tried your suggestions, but still get the same error.
    I have been working around this problem by storing the calculated date, and then there is no problem using the due date as a parameter. I no storing calculated data is a no no but so far that's all I've got.
    Any more suggestions would be greatly appreciated.
    Thank You

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Parameter Query (97 VR1)

    Post the SQL as it appears when you add the criteria. Otherwise, there's no way to tell what's wrong.
    Charlotte

  9. #9
    PFI
    Guest

    Re: Parameter Query (97 VR1)

    Here is how the query looks now:
    SELECT B.Status, A.[1 Cycle], A.[Batt Date], B.[Cust #], A.[Batt Prepay], A.BattType, A.NumberofBatteries, [Batt Prepay]-[NumberofBatteries] AS Remaining, FirstOfMonth([Batt Date],[1 Cycle]) AS BatteryDueDate
    FROM [QryCustomerName&Address] AS B INNER JOIN QryBatteryPlan1 AS A ON B.[Cust #] = A.[Cust #]
    WHERE (((B.Status) In ("Current","REFERRAL","VET","Vet Staff","REF.","REF..","PITA")) AND ((FirstOfMonth([Batt Date],[1 Cycle]))=[Enter Battery Due Date:]) AND (([A].[Batt Prepay]-[NumberofBatteries])>=1));
    Do you think using the DLOOKUP function is a posibility?
    Thanks

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Parameter Query (97 VR1)

    I think you may be still having reference problems. You should always fully reference the fields in the Where conditions. Try this:

    SELECT B.Status, A.[1 Cycle], A.[Batt Date], B.[Cust #], A.[Batt Prepay], A.BattType, A.NumberofBatteries, A.[Batt Prepay]-A.[NumberofBatteries] AS Remaining, FirstOfMonth(A.[Batt Date],[1 Cycle]) AS BatteryDueDate
    FROM [QryCustomerName&Address] AS B INNER JOIN QryBatteryPlan1 AS A ON B.[Cust #] = A.[Cust #]
    WHERE (((B.Status) In ("Current","REFERRAL","VET","Vet Staff","REF.","REF..","PITA")) AND ((FirstOfMonth(A.[Batt Date],[1 Cycle]))=[Enter Battery Due Date:]) AND ((A.[Batt Prepay]-A.[NumberofBatteries])>=1));
    Charlotte

  11. #11
    PFI
    Guest

    Re: Parameter Query (97 VR1)

    Hi Charlotte;
    I gave it a try and I'm still getting the same error. I wonder if its how I'm requesting the date or if I can use another function that would be more simple.
    Thanks

  12. #12
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter Query (97 VR1)

    I just recently got a variety of error messages including the "Expression is typed incorectly or is too complex " message involving a date and a parameter (in Access 97). After tracking down a couple of KB that might possibly relate to the problem and tearing my hair out, I finally discovered the problem -- BAD DATA. I had tested my query with my test data, but as soon as I imported the _real_ data, the holes in the data appeared. You might look for null data in the field that you are creating the calculated field in your query.
    Pat

  13. #13
    PFI
    Guest

    Re: Parameter Query (97 VR1)

    You might be on to something. I created a new database and used my function to determine the first of the month, and I had no trouble using that field as a parameter. When I cut and pasted some dates from the real data it gave me an error. The data I'm using was data I imported from an old QA4 database. When I pasted some of the dates into my practice database I noticed them comming in as times and when I set the Date/Time to "short date" the time disapeared, but maybe it needs to be stripped from the date. I didn't have time to check that out.

Posting Permissions

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