locked
query for optional primary key with null value RRS feed

  • Question

  • i have a table payments ,  for real estate application , which save the payment Receipts  for apartments. this table have id column as primary key which identify each payment .

    i make query to search payments by id , amount , building ... ect

    All  Query Parameters are Optional , i add a screen for that query , 

    and add to button to execute and clear the Query , 

    the issue is that when payment id is null i get validation error , saying that i have to enter value for id.

    but the query is working and filtering data , only this validation error is fireing when id is null which is annoying.

    i seems that , this is because the id column is primary and is not null in payment Table design 

    how to solve that issue , to be able to search query for primary with optional null value.

     
    • Edited by omar_awwad Thursday, January 31, 2013 10:27 PM
    Thursday, January 31, 2013 10:26 PM

Answers

  • Is the id data type an integer?  Why would this be relevant to the user?  If it is not relevant remove it.

    If it is used as some sort of payment confirmation number or something, you could make a string field to store a number (based on the id if you want) which would be better suited for this purpose.  To do this, click "write code" and select Payment_Inserted. 

    Me.ConfirmationNumber = Me.Id

    You can do whatever you want with this number in code.

    Remove the id from your query parameters and add the ConfirmationNumber parameter.



    • Edited by Hessc Friday, February 1, 2013 3:43 AM
    • Proposed as answer by Angie Xu Tuesday, February 26, 2013 1:10 AM
    • Marked as answer by Angie Xu Tuesday, February 26, 2013 5:28 AM
    Friday, February 1, 2013 3:38 AM