Using data from table to populate txtbox in form from arithmetic argument

[cp1981]

Hope I summed up my question good enough lol



Ill chalk this up as a newbie question because it seems easy, but I just dont know my way around VBA enough to cough it all up and make it work.

Im designing a personal budget/expense tracker database. Im about to design a form that when I type information about a certain expense/bill and the amount that I owe or is due and the amount that i pay displays a text string from another table (tblStatus) i.e. "Paid- In full", "Paid- Partial", etc and also when I pull up the account that im paying on, the due date from the account compares to the current date and in a text box tells me whether Im "Past Due" or as long as Im within lets say within 1 week of the due date, "Due".

Currently I have (what will correspond to what I want to do):

tblAccount Includes fields "DueDate" and "Amount" (Due)
tblPaymentStatus Includes strings "Paid- In Full", "Paid- Partial", "Due", "Overdue"
tblTransaction Includes fields "TransactionDate" (Paid Date), "AmountPaid", "Status" (Where I want the "Status" string to appear)

qryNonRecurringAccount Shows my non-Recurring Accounts
qryRecurringAccount Shows Recurring accounts
qryBankATransaction What comes out/in to Bank A
qryBankBTransaction What comes out/in to Bank B

Also, like the Northwind DB on the main page, I would like to see what accounts are due and overdue through a datasheet view as well as show a pie chart of expenses.

I by no means expect anyone to do the leg work for me. Point me in the right direction or tell me what to search for or get educated about specifically.

Any help would be very much appreciated and gratefull.

[ranman256]

I think you need 2 functions for your queries (or form), to give the results on the fly. Just submit the field in query: getDueStatus([DueDate]) as DueStatus.

Public Function getDueStatus(ByVal pvDueDate)
    Select Case pvDueDate
      Case Is < Date
        getDueStatus = ""
    
      Case Is = Date
        getDueStatus = "Due"
    
      Case Else
        getDueStatus = "Overdue"
    End Select
End Function

Public Function getPaidStatus(ByVal pvAmtPd, ByVal pvAmtDue) As String
Select Case pvAmtPd
  Case 0
    getPaidStatus = "Total Due"
  Case Is < pvAmtDue
    getPaidStatus = "Paid- Partial"
  Case Else
    getPaidStatus = "Paid- In full"
End Select
End Function

[cp1981]

Thanks ranman. Much appreciated. Got it running good.