Working with date/month.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

On a form "transaction" with fields "date" and "amount€", I need another
field which automatically converts "amount€" in us$ according to another form
"exchangerate" with fields "exchangerate" and "date" in which I insert
manually at the beginning of each month the exchange rate.
I need to compare transaction.date and exchangerate.date. If the month is
the same, then I use the corresponding exchangerate. If I have not inserted
the exchangerate for a certain month, a messagebox should appear asking me to
insert value.
I hope I was clear enough.
Thanks for your assistance on this matter
 
Hi Ghislane,

I'm afraid I am no MVP but I hope I can still help.

This is a query I used on one of my DB's to calculate totals:

SELECT a.TransDate, a.Account, -First(a.TransAmount) AS
Amount, -SUM(b.TransAmount) AS Balance
FROM [Q:DataComplete] AS a INNER JOIN [Q:DataComplete] AS b ON
(b.TransDate<=a.TransDate) AND (b.Account=a.Account)
GROUP BY a.TransDate, a.Account;

You could use a similar query, something along the lines of:

SELECT [amount?], [ExchangeRate], [amount?]*[ExchangeRate] AS [amount$]
FROM [TransactionsTable] INNER JOIN [ExchangeRatesTable] ON
([ExchangeRateFrom]<=[TransactionDate]) and
([ExchangeRateTo]>=[TransactionDate])

Hope that makes sense.

In addition I would also advise against using symbols in your field names,
for example "amountEur" and "amountDol" are less likely to cause you trouble
in the future. In addition you might want to google the "Leszynski Reddick"
naming conventions for access. Bit of a nightmare at first but once you get
used to them they are a godsend...especially if you are trying to decipher
someone elses code.

I'll have a think on your other question regarding the pop-up box.
Relatively easy but I just need to decide what code is needed.

John
 
On a form "transaction" with fields "date" and "amount¤", I need another
field which automatically converts "amount¤" in us$ according to another form
"exchangerate" with fields "exchangerate" and "date" in which I insert
manually at the beginning of each month the exchange rate.
I need to compare transaction.date and exchangerate.date. If the month is
the same, then I use the corresponding exchangerate. If I have not inserted
the exchangerate for a certain month, a messagebox should appear asking me to
insert value.
I hope I was clear enough.
Thanks for your assistance on this matter
Click to expand...

Do you really have a field named "Date"?
Date is a reserved Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'
 
Back
Top