Results 1 to 5 of 5
  1. #1
    jgelpi16 is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Mar 2010
    Location
    Charlotte, NC
    Posts
    544

    Question Storing code in a table

    Is there a way to store code/SQL in a table, and reference it using a DLookup()? I made a limited attempt at it when I was trying to develop an e-mail. My thought was to dump the body, header, footer, subject, etc. in a table, and just reference the table when I wanted to send an e-mail. However, when I did that it came over as one big string, and didn't split out as expected (see below example).



    Code:
    'Where Field has the value of:
    '"<body>This is a test for " & #" & Date & "# & ".</body>"
    strBody = DLookup("Field", "Table", "[Field2] = 'Yes'")
    The output in the email was quite literally

    "<body>This is a test for #" & Date & "#.</body>"

    Instead of the desired

    "This is a test for 7/27/2011."

    **Note In my actual code I was including the additional necessary HTML tags, but just omitted them for this example.

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    "<body>This is a test for " & CurrentDate & ".</body>"
    if you are referencing a field in a form or table

    "<body>This is a test for " & date() & ".</body>"
    if you are referencing the current day's date

    do not use the word date unless you are specifically trying to recover today's date. Date is a reserved word in access and may be the cause of your trouble I don't know but your string generation was off too if you wanted the output to be

    <body>This is a test for 7/27/2011.</body>

  3. #3
    ketbdnetbp is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Mar 2011
    Location
    Midwest
    Posts
    254

    jgelpi16 -

    IMHO, I think the DLookup function is doing exactly what it is suppose to do, return the value from the table. However, from the looks of it, the Date function is not executing in the process. So, yes, you can store the code in a table but, you also might need a way to execute any imbedded functions in the lookup string when it is called.

    Just my 2 cents.

    Jim

  4. #4
    jgelpi16 is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Mar 2010
    Location
    Charlotte, NC
    Posts
    544
    Quote Originally Posted by rpeare View Post
    "<body>This is a test for " & CurrentDate & ".</body>"
    if you are referencing a field in a form or table

    "<body>This is a test for " & date() & ".</body>"
    if you are referencing the current day's date

    do not use the word date unless you are specifically trying to recover today's date. Date is a reserved word in access and may be the cause of your trouble I don't know but your string generation was off too if you wanted the output to be

    <body>This is a test for 7/27/2011.</body>
    I apologize. I as using Date to return today's date. However, in VBA it uses Date instead of Date().

    Quote Originally Posted by ketbdnetbp View Post
    IMHO, I think the DLookup function is doing exactly what it is suppose to do, return the value from the table. However, from the looks of it, the Date function is not executing in the process. So, yes, you can store the code in a table but, you also might need a way to execute any imbedded functions in the lookup string when it is called.

    Just my 2 cents.

    Jim
    Jim - I agree with you, I think the DLookup() is doing what it should, but it's not giving me what I want . Any ideas for extracting that imbedded function/variable? I would like to use variables more than functions.

    Thanks to you both for your help and input.

  5. #5
    ketbdnetbp is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Mar 2011
    Location
    Midwest
    Posts
    254

    jgelpi16 -

    I'm at a total loss on the embedded function issue. Just for the Date, the only thing I can think of might be to use the Replace() function but, it is just a guess.

    AIR CODE (not tested):

    dim dte1 as Date
    dte1 = Format(Now(),"Short Date")

    strBody = Replace(DLookup("Field", "Table", "[Field2] = 'Yes'"),"& Date &",dte1)

    Not sure if the dte1 variable will work but, it may be worth a try.

    Sorry, I couldn't be of more help.

    All the best,

    Jim

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Storing information from combobox in table
    By michaelb in forum Forms
    Replies: 4
    Last Post: 05-04-2011, 09:42 AM
  2. Replies: 7
    Last Post: 04-15-2011, 08:46 AM
  3. storing and printing on a pdf
    By jlm722 in forum Reports
    Replies: 4
    Last Post: 10-27-2009, 02:48 AM
  4. Replies: 17
    Last Post: 08-26-2009, 11:27 AM
  5. Storing an Equation in Table
    By tmedler in forum Programming
    Replies: 0
    Last Post: 03-09-2009, 09:25 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums