Page 3 of 3 FirstFirst 123
Results 31 to 43 of 43
  1. #31
    ismafoot is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2018
    Posts
    22

    Quote Originally Posted by Ajax View Post
    no reason why you can't but I have to question why you need to.

    If you have 4 accounts, then they should be in the same transactions table - but with an additional field to show the account name - and you would need to modify the queries provided to filter or group by the account name
    see below:
    template2.zip

  2. #32
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I've seen - what is the question?

  3. #33
    ismafoot is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2018
    Posts
    22
    I can`t seem to put an input mask to require a positive amount in the column advance and negative for payment. Ive also change the Data Entry to Yes.
    Problem is that when i add the input mask in the payment column, I can`t enter an amount in the advance column...

    So what I did is I put in the 0 for default values both in the payment and advance column
    and >=0 for the input mask for advance and <=0 for payment. Except when i enter something in either column , it gets copied into the other column.

  4. #34
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    without seeing what you are actually doing, difficult to advise. Don't understand why you are using an input mask for basic numbers - they are usually used for thing like telephone number, card numbers and the like

  5. #35
    Beetle is offline Unrelatable
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Camp Swampy (Denver, CO)
    Posts
    207
    Quote Originally Posted by ismafoot View Post
    I can`t seem to put an input mask to require a positive amount in the column advance and negative for payment.....
    This is not a data display problem, it's a data validation problem. An input mask is not the correct solution. You need to use the Before Update event of each Control, and, as an extra measure you may want to use the Before Update event of the Form as well, to do your data validation. Example (air code);

    Code:
    Private Sub txtPayment_BeforeUpdate (Cancel As Integer)
    
    If Nz(Me.txtPayment, 0)>0 Then
       MsgBox "Payment amount must be negative"
       Cancel = True
    End If
    
    End Sub
    Reverse the logic for the Advance control

    As an aside about input masks, I personally think they cause more problems than they solve. Better to just let the users enter the data, use the proper Form or Control events to validate the data, then use a Format if you want it displayed a certain way (IMO).

  6. #36
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    if you are using my example, once a value is entered it is automatically displayed in the correct column depending on its sign - and then the control in the other column is disabled. Users will soon learn that if they enter a negative number it will appear in the left column and a positive in the right. They can still edit the number wherever it is to change the sign if they got it wrong

  7. #37
    ismafoot is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2018
    Posts
    22
    Quote Originally Posted by Ajax View Post
    if you are using my example, once a value is entered it is automatically displayed in the correct column depending on its sign - and then the control in the other column is disabled. Users will soon learn that if they enter a negative number it will appear in the left column and a positive in the right. They can still edit the number wherever it is to change the sign if they got it wrong

    I meant to write Validation Rule.
    Well it has happened that in even easier interfaces , users would enter an amount in a column and not verify which column it has been entered into

  8. #38
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    the suggestion by Beetle is a good one and the way I would go

  9. #39
    ismafoot is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2018
    Posts
    22
    Quote Originally Posted by Beetle View Post
    you may want to use the Before Update event of the Form as well, to do your data validation. Example (air code)
    Worked out perfectly. I have added the code in the Before Update event for txtPayment & txtAdvance but I am not sure why I need to add a code for the Form as well.

  10. #40
    Beetle is offline Unrelatable
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Camp Swampy (Denver, CO)
    Posts
    207
    You may not necessarily need to. Before Update of the Form can be used as a final validation event, before the record gets saved, to verify that all required fields have valid data. Your choice whether or not you want to have that extra level of validation.

  11. #41
    ismafoot is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2018
    Posts
    22
    Thank you Beetle!

  12. #42
    ismafoot is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2018
    Posts
    22
    Quote Originally Posted by Ajax View Post
    the suggestion by Beetle is a good one and the way I would go
    Hi Ajax!
    I am in the testing phase and something came out weird... Not sure why this is happening.
    I entered an amount with a date value in the past (i.e: On may 29th, i entered a payment for may 10th).
    On that same day, another transaction was made. The balance column isnt representing the correct amount per row (see May10th):
    2018-05-29 11-38-26.pdf

  13. #43
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    without knowing the code you are actually using or which transaction you are referring to, difficult to say, but looks like you haven't provided a proper sort on the data to include the ID field, if that is what you have done

Page 3 of 3 FirstFirst 123
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 07-10-2014, 02:24 PM
  2. Comparing positive to negative values.
    By rubberducky in forum Access
    Replies: 1
    Last Post: 01-20-2014, 04:44 PM
  3. Replies: 2
    Last Post: 06-05-2013, 09:37 AM
  4. Replies: 7
    Last Post: 09-16-2011, 01:39 PM
  5. Convert Negative Values to Positive
    By creativefusion in forum Queries
    Replies: 1
    Last Post: 10-21-2009, 02:47 AM

Tags for this Thread

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