Results 1 to 9 of 9
  1. #1
    deepakg27 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    160

    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 offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    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?
    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
    deepakg27 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    160
    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 offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Okay,

    BETWEEN DMax("BillDate","tblBill") AND Date()
    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.

  5. #5
    deepakg27 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    160
    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:	19 
Size:	17.7 KB 
ID:	32097

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

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

    PLease help.

    Thanks and Regards
    Deepak Gupta

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

    It works for me.
    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.

  7. #7
    deepakg27 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    160
    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 offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The form. Textbox ValidationRule property.
    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.

  9. #9
    deepakg27 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    160
    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, 11:03 AM
  2. Bill of Materials Data Model
    By uaguy3005 in forum Database Design
    Replies: 3
    Last Post: 12-21-2015, 02:56 PM
  3. Replies: 4
    Last Post: 04-22-2015, 05:46 PM
  4. How to calculate a bill
    By Diamond in forum Queries
    Replies: 1
    Last Post: 03-21-2013, 07:17 AM
  5. Parsing a Bill of Materials
    By Pat in forum Programming
    Replies: 3
    Last Post: 05-04-2012, 04: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
  •  
Other Forums: Microsoft Office Forums