Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2010
    Posts
    25

    Unanswered: Getting Enter Value prompt when not asked/setup

    I am getting a prompt in a query to Enter Value when I have not asked for. This comes up only when I enter the Like function.

    Can someone please please help. I dont understand what is wrong

    thanks
    Sebastian

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can you post your query?
    George
    Home | Blog

  3. #3
    Join Date
    Aug 2010
    Posts
    25
    Max: IIf([Week1QB].[Days_Receipt]>[Week1QB].[Days_Invoiced],[Week1QB]![Days_Receipt],[Week1QB]![Days_Invoiced])

    Aging: IIf([Max]>90,">90 days",IIf([Max]>=30 And [Max]<60,"30 - 60 days",IIf([Max]>=60 And [Max]<=90,"60 - 90 days",IIf([Max]>=15 And [Max]<30,"15 - 30 days","<15 days"))))

    So when I add the "Like" (Like [Forms]![ZFiagedfrm]![cboaging] & "*")function it prompts me for "Max". If I remove it everything works fine.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what do you think happens when you use [max]?
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Aug 2010
    Posts
    25
    there are two columns with total # of days. One is days since receipts and other is since Invoiced so which every is the highest is what Max is get for me.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    OK
    so how do you think
    the highest is what Max is get for me.
    works
    its not an Access function you are suign there., if you think its then you are usign the incorrect syntax.

    what's happening is the SQL parser doesn't recognise [max] and therefore assumes that this is a user prompt, so its expecting you to give a value for [max]

    again we woudl need to see the SQL to be absolutely certain
    so what I'd suggest you do is open the query in design mode, then switch the view (leftmost button under file) to SQL view and paste the SQL here.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Aug 2010
    Posts
    25
    here the SQL code
    SELECT DISTINCTROW Week1QB.Report_Date, [All Location List].Inventory_Analyst, [All Location List].Report_Analyst, Week1QB.Combo, Week1QB.Plant_code, Week1QB.[PO_#], Week1QB.[Line_#], Week1QB.[Material_#], Week1QB.[Vendor_#], Week1QB.Ordered_Quantity, Week1QB.UOM, Week1QB.Qty_Receipts, Week1QB.Qty_Invoiced, Week1QB.Days_Receipt, Week1QB.Days_Invoiced, IIf([Week1QB].[Qty_Receipts]=0,"Missing GR",IIf([Week1QB].[Qty_Invoiced]=0,"Missing IR",IIf([Week1QB].[Qty_Receipts]>[Week1QB].[Qty_Invoiced],"Qty desc","Qty desc"))) AS Issues, IIf([Max]>90,">90 days",IIf([Max]>=30 And [Max]<60,"30 - 60 days",IIf([Max]>=60 And [Max]<=90,"60 - 90 days",IIf([Max]>=15 And [Max]<30,"15 - 30 days","<15 days")))) AS Aging, Week1QB.Complete, Week1QB.Comments
    FROM [All Location List] INNER JOIN (Week1QB LEFT JOIN Week5QB ON Week1QB.Combo = Week5QB.Combo) ON [All Location List].Plant_Code = Week1QB.Plant_code
    WHERE ((([All Location List].Inventory_Analyst) Like [Forms]![ZFiagedfrm]![cboIA] & "*") AND (([All Location List].Report_Analyst) Like [Forms]![Zfiagedfrm]![cboReportAnaly] & "*") AND ((Week1QB.Plant_code) Like [Forms]![Zfiagedfrm]![cboplant] & "*") AND ((Week1QB.[PO_#]) Like [Forms]![Zfiagedfrm]![txtpo] & "*") AND ((Week1QB.[Material_#]) Like [Forms]![Zfiagedfrm]![txtmat] & "*") AND ((IIf([Week1QB].[Qty_Receipts]=0,"Missing GR",IIf([Week1QB].[Qty_Invoiced]=0,"Missing IR",IIf([Week1QB].[Qty_Receipts]>[Week1QB].[Qty_Invoiced],"Qty desc","Qty desc")))) Like [Forms]![ZFiagedfrm]![cboissues] & "*") AND ((IIf([Max]>90,">90 days",IIf([Max]>=30 And [Max]<60,"30 - 60 days",IIf([Max]>=60 And [Max]<=90,"60 - 90 days",IIf([Max]>=15 And [Max]<30,"15 - 30 days","<15 days"))))) Like [Forms]![ZFiagedfrm]![cboaging] & "*") AND ((Week1QB.Complete) Like [Forms]![ZFiagedfrm]![cbocomp] & "*"));

  8. #8
    Join Date
    Aug 2010
    Posts
    25
    Can any one help me with my problem. Please

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the problem is that you cannot use a column alias in the same select statement as you defined the alias
    what you could do is use a sub select to generate the value for the alias 'max', and then use that in the top level query

    Id also be wary of using an alias such as max, as its one of the reserved words in JET
    List of reserved words in Access 2002 and in later versions of Access
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Aug 2010
    Posts
    25
    Problem solved Finally. What I did was just pasted the max condition in the Aging and everything worked just fine.

    Thanks everyone for your time and effort to respond back and help

Posting Permissions

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