formula for debits and credits & date selection

[Guest]

I posted this question on 5/16 under the Subject Help with formula problem;
but received no reply. I'll quote the original post and then try to clarify
my question and add another.

<<I have a large database with accounting relevant information. New data must
be appended in a particular format for export to the accounting department.
There are 26 inter-company transfer accounts that always remain the same
except for the debits and credits. I have placed them in a separate table.
Two fields identify the codes for the transfers, i.e., an account number and
the corresponding transfer account. When one of the accounts is debited, the
corresponding account must be credited by the exact same amount (the debit is
entered as a positive number, and the credit as a negative). I would like to
have the corresponding account credited when the debit amount is entered. For
example, Field1=Entity, Field2=account. Let's say entity number 08101 is
debited +20,000.00; then the corresponding account, say 08001 must be
credited -20,000.00. Can I put this in some simple code for the debit and
credit fields? I know how to do this in Excel, but I don't know if it is
doable in Access. Essentially then, this table with its monthly transfers is
appended to the "holding" table that exports a delimited file to the
accounting department.>>

MyTable has Field1, Filed2, FieldCredit, and FieldDebit. I want to query
Field1 and the last part of Field2. If Field1 has a corresponding positive
amount in the FieldCredit, then I want the negative of the amount entered in
FieldCredit to be entered into the Field1 corresponding FieldDebit. Field1
identifies an accounting entry, and the last four characters of Field2
identify the corresponding transfer account (i.e., four characters that are
the same as the Field1 entries). I hope this makes it a little clearer on
what I want to do. Can anyone please help me?

The new part of my question is this: I have a field that is the last friday
of every month. I'm trying to figure a way to present the user an easy way to
select these dates for forms, queries, and reports.

I really appreciate the great value of help available in this group.

Someone please help.

 

[Guest]

Ray,
Here is a function that will return the last friday of the month. You can
pass any date within the month you want the last friday for. For example,
1/1/2005 or 1/31/2005 will both return 1/28/2005.

Function LastFriday(dtmBaseDate As Date) As Date
'Compliments of Dave Hargis
Dim intCurrMonth As Integer
Dim intNextMonth As Integer
Dim intNextYear As Integer
Dim blnAllDone As Boolean
' Find the Current and Next Months
intCurrMonth = Month(dtmBaseDate)
intNextYear = year(dtmBaseDate) + 1
intNextMonth = intCurrMonth + 1
' Find the Friday for the date passed
dtmBaseDate = DateAdd("d", vbFriday - DatePart("w", dtmBaseDate),
dtmBaseDate)
If Month(dtmBaseDate) = intNextMonth Then
'The Friday for the date entered is in the next month
'so subtract a week and it will be the last Friday
LastFriday = DateAdd("ww", -1, dtmBaseDate)
Exit Function
End If
If Month(dtmBaseDate) <> intCurrMonth Then
'The Friday for the date entered is in the previous month
'so add a week to get back to current month
'Used <> instead of < because in Jan(1) you can end up in Dec(12)
dtmBaseDate = DateAdd("ww", 1, dtmBaseDate)
End If
blnAllDone = False
Do Until blnAllDone
dtmBaseDate = DateAdd("ww", 1, dtmBaseDate)
blnAllDone = Month(dtmBaseDate) = intNextMonth Or _
year(dtmBaseDate) = intNextYear
Loop 'Until blnAllDone
LastFriday = DateAdd("ww", -1, dtmBaseDate)
End Function

Now for the rest of your question, I am confused. See if this is what you
are after:

In your database table you have:
Field1 (Entity) = 08101
Field2 (Account) = 08001
FieldCredit = 0
FieldDebit = +20,000

So in the Holding table you want

Field1 = 08101
Field2 = 08001
FieldCredit = -20,000
FieldDebit = +20,000

If this is not correct, post back with the corrections.

 

[Guest]

Thanks Klatuu,

As to the function, there appears to be an error in the following line:

dtmBaseDate = DateAdd("d", vbFriday - DatePart("w", dtmBaseDate),
dtmBaseDate)

I'll get back to you on the second part a little later today.

 

[Guest]

I got the function right (it was missing a parenthesis). But, when I ran it
in the immediate window, I got this.

?LastFriday(4/1/2005)
12/29/1899 12:02:52 AM

It should have returned 4/29/2005

 

[Guest]

Ray,
It works, you are calling it wrong. Dates have to be delimited with #
?LastFriday(#4/1/2005#)
Access doesn't tell you a number is not a date, it tries to convert it to
date. Access carries dates as numbers and the internal functions convert the
number to a date.

 

[Guest]

Thanks Klatuu, You're right, it does work quite well. I should have known I
was missing the # delimiters, but I'm still pretty new at this. I love this
group because I learn something every day.

 

[Guest]

I learn something every day, too. So how is the rest of your Debit/Credit
problem going? I has posted a request for clarification so I could help with
an answer, but maybe you have that under control already.

 

[Guest]

No, I don't have that under control. Let me see if I can clarify using a
simplified example. The relevant fields are entity, amount, account, and
debit or credit (let's call it d_or_c). One table, let's call it journal,
collects all the information from various entry sources. These are all
appended to the journal for a once-a-month balancing and forwarding to the
accounting department. Balancing is done through a number of pre-determined
account transfers. Those entries are appended (of course, without the
transfer amounts entered yet) to the journal table. By doing a query on the
journal table I can view a sum or the transaction amounts associated with
each entity number. Let's say entity number 08001 has a sum of $197,000.13. I
need to copy that amount to the "transfers" section of the journal. That
section has two entries reflecting that entity number. Where the last five
digits of the account number is 08001, I paste the positive of that in the
amount field and place a "C" in the d_or_c field. Then I look in the entity
field of the journal table in the "transfers" section. There I paste the
negative in the amount field and place a "D" in the d_or_c field. After I do
all of the transfers (a particular account must be saved for last), then if
my debits and credits don't balance, I must do a manual adjustment entry. Of
course, ideally, I want a perfect balance; but any discrepancy must be
documented through a manual adjustment before it goes to the accounting
department.

I'm still trying to wrap my brain around this...