Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Dlookup trouble (XP/2K)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Dlookup trouble (XP/2K)

    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.
    Charlotte

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dlookup trouble (XP/2K)

    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

  4. #4
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Dlookup trouble (XP/2K)

    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.

  5. #5
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Dlookup trouble (XP/2K)

    Guess whatIt worked.
    thanks Hans

    Zave Rudman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •