Long Date / Between and...

Hi all,
I just converted from 2003 to 2007 and at the same time I took the 
opportunity to “clean up” some in tables…I changed  [DateCreated] format from 
short to long date since the Date/Time stamp
Is valuable (previous I used one separate date and time) ..Here is the 
issue. Running the query  with the parameters in the [DateCreated] field, 
Between(First Date) AND (Second Date) does not function.. what am I doing 
wrong?

Thanks!

0
Utf
1/18/2010 1:43:01 PM
access.formscoding 7493 articles. 0 followers. Follow

4 Replies
829 Views

Similar Articles

[PageSpeed] 15

Changing the format of a field doesn't actually change what's stored in the 
field: it just changes how it's presented.

If DateCreated contains both date and time and FirstDate and SecondDate are 
both the same day, you won't retrieve any rows. Instead, try:

[DateCreated] >= First Date AND [DateCreated] < Second Date

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Peter" <Peter@discussions.microsoft.com> wrote in message 
news:9CB6824A-948B-4BA1-9A54-F3FD1BFAC02D@microsoft.com...
> Hi all,
> I just converted from 2003 to 2007 and at the same time I took the
> opportunity to "clean up" some in tables.I changed  [DateCreated] format 
> from
> short to long date since the Date/Time stamp
> Is valuable (previous I used one separate date and time) ..Here is the
> issue. Running the query  with the parameters in the [DateCreated] field,
> Between(First Date) AND (Second Date) does not function.. what am I doing
> wrong?
>
> Thanks!
> 


0
Douglas
1/18/2010 1:54:02 PM
"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_gmail.com> wrote in message 
news:%23SEzyWEmKHA.1824@TK2MSFTNGP04.phx.gbl...
> Changing the format of a field doesn't actually change what's stored in 
> the field: it just changes how it's presented.
>
> If DateCreated contains both date and time and FirstDate and SecondDate 
> are both the same day, you won't retrieve any rows. Instead, try:
>
> [DateCreated] >= First Date [DateCreated] < Second Date


Doug, shouldn't that second criterion be,

    [DateCreated] < (SecondDate + 1)

or the equivalent using DateAdd to add a day?

Assuming that FirstDate and SecondDate are entered as dates only, not 
dates+times, that is.

-- 
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

0
Dirk
1/18/2010 2:44:57 PM
Good catch, Dirk.

I was focussing on the < as opposed to <= and forgot to change the value!

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Dirk Goldgar" <dg@NOdataSPAMgnostics.com.invalid> wrote in message 
news:5C66DED6-028F-44E5-97F9-2401D4962C4A@microsoft.com...
> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_gmail.com> wrote in message 
> news:%23SEzyWEmKHA.1824@TK2MSFTNGP04.phx.gbl...
>> Changing the format of a field doesn't actually change what's stored in 
>> the field: it just changes how it's presented.
>>
>> If DateCreated contains both date and time and FirstDate and SecondDate 
>> are both the same day, you won't retrieve any rows. Instead, try:
>>
>> [DateCreated] >= First Date [DateCreated] < Second Date
>
>
> Doug, shouldn't that second criterion be,
>
>    [DateCreated] < (SecondDate + 1)
>
> or the equivalent using DateAdd to add a day?
>
> Assuming that FirstDate and SecondDate are entered as dates only, not 
> dates+times, that is.
>
> -- 
> Dirk Goldgar, MS Access MVP
> Access tips: www.datagnostics.com/tips.html
>
> (please reply to the newsgroup)
> 


0
Douglas
1/18/2010 4:02:31 PM
Thank you very much Douglas and Dirk..!

"Douglas J. Steele" wrote:

> Changing the format of a field doesn't actually change what's stored in the 
> field: it just changes how it's presented.
> 
> If DateCreated contains both date and time and FirstDate and SecondDate are 
> both the same day, you won't retrieve any rows. Instead, try:
> 
> [DateCreated] >= First Date AND [DateCreated] < Second Date
> 
> -- 
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
> 
> "Peter" <Peter@discussions.microsoft.com> wrote in message 
> news:9CB6824A-948B-4BA1-9A54-F3FD1BFAC02D@microsoft.com...
> > Hi all,
> > I just converted from 2003 to 2007 and at the same time I took the
> > opportunity to "clean up" some in tables.I changed  [DateCreated] format 
> > from
> > short to long date since the Date/Time stamp
> > Is valuable (previous I used one separate date and time) ..Here is the
> > issue. Running the query  with the parameters in the [DateCreated] field,
> > Between(First Date) AND (Second Date) does not function.. what am I doing
> > wrong?
> >
> > Thanks!
> > 
> 
> 
> .
> 
0
Utf
1/18/2010 4:32:01 PM
Reply:

Similar Artilces:

Date Function
I am running a query from a table that limits the output of data to a specific time frame. I am working on the last month minus 12 months. So what I want to see is from April of this year to May of last year. This is progressive as the months change. i have been using: Between (DateAdd("m",-1,Date())) And (DateAdd("m",-12,Date())) This gives me this years May which I do not want. Any help would be appreciated -- Message posted via http://www.accessmonster.com On Wed, 09 May 2007 19:18:04 GMT, "rollover99 via AccessMonster.com" <u14080@uwe> wrote: ...

error LNK2001: unresolved external symbol "long __cdecl _com_dispatch_method(struct IDispatch *,long,unsigned short,unsigned short,void *,wchar_t const *,...)" (?_com_dispatch_method@@YAJPAUIDispatch@
Hi, I have installed Visual Studio Express 2005, and the platform SDK. I'm trying to compile, but get this linker error. The only reference I found on the web was a post of this person describing the possible cause: http://www.eggheadcafe.com/ng/microsoft.public.platformsdk.sdk_install/post20679802.asp Any clues, hints.. ? Leo Well, since you didn't bother to put the error message in the body of the text, it is a bit hard to read. Please do not put content in the headers. I only see the first segment of the header in my reader. A header that contains interesting informatio...

Orders that have multiple release dates
I don't know of any other way to do this, but I am looking for suggestions. Many times, our customers order parts, but want only so much one month, so much the next, etc. In Great Plains, all of our orders are entered in as back orders, and for each release of parts to the customer, that is entered in as a separate back order. I have read here on the newsgroup that each line item can have its own require date, but then what? I have set that up in a testing company here. I entered in some back orders with the same parts on it, but with different require dates. Is there a report that w...

Date/ Phone log
I needed to create a spreadsheet for a phone log for my company. What it needs to do is when a long distance call is entered in, well say column B. Then the date and time the call was made needs to appear in column A. I've tried several different approaches but none work. Thanks for your help, Greg Take a look at http://www.mcgimpsey.com/excel/timestamp.html BTW - saying you've tried several different approaches but none work isn't particularly helpful - *what* methods did you try, and what didn't work? In article <AE61899F-B6C1-4BE0-9239-AA6B7602245F@micro...

Conditional Date Format.
I would like to conditionally format a date. I have read earlier posts on conditionally formatting numbers but I have been unable to transfer this to dates. Here is what I want and maybe someone out there has done this. If the date is before some cutoff point say 1970, I want the format to be blank, otherwise format the date in RED as m/d/yyyy. Is this doable? Thank you. Kevin You can't blank out the value within the cell using format|conditional formatting. But you could use format|conditional formatting to make the cell look empty--white fill on a white background. Select the ...

Milo: What is microsoft.windowslive.translator.btn? Name too long
In "C:\Documents and Settings\[myname]\Application Data\Microsoft\Windows Live\Toolbar\Custom Buttons", there is a folder called microsoft.windowslive.translator.btn that is causing me to have trouble backing up my computer. I'm being told that that path or folder name is too long. At first I thought it was the path, but trying to copy microsoft.windowslive.translator.btn DIRECTLY into the MyDocuments folder caused the same error, so now I'm pretty sure that it’s the 36 characters in that folder name that is the problem. Sure enough, when I renamed a copy...

Keyboard shortcut for current date and time
Hi, Ctrl+ ; inserts current date and Ctrl+Shift+; inserts current ti me Ctrl+Shift+; inserts the current time with the date serial as 0 and not the current date's date serial. Presently I am adding the two (ie current date and time) to get the current date and time. Is there a keyboard shortcut that does this? Thanks in advance. Regards, Raj CTRL+; then SPACE then CTRL+SHIFT+; -or- =Now() -- Jim Cone Portland, Oregon USA http://www.mediafire.com/PrimitiveSoftware "Raj" <rspai9@gmail.com> wrote in message Hi, Ctrl+ ; inserts current dat...

Formating cells with date format
Hi everyone. I have a worksheet with two columns. The first with date and the other with text formated cells. When I enter in the first one data with the year < 1900 the format never assumes the right date format Can anyone provide me with some explanation about this? Regs Alexandre Duarte http://www.cpearson.com/excel/datetime.htm "Alexandre Duarte" <AlexandreDuarte@discussions.microsoft.com> wrote in message news:71113928-7E5A-4446-AD44-82BFE938BBF2@microsoft.com... > Hi everyone. > I have a worksheet with two columns. The first with date and the other > wi...

date confusion
hi im new to excel 2007 i want to ask that when i type 7/4 in a cell excel thinks that the slash is division operator so it divides it ?? but in different cell it interpret it as date ok another problem is that when I enter 7/4 in the different cell excel interpret it as 4-Jul only but How do I display the year too ? even if i type 7/4/2008 or 7/4/08 it still shows 4-JUL ? Help please thank you very much Format the cell as m/d/yyyy, you created the d-mmm format by just entering 7/4 so you need to reformat the cell to the date format you want. Always use the full date when entering dat...

Long Filename Hunter
hello, is the anyone know of a program/freeware that can search the disk for files with "long" file names? it is laborious to drill down dozens of folders and hundreds/thousands of subfolders hunting down down long file names. for example I found a couple of tiles with filenames as long as a sentence, obviously using all the amount allotted for naming a file. thanks... ~db -- db���`�...�><)))�> DatabaseBen, Retired Professional - Systems Analyst - Database Developer - Accountancy - Veteran of the Armed Forces - Microsoft Partner...

Changing Publication Date and Pg. Nos.
I've created a newsletter using a Publisher template. At the top of each page, the template automatically inserts the volume and issue number on one side and the page number on the other side. I want to have the volume and issue number at the left and the date at the right on the first page. On the inside pages, I want the page number, newsletter name, and date to be at the top of each page, with the publication name centered and the page number and date alternating positions (because the newsletter will be printed front and back). I've had no success trying to find the wa...

Long Long Long Nested If Function
Hello, I need some help on a very long nested if formula. Or at least thats the type that I have been trying to use to get the calculations on my spreadsheet. I have this formula which is working for me but it is only one of the 5 variables I need to include in it. 1) =IF(B5="cdy",IF(P23=20,1625,(IF(P23=40,1875,2200)))) to this formula I need to add =IF(B5="nwr",IF(P23=20,850,(IF(P23=40,935,1260)))) =IF(B5="lsp",IF(P23=20,1125,(IF(P23=40,1225,1550)))) =IF(B5="mtrl",IF(P23=20,1035,(IF(P23=40,1150,1500)))) =IF(B5="trto",IF(P...

PLEASE HELP! need conditional formatting related to date, blank cells and today's date
I want to create a conditional formmatting formula that will highlight a cell if that cell is blank and another cell that has a date in it is more than 30 days from today's date. So, if J2 has a date in it and that date is greater than or equal to 30 days past today's date AND K2 is blank, I want to highlight K2 red. If K3 is not blank I dont want it to be highlightd. can anyone help me with the correct formula/conditional formatting? Thanks so much! Try this in the Formula box of the CF dialogue: =3DAND(K2=3D"",J2-TODAY()>=3D30) If J2 is in the past (not clear from...

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_...

Dates 01/1/02005 comes u ps as 0.011111111
When I format for dates in a column I get this bogus number. How can I fix it so the column reads the date entries????? Thank you Discounting the typo, maybe you have a Lotus 123 setting enabled. Tools|Options|Transition Tab Uncheck Transition Formula Entry (I unchecked all those Lotus 123 settings.) DateConfused wrote: > > When I format for dates in a column I get this bogus number. How can I fix it > so the column reads the date entries????? > Thank you -- Dave Peterson ...

Does Validation works for date ??
Hi Does data validation works for date ?? For example : A1 contains today's date in it & if some one tries to put yesterday's date in B1 it will not allow(we can put tomorrow's date).How it would be possible ?? -- Little Master ------------------------------------------------------------------------ Little Master's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28227 View this thread: http://www.excelforum.com/showthread.php?threadid=480089 I just used data validation yesterday for a date. What exactly do you want to check? "Little Ma...

Date Calculation 05-17-07
I have two date fields in the format of dd/mm/yyyy. I want to be able to do a calculation where it calculates the difference between the two dates. Therefore the output will display the number of days. -- Thank you for your help how does this work?? -- Thank you for your help "KARL DEWEY" wrote: > Use DateDiff function. > -- > KARL DEWEY > Build a little - Test a little > > > "Kirt84" wrote: > > > I have two date fields in the format of dd/mm/yyyy. I want to be able to do a > > calculation where it calculates the difference ...

check between 2 dates
Hi Is it possible to have excel check a date range from something like a form? for example: I have various data entries on a sheet, the first column has dates (01/01/04) format. I would like to be able to check something like: 01/01/04 to 05/01/04. Thanks in advance Paul Hi Paul! Try: =IF(AND(A1>=--"2004-01-01",A1<--"2004-05-01"),TRUE) OR: =IF(AND(A1>=DATE(2004,1,1),A1<DATE(2004,5,1)),TRUE) OR: =IF(AND(A1>=E1,A1<E2),TRUE) The third form is usually best as hard coding makes formulas less adaptable. The second form might be better if the DATE argument...

Replace Cell Address in A Long Formula
From time to time, I need to do some change in some very long formula. For example: =TRIM(IF(ISERROR(FIND(" ",B46,FIND(" ",B46,1)+2)),MID(B46,FIND(" ",B46,1)+1,LEN(B46)),MID(B46,FIND(" ",B46,FIND(" ",B46)+2),LEN(B46)))) I want to replace all the "B46" with "F18". Is there a easier way? Thanks. One quick and dirty way is to Edit>Replace B46 with F18. HTH Anders Silven "plumstone" <plumstone@discussions.microsoft.com> skrev i meddelandet news:29A2448B-1055-4847-9779-EA0FDDA5C05D@microsoft.com... > From...

Inserting Holidays and special dates in Calendar
Is there any easy way to insert Holidays and special dates into a publisher calendar? There is no easy way.... You can merge. What database programs do you have? -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "bwarten" <bwarten@discussions.microsoft.com> wrote in message news:EE1E6121-AC8F-4393-9B6D-26B5BBED5E7D@microsoft.com... > Is there any easy way to insert Holidays and special dates into a publisher > calendar? ...

Date changing to current date when doc reopened
When I draft a letter and save it and then reopen it later, the date always changes to the current date, even when I do not use the auto insert date. i erase the date and put it in manually but it still happens. -- Michele Michelle, sounds like you used teh insert date feature of Word. This inserts a current field, which will update every time opened. Try replacing the current date field with teh Create date field. HTH "Michele" wrote: > When I draft a letter and save it and then reopen it later, the date always > changes to the current date, even when I ...

how long does outlook 2003 save sent items?
i just looked today and all of my sent items from the past 4 years are GONE!!! i do not use archiving, and i have checked my old archive folder for them, they are not there. any help would be much appreciated - r gatestoo <gatestoo@hotmail.com> wrote: > i just looked today and all of my sent items from the past 4 years are > GONE!!! Then you deleted them somehow or are hiding them with a view filter because Outlook doesn't delete anything automatically, unless you've instructed it to do so. -- Brian Tillman ...

Date in adjoining field
I am new at this. Any help would be appreciated!! I have a spreadsheet with data in L2. When the data in L2 is changed, I would like excel to automatically post today's date in K2. Thanks for your help!!!!!!!!! Keith Thompson For a possible solution see: http://www.mcgimpsey.com/excel/timestamp.html -- For everyone's benefit keep the discussion in the newsgroup. Regards, Peo Sjoblom "Unorganized" <boltman17@comcast.net> wrote in message news:ohk2b0d657fd7m486etop96jkluqj9rljt@4ax.com... > I am new at this. Any help would be appreciated!! I have a > ...

long, LONG anf LPARAM
Hi Guys, A very Basic Question.Can anyone tell me whats exactly the difference between long, LONG and LPARAM in windows.If they all are same then what was the need to having 3 different names.any idea..?? thanks and regards, Sada "sada" <sadashiv.borkar@gmail.com> wrote: >Hi Guys, > A very Basic Question.Can anyone tell me whats exactly the >difference between > long, LONG and LPARAM in windows.If they all are same then >what was the need to having 3 different names.any idea..?? "long" is a primitive type in C++. Windo...

Sum Range of Dates
Can you do the following? I have a big long spreadsheet with a lot of different dates. Can I find out the results of one particular date in one cell at the top of the spreadsheet? For instance, how much profit was there on 10/6/10? Or even still, how much profit was there TODAY. Even though today is ongoing and not completed. In other words I can see how the day is shaping up even though all haven't reported in. Thanks Yes, I can. Look for help on the SUMIF function. Or see my earlier post which gives an example that should fit your needs. On Sat, 09 Oct 2010 18:19:32 -0700, Jim...