Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Posts
    139

    Unanswered: conditional macro does not work w/ SQL BE

    Hi - I have an Access 2002 FE and converted my tables over to SQL server with the upsizing wizard. I had no errors during the conversion. However, I have a macro expression that worked with the back end in Access but now will not evaluate to true when I know it should. I converted the macro into VB using the wizard but of course that does not change things. I am sending the VB code in hopes that someone can tell me what I need to change to get it to work with the SQL tables.

    Function CHECK_FOR_3_MONTH_WARRANTY1()
    On Error GoTo CHECK_FOR_3_MONTH_WARRANTY1_Err

    If (Eval("(DLookUp(""[SERIALSHP]"",""[SS ORDER DETAILS]"",""[SERIALSHP]=[FORMS]![SR ORDER FORM]![SR FORM (SUB)].[FORM].[SERIALREC] And [SHPDT] Between Now()-1 And Now()-90""))")) Then
    Beep
    MsgBox "it is triggering", vbOKOnly, ""
    DoCmd.PrintOut acPrintAll, 1, 1, acHigh, 1, True
    DoCmd.Close acReport, "3 MONTH WARRANTY"
    DoCmd.GoToControl "WARRANTY"
    Forms![SR ORDER FORM]![SR FORM (SUB)].Form!WARRANTY = "WARRANTY"
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.GoToControl "QTYREC"
    End If


    CHECK_FOR_3_MONTH_WARRANTY1_Exit:
    Exit Function

    CHECK_FOR_3_MONTH_WARRANTY1_Err:
    MsgBox Error$
    Resume CHECK_FOR_3_MONTH_WARRANTY1_Exit

    You will notice that the IIf statement has the DLookup function as that is what I used originally in the macro.

    Any suggestions for changing the code would be appreciated as I do not know how to write code. I have tried to figure it out from books but I do not know how the SQL has impacted the operation of this macro.

    Thanks,
    B&R

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Remove the quad quotes i.e.
    Code:
    ...If (Eval("(DLookUp(""[SERIALSHP]"", ....
    Becomes
    Code:
    ...If (Eval("(DLookUp("[SERIALSHP]", ...
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Posts
    139
    I kept getting compile errors. Is there any way that you could put the whole code on there with the changes so I can better understand where the quotes should be? I am sure I have other conditional macros that are not working and I will be able to apply what I learn from your example.

    Appreciate anything you can do to help!

    Thanks,
    B&R

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Not going to do this for you
    If you've had a try - post back your efforts here.
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2004
    Posts
    139
    I can get the macro returning a true value if I remove this:
    And [SHPDT] Between Now()-1 And Now()-90"")

    I believe that the date is the issue. Does anyone have an idea to how I need to change this code to communicate with the SQL tables regarding the date?

    Appreciate any help!

    B&R

  6. #6
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,422
    Provided Answers: 7
    yes
    you Have to Use the convert function
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Now() - 1 should be changed. Look up the DateDiff() function in the help files
    George
    Home | Blog

Posting Permissions

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