Queries :: Filtering On Value Of Formatted Date Field
Feb 22, 2014
I have a database with a table of employees, and that has a column named "DaysOff", into which I type a couple of days like this: "SUNDAY AND MONDAY"
Next, I have a form onto which I will display a query of those employees. The form contains a field called DATE in which I display the mm/dd/yyyy date. What I want to do is this:
I only want those employees that have a DaysOff field that DOES NOT CONTAIN the WEEKDAY NAME of the date in the DATE field. So, for instance, if the DATE field read 2/22/2014, and I have three employees as such:
Employee 1 DaysOff "Monday and Tuesday"
Employee 2 DaysOff "Friday and Saturday"
Employee 3 DaysOff "Sunday and Monday"
In that case, ONLY EMPLOYEES 1 and 3 should show up. Employee 2 will NOT show up, because his DaysOff field contains the word "Saturday", and the WEEKDAY FORMATTED value of the DATE field is "Saturday"
I hope that's not too confusing. Again, this needs to be the filter criteria for the query. That query will only return a list of employees that are NOT off on the day in question. If one of their off days is the day in question, the query won't return that record.
I've tried a few things, but I can't get it to work. If you want, I can list the various things I've tried, but I imagine one of you knows exactly how to do what I need to do, and you won't need my feeble attempts as a springboard.
View Replies
ADVERTISEMENT
Jul 22, 2015
I need to import an excel file with incorrectly formatted date field and it worked only to import them as text:
1 jan 2015
1 feb 2015
1 mar 2015
etc.
Using CVdate converts jan, feb, nov and dec to correct date, but gives an error message with mar to oct.
View 1 Replies
View Related
Jun 12, 2013
In order to perform conflict searches in my Access 2007 database, you enter a bunch of information and then receive a report of cases with similar information in order to avoid any conflicts of interest.One of the fields is "Date of Accident". Currently I have its format set as yyyy/mm/dd. However, my expression
Code:
Date Searched: [Forms]![frmConflictSearch]![Date of Accident]
returns nothing (if I look at the query in Datasheet the column is blank).I found a fix for getting it to appear by removing the format on Date of Accident, however, my query doesn't retrieve cases with matching dates (the dates are formatted to dddd, mmmm dd, yyyy as a Date/Time Data Type).Is there any way I can get the query to both return the date searched, AND retrieve matching cases?
View 4 Replies
View Related
Aug 22, 2013
I am creating a access db for use with a training company. All is working great.
What I want to do is create certificates within Access and then print by the Course load. Easy enough and I can do this.
The courses usually last two or three days, so i want to create a field which has the start and finish date on the certificate formatted correctly.
I currently use this
Code:
CourseDate: DatePart("d",[StartDate]) & " & " & DatePart("d",[EndDate]) & " " & Format([EndDate],"mmmm") & " " & DatePart("yyyy",[EndDate])
within a query which returns this
29 & 30 May 2013
what i want to print on the certificate is this
29th & 30th May 2013
I need to have the 'st' or 'nd' or 'rd' or 'th' in the date.
View 3 Replies
View Related
Feb 27, 2007
I am using this formula to format my date field
Convert this:A601030730 to 03-Jan-06
A6 = Year
01 = Month
03 = Day
0730 = Time (not worried about that part)
ApptDate: IIf([abap_030] Is Null,"",Format(DateSerial(Val(Mid(Left([abap_030],6),2,1)),Val(Mid(Left([abap_030],6),3,2)),Val(Right(Left([abap_030],6),2))),"dd-mmm-yy"))
I get the date to "Look" the way I want, but Access does not seem to recognize the formatting as it will not sort properly?
Any suggestion would be welcome, thanks
Ziggy
View 3 Replies
View Related
Jan 14, 2014
Access version: 2013 / The user can click a button on the report that opens a form where they can select various filters. Once the user has selected the filters and clicks done, I have some VB code that fires which builds an SQL string with all of the filters selected. It then reopens the report with the new filter.
What I am running into issues with is one specific filter that the user can select. They can select a "from date" and "to date". Sometimes this filter returns what I want and sometimes it doesn't.
For example. I set 01/01/14 as the start date and end date as 15/01/14. <-- this works as desired.But if I put start date as 10/12/13 and end date 15/01/14 for example then it returns all previous records up to the specified end date. I.E dates such as 10/10/13 will show.
This is the VB code used to build the SQL for this filter:
Code:
newWhere = newWhere & "[FollowUpDate] > #" & Me.txtFromDateSelect & "# AND [FollowUpDate] < #" & Me.txtToDateSelect & "#" & " AND "
note: I have also tried using the between SQL syntax for querying the date to no avail and the same results.
This is the SQL that the above code creates:
Code:
[FollowUpDate] > #1/01/2014# AND [FollowUpDate] < #15/01/2014#
This only happens when I try select a start date prior to 12/12/13 (so the year is not the issue)
This happens both when I apply just the from date and to date filter and also when I mix it in with other filters. I.E Company name, product etc..
View 6 Replies
View Related
Dec 7, 2004
I have a table with sales by day. I want to display the data in a graph summarised by week but the period spans several years. If I format the date thus Format(MyField,"yyyy ww") then Access sorts the results thus 2003 1, 2003 10, 2003 11 but it should be 2003 1, 2003 2, 2003 4 etc.
How can I get Access to sort in ascending order correctly on the formatted date?
Thanks
View 2 Replies
View Related
Aug 14, 2013
I just wanted to know how can i set a parameter on the following cross tab query to filter dates. Date field is [pdate By Day].
TRANSFORM Sum([PettyMaster Query2].Amount) AS SumOfAmount
SELECT [PettyMaster Query2].[Petty Cat].Field2, [PettyMaster Query2].[pdate By Day], [PettyMaster Query2].ProjLoc, [PettyMaster Query2].descriptionofpay, [PettyMaster Query2].projno
FROM PettyMaster, [PettyMaster Query2]
GROUP BY [PettyMaster Query2].[Petty Cat].Field2, [PettyMaster Query2].[pdate By Day], [PettyMaster Query2].ProjLoc, [PettyMaster Query2].descriptionofpay, [PettyMaster Query2].projno
PIVOT [PettyMaster Query2].PettyCOA.Field2;
View 11 Replies
View Related
Aug 17, 2015
I've successfully created a DateDiff calculation that is generated with a d:h:nn format. Now the question:
How can i select or identify specific values, such as <= 2 days using those outcomes generated by the expression? Is it possible?
Example: a value is generated as 3:14:35. I would want to indicate this more than two days by putting a "N" in a separate column.
How is this done?
View 3 Replies
View Related
Jun 16, 2015
I am trying to show the Current Repair Estimate total in currency form on my report. The attachment shows my criteria, which works on the query, but does not show in the report. The second attachment shows what pops up when I try to view the report. What can I do to get this current rep estimate to show correctly in currency form on the report?
View 8 Replies
View Related
Mar 20, 2015
I suspect my design is flawed
Code:
SELECT tblData2.Prefix, tblData2.LineNum, tblData2.Year, tblComments.comment, tblComments.Address
FROM tblData2 LEFT JOIN tblComments ON tblData2.LineNum = (NumbersOnly([tblComments].[Address])
WHERE (((tblData2.MyYear)<1990))
ORDER BY tblData2.LineNum;
The NumbersOnly function returns a set of numbers from a custom formatted string. The above fails on the join (I think) but maybe there's another way of doing it?
View 4 Replies
View Related
Apr 26, 2013
I have a query based on a table which has a date field. the field both in the table and the query have the time also in the date value so when I try to query on a date I get nothing if I copy the date and time from the field I will get the result for that record if I just use the date I get nothing. I have tried the format which does display just date but if you click on the field the time is also there You must be able to query for a date only and get all the records.
View 11 Replies
View Related
Jun 8, 2013
I am trying to deduct a datetime field from a date field to decide how many days are in between. It doesn't let me. So do I need to convert the datetime field to a date field first? How would I do that?Or is there a function I can use to calculate the date difference?
View 2 Replies
View Related
May 17, 2007
This query is to show aging. I have a field for processed date. I have a query that allows a date range to be selected using the Between function.
The query runs and pulls these variables from a form. It shows only those accounts which were processed between those dates.
However, it is also showing all of the accounts that have no date in this field. I need to set it up so that if the field has no date then it doesn't show those accounts.
I've tried Not (is null) for the criteria but that isn't working. I've tried a lot of stuff but nothing seems to work.
Thanks for the help :)
View 1 Replies
View Related
Aug 11, 2005
i have created a new form based on a database called RptSmp97 from microsoft knowledge DB.
My form, as in the sample, calls a report when it is opened and at the same time shows you a multicombo form for filtering the report. In the sample the form uses only text fields, but i need to use numeric and date fields too. I could solve the problem with the numeric combos adding a condition for them, however, when i add another condition for the date it doesnt work.
Also, at the end of the code i print the sql and it looks perfect.
here is the code when the apply filter button is pressed....
Private Sub Command18_Click()
Dim strSQL As String
Dim intCounter As Integer
'Build SQL String
For intCounter = 1 To 15
If Me("Filter" & intCounter) <> "" Then
MsgBox Me("Filter" & intCounter).Tag
If Me("Filter" & intCounter).Tag = "effective_dt" Or Me("Filter" &
intCounter).Tag = "issue_eff" Then
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & "
>= (" & Me("Filter" & intCounter) & ") And "
End If
If Me("Filter" & intCounter).Tag = "change_id" Or Me("Filter" & intCounter).Tag = "priority" Or Me("Filter" & intCounter).Tag = "Dom" Or Me("Filter" & intCounter).Tag = "Intl" Or Me("Filter" & intCounter).Tag = "Tasman" Or Me("Filter" & intCounter).Tag = "Regional" Or Me("Filter" & intCounter).Tag = "AA" Then
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " = " & Me("Filter" & intCounter) & " And "
End If
If Me("Filter" & intCounter).Tag = "name" Or Me("Filter" & intCounter).Tag = "status" Or Me("Filter" & intCounter).Tag = "assignee" Or Me("Filter" & intCounter).Tag = "app_status" Then
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
End If
End If
Next
If strSQL <> "" Then
'Strip Last " And "
strSQL = Left(strSQL, (Len(strSQL) - 5))
'Set the Filter property
MsgBox strSQL
Reports![RepFilter].Filter = strSQL
Reports![RepFilter].FilterOn = True
End If
End Sub
thank you in advance, max.
View 6 Replies
View Related
Dec 1, 2005
i am trying to build a marine turbine and parts log and have a problem in satisfying a working rule.
i have 2 tables which i have attached,
table 1 shows the on / off dates that a generator was fitted to a turbine
table 2 shows a general date history of the generator, when it had checks done, fitted, etc
what i would like to do is fit the generator again to another turbine
using a continous form to show the available dates in table 2, i would NOT like to see the dates during the period when the generator was fitted previously as shown in the on / off the dates in table 1.
the working rule being that a generator can not be fitted to 2 different turbines at the same time
can anybody point me in the right direction or assist in how i can overcome this problem
many thanks in advance for help provided
View 3 Replies
View Related
Oct 28, 2013
I've created a Form where I enter all my data - then I created a Report to output this data, it includes two buttons to filter based on one category. That's working fantastically well for me.
But now I would like to filter on dates. For example, last 7 days, last 14 days etc - and also give the user the option of filtering between x and y dates. I've discovered this is easily done by right-clicking the date field in the report, but I'd prefer to embed these commands into buttons etc so it's easier to navigate.
View 2 Replies
View Related
May 18, 2013
I am trying to track vegetables that I will be planting in my garden. I will plant some vegetables. I have a StartDate and HarvestDate and Estimated HarvestDate Fields. I also have a field named GrowDays. This field states how long it takes for each vegetable to mature i.e. 75 days or 58 days or 42 days etc. etc. What I want to do in a query is to automatically add the number of days from the GrowDays field to the StartDate and come up with the Estimated Harvest Date. If I plant a Tomato on April 13th and it takes 75 days to mature then I want the EstimatedHarvestDate field to reflect 6/27/2013 which is the StartDate + 75days. I am trying to figure out how to do this when the GrowDays field changes numbers. I can use this "Estimated HarvestDate: DateAdd("d",+75,[GrowDays])" except this only works if the GrowDays Field is 75. I need to be able to accomplish this no matter what the GrowDay field says.
View 2 Replies
View Related
Oct 29, 2006
Hi all!
I want to add a function to a form that filters records from a specific date period.
Each record has a field containing the date the record was added.
Example: I want to show the records added between 15.09.2006 and 10.10.2006.
Furthermore, the “from” and “to date” must be defined in textboxes, and the filter initiated with a button.
I need this functionality on a continuous form and in a report. How do I achieve this?
View 14 Replies
View Related
Jan 27, 2014
I have an Excel workbook which I use to extract data from an Access database using VBA. A requirement has popped up to show things from the last week. Unfortunately, I can't just use a Date()-7 query in Access as the start point might be different, so it needs to be based on seven days prior to a date field in Excel.
So, I have a cell in Excel with a date, in this case "16/01/2014". I want to then use that date in my query to say "greater than 8 days prior to to this date". The field is formatted as a date and the variable (DatafileDate) taken from this field and passed to the function is a Date. In my Select query I then have this line to select the top 5 marked as UK accounts in this case:
Code:
SearchText = "SELECT TOP 5 * FROM `" & TableName & "` WHERE `" & TableName & "`.`Account`='UK' AND `" AND `" & TableName & "`.`Close Date` > #" & DatafileDate & "#"
Code:
#" & DatafileDate & "#" -8
Should mention that the TableName stuff is so the name of the table can be set elsewhere. "Close Date" is the column in my Access query I need to filter on.
View 2 Replies
View Related
Mar 13, 2008
I have a table that has a list of order information, there are multipule records per order that have information about when the order was processed. There is a day tied to the process time of each record, I need to filter out the duplicate records and get one record for an order, but that one record must be the first process time entry for that order. Anyone know how to make this query? Let me know if this doesnt make sense.
View 4 Replies
View Related
Aug 25, 2014
I am new in MS Access programming and is trying to create a search form for all records based on selected dates and check box. I was able to filter the records by dates but can't find ways to filter the records that meet the dates to be filtered again based on the selected check box/s. Basically what I wanted to is display all the records within the selected dates and check box/s.
View 9 Replies
View Related
Mar 13, 2013
My report generates base on 2 date inputs(from textboxes) namely, ProcStartDate & ProcEndDate & a combobox(cmbRptSupplier) to select the supplier:
Code:
Private Sub btnReport_Click()
Dim strDocName, strWHERECondition As String
strWHERECondition = ""
If Me.cmbRptSupplier = "" Or IsNull(Me.cmbRptSupplier) = True Then
[Code].....
When I leave my cmbRptSupplier blank (so that I can leave supplier out of my criteria), & fill in 1/2/2013 for ProcStartDate, 14/3/2013 for ProcEndDate, I get ALL the invoices displayed in return for my report. Even with those before 1/2/2013.
View 10 Replies
View Related
Jan 19, 2014
I have a table having column as date, format as mm/dd/yyyy and i update the column as now() using VBA.
However some places the date is in mm/dd/yyyy and in some mm/dd/yyyy nn:mm:ss are appearing which is making the filtering of the data ambiuge.
View 2 Replies
View Related
Mar 27, 2014
I filter my subform (datasheet) by fields, everything works great except "date" column. MS Access shows that record does not exist but it isn't true.
Code:
Me![frmAktPD].Form.Filter = "[Data przyjęcia] = " & Me.DataPrzyjecia
Me![frmAktPD].Form.FilterOn = True
Where is the problem?
View 1 Replies
View Related
Aug 21, 2014
The following code should sum a field depending on the date. The field IncidentYear is created and set to Last, This or "-" depending on the date. I should get three rows, one for each of the three values..
I get..... you tried to execute a query that does not include the specified expression as part of an aggregate function
I'm sure I've missed something obvious and I can't see it! If I remove the group by clause and the sum field..... it works.
SELECT IIf([Incident Date]>=#01 July 2012#And [Incident Date]<=#30 June 2013#,'Last',
IIf([Incident Date]>=#01 July 2013# And [Incident Date]<=#30 June 2014#,'This','-'))
AS IncidentYear,
Sum(tbl_2c_Acc.[Vehicle Off Road Time]) AS VOR_Acc_Days
FROM (tbl_2c_Acc
LEFT JOIN tbl_Value_Proposition
ON tbl_2c_Acc.Lessee = tbl_Value_Proposition.[Lessee No])
LEFT JOIN tbl_Vehicle_Type
ON tbl_2c_Acc.[Vehicle Reg] = tbl_Vehicle_Type.[Reg No]
GROUP BY IncidentYear;
View 8 Replies
View Related
