Results 1 to 9 of 9

How to control Bill Number

  1. #1
    deepakg27 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    48

    How to control Bill Number

    HI guys,

    I am working on a simple billing database. I am having a tblBill, which has access defined PK and I have also added a field BillNumber , which has property set to Indexed & No duplicates. Bill Number is entered by the user.

    Now Supposedly last bill number is 51 dated 5th-January-2018, Now I want to control that Bill Number 52 cannot be dated prior to 5th-January-2018. I am using forms for the input.

    Kindly guide me in the right direction.

    Thanks and Regards
    Deepak Gupta

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    42,926
    Could try using the ValidationRule property: >=DMax("BillDate","tblBill"), otherwise VBA in some event.

    Would you want to allow date entered that is prior to current date?
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  3. #3
    deepakg27 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    48
    Thanks for your reply.

    I don't want to allow the date to be prior to the last bill date.
    For example
    Like today is 12-January-2018 AND last bill is #51 dated 5th-January-2018, I want bill #52 to be between date 5th- January-2018 to 12th-January-2018.

    Thanks and Regards
    Deepak Gupta

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    42,926
    Okay,

    BETWEEN DMax("BillDate","tblBill") AND Date()
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  5. #5
    deepakg27 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    48
    HI June7,

    Thanks for your help and guidance. But sorry to inform unable to get the results.

    I applied following :
    Between DMax("SaleDate","tblBill") And Date()

    I tried applying it to the validation property of the table and form and both gave problems.
    When applying to to table it gave me following error
    Click image for larger version. 

Name:	e1.jpg 
Views:	12 
Size:	17.7 KB 
ID:	32097

    And when adding to form got the following error
    Click image for larger version. 

Name:	e2.png 
Views:	12 
Size:	8.1 KB 
ID:	32099

    PLease help.

    Thanks and Regards
    Deepak Gupta

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    42,926
    Expression looks good.

    It works for me.
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  7. #7
    deepakg27 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    48
    But I am facing the problem and have sent you the screen short also. Could you please confirm where expression is to be used (in form or table).

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    42,926
    The form. Textbox ValidationRule property.
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  9. #9
    deepakg27 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    48
    ok thanks , it is working.

    Regards
    Deepak Gupta

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

Similar Threads

  1. How to generate watch bill
    By mtcutchen in forum Access
    Replies: 2
    Last Post: 11-14-2016, 10:03 AM
  2. Bill of Materials Data Model
    By uaguy3005 in forum Database Design
    Replies: 3
    Last Post: 12-21-2015, 01:56 PM
  3. Replies: 4
    Last Post: 04-22-2015, 04:46 PM
  4. How to calculate a bill
    By Diamond in forum Queries
    Replies: 1
    Last Post: 03-21-2013, 06:17 AM
  5. Parsing a Bill of Materials
    By Pat in forum Programming
    Replies: 3
    Last Post: 05-04-2012, 03:09 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
  •  
Tech Forums: Microsoft Office Forums