Results 1 to 10 of 10

Syntax If Exists

  1. #1
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    119

    Syntax If Exists

    The below code throws an error on this line: If [RetLocationID] Like "S*" Then.
    I thought it was because there are times when no values will be present.


    In the query I'm checking the field [RetLocationID] for Null and replacing with "A", but it still throws an error.
    So clearly I'm doing something wrong.
    What am I doing wrong?

    Code:
    RetLocationID: Nz([POHeader]![RetLocationID],"A")
    Code:
     If [RetLocationID] Like "S*" Then
            Me!BarCodeMsg.Visible = True
            Me!BarCode.Visible = True
            Me!BarCodeReceiptID.Visible = True
            Me!StoreAssociate.Visible = True
            Me!StoreAssocInst.Visible = True
        Else
            Me!BarCodeMsg.Visible = False
            Me!BarCode.Visible = False
            Me!BarCodeReceiptID.Visible = False
            Me!StoreAssociate.Visible = False
            Me!StoreAssocInst.Visible = False
        End If
    thanks!

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,719
    Knowing what the error description is might help. Is the form bound to that query? Is there a control on the form bound to that field?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    119
    Screenshots of the errors attached.

    Code:
    [RetLocationID] is a field in the page footer and tied to the same field name in the report query.

    thanks!
    Attached Thumbnails Attached Thumbnails S1.jpg   S2.jpg  

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,719
    Have you tried referring to the textbox similar to the others?

    Me.[RetLocationID]

    Can you attach the db here?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    119
    I think I approached this wrong. When I populate the Report's table/query with data it works as expected. What I'm not able to do is open the report, in print view, when there are no records, without triggering the error. The error is caused by that first line. So I guess what I'm asking is if there are no records, how do I structure the code to not run or not evaluate the data?

    thanks!

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,719
    Typically you'd have this in the No Data event of the report:

    Cancel = True

    and trap for error 2501 in the code that opens the report.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    119
    I did a bit of Googling for your suggestion and it appears it will stop opening, stop print or close. I want the empty form to open without getting the error. I know diddly about VBA but the below code is what I think I'm trying to accomplish. See attached screenshot of the error I get. I'm pretty sure it's a syntax issue. So, if RetLocationID is null, how do I stop that code from running?

    Code:
    If IsNull(Me!RetLocationID) Then
            End Sub
        Else
            If Me!RetLocationID Like "S*" Then
            Me!BarCodeMsg.Visible = True
            Me!BarCode.Visible = True
            Me!BarCodeReceiptID.Visible = True
            Me!StoreAssociate.Visible = True
            Me!StoreAssocInst.Visible = True
        Else
            Me!BarCodeMsg.Visible = False
            Me!BarCode.Visible = False
            Me!BarCodeReceiptID.Visible = False
            Me!StoreAssociate.Visible = False
            Me!StoreAssocInst.Visible = False
        End If
        End If
        
    End Sub
    thanks!
    Attached Thumbnails Attached Thumbnails S1.jpg  

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,719
    I see where you're heading but I don't think it will work. The correct syntax would be:

    Code:
      If Not IsNull(Me!RetLocationID) Then  
        If Me!RetLocationID Like "S*" Then
          Me!BarCodeMsg.Visible = True
          Me!BarCode.Visible = True
          Me!BarCodeReceiptID.Visible = True
          Me!StoreAssociate.Visible = True
          Me!StoreAssocInst.Visible = True
        Else
          Me!BarCodeMsg.Visible = False
          Me!BarCode.Visible = False
          Me!BarCodeReceiptID.Visible = False
          Me!StoreAssociate.Visible = False
          Me!StoreAssocInst.Visible = False
        End If
      End If
    But I suspect that if no records are returned the value won't be Null, it won't exist at all. You could put a hidden textbox in the report footer with:

    =Count(*)

    and test that for being greater than 0.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    119
    You are correct. It caused an error as well. I will go with the =Count(*) textbox.

    Much appreciated!

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,719
    Happy to help! I think that will work, but post back if not.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Guide for interpreting MSDN / MS documentation syntax-syntax !!
    By pisorsisaac@gmail.com in forum Access
    Replies: 4
    Last Post: 11-18-2017, 07:07 AM
  2. Convert SQL Server Syntax To Access Query Syntax
    By chalupabatman in forum Queries
    Replies: 1
    Last Post: 10-18-2017, 08:53 PM
  3. Not exists
    By ASWilliams in forum SQL Server
    Replies: 1
    Last Post: 07-20-2016, 03:49 AM
  4. Replies: 10
    Last Post: 10-30-2013, 02:06 PM
  5. Incomplete Syntax Clause (syntax error)
    By ajetrumpet in forum Programming
    Replies: 4
    Last Post: 09-11-2010, 10:47 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
  •  
Tech Forums: Microsoft Office Forums