Results 1 to 4 of 4
  1. #1
    SaskiFX is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Mar 2013
    Posts
    30

    Question Switch not returning what I expected, all I get is #Error

    I have a Form, and on that form I have a sub form that is being generated by a Query, and another Query nested under that one. (The form is going to be used for showing prices for parts from different vendors.)



    When I look at my parts list table, I have a Quantity field that is a text format, as I can have a static quantity of an item such as 12, or I have entries EST or AS for Estimated or As Required. I have another table that has the list of vendors, and another one that has prices for each item from each vendor. Everything is working as I want, but I have an issue with the AR and EST entries.

    Currently when I click the + to expand an entry that has an actual numerical quantity, everything works. If there is an AR or EST I would like it to sub in a 1 for the Quantity, but right now I just get an error.

    Here is the currently SQL code for the Query:

    Code:
    SELECT tblPrice.Cost AS [Cost Each], 
    tblVendors.[Vendor Name], 
    SWITCH([Quantity] LIKE 'AR',[Cost Each],[Quantity] LIKE 'EST',[Cost Each],[Quantity] NOT LIKE '0',tblPrice.Cost*[Quantity]) AS [Total Cost],
    tblBOM.[Part Number], 
    tblPrice.[Price Date]
    FROM tblVendors INNER JOIN ((tblParts INNER JOIN tblBOM ON tblParts.[Part Number] = tblBOM.[Part Number]) INNER JOIN tblPrice ON tblParts.[Part Number] = tblPrice.[Part Number]) ON tblVendors.VendorID = tblPrice.VendorID
    ORDER BY tblPrice.Cost;
    Below is how it looks on my screen if it helps.

    Click image for larger version. 

Name:	image3.png 
Views:	12 
Size:	16.1 KB 
ID:	13173

    I have tried all the GoogleFu I know, and I'm stumped. Any ideas?

    Thanks!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,619
    LIKE operator is meaningless without wildcard, might as well just use = sign.

    SWITCH([Quantity] LIKE 'AR*' OR [Quantity] LIKE 'EST*',[Cost Each], [Quantity]<>'0',[Cost]*[Quantity])

    or

    IIf([Quantity] LIKE 'AR*' OR [Quantity] LIKE 'EST*', [Cost Each], Cost*[Quantity])

    Quantity is a text field?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Dal Jeanis is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Code:
    SWITCH(
    [Quantity] LIKE 'AR',[Cost Each],
    [Quantity] LIKE 'EST',[Cost Each],
    [Quantity] NOT LIKE '0', tblPrice.Cost*[Quantity]) 
    AS [Total Cost],
    What happens in that switch statement when [Quantity] is LIKE '0'?

    You might want to add ",True, 0" immediately before the close paren in your switch statement.

  4. #4
    SaskiFX is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    30
    Quote Originally Posted by June7 View Post
    IIf([Quantity] LIKE 'AR*' OR [Quantity] LIKE 'EST*', [Cost Each], Cost*[Quantity])

    Quantity is a text field?
    The IIf statement worked! The Switch statement didn't. I had tried looking into an IIf statement but I was making things overly complicated.

    Quantity is a text field so it can hold AR and EST as well as numbers. When it's displayed things seem to work ok.

    Quote Originally Posted by Dal Jeanis View Post
    Code:
    SWITCH(
    [Quantity] LIKE 'AR',[Cost Each],
    [Quantity] LIKE 'EST',[Cost Each],
    [Quantity] NOT LIKE '0', tblPrice.Cost*[Quantity]) 
    AS [Total Cost],
    What happens in that switch statement when [Quantity] is LIKE '0'?

    You might want to add ",True, 0" immediately before the close paren in your switch statement.
    In this setup the Quantity should never be 0, I was using that for a True if everything else fails bit, since some help page on Switch suggested I make sure something evaluated true or the Switch might not work.

    Thanks so much on this one, I know my way around programming but am a novice to applying it in access.


Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 04-26-2013, 01:37 PM
  2. Error on too few parameters expected 3
    By haishuoBB in forum Programming
    Replies: 7
    Last Post: 02-27-2013, 03:26 PM
  3. Query not returning all expected results
    By amenitytrust in forum Queries
    Replies: 6
    Last Post: 11-05-2012, 07:13 AM
  4. Expected End of Statement error
    By SamNotSoWise in forum Programming
    Replies: 5
    Last Post: 06-25-2012, 04:41 PM
  5. Runtime Error 3061 Expected 3
    By kumail123 in forum Programming
    Replies: 1
    Last Post: 03-28-2012, 09:44 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums