Storing code in a table

**jgelpi16** · 07-27-2011, 05:45 AM

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.

**rpeare** · 07-27-2011, 09:40 AM

"<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>

**ketbdnetbp** · 07-27-2011, 10:35 AM

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

**jgelpi16** · 07-27-2011, 05:47 PM

Originally Posted by rpeare

"<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().

Originally Posted by ketbdnetbp

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.

**ketbdnetbp** · 07-27-2011, 07:22 PM

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

Storing code in a table

Thread Tools

Storing code in a table

jgelpi16 -

jgelpi16 -

Similar Threads

Storing information from combobox in table

SQL code to remove duplicates from table then import into a existing table

storing and printing on a pdf

How to Display Images in a Form or in a Report Without Storing the Images in a Table

Storing an Equation in Table

Tags for this Thread

Posting Permissions