Results 1 to 13 of 13
  1. #1
    rhubarb is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    76

    Value you entered isn't valid for this field

    I have a form I am using to filter and display reports. I've attached a stripped-down version to this post. If you open the database and open the Reports form and Select "Billable" or select "Ticket Number" then make selection from the "Filter tickets" combo box and click on "Preview" it will show the proper reports. If you close the reports and select any other selection but "Ticket Number" or "Billable" and Filter again and hit preview you receive this error: Value you entered isn't valid for this field.



    It's as if it is expecting a number for some reason and I'm not sure why. If I close and re-open and select any other report it works fine. It's only if I select Billable or Ticket Number. It's as if the number gets cached in lstTicketFilter and calculates before I make another selection from the lstTicketReport. Any direction or help is appreciated!

    Also is there any way to not have Billable (Yes/No) transform into a -1 or 0? It's a cosmetic annoyance. As is the blank in the Return to Filter Tickets combo box. Not sure how or if I can change or fix but may be easy and I'm missing.

    Thank you!

  2. #2
    rhubarb is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    76
    I've resolved the blank in the Return_To field by adding WHERE [Tickets].[Return to] is not null; to the filter row source. It seems the Billable -1/0 is just how access is going to display this especially as I am pulling directly from table values. I can ignore it.

    The main error, however, still persists. So any help with that is appreciated.

  3. #3
    Bob McClellan is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2011
    Location
    New Jersey
    Posts
    17
    downloaded your accdb and tried to open. I don't have 2010. ( can you save it as 2007 ).

  4. #4
    rhubarb is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    76
    accdb is the default format for 2007 and 2010.

  5. #5
    Bob McClellan is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2011
    Location
    New Jersey
    Posts
    17
    then the file you uploaded is corrupt.

  6. #6
    rhubarb is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    76
    okay I zipped again and attached. I even attempted to unzip and run and was able to do so without issue. I don't think there is any 2010 functionality that I added that would make it so you would be unable to open.

  7. #7
    Bob McClellan is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2011
    Location
    New Jersey
    Posts
    17
    sorry... downloaded again. same problem. I have a multitude of apps running here so I know that my access2007 is working fine. I am unable to open your application.
    Hopefully someone else can help.

  8. #8
    rhubarb is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    76
    Okay, thanks for trying.

  9. #9
    Bob McClellan is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2011
    Location
    New Jersey
    Posts
    17
    try using chr(34) in your filter.. chr(34) = ".
    Then all filters will resolve to string.

    and as far as the ..."Billable (Yes/No) transform into a -1 or 0?"
    try using iif.
    iif(Billable = "yes",-1,0)

    hth,
    ..bob

  10. #10
    rhubarb is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    76
    I am not sure I understand. Where would I put chr(34)=?

    This is my function to filter to lstTicketReport into the lstTicketFilter.

    Private Sub InitFilterItems()

    Me.lstTicketFilter.RowSource = DLookup("[Filter Row Source]", "Ticket Reports", "[Group By]='" & Nz(Me.lstTicketReport) & "'")
    Me.lstTicketFilter = Null

    End Sub


    with the function being called below:

    Private Sub lstTicketReport_AfterUpdate()

    InitFilterItems
    Me.lstTicketReport.Requery
    End Sub

  11. #11
    rhubarb is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    76
    I'm attaching my updated database as I have cleaned up a lot of the code and feel it is a bit easier on the eyes.

    I am not sure if the issue is stemming from the "Tickets Reports" table or perhaps a problem with a data type of one of my fields from the "Tickets" table.

    Please help!

  12. #12
    Bob McClellan is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2011
    Location
    New Jersey
    Posts
    17
    sorry for the delay in this reply.
    I can see by your post: DLookup("[Filter Row Source]", "Ticket Reports", "[Group By]='" & Nz(Me.lstTicketReport) & "'")
    that you are already addressing the filter as a string. The '" & "'" basically does the same as the chr(34) would do.

  13. #13
    rhubarb is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    76
    So maybe the issue is the DLookup() is returning a string. On my report form: 3/6 fields are returning a text data type, 1/6 (date) is not returning at all, and the 2 that are giving me trouble, if selected first, return numbers. Billable returns a Yes/No which evaluates to -1,0 and Ticket Number evaluates to a number.

    Perhaps I need a separate DLookup() to return numbers and only use it when those two are selected? I'll try this and post back.

    If Billable or Ticket Number are selected first it makes my control lstTicketFilter a number format. If I attempt to pull a string after it complains. It's starting to make sense I just need to think about how to apply a solution.

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

Similar Threads

  1. Replies: 4
    Last Post: 08-25-2011, 07:57 AM
  2. Replies: 14
    Last Post: 05-27-2011, 02:35 PM
  3. Replies: 8
    Last Post: 11-12-2010, 10:55 AM
  4. Replies: 17
    Last Post: 03-09-2010, 07:00 AM
  5. Replies: 1
    Last Post: 07-21-2009, 03:01 PM

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