selecting a date range in a recordset 1

[legos]

I am exporting a table into excel and i have all of the code i need with one exception. I need to select records that were entered within the last 2 weeks. The table has a field called orderdate with the date entered for each record. This is the line i need to change.
Set rs = db.OpenRecordset("cambeorders", dbOpenSnapshot)
i was thinking something like this
Set rs = db.OpenRecordset("Select * FROM cambeorders WHERE orderdate < #" & Date & "# AND orderdate > #" & DateAdd(ww, -2, date) & "#", dbOpenSnapshot)

Durible Outer Casing to Prevent Fall-Apart

 

[legos]

this wouldn't be nessesary if someone knows a way to make a query with this style format.

Durible Outer Casing to Prevent Fall-Apart

 

[TheAceMan1]

How are ya legos . . . . .

Try this:

[blue]   Dim db As DAO.Database, rs As DAO.Recordset
   Dim SQL As String, StartDate As Date, EndDate As Date
   
   Set db = CurrentDb()
   EndDate = Int(Now())
   StartDate = EndDate - 14
   SQL = "Select * FROM cambeorders " & _
         "Where [orderdate] >= #" & StartDate & "# AND " & _
               "[orderdate] <= #" & EndDate & "#;"
   Set rs = db.OpenRecordset(SQL, dbOpenSnapshot)[/blue]

See Ya! . . . . . .

 

[PHV]

You may try this:
Set rs = db.OpenRecordset("Select * FROM cambeorders WHERE Now()-orderdate<=14", dbOpenSnapshot)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[legos]

awsome thanks for your help. One last thing the reason i am exporting this data to excel is so that i can email it as an attachment for an order. the excel file is created perfectly all i need to do now is close it and save it. I have this at the end of my code...
Set objSht = Nothing
Set objWkb = Nothing
'Set objXL = Nothing
objXL.Quit
Set rs = Nothing
Set db = Nothing
it closes excel but i am prompted on whether or not i want to save the file. Is there a way to automatically save and close the file?

Durible Outer Casing to Prevent Fall-Apart

 

[RoyVidar]

When you close the workbook, use True as the SaveChanges arguement:

[tt]objWkb.close true
Set objSht = Nothing
Set objWkb = Nothing
objXL.Quit ' NOTE - changed sequence
Set objXL = Nothing
Set rs = Nothing
Set db = Nothing[/tt]

Roy-Vidar

 

[legos]

Perfect, one last thing and i'm done. I wonder if there is a way to remove the item.send warning. here is the description.

A program is trying to send mail using Item.Send
A program is trying to automatically send e-mail using a Microsoft Outlook Visual Basic Application command, Item.Send. If you want this program to send this e-mail, click Yes. To stop the program, click No. If you are unsure which program is sending the e-mail or why, you may want to click No to avoid any possible spread of viruses.

Note When this message is displayed, the Yes button is not available for 5 seconds.


Is there a way to make that warning not come up? something like set warnings = false?

Durible Outer Casing to Prevent Fall-Apart

 

[RoyVidar]

Haven't had to deal with that, but it seems searching for "Redemption" with your favourite web search engine is supposed to give some alternatives, from what I read here.

Roy-Vidar

 

[legos]

must be a service pack 2 sort of thing, i'll check around, thanks for all your help!

Durible Outer Casing to Prevent Fall-Apart

 

[PHV]

Do a google search for outlook object model guard

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[legos]

Just as a follow-up to anyone who is interested in a solution to the item.send security problem in SP2 here is what i found.
Outlook Redemption provides a COM interface to Outlook objects that avoids the "object model guard" and exposes properties and methods not available through the Outlook model, such as sender address, the RTF body of an item, Internet message headers, and many more. Several security features protect it from being used by malicious programs to send Outlook mail. Redemption is free for personal use. The redistributable developer version adds a Profman.dll component with the ability to enumerate, add, delete, and modify Outlook profiles using VB or VBScript.
Is Redemption itself a security risk? Redemption's author, Outlook MVP Dmitry Streblechenko, responded in the outlook-dev discussion list to the topic In My World Redemption Is A Security Risk.

Using Redemption to send a message is quite simple. You save the Outlook MailItem object and then set the SafeMailItem.Item property to point to that MailItem:

objMailItem.Save
Set objSafeMail = CreateObject("Redemption.SafeMailItem")
objSafeMail.Item = objMailItem
objSafeMail.Send


Additional samples are available at the Redemption web site, or you can search for SafeMailItem for more samples on this site.

Note that Redemption does not provide access without security prompts to the Inspector.HTMLEditor and Inspector.WordEditor object properties, which are blocked in Outlook 2002 Service Pack 3 and later versions.
you can download redemption here:

http://www.dimastr.com/redemption/

Durible Outer Casing to Prevent Fall-Apart