Use data in aform from another table

[Veli Izzet]

Hi all,

1- I use both usd and euro values on a form; I calculate a value in
local currency, and I also need the usd and euros.

I input the conversion values manually everytime I open the form, now I
want to do it automatically.

I will create a table, Conversions, with Date, ConvEuro, and ConvUsd
fields, and I will input the values everyday.

I want that when the form opens, the form will go to the latest value,
and bring all three values from the Conversions table

2- I also want to use the same ability on a report, Price list report
contains the usd and euro prices. Now, at the start I have to input the
conversion manually.

Thanks for answers

 

[Guest]

You can have 2 hidden boxes on your form and report or just 2 on the form and
reference them from the report. Set the control source property of the 2
boxes to

=DLookUp("ConvEuro","Conversions","Date = #" & Date() & "#")
=DLookUp("ConvUsd","Conversions","Date = #" & Date() & "#")

 

[Guest]

Just to add to Dennis's reply, if you are using a system date format other
than US date format you'll need to format the return value of the Date
function as date literals must be in US format or otherwise internationally
unambiguous. As this is a common requirement here in Europe I have the
following function in a standard module:

Function USDate(varDate)

If Not IsNull(varDate) Then
USDate = "#" & Format(varDate, "mm/dd/yyyy") & "#"
End If

End Function

which you'd call like so:

=DLookUp("ConvEuro","Conversions","Date = " & UsDate(Date()))
=DLookUp("ConvUsd","Conversions","Date = " & UsDate(Date()))

 

[Veli Izzet]

Hi,

I get "#Error".

I used copy and paste, so there must be no typo. The Date is in european
format (short date in the field - like 18.07.2005)

The Conversions db is indexed on the Date field.

What can be wrong?

 

[Veli Izzet]

Update:

When I go to the properties of the textbox that gives the error; I get
the message:

Syntax error in date in query expression 'Date = #07.18.2005#'

Why there is a syntax, why the " changed to ' is beyond me.

I also think this shows that the european date is changed to us date
(the function works).

 

[Veli Izzet]

Another update:

The syntax " =DLookUp("ConvEuro","Conversions","Date = Date()")

Solved the problem.. no need for USDate, no need for the #'s.