Bank Register - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Bank Register

J David Bacon

4 years ago

My bank transactions are downloaed into a table named CYTransactionT. I would like to split some transactions into multiple accounts. For example: Mortgage Payment in Principle, Interest, Homeowners Insurance, Property Taxes, etc.

I want to assign these split transactions into a separate table which I have labled CYSplitTranactionT.

When a transaction is selected from CYTransactionT I first want to determine if the transaction exists on the CYSplitTransactT table.

The following If statement is used...but it does not recognize existing transactions. Instead, it displays a message stating there is not Split Transactions for the selected transaction.

If IsNull(DLookup("TransactionNumber", "CYSplitTransactionT", "TransactionNumber= """ & transNbr & """")) Then

This above will create a new split transaction after the then clause.

Any help would be greatly appreciated.

	Kevin Robertson @Reply 4 years ago
Try this instead `If Nz(DLookup("TransactionNumber", "CYSplitTransactionT", "TransactionNumber= """ & transNbr & """"),0) = 0 Then`

	J David Bacon OP @Reply 4 years ago
Kevin, I have not had the time to try your suggestion, will advise if this addresses the problem.

	J David Bacon OP @Reply 4 years ago
Kevin: I tried you suggestion it did not work, still getting No Split Transactions for the selected transaction.

Adam Schwanz @Reply

4 years ago

Kevin's code really should work, makes me think you have something else off.

Try these, make sure you get values returned.

Msgbox DCOUNT("TransactionNumber","CYSplitTransactionT")

Msgbox DCOUNT("TransactionNumber","CYSplitTransactionT","TransactionNumber=""" & transNbr & """")

If you do, you can try

If DCOUNT("TransactionNumber","CYSplitTransactionT","TransactionNumber=""" & transNbr & """") < 1 Then

'Theres no records

Else

'Theres records

End If

J David Bacon OP @Reply

4 years ago

Kevin & Adam:

Still not getting the desired results...note the following:

When the user double clicks on a transaction from the list of transactions on CYTransactionT, they are directed to the Split Transaction form SplitTransactionF.  The follow code is used for determining if the transaction exits:

Private Sub Form_Load()
    '
    '       Setup Default Settings
    '
    dim transNbr as string
    transNbr = Forms!BankTransactionF!TransactionNumber
    MsgBox "TransactionNumber= """ & transNbr & """"
    MsgBox DLookup("TransactionNumber", "CYSplitTransactionT")
    If DLookup("TransactionNumber", "CYSplitTransactionT", "TransactionNumber= """ & transNbr & """") < 1 Then
        MsgBox "No Split Transactions in Database - Show transaction from CYTransactionT
    else
        Msgbox "Split Transaction found - Display existing Split Transactions form CYSplitTransactionF"
    endif
End Sub

The above msgboxes only show the selection transaction number and not the count.

	Kevin Robertson @Reply 4 years ago
If you need a count of records you'll need to use DCount.

	Adam Schwanz @Reply 4 years ago
Yea, this line needs to be DCOUNT not DLOOKUP If DLookup("TransactionNumber", "CYSplitTransactionT", "TransactionNumber= """ & transNbr & """") < 1 Then Same with the MsgBox DLookup("TransactionNumber", "CYSplitTransactionT") we want DCOUNT

	J David Bacon OP @Reply 4 years ago
Kevin/Adam: Problem solved. The form which calls SplitTransactionF did not have the correct "Quote, Double Quote" format. Once I change to the proper format, the form displayed the correct information. Thank you for your help with this problem. Dave

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View