Using Between...And with General Date

I am trying to query all records created between given dates.  The field, 
DtRecCrt, is a general date field that is set to =Now() whenever the records 
are created.  However, when I run the query and have the criteria for 
DtRecCrt set to "between [start date] and [end date]," it will return records 
up to but not including the end date specified.  How do I make the query 
include records created on the end date?  Thanks
0
Utf
4/30/2010 8:08:04 PM
access.queries 6343 articles. 1 followers. Follow

3 Replies
939 Views

Similar Articles

[PageSpeed] 39

On Fri, 30 Apr 2010 13:08:04 -0700, mewins wrote:

> I am trying to query all records created between given dates.  The field, 
> DtRecCrt, is a general date field that is set to =Now() whenever the records 
> are created.  However, when I run the query and have the criteria for 
> DtRecCrt set to "between [start date] and [end date]," it will return records 
> up to but not including the end date specified.  How do I make the query 
> include records created on the end date?  Thanks


Your Date Field includes a Time value.
Therefore any data past 00:00:00 AM of the [EndDate] is not within the
Between [FromDate] And [EndDate] criteria.
Easiest work-around is to just add one day manually to the [EndDate]
when prompted, i.e. instead of entering 11/24/2005 enter 11/25/2005.

A better work-around would be to set 
[FromDate]
and
[EndDate]
and their expected Datatype as query parameters in the Query Parameter
Dialog (Click Query + Parameter).
Then change your criteria to:
Between [FromDate] and ([EndDate] + 1)

The best solution, if the Time of day is not necessary, is to run an
Update Query to remove the time value from the date:

Update YourTable Set YourTable.[DateField] = DateValue([DateField]);

Then make sure the time is not included in any new entries, i.e. use
Date() instead of Now().

-- 
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
0
fredg
4/30/2010 9:01:59 PM
Hello,

Try using this as your criteria - 

>=#1/1/2010# And <=Now()

Put your start date between the '#' signs. That's how I search in my 
database and it includes the start date I selected and any records that have 
the current date on it.

I hope that helps! :)



"mewins" wrote:

> I am trying to query all records created between given dates.  The field, 
> DtRecCrt, is a general date field that is set to =Now() whenever the records 
> are created.  However, when I run the query and have the criteria for 
> DtRecCrt set to "between [start date] and [end date]," it will return records 
> up to but not including the end date specified.  How do I make the query 
> include records created on the end date?  Thanks
0
Utf
4/30/2010 9:02:01 PM
mewins wrote:

>I am trying to query all records created between given dates.  The field, 
>DtRecCrt, is a general date field that is set to =Now() whenever the records 
>are created.  However, when I run the query and have the criteria for 
>DtRecCrt set to "between [start date] and [end date]," it will return records 
>up to but not including the end date specified.  How do I make the query 
>include records created on the end date? 

That's to be expected because the time part from Now is
after the time part (midnight) in [end date].

To deal with that change your criteria to:
	>=[start date] And <DateAdd("d", 1, [end date])

-- 
Marsh
MVP [MS Access]
0
Marshall
4/30/2010 9:14:32 PM
Reply:

Similar Artilces:

Date construct with DatePart is kicking my.... well, you know.
Private Sub cmdCreatePath_Click() Dim strDateCons As String <bunch of my code that is (surprisingly) working like it should> strDateCons = DatePart(yyyy, Me.txtDateEntered) & "-" & DatePart(mm, Me.txtDateEntered) & _ "-" & DatePart(dd, Me.txtDateEntered) Debug.Print strDateCons End Sub When I click the magic button... nuffin'! I get an error message that states "Compile error: Variable not defined" and it highlights the 'yyyy' part of my first DatePart statement. What I actually wanted it to do was to take the date ...

How to use a DLL!?
Hi all, according with your experince: 1. I'm exporting class functions from DLL instead of global function, is there something i've to check!? 2. I need to run a DLL func in a separate thread, at moment the app create the thread and exec this func. I actually prefer the DLL to start the thread, any problems doing so!? Thanks you all!! Ale ...

No results using DateDiff
I am working with DateDiff . I get no error messages but also do not get any results in my calculated field. I have two dates, START_DATE and END_DATE and I need to know the number of days between them. START_DATE and END_DATE are both Date/Time type with a Short Date format. I am working in the QBE grid to write the query. I select an empty column and put my DateDiff calculation on the line below Or: in the Criteria area. I have tried: [END_DATE]-[START_DATE] and DateDiff("d",[START_DATE],[STOP_DATE]) Both approaches ask for parameter values for Time and STOP_DATE. If I d...

Changing a text field to a date field
Hi I know this is a problem for lots of people but I still cant find an answer. I have imported a text file into excel with a number field that displays credit card expiry dates as a 4 digit number. example 0408 so month + year. I have converted the number field to a text field so it doesnt drop off the first zero and now I need to convert it to a date field. Any nice easy simple ideas. Any help would be much appreaciated. Or direction to a question the same. Perhaps =DATE(100+RIGHT(A1,2),LEFT(A1,2),1) format as date -- daddylonglegs --------------------------------------------...

Date and time onto a form
Hi, I am designing a form that my client would like the date and time automatically recorded in a field and stored so that when they refer back it shows when the record was done. The 'NOW' function only shows the current date & time and keeps updating. Is there a way of putting the 'NOW' value into a field, then locking it so it doesn't change when the record is reopened. Hope that makes sense! Kazlou The form stores data in a table. You need to add a date/time field to your table to record the date and time a record was last updated. 1. Open the table in desig...

Transfer Files Using VBA
Hi, I'd like to transfer files from several drives on my pc to one cetnral location. From A:\Office1\ B:\Office2\ C:\Office3\ To D:\HeadQuarters\ My routine now is the following 1) Copy the files from drives: A, B, and C; and then 2) Paste the filesto the D drive 3) Delete the files permanently from drives A, B, and C. As you see this is a tedious routine, which I'd liket o avoid by using VBA. Can someone start me off with some code on how to accomplish this. Thanks so much in advance! Jrew Jrew, You could create a batch file (from the olden days) using XCOPY and desired bel...

date #4
Hi All I live in South Africa, and I have an interesting problem that persists in Excel 2003 and 2007. =TEXT(NOW(),"dd mmm yyyy") returns 15 Dec 2006 =month(now()) returns 12 =text(month(now()),"MMMM") returns January =year(now()) returns 2006 =text(year(now()),"yyyy") returns 1905 Why would this be? Thanks Chris Chris, =text(month(now()),"MMMM") should be =text(now(),"MMMM") and =text(year(now()),"yyyy") should be =text(now(),"yyyy") or just =year(now()) Otherwise, you are calcing the month of the 12th day afte...

Date question 01-14-10
Can anyone help with this function: =IF[Date Parts Ordered Completed]+ [Date Part Completed]+[Date Purchasing Completed],not blank,=Date The goal is that is all three fields have a date in them, then put in today's date in the final field. If one of the dates is missing, then the final date is blank. The only problem is if I open up that form I do not want it to change the date to today. I want it to stay the date of the actual completion. Any help is greatly appreciated. Thanks, P-Chu -- Message posted via http://www.accessmonster.com Try this -- Completion_...

Use of subkeys in SetRegistryKey
The following code causes ini settings to be stored in HKEY_CURRENT_USER\Software\Company\Product Group\<application name> \<section name>\<value name> SetRegistryKey("Company\\Product Group"); This is what I want it to do, but there is nothing in the documentation to say that this should work. Is this unsupported behaviour that may not work in future windows versions or supported but undocumented behaviour? Thanks <dimstthomas@yahoo.com> wrote in message news:1190714009.923549.98910@k79g2000hse.googlegroups.com... > The following code causes ini sett...

How to show the month of the referenced cell (containing a date)
If a cell holds the date "2/1/08", what formula can I use that will give me the following result: "Feb - 08" I tried =Month(a2)......but I just get the number of the month. Thanks. =TEXT(A2,"mmm - yy") Or simply =A2 with the cell custom formatted mmm - yy HTH. Best wishes Harald "Dave K" <fred.sheriff@gmail.com> skrev i melding news:165b03da-19ba-40b8-b9fe-77b17ef045b5@d45g2000hsc.googlegroups.com... > If a cell holds the date "2/1/08", what formula can I use that will > give me the following result: > > "Feb - 08&...

'Recently used file' list.
When I open Excel nine filenames appear on the RHS as 'recently used files' (this number was set via Tools/Options/General, of course). In the middle of this list are 3 no-longer-used files. Though the files themselves have been deleted their names remain in this list. Is there an easy way to remove those entries from that list, please? TIA, DB. There is an entry in the help file "Display or hide recently used workbooks on the File menu" which says uncheck the 'recently used files' checkbox, the click OK. Then, basically, start again. "DB.&...

Using Outlook with a VPN
Yesterday i set up a VPN so as to allow our employees to work from home, today one of the employees highlighted a problem with it though. When connected through the vpn, they are unable to access their Outlook emailand insted have to disconnect and then access through a normal dial-up. Any thoughts? Many Thanks Try this. When you get the VPN login screen, choose Properties. On the Options tab, check the box next to Include Windows logon Domain. That helped me. Bob >-----Original Message----- >Yesterday i set up a VPN so as to allow our employees to >work f...

GL Posting date is missing or invalid error message
Hello, When trying to post a Sales order through Sales transactions entry window, we get an error message 'General ledger posting date is invalid or missing' The dates seem correct. The fiscal periods are all set up correctly. This happens whether we try to post an individual transcation or a batch. The batch looks like it is posting, but all the posting reports have zero mamounts in them & the batch still sits there even after posting as if it has never been posted. We are running GP 8.00g34 What is causing this & how do we fix it? Thanks Did you recently change your pos...

Frustrated Beyond Measure
I loved Money 2005, recommended it to friends and got 2 great years of use out of it. Then, it forced me to upgrade to continue online services. I didn't mind since it was only $40 and I got two good years out of it. However, Money Plus Deluxe has been a nuisance. I have never gotten portfolio manager to work. I spent 2 weeks sending emails with support where the guy eventually said "if this doesn't work, I can't help you." Great... shoudl I go buy Quicken? This weekend, I decided to try to completely uninstall Money and try a reinstall to get Portfolio Manag...

Issue when using Outlook 2003 on Exchange 5.5 SP4
Hi! I have seen issues on using Outlook 2003 on MS Exchange 5.5 SP4. Can anyone guide me on how to handle this? M P What issues have you seen? -- Ed Crowley MVP - Exchange "Protecting the world from PSTs and brick backups!" "M P" <mark@textguru.ph> wrote in message news:eoqSaBjTGHA.5884@TK2MSFTNGP14.phx.gbl... > Hi! > > I have seen issues on using Outlook 2003 on MS Exchange 5.5 SP4. Can > anyone guide me on how to handle this? > > M P > It was pretty easy for me to find this in the Microsoft Knowledge Base: http://support.microsoft...

Exchange no longer available
Courtesy of Katrina, my brother-in-law's job and Exchange server are no more. He knows he has personal folders and contacts on his laptop, but Outlook choles on not connecting to Exchange and won't let him see the local stuff either. How does one tell Outlook not to try to connect to Exchange and instead use the local pst file? It might be easier to create a new profile and then use File Open to access the personal folder file, you create a new profile in Control Panel || Mail <philmcin@yahoo.com> wrote in message news:1126736961.961262.92120@g43g2000cwa.googlegroups.com... ...

Date range for a report
Hi I am trying to run a query and can't seem to nail down the right code. We use the access database to track files, incoming/outgoing correspondence dates etc. I am trying to run a query that show me files with dates in a follow up field of -60 days to +7 days, so essentially any follow ups missed in the last 2 months and up coming in the next week. This report is run on a weekly basis. Appreciate any help Thanks Assuming that the follow up field is actually a date/time data type, try this in the criteria: Between Date() - 60 and Date() + 7 -- Jerry Whittle, ...

How to use excel file created in version earlier than 4.0a in 4.0
Thanks to whomever answered my query but when I opened the email the screen was blank. My laptop is sick. My question was and still is, how can I use my Excel files created in a recent version on my laptop in my PC which is loaded only with Excel 4.0a . Please and thanks. -- Thanks, Ajimmo Hi Ajimmo You can't, the file format changes twice since version 4. You need a newer Excel version, a compatible spreadsheet program, or simply a viewer if all you want to do is view / print the file. HTH. Best wishes Harald "ajimmo" <ajimmo@discussions.microsoft.com> skrev i m...

Solution: Error 2306: There are too many rows to output (Access to Excel using OutputTo)
I've joined the legions who've encountered this problem with the OutputTo method in Access: Run-time error 2306 There are too many rows to output, based on the limitation specified by the output format or by Microsoft Access A lot of people seem to have encountered it. The reason is because OutputTo defaults to an old version of Excel which only allows 16K rows A lot of people also suggest using TransferSpreadsheet instead, but that caused me a lot more problems (putting a single quote in front of my text data) Intellisense for the command does not list what your options are for th...

having problems using performanceCouter
hi, i've made a program that shows the CPU idle time using the performance counter, using Visual C# . however, from some reason, it works on any computer other than my home PC . i've searched on the net but couldn't find what causes it. what i get is an exception that if is not caught, closes the program. here's a picture of what i get while debugging: http://img294.yfrog.com/i/picture20091211182403.png/ what i think is that maybe some service of windows (that the performance counter needs) doesn't work well(or is just disabled) . my computer runs on Windo...

Using Jet to read excel file returns blank for last cell
With VB6 I am opening an Excel file to query the contents using ADO. I am then using the contents of the worksheet to create a format file that is used for a bulk insert into SQL Server. We designed the program this way so that users can use a spreadsheet to import data in any format they want and process the data based on values set in the spreadsheet. This keeps us from having to design new tables and format files for new record layouts which come down pretty often. Everything works fine usually. There is just one perculiarity that is happening with the Excel file. Sometimes, th...

Date Formula #3
I am having difficulty with the "NOW" formula. What I would like for it to do is when I enter a value in one cell have it update in another cell the date when the information was entered and that date stay permanenty. What I am having is the date changes to the current date everyday. Is this possible to do? Any help would be appreciated. Thanks. Todd You can do it with the change event of the worksheet This example will place the date/time in the B column if you change a cell in the range A1:A20. Place the code in the Sheet module Right click on a sheet tab and choose ...

using screen...
Hi, I have problem with the query in the cascade combobox when the form name change. Is there a way to replace the following using screen.activeform [Forms]![frmCoFundReview]![Partner] => screen.activeform.[Partner] ? SF Not that I know. You could use a separate query for each form. Each query would have the correct form name for the combo box. Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "SF" <samnangs@pactcambodia.org> wrote in message news:u%23z%23g6iyKHA.2644@TK2MSFTNGP04.phx.gbl... > Hi, > > I have probl...

General SOA question
As I was reading SOA Manifiesto, one line got my attention "SOA can be realized through a variety of technologies and standards." as of now, only way I can think of implementing bussiness service at this time which is decoupled and scalable is though web services. This gives cross platform/scalable/maintainable. Is there any other way we can implement services which has the same things? Thanks, I just started working with SOA, and the way I understand it (which is not very thorough at that) is that SOA is basically web services taken to the next level of interoperability (a...

HELP! Microsoft.Crm.Platform.Proxy is not intended for direct use, please use the public SDK.
Can someone post the following DLL's from CRM 1.2? They should be on the installation CD. If you want to e-mail send to robert[at] ockhamgroup.com Microsoft.Crm.Platform.Proxy.dll (1.2.3297.0) Microsoft.Crm.Platform.Types.dll (1.2.3297.0) Details... I'm attempting the following solution: Moving External Data into Microsoft CRM: Lead Generation http://msdn2.microsoft.com/en-us/library/ms913871.aspx http://download.microsoft.com/download/e/4/5/e45c4994-6878-4871- 8699-6793b8f13c34/leadgeneration.exe What I need is either: 1. a working compiled LeadGenerator.exe executable; o...