Dlookup trouble (XP/2K)

Topic

New Reply

I have a table that keeps track of exchange rates from dollar to NIS (local currency). Every time someone starts the program it is supposed to check if today’s rate is in. If it is in then it puts the rate on the startup switchboard. If not then it asks for the rate and puts the number in a table (tblYatzig), and then posts the rate on the switchboard. All of this runs from the on open event of the switchboard which opens when the program opens.
The way it checks if a rate was entered for today is by a DMAX for date in the table tblyatzig, and then comparing that to today’s date. If they are equal then it uses today’s date to find the existing rate in the table.
IT DOES NOT WORK when it hits the DLOOKUP. The code is below any help would be greatly appreciated.

Thanks
Private Sub Form_Open(Cancel As Integer)
Dim curYatzig As Currency
Dim x As Variant
Dim dtNow As Date
Dim DtMax As Date
Dim rst As Recordset

Set rst = New Recordset
Set rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenKeyset
rst.LockType = adLockOptimistic
rst.Open (“tblyatzig”)

dtNow = Now()

DtMax = DMax(“[dtYatzig]”, “tblyatzig”)
dtNow = Format(dtNow, “short date”)
Debug.Print DtMax & ” now ” & dtNow
If dtNow > DtMax Then

curYatzig = InputBox(“Enter rate”, “thanks”)
DoCmd.OpenForm “frmmain”, acNormal, , , , acHidden
Forms![frmMain].[CurSharYtzig] = curYatzig
With rst
.AddNew
!intyatzig = curYatzig
!dtyatzig = dtNow
.Update
End With
rst.Close
Set rst = Nothing
Else
rst.Close
Set rst = Nothing
Debug.Print DtMax
curYatzig = DLookup(“[intYatzig]”, “tblYatzig”, “[dtYatzig]=#” & DtMax & “#”)
‘
Debug.Print curYatzig
DoCmd.OpenForm “frmmain”, acNormal, , , , acHidden
Forms![frmMain].[CurSharYtzig] = curYatzig
End If

End Sub

Reply | Quote

Replies

What exactly does “DOES NOT WORK” mean? Do you get an error message, or do you get an unexpected result? If the former, tell us what the error is so we have a basis for suggestions. If the latter, tell us what you are getting and what you think you should be getting so we can figure out what’s happening.

I will point out, though, that you are specifically looking up a value based on DtMax *after* you have created a new later record using DtNow. Is that what you intended to do and if so, why? What’s the point in creating a new record and then looking up the previous one? Oh, one more observation: you are accepting input into a currency format and storing it to a field with a name that suggests it’s an integer. If the field isn’t an integer, you’ve tripped over the reason not to use tags on field names. If it is an integer, it won’t handle the 4 decimal places currency allows.

Reply | Quote

Thanks for the help.
the code breaks and it says that it is getting a null value.
I do not think that I have created a new record yet. I only create the new record if the result of the first part of the If-then is negative. Otherwise it is supposed to find the last record entered based on the previous DMax. AS i think I wrote it. If dtnow is geater than dtMax (in other words there is no record yet for today) then it opens an input box gets today’s rate from the user saves it into the table and sets the text box on the switchboard with that value.
If the max date in the table is today’s date then it closes the table that was opened, and tries to Dlookup the value in the table using dtmax as the criteria. At that point all I get is a Null value. If I remove the criteria from the Dlookup it will work, just it will return a random number.
I see what you mean about the currency and intyeger field, but that is not the problem here (I think).

I hope this helps.
Thanks for the help.

Reply | Quote

I don’t know what the date setting is in the location where the database is used. The Criteria argument of DLookup must follow SQL rules, i.e. dates in US format. So perhaps this will help (just a wild guess, though):

curYatzig = DLookup(“[intYatzig]”, “tblYatzig”, “[dtYatzig]=#” & Format(DtMax, “mm/dd/yy”) & “#”)

BTW, I would replace

dtNow = Now()
dtNow = Format(dtNow, “short date”)

by

dtNow = Date

Reply | Quote

Guess whatIt worked.
thanks Hans

Zave Rudman

Reply | Quote