Using external query (mdb) for dynamic date range

I'm trying to set up an Excel workbook with a dynamic link to an Access 
database containing quality data, involving dates.  I want the workbook to 
always import data for the last 90 days.  When I try to set this up using MS 
Query, to try to filter data I've tried several different variations where 
time is greater than or equal to:

date()-30
today()-30
now()-30

And every time, it comes back as an error.  It seems to be setting the query 
so that the entire statement is a date variable, eg "Syntax error in query 
expression '((Table1.Time>=#date()-30#))'" where I'm pretty sure that the 
query should read "((Table1.Time>=date()-30))", but I can't find a way to 
edit the query directly.  It insists on using the Query Wizard instead.

-- 
Burnnie Holliday
Data Wrangler
0
Utf
5/5/2010 10:00:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

2 Replies
792 Views

Similar Articles

[PageSpeed] 45

Heh... make that the last 30 days.  You get the point.
-- 
Burnnie Holliday
Data Wrangler


"Burnnie Holliday" wrote:

> I'm trying to set up an Excel workbook with a dynamic link to an Access 
> database containing quality data, involving dates.  I want the workbook to 
> always import data for the last 90 days.  When I try to set this up using MS 
> Query, to try to filter data I've tried several different variations where 
> time is greater than or equal to:
> 
> date()-30
> today()-30
> now()-30
> 
> And every time, it comes back as an error.  It seems to be setting the query 
> so that the entire statement is a date variable, eg "Syntax error in query 
> expression '((Table1.Time>=#date()-30#))'" where I'm pretty sure that the 
> query should read "((Table1.Time>=date()-30))", but I can't find a way to 
> edit the query directly.  It insists on using the Query Wizard instead.
> 
> -- 
> Burnnie Holliday
> Data Wrangler
0
Utf
5/5/2010 11:07:01 PM
Nevermind, I've just figured out the answer.
-- 
Burnnie Holliday
Data Wrangler


"Burnnie Holliday" wrote:

> I'm trying to set up an Excel workbook with a dynamic link to an Access 
> database containing quality data, involving dates.  I want the workbook to 
> always import data for the last 90 days.  When I try to set this up using MS 
> Query, to try to filter data I've tried several different variations where 
> time is greater than or equal to:
> 
> date()-30
> today()-30
> now()-30
> 
> And every time, it comes back as an error.  It seems to be setting the query 
> so that the entire statement is a date variable, eg "Syntax error in query 
> expression '((Table1.Time>=#date()-30#))'" where I'm pretty sure that the 
> query should read "((Table1.Time>=date()-30))", but I can't find a way to 
> edit the query directly.  It insists on using the Query Wizard instead.
> 
> -- 
> Burnnie Holliday
> Data Wrangler
0
Utf
5/5/2010 11:43:01 PM
Reply:

Similar Artilces:

Excel VBA
Hi again, I have enclosed a timesheet which i want to break down the day activities by a percentage, so if my sheet says an activity took a hour then the day column would tell you percentage of a seven hou day. Also i want the date column date to change each day. Currently it work for one day but then the next day it changes all the above cells to th current date. I have attached the file Cheers Stev Attachment filename: book2.xls Download attachment: http://www.excelforum.com/attachment.php?postid=56396 -- Message posted from http://www.ExcelForum.com ...

Change background color of query results from access in worksheet
Hi, I have Excel 2007 and selected Date => From Access and then navigated to an Access DB and selected a query. I chose "Table" view. The query results now appear in my worksheet which is great. However, I want to change the background. Right now it alternatives by row between a light blue and a darker blue. I want the results to have a white background with black borders but have been unable to change the format. If I go to Home and select No fill for a color background nothing happens. Is there a way to change the query results? Thanks, Could be the r...

External Account From The Same Domain Name.
Hi everybody, I have a problem and i hope somebody could help me. We have a customer with an Exchange 2003 in a Windows SBS 2003 environment. We have setup an SMTP connector to send mails to the ISP's SMTP Server since their ISP would not let them send email unless it went through their servers. Users of the organisation are receiving their mails through a POP3 Connector. The problem is that the company has another branch in another place and the users who work there are receiving their mails through the ISP directly. Because these users don't belong to the organisation, nobody insid...

due date function
I try to make function for due payment. When I made copy file and then drag down the formula and it's succed. But for the cell that I not fill yet has due date also. How to make that cell became 0 (zero) if not fill the cell yet. example : A1 A2 19/8/05 =A1+30 - 30/01/00 - 30/01/00 Thanks Lando =IF(C5=""; "" ;C5+30) Explanation detail if ([logical test] c5 equals ""; then [result if true] ""; else [result if false] c5+30) Explanation brief if c5 eq...

Date datatype in my schema
I have created a simple XML document, generated a schema from it (which Visual Studio makes everything a string element). I then went through and changed all fields that are dates to the Date data type. Now, when I use the datagrid to edit the data in the XML file and then switch back to the raw XML text date, the dates are formatted like this "1973-10-27T00:00:00.0000000-06:00" and not as I expected, which are supposed to be a simple date, such as "1973-10-27" as per the W3C spec. Also, when I validate the document, it fails on all date elements with this error: C:\Doc...

How can I autofill dates having a blank cell between each day?
Hi Dee, Enter a date in cell A1 (eg 5/13/08), enter the next day's date in cell A3 (eg 5/14/08). Select cells A1 trough to A4 (ie select two dates and two blank cells). See the little dot at the bottom right of the selection rectangle? Click on that (the cursor will change to a cross when you are over the right spot) and drag it down as far as you want. Ed Ferrero www.edferrero.com ...

Date Problems
Dear Microsoft: Since we all know that the cost of a 1st class stamp is going to go up, I thought I would plot the cost over time. http://www.nonprofitmailers.org/tools/ratehist.htm 1. I formatted a column as DATE, but the 3 dates prior to 1900 that I entered as x-x-xxxx were not recognized by Excel as dates. (??!!???!). When I reentered them as x/x/xxxx Excel recogized July 1, 1885, but not the other 2. (Huh?) 2. So then I charted the dates and the costs as a line chart, the costs are displayed ok. The dates are displayed on the x-axis, but the dates are equidistant. That's...

Can't set Date Properties.
I am working with data that has dates set as text. So in a query I am using a variable that sets the text to a date suing CDATE. At least I thought it did. I notice that in forms etc when I try to set the date property to long, medium etc, there is nothing to choose so presumably it is not a date after all! Any idea what I'm doing wrong please. Cheers. Roger --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.673 / Virus Database: 435 - Release Date: 01/05/2004 Sorted. I hadn't set the function as a date. Well I am n...

Open Spreadsheet--Formatting Changed to Date??
I opened my spreadsheet and some, but not all of the columns on every sheet in the workbook has changed to a date format. I opened other spreadsheets and there was no change. Can anyone explain this. It's a real pain to deal with. ...

Add new record using first available non-numeric ID; update two ta
Access 2003, Windows XP Pro SP2 I have two tables, tblDed and tblVendor. tblDed contains two columns (DedID and Avail). The first column contains a list of all DedIDs allowed to be used. The second is a Yes/No choice indicating whether or not that DedID has been used already. tblVendor contains multiple columns with misc. data relevant to the Vendor. I want to create a form that will accomplish three things: 1. Use the next available unused DedID when a command button on a referring menu page (frmMenu, btnNew) is pressed. 2. Mark the now-used DedID in tblDed as "No" - no ...

Setting default date system to 1900 versus 1904
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I work in a large corporation that uses excel 100 times a day. My issue is that most of the files we work with are created on Win platform which defaults to the 1900 date system. When I create on the Mac, it defaults to the 1904 date system. I realize I can change the date format in preferences on an individual basis but is there anyway to set the default to 1900 versus me having to remember to go to preferences every time on each individual sheet? Thx On 3/4/09 9:22 AM, in article 59b6e6ef.-1@webcrossing.caR9absDaxw,...

RFID Use with Access
I am looking to find the steps necessary to make Access ready to interface RFID information captured from passive RFID devices. I would appreciate anyone in the group sharing this how-to with me. Zachry1 <Zachry1@discussions.microsoft.com> wrote: >I am looking to find the steps necessary to make Access ready to interface >RFID information captured from passive RFID devices. You should really be looking to the vendor of the RFID capture devices. They should have DLLs/OCXs which you can install and sample code. VB 6 code usually works just fine in Access. Tony -- Tony Toews...

OL2003 uses 100% of CPU resources after shutdown
If leaving my computer on over night with OL2003 with Business Contact Manager (BCM) running usually results in OL2003 stop responding but maintain 100% CPU usage. Even shutting down OL2003 w/BCM does not free up the CPU, which still shows OL2003 claiming 100% CPU usage. This also prevents my computer from going into power standby mode. How do I resolve this issue? ...

COUNTIF using formatting?
Here is a basic example of what I'm trying to do: I have a list of football teams. Each week, I format the football teams that won with red font. Is there a way to find a count of those teams formatting in red and display that number in my worksheet? I know an alternate way around this would be to have a win/loss column and do a COUNTIF based on the values there. But I'm wondering if there's a way to do it based on the formatting instead of the values. Nicolle K. wrote: > Here is a basic example of what I'm trying to do: > > I have a list of football team...

Dates in the Footer
I know how to insert a date in the footer of a document, but wondering if Word can automatically change the date of ONLY a page in a document that is updated...not the entire document. Would be very helpful to know when certain info is updated, but also to keep the last time other pages were updated. Thanks for any advice. JT On Sat, 24 Apr 2010 06:04:01 -0700, JT <JT@discussions.microsoft.com> wrote: >I know how to insert a date in the footer of a document, but wondering if >Word can automatically change the date of ONLY a page in a document that is >u...

Outlook 2007
Hi All I have a mail server configured on a IPv6 only machine. I would like to specify my POP3 account with these IPv6 Addresses as the Incoming mail server and SMTP server. For that in the Email Accounts i want to use IPv6 addresses. This is not working, i have also tried using the IPv6 address resolved name also, still it doesn't work. It works fine with IPv4 address and hostname. Can anyone help me solve this problem. Thanks Anilkumar Can you PING the IPv6 address? Can you use a HOSTS file to map the address to a name? <anilgkonline@gmail.com> wrote in message news:11781...

File Transfer using vb.net code
Hi, I would like to transfer/move a file from one folder to other folder in shareopint site. That needs to be done using vb.net. Could you please prove the code for that? Regards, Balu Submitted via EggHeadCafe - Software Developer Portal of Choice Dr. Dotnetsky's Cool .NET Tips and Tricks # 16 http://www.eggheadcafe.com/tutorials/aspnet/960bc693-889f-46c1-9b0e-1b0a67d13ea5/dr-dotnetskys-cool-net.aspx So your asking someone on this list to do your work for you? For free? Sounds like you might want to get some consulting time. -- Daniel A. Galant Imagine what w...

Using Web Services To Add Leads to Marketing List
Is it possible to use web services to add leads/contacts to marketing list? I have a sales contact form that creates a lead, and I want to give those leads the option to signup for a mailing list we have. Does anyone know if this is possible? If so, know any examples? Thanks, Tareef Hi Tareef, The messages to handle list membership are AddMemberList and RemoveMemberList. They are described in the SDK documentation along with some sample code. -- Michael H�hne, Microsoft Dynamics CRM MVP Web: http://www.stunnware.com/crm2 Feed: http://www.stunnware.com/crm2/atom.aspx Custom Lookup D...

multiple ranges on Vlookup
I currently have my Vlookup stmnt as this: =IF(E2="","",VLOOKUP(E2,'Code Descrip'!T$3:U$27,2,FALSE)) 'Code Decrip' is the name of the worksheet I need to add another range X$3:Y48 What is the proper syntax - I wasnt able to get it right after searching online for it. Thanks so much. Hi lpj Not quite clear what you want to achieve here? Why not have everything in the same range? Am I correct in guessing that, if you do not find a matching record in the primary range, you then want to do a lookup in the secondary range? If so, you will have to use an...

Access query
I have a access query that I would like to show in my excel spreadsheet. Is there a way to insert? In the destination workbook, choose Data>Import External Data>New Database Query Click on MS Access Database as the data source, and click OK Select the database with the list, and click OK In the Query Wizard, select your query, and click the > button, to add the columns to the query Click Next, three times, then click Finish. Select a starting cell for the imported list, and click OK Right-click a cell in the imported list, and click Data Range Properties Add a check mark to R...

AddNew fails
I have the following section of code (and several other similar ones, which all work fine): Set dbs = CurrentDb() Set qdf = dbs.QueryDefs("qryInspAccess_Current") qdf.Parameters("[CardID]") = Me.InspCardID Set rst = qdf.OpenRecordset(, dbOpenForwardOnly) Set rstSub = dbs.OpenRecordset("RWOP_DAT_InspAccess", dbOpenDynaset, dbAppendOnly) Do Until rst.EOF rstSub.AddNew rstSub!AccessID = rst!AccessItemID rstSub!InspectionID = Me.InspID rstSub.Update rst.MoveNext Loop The problem is that the .AddNew statement generates error 3022 (...

Looking for help...I can't send e-mail using CRM web client
I'm having a problem with trying to send e-mails vie the CRM web client. When I try to send the e-mail I get a pop-up with the message in bold "Unexpected Error" "An error has occurred. For more information, contact your system administrator". I've checked the event logs, no errors are showing up in either my CRM server or my exchange server. I'm lost, new to CRM. Any help or ideas is appreciated. Thanks, You could try to add pre-windows2000 account to the administrator group on the exchange server. Some time that helps "Marty" wrote:...

notes section and date
hello, Does anyone know how to add the createdon field to the notes section? I see that it is available in the deployment manager under annotation. I can't figure out where I need to go or what I need to do to get the column to show up. Also, does anyone know how to make the topic field not mandatory? Thanks, Jen ...

auto copy and paste to another workbook by using specific titles
I am trying to get information from a row that contains a specific title to be transferred to another workbook automatically. I also need to know how to do this with the cells not matching up. Hello: Your question is vague. If you mean that when someone opens the spreadsheet, the contents of a row with a certain title is copied to another spreadsheet, then you can use the Auto_Open() event to search for the row, open the target spreadsheet, insert it, and then close the spreadsheet, saving the contents. Again, I may not be addressing your question, but again, it very gener...

Help in Query with two condition
hello all, I have a table like this: Planner Qty User6 CUT SUB BAM-06 1 CONC Yes No BAM-06 1 CONC No Yes BAM-06 1 TOC Yes No BAM-06 1 TOC No Yes BAM-06 1 TACT Yes No BAM-06 1 TACT No Yes JAXM-06 1 CONC Yes No JAXM-06 1 CONC No Yes JAXM-06 1 TOC Yes No JAXM-06 1 TOC No Yes JAXM-06 1 TACT Yes No JAXM-06 1 TACT No Yes Note: CUT and SUB are check box I want a result as follow: User6 A B C D E F CONC 2 2 1 1 1 1 TACT 2 2 1 1 1 1 TOC 2 2 1 1 1 1 WHERE: A SUM Qty =BAM-06 B SUM Qty =JAXM-06 C SUM Qty =BAM-06 if Cut=YES D SUM Qty =JAXM-06 if Cut=YES E SUM Qty =BAM-06 if SUB=YES F SUM Qty =JAXM-06 i...