Date manipulation 10-05-07

This works in a query design view column:

WeekOf: 
IIf((Weekday([Date])=1),([Date]-6),(DateAdd("d",-(Weekday([DATE])-2),[DATE])))


It looks at the date field and assigns it to the 'WeekOf' which is always 
the preceeding Monday.

But in the next query I apply a Criteria to the WeekOf field to range it 
between date start and date end fields that exist in a form.  The form format 
is definitely date.  But I get a data mismatch error when I attempt to apply 
the criteria.

It leads me to believe that my manipulation of the date with the Iif has 
changed its status to not being recognized as a date.

Would welcome advice.


-- 
NTC
0
Utf
10/5/2007 7:08:05 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
681 Views

Similar Articles

[PageSpeed] 24

thanks to both.   wrapping in CDate in particular....

As to the word DATE being reserved;  I understand and agree.  These dbs get 
started by lay people and it really isn't feasible to expect them to know 
that such a commonly used business term can not be used.

MS really needs change their reservation to acDate or something else less 
probable...reserving the word Date is almost inviting end user 
problems...particularly if the [ ]  brackets is not going to shield this issue

but much thanks....
-- 
NTC


"Klatuu" wrote:

> Is [DATE] a field in your table or are you trying to use the Date() function?
> If it is a field, you really need to change its name.  Date is an Access 
> reserved word and can cause Access confusion.  I know the rule says if you 
> enclose it in brackets it will resolve the problem; however, I once ran 
> accross a situation where even with the brackets it was returning incorrect 
> values.  In this case it was
> IIf([Date] < Date, "foo", "bah")
> 
> Because I was not allowed to change the field name, the only way I could get 
> it to work was:
> IIf([Date] < VBA.Date, "foo", "bah")
> -- 
> Dave Hargis, Microsoft Access MVP
> 
> 
> "NetworkTrade" wrote:
> 
> > This works in a query design view column:
> > 
> > WeekOf: 
> > IIf((Weekday([Date])=1),([Date]-6),(DateAdd("d",-(Weekday([DATE])-2),[DATE])))
> > 
> > 
> > It looks at the date field and assigns it to the 'WeekOf' which is always 
> > the preceeding Monday.
> > 
> > But in the next query I apply a Criteria to the WeekOf field to range it 
> > between date start and date end fields that exist in a form.  The form format 
> > is definitely date.  But I get a data mismatch error when I attempt to apply 
> > the criteria.
> > 
> > It leads me to believe that my manipulation of the date with the Iif has 
> > changed its status to not being recognized as a date.
> > 
> > Would welcome advice.
> > 
> > 
> > -- 
> > NTC
0
Utf
10/7/2007 6:39:00 PM
Reply:

Similar Artilces:

Excel 2002
I have a user using Excel 2002 on windows. Just this month whenever she enters a date formatted for any day in 2004 it automatically changes the month to the day. We've checked the cell format and even tried to change it to a different format and it still occurs. (ie 01/04/2004 to 1/4/2004). The vesion of excel is Excel 2002 (10.3506.3501) SP-1. I searched the Microsoft site for an upgrade but there wasn't another SP version listed for Excel 2002. Any one else come across this problem? Thank you! Rebecca http://www.geekmom.net/downline --- Message posted from http://www.ExcelFor...

Microsoft CRM Report Viewer Bug ? 04-15-07
When viewing report in Microsoft CRM Report Viewer, it extracts the filter info from the RDL file. When user saves the default filter it's also stored in the RDL. The filter info is actually the Fetch XML which is build dynamically by the viewer control. I've created a report and in the RDL file specified the Fetch XML with linked entities and attributes for each linked entity. This way when report is executed, the Fetch XML is converted to the SQL statement which is used as report data (when executed). Report viewer displayes the filter correctly BUT when it builds the Fetch ...

Help needed changing date formats from m/d/y to d/m/y
I have a spreadsheet with a column of dates in the format ddd m/d/yyyy, e.g. "Mon 9/29/2003". The data has come in as text as I have cut and pasted it from another source. I sort of have something working, but it only works for dates with 2 digits (i.e. from October (10th month) on and from the 10th of each month on), e.g. "Wed 29/10/2003". The formula I have is: =DATE(RIGHT(E3,4),MID(E3,6,2),MID(E3,9,2)) Can anyone help me get this to work for all dates? Thanks in advance. Craig An alternative: Select your column. Choose Data/Text To Columns. Select the Delimited ...

Access 2003 05-25-07
I have a form saved as a html and linked it to a web page. The form will open and is funtional but will not write to the tables in the database. Any suggestions? ...

passing parameters to Crystal 07-01-03
Is it possible to programmatically pass parameters to a crystal report? say a quote id... If so, how is this done? Any insight would be greatly appreciated. Thanks, Justin Nope... Beautiful... eh? Rob Schrader FrontLine Technology "justin" <justinda@invision.net> wrote in message news:063e01c33fdf$10f73840$a001280a@phx.gbl... > > Is it possible to programmatically pass parameters to a > crystal report? say a quote id... > > If so, how is this done? > > Any insight would be greatly appreciated. > > > Thanks, > Justin You're...

CRM Errors 07-05-07
I've got several users encountering the same error: [WebException: The request failed with the error message: -- <html><head><title>Object moved</title></head><body> <h2>Object moved to <a href='/CustomErrors/InvalidAccess.htm'>here</ a>.</h2> </body></html> --.] System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall) +1296 System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Obj...

Date Picker
Hi, Could someone help me with a code for a date picker so that when I click a cell say E5 this will bring up the following dates either by calendar or a sequence of dats for 1 month. Many thanks Mark Simple solution: On another worksheet in the same workbook: In cell A1 enter =today() in cell A2 enter =A1-1 autofill down to row 30 or 31 (however many days you want) While still on this sheet, create a named range that represents that range of cells, say A1:A31. For example, call it MyDateList On your main worksheet, go back to the cell where you want the date. ap...

newsgroups 08-29-10
Hi, I'm using Windows 7 - IE 8 - Windows Live Mail. Since a week ago, I notice that the groups are not synchronized when pushing on the button. Is this due to some installed updates ? Thank you for the help. Jaypie "Jaypie" <jaypie281@sympatica.ca> wrote in message news:i5ee6p$dt$1@news.eternal-september.org... > Hi, > > I'm using Windows 7 - IE 8 - Windows Live Mail. > > Since a week ago, I notice that the groups are not > synchronized when pushing on the button. > > Is this due to some installed updates ? > ...

Indexing Service 07-09-03
Does Indexing Services need to be running only during the CRM Sales for Outlook installation or does it need to be running at all times on the client computer? All times "JawKnee" <jfle37@hotmail.com> wrote in message news:0a0101c3463b$f7ca7040$a001280a@phx.gbl... > Does Indexing Services need to be running only during the > CRM Sales for Outlook installation or does it need to be > running at all times on the client computer? > > Maybe I am wrong just disabled the indexing service on my client computer and sales for outlook still runs. I am running t...

Powerpoint 2007 03-12-10
I have tried to change the theme or template to an existing presentation but Powerpoint will not apply the change? Suggestions? How are you going about changing the theme/template? What is happening -- or not happening, as the case may be? Which version of PPT? Assuming PPT 2007, you might try hitting the Reset button on Home tab | Slides group (next to the New Slide button). Sometimes if there are a lot of customizations to the slide in question, you have to reset its layout. -- Echo [MS PPT MVP] http://www.echosvoice.com What's new in PPT 2010? http://www.echosvoic...

Date Add
Hey all, Im trying to do somthing that I am guessing is quite simple. Im working on a Db that tracks a fleet of rental skiis. Every 2 years these skis must be tested. I have a table that tracks all the information about a given pair of skis, including the date they were last tested. What I am trying to do is have a feild within that table autocomplete using the [test date] feild, plus 2 years. This new feild would then become the date when the next test was required. The Table is called [Ski Info] The first date feild is [Test date] the feild I would like to have calculated is [Next te...

data in row didnt match import type 12-30-05
while importing almost 800 accounts , i got the following error for majority of accounts 'Data in row did not match the import type' what is the solution? How can i match it? whats is the way of comparision? It looks like a data problem. You need to check if all dates are correct (and filled), if numbers are numbers (not letters), etc... Regards, -- Erik van Hoof CWR Mobility Check our weblog at: http://www.cwrmobility.com/weblog "Aam" <Aam@discussions.microsoft.com> wrote in message news:C51E8ED0-443D-4B35-B436-0A9EDB09B419@microsoft.com... > while impo...

Using MS Access to Manipulate Excel Workbook
I have an Excel Workbook that is a front end to an Oracle databse (9i). The excel spreadsheet has a button I click to Logon to the Oracle database and then it has another button I click that brings up a box where I enter my SQL Code. I was wondering if there was anyway I can do this through Access? So that Access will log me on to this spreadsheet and send the SQL Code that I want to use.. Thanks for any help Unfortunately, the answer is "it depends". Try recording a macro in Excel while you're logging onto Oracle and entering the SQL. If you can play back the macro in Exc...

Count Consecutive Dates
I have a database that allocates people to jobs, which is based ontheResource Scheduling database.I would like to show on a Form the current amount of shifts anemployee has worked, by counting consecutive dates from a given date(ScheduleDatetxt on the form).Relevant tables used in the formEmployeesEmploy_Ref (text)Surname....ScheduleDetails (Where jobs allocation is saved)ScheduleDetailsIDScheduleIDEmploy_Ref (text)Schedule (job date is saved)ScheduleIDScheduleDateIs there anyway I can do this using VB or SQL? Any help would begreatlyappreciated....

Column-Line Chart w/ Dates
I have a chart that is using columns for category "A" on the primary x-axis (a $ amount). This category is broken down first by person, then by month. So essentially from left to right the columns will be person "1" and month "1", person "1" and month "2", person "1" and month "3", person "2" and month "1", and so on.... The chart is also using lines to mark category "B" on the secondary x-axis (a $ amount that is a portion of category "A", but not a percentage. Essentially c...

Return a date range based on today's date
I need to build a query (to be used for eternity) to return records for dates between 01 July (the year prior) and 30 Jun (the current year) based on the date when the query is run. Is this possible? Take a look at the DateSerial() function in Access HELP. It should provide you the mechanism for doing what you're describing. Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code o...

Qry with dupes looking for only most recent date
Hello all, I have a query I'm running and when it pulls it has a bunch of duplicates, which is expected. Is there some type of criteria or something I can do to only pull the most recent occurence of each result? Thank you! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201002/1 Four ways to do it at http://www.mvps.org/access/queries/qry0020.htm Vanderghast, Access MVP "The Joker via AccessMonster.com" <u17775@uwe> wrote in message news:a3d2d69829281@uwe... > Hello all, > > I have...

turning a short date (11/21/07) into a long date (21st Nov 2007)
I've got a form for entering information into my database. The first piece of information entered is the date time group (in the format yymmdd e.g 071101 is the 1st nov 2007). This is the primary key and so is a unique number in the records. the next fields are year, month and day. im trying to get it to work so that these 3 fields automatically fill themselves out based on the information in the date time group field (i.e with the example above, 2007 would automatically go in the year field, nov in the month etc). any help would be much appreciated, thanks a lot =?Utf-8?B?YmVubnk=...

OLE Notes
Does anyone know why I am not seeing the OLE notes button in the customer card note attachment window? Thanks! Craig Craig, Make sure that the OLE objects Path is defined in your DEX.ini. Regards, -- Mohammad R. Daoud MCP, MCBMSP, MCTS, MCBMSS Mob: +962 - 79 -999 65 85 Great Package For Business Solutions daoudm@greatpbs.com http://www.greatpbs.com http://mohdaoud.blogspot.com/ "Craig" wrote: > Does anyone know why I am not seeing the OLE notes button in the customer > card note attachment window? > > Thanks! > > Craig Yes, the path has been specifie...

Comparing Tables 02-22-10
I have looked at the postings for this but don't quite understand. I have an excel doc that is now a table used in DB. There are several fields, Carrier, Client_Name, Appeals_Phone, Fax, Comp_Add2, City, State, Zip I get a revised table every quarter and would like to compare the old and new and see what changes there are. For example Carrier 1234, Client Sesame Street, Appeals_Phone 999-999-9999, Fax 123-456-7890, Comp Ses St, Add2 1234 Big Bird Lane, Yardley, PA, 12346 Now a new list comes in and the address is different 4567 Big Bird Lane. How do I run a query against the 2 ...

Outlook client error 08-23-05
Hi, I get the following errors only in the CRM outlook for sales client with all users and from all workstations. In Outlook client: 1. When in any folder (example: the Leads folder) and you click the Expansion arrow next to the Lead name i get this error: "The stylesheet does not contain a document element. The stylesheet may be empty, or it may not be a well-formed XML document" 2. When i click the CRM contact button, the window opens and i can type all my information but when i click save, i get this error: "An error occurred while trying to synchronize the contact i...

Alternating Positioning of Header 03-12-10
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I'm writing a reference book, using Word 2008 if it will do what I need it to do. Each chapter will be its own section so that I can have a unique header, and I'll suppress the header on the first page of each chapter. However, I want both the header and page number to be to the left on even pages, and to the right on odd ones, and I can't seem to find a way to do this. <br><br>I used Word 2003 for Windows to write the first book I did, and I ended up hiring a guy to write a VB macro to...

Chart-date axis format
I am using date-times as the x axis. The differences between two point is not always the same. Could be 3 days, 8 days etc. But the data axis formats are in day order day 1, 2, 3, 4 etc.. I can't seem to modify teh axis format correctly to get what I need. What format option can I set so that if I have 3 data points on day 1, 5 and 15 that I have 3 dates on the x axis instead of the 15 I get by default. Thanks in advance for any hints/help Right click the chart; select Chart Options On the Axes tab; chance the Primary axis from Automatic (or Time Series) to Category best wishes --...

Date display in Pivot Chart
I have a piviot chart and the x-axis has dates. It is currently displayed as "7/31/08" and I want it to be "Jul-08". I've tried to change the "Field Settings" in the pivot table but the dates still show as 7/31/08 in the chart. HELP!! I'm pulling my hair out!! Hi, Since you didn't tell us what version of Excel, lets assume 2003 - In the pivot chart right-click on the field button for the Date field, choose Format Pivot Chart Field, click Number, and either use one of the built-in date formats or choose Custom and enter your own. -- If this...

How do I set up a formula to flag up a future expiry date say 60 d
excel 2003 How do I set up a formula to flag up a future expiry date of say 60 days Conditional formatting. Select the cells, say A1:A10 Menu Format>Conditional Formatting Change Condition1 to Formula Is Add a formula of =A1=TODAY()+60 Select the pattern tab, choose a colour exit out -- HTH RP "Terrano2" <Terrano2@discussions.microsoft.com> wrote in message news:88511434-A400-45C7-A25C-5F5BFF06816E@microsoft.com... > excel 2003 > How do I set up a formula to flag up a future expiry date of say 60 days ...