 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / May 2008Extracting by Date
I have a query that is extracting from a table by date using a date as a beginning point and then working backwards. It is supposed to pull a weeks worth of data which would be 84 data points, And it usually does. However now it is pulling 96 data points if I make the equation (date -8) or 72 data points if I make the equation (date-7) which is 8 days or 6 days. It will not pull 7 days worth of data! This is the equation I have tried in various forms. Combo0 would be Mondays date. >=([Forms]![Print Weekly Reports]![Combo0])-8 And <=([Forms]![Print Weekly Reports]![Combo0]) I have also tried >=([Forms]![Print Weekly Reports]![Combo0])-8 And <=([Forms]![Print Weekly Reports]![Combo0])-1 and >=([Forms]![Print Weekly Reports]![Combo0])-7 And <=([Forms]![Print Weekly Reports]![Combo0]) How can I get 7 days of Data? The table is correct Thanks in advance. Just change your formula a bit. >([Forms]![Print Weekly Reports]![Combo0])-7 And <=([Forms]![Print Weekly Reports]![Combo0])  SignatureDave Hargis, Microsoft Access MVP > I have a query that is extracting from a table by date using a date as a > beginning point and then working backwards. It is supposed to pull a weeks [quoted text clipped - 15 lines] > > Thanks in advance. That didn't work. The dates I am trying to pick up are Monday through Sunday. For some reason I keep getting either Sunday through Sunday (8days) or Tuesday through Sunday(6days) and Monday won't come in to the query. The Combo0 is also a Monday date, but it is the date I want to go back from??? > Just change your formula a bit. > >([Forms]![Print Weekly Reports]![Combo0])-7 And <=([Forms]![Print Weekly Reports]![Combo0]) [quoted text clipped - 18 lines] > > > > Thanks in advance. So Combo0 has a Monday date and you want to extract data that starts on the previous Monday and goes through Sunday. For example: Combo0 = 5/19/2008 And you want data from 5/12 through 5/18? Another question, do your date fields have time in them? When trying to filter dates with times in the date fields, it can give you incorrect results. Let me know, and I can show you the correct formula for your situation. SignatureDave Hargis, Microsoft Access MVP > That didn't work. The dates I am trying to pick up are Monday through Sunday. > For some reason I keep getting either Sunday through Sunday (8days) or [quoted text clipped - 23 lines] > > > > > > Thanks in advance. Yes, I want to go from the 12th through the 18th of May on this particular report. The Combo0 field does have a time field. When I tried to make it a general date it didn't pull any data at all. I have been flipping this formula back and forth from -8 to -7 for several monthes now to pick up Mondays date. > So Combo0 has a Monday date and you want to extract data that starts on the > previous Monday and goes through Sunday. For example: [quoted text clipped - 33 lines] > > > > > > > > Thanks in advance. What about the date field in the table? Does it have time or not? SignatureDave Hargis, Microsoft Access MVP > Yes, I want to go from the 12th through the 18th of May on this particular > report. [quoted text clipped - 40 lines] > > > > > > > > > > Thanks in advance. Yes, both the combo0 and date field in the table have date and time. The table is formatted in a short date, but the input mask is 99/99/0000;0; And the combo0 is imported on a macro to a caption. the expression is Now() > What about the date field in the table? Does it have time or not? > [quoted text clipped - 42 lines] > > > > > > > > > > > > Thanks in advance. The table is formatted in a short date Not really. Dates are carried as floating point decimal fields and have no internal formatting, Any formatting is done when the data are retrieved, not when it is stored. Input Masks only control how the user is allowed to enter values, not how the values are stored for dates. For text fields, you have that option; however. Formats only control how the value is to be displayed when retrieved. Again, it has no effect on how the value is stored. the input mask is 99/99/0000;0; This means the user can only put in month, day, and year. There is no facility to enter time. the combo0 is imported on a macro to a caption This part I don't understand. But since you have times in the fields, you need to allow for that when you are filtering. That is because when you do the comparisons, it considers the entire value so that if you say <=#5/20/2008# That implies 05:20:2008 00:00:00 AM If the value in the field is 05:20:2008 00:00:01 AM it will be excluded, because it is greater than the compare value. For this reason, you should only use time value in a date field when the time is important; otherwise, to compare by date only, you need to exclude the time value. You can do that using the DateValue() function. It returns only the date part. So to get what you want, assuming your combo is 5/19/2008 and you want the data form 5/12/2008 through 5/18/2008 BETWEEN DateValue(DateAdd("ww",-1,[Forms]![Print Weekly Reports]![Combo0])) AND DateValue(DateAdd("d",-1,[Forms]![Print Weekly Reports]![Combo0])) SignatureDave Hargis, Microsoft Access MVP > Yes, both the combo0 and date field in the table have date and time. > The table is formatted in a short date, but the input mask is 99/99/0000;0; [quoted text clipped - 46 lines] > > > > > > > > > > > > > > Thanks in advance. We are almos there! I am picking up the 12th (M0nday now but I am not picking up the 18th(Sunday). I see where the "d" is the day but what is the "ww"? > The table is formatted in a short date > Not really. Dates are carried as floating point decimal fields and have no [quoted text clipped - 81 lines] > > > > > > > > > > > > > > > > Thanks in advance. Thanks again, I have it now. I replaced the -1 with a 0 and pulled all the dates I needed. WW stands for week of the year so now it is all falling into place. > The table is formatted in a short date > Not really. Dates are carried as floating point decimal fields and have no [quoted text clipped - 81 lines] > > > > > > > > > > > > > > > > Thanks in advance. Actually, the arguement tells the function what interval of time to add. d = day w = weekday (1 = sunday, 2 = monday, etc) ww = week m = month etc. I don't know why -1 doesn't work because it should return 5/18/2008. 0 would not add or subtract anything. SignatureDave Hargis, Microsoft Access MVP > Thanks again, > I have it now. I replaced the -1 with a 0 and pulled all the dates I needed. [quoted text clipped - 85 lines] > > > > > > > > > > > > > > > > > > Thanks in advance.
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.