Queries :: Date Conversion Comparison Not Working?
Nov 8, 2013
I recently (temporarily)took over a position that uses an Access database that does not work properly, and I'm stumpped on how to fix it.
The query is supposed to pull all data where the "Date Overdue" field is less than today.
"Date Overdue" is a calculated value that pulls from the field "Date Input", which is in a text format (DDMMMYY) Such as 03NOV13. It is 8 days after the date input.
It prints out like this: "Monday, November 11, 2013" which is 8 days after the 3rd.
"Date Overdue" is set to this value:
Code:
DATE OVERDUE: DateValue(Left([DATEINPUT],2) & "/" & Mid([DATEINPUT],3,3) & "/" & Right([DATEINPUT],2))+8
"Date Overdue" has the criteria "<DateValue(CDate(Now()))"
I'm not going to go into all the different steps I've taken to try and get this to work because I've toyed with it a lot..
The output that I always seem to get is a mixture of all records that are available, before and after today's date, I just wanted those that are less than today.
I suspect that the date values that are shown in the query aren't true dates because when I click on the filter button it gives me this error:
"Syntax error (missing operator) in query expression 'DATE OVERDUE' "
NOTE: I'd like to add that this is just a regular Select query.
Code:
SELECT DateValue(Left([DATEINPUT],2) & "/" & Mid([DATEINPUT],3,3) & "/" & Right([DATEINPUT],2))
AS [PRODUCT END PERIOD], DateValue(Left([DATEINPUT],2) & "/" & Mid([DATEINPUT],3,3) & "/" & Right([DATEINPUT],2))+8
AS [DATE OVERDUE], [QBR ON EQUIP].DATEINPUT, [ALL ERRORS].[ERROR STATUS],
Hi all- I'm creating an update query.I have a field [dateLastEdit] that is stored as text, I have to compare it to a date entered on a form by user [date_from] (I need to select all dates greater than this) but I'm running into problems:when I use date_from in text or date format and compare it to dateLastEdit in text format the comparison is made in numeric order(so 12/02/2006 is selected as well as 12/02/2005 when date_from is 12/01/2006)SELECT PTSData.LastEditDateFROM PTSDataWHERE (((PTSData.LastEditDate)>=[Forms]![chMgmt_frm]![date_from]));when I use date_from in date format and compare it to dateLastEdit converted to date format I get "expression typed incorrectly or it is too complex to be evaluated" msg.SELECT DateValue([LastEditDate]) AS Expr1FROM PTSDataWHERE (((DateValue([LastEditDate]))>=[Forms]![chMgmt_frm]![date_from]));[LastEditDate]'s format is always mm/dd/yyyy hh:mm:ss, but as text, not date data type.I've tried CDate with same results, isolating it in its own query to test it, using hardwired dates as criteria, but no joy, am I missing something obvious?forgot to add- I've used all the above with left also to select only date portion with same results
I'm trying reference a dcount on two fields, one is a string and works fine, the other is on a date which does not.
I've investigated the problem and found a couple of references to making sure I use a # symbol to reference the date but I think the thing that's throwing the comparison out is that the date field stores time as well, even if you can't see it?
I thought should count the records in the healthcheck table that match the check_date field against the first_health field on the current form, but I keep getting a 0 count even though the dates seem to match.
I have a date field (DueDate) that I am comparing against the current date (Date()). When the current date is greater than the DueDate I filter just those dates. I have done that without any problems. Now I am trying to figure out how to compare that filtered information to show that the DueDate is either over 30 days, over 60 days, or over 90 days overdo. If it shows up on either one of those lists I do NOT want it to show up on the other lists. I also want to have a counter by states. For example:
Current Date DueDate State a. 9/22/14 2/1/14 GA *(90) b. 9/22/14 8/20/14 WV *(30) c. 9/22/14 7/1/14 WV *(60) d. 9/22/14 8/19/14 GA *(30) e. 9/22/14 7/31/14 GA *(30) f. 9/22/14 3/1/14 WV *(90) g. 9/22/14 7/20/14 SC *(60)
Okay, me again, on my DAP (Data Access Page) I have a piece of JavaScript that tells my page onload compare 2 dates. It's straight forward and all and my code works. That's actually where the problem is... When access loads my DAP up it builds the page, executes the javascript and THEN populates my fields with all the records. So my comparison javascript will always execute BEFORE the data loads into my DAP...
<body language=”javascript” onload=”colorBox();”>
<SCRIPT LANGUAGE=”javascript”> Function colorBox(){ If (Quarter1DueDateP1 >= Quarter1CompleteddateP1){ Box3.{background-color:Green;} } Elseif (Quarter1CompleteddateP1>=Quarter1DueDateP1){ Box3.{background-color:Red;} } Else { Box3.{background-color:Yellow;} } }
date formula that I was using in access 2007 doesn't seem to be working in 2010.
The formula that I had was
Between DateSerial(Year(Date())-IIf(Month(Date())<4,1,0),4,1) And DateSerial(Year(Date())+IIf(Month(Date())>3,1,0),3 ,31).
The calculation allowed me to count holiday hours taken between 1st April and 31st March. The problem is that it was working up till 31st march but is not now showing holidays taken since 1st April.
:eek: I have tried almost everything and I still can get this to work. I thought I had it with this attempt until I inputted October data and found that Access was sorting it by Number vs. Date.
I am simply trying to compare the number of tickets opened each month to the number of tickets closed each month. I also need to address how a report will appear if no tickets were opened or if no tickets was not closed in a month.
Incase the SQL in screencap is too difficult to read:
Query: qryTotals_Assigned_And_Completed - This calls the following two queries. ############################################# SELECT qry_AssignedByMonth.MyAssigned, qry_AssignedByMonth.Count, qry_ClosedByMonth.MyClosed, qry_ClosedByMonth.Count, qry_AssignedByMonth.Month, qry_AssignedByMonth.Year, qry_ClosedByMonth.Month, qry_ClosedByMonth.Year FROM qry_AssignedByMonth INNER JOIN qry_ClosedByMonth ON qry_AssignedByMonth.Month = qry_ClosedByMonth.Month ORDER BY qry_AssignedByMonth.Month, qry_AssignedByMonth.Year, qry_ClosedByMonth.Month, qry_ClosedByMonth.Year; ############################################# qry_AssignedByMonth ############################################# SELECT (Format([DateAssigned],"mmm"" '""yy")) AS MyAssigned, Str(Month([DateAssigned])) AS [Month], Str(Year([DateAssigned])) AS [Year], Count(*) AS [Count] FROM tblRequests GROUP BY (Format([DateAssigned],"mmm"" '""yy")), Str(Month([DateAssigned])), Str(Year([DateAssigned])) ORDER BY Str(Month([DateAssigned])), Str(Year([DateAssigned])); ############################################# qry_ClosedByMonth ############################################# SELECT (Format([DateClosed],"mmm"" '""yy")) AS MyClosed, Str(Month([DateClosed])) AS [Month], Str(Year([DateClosed])) AS [Year], Count(*) AS [Count] FROM tblRequests GROUP BY (Format([DateClosed],"mmm"" '""yy")), Str(Month([DateClosed])), Str(Year([DateClosed])) ORDER BY Str(Month([DateClosed])); #############################################
I have found a SQL Query I have tried to use, however I am having no joy. The query is such:
WITH rows AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY VisitID) AS rn FROM Data ) SELECT DATEDIFF("n", mc.EndDTM, mp.StartDTM) FROM rows mc JOIN rows mp ON mc.rn = mp.rn - 1
I am comparing the columns in two tables in a JOIN to see if there are differences. The columns, COL3 and COL4, are "Text" fields. They are declared as:
Field Size = 255 Default Value = "" (an empty string) Required = No Allow Zero Length = Yes Indexed = No
I use the SQL comparator <> in my test... so ...
Code: SELECT * FROM Table_A AS Tgt LEFT JOIN Table_B AS Src ON (Src.Col1 = Tgt.Col1 AND Src.Col2 = Tgt.Col2) WHERE Src.Col3 <> Tgt.Col3 OR Src.Col4 <> Tgt.Col4
I expected that where Src.Col3 was set to 0 (the string character zero) and Tgt.Col3 was set to the default of "" (a zero length string) that the query would return that row? It does not however? No doubt this is tied in with the old NULL string and Empty string chestnut?What is the best way to compare "text" fields in Access.
I've put in -100 as an arbitrary never possible number. Obviously, this does not return Null values.
Question: How can I return both Null & Numbers when the Textbox in the form is left blank? in all the columns. Currently, I am not getting Null values
Code: >IIf(IsNull([Forms]![MainForm]![Criteria]),SHOW ME EVERYTHING INCLUDING NULL VALUES & NON-NULL NUMBERS,[Forms]![Material Finder]![txtPS])
or in other words
If the textbox is blank, show me all the data available, else if it is not blank then show me only the values that are greater than the number entered in the textbox from within column A, B, C, D ....
I have a report where my customer wants to be able to input a value and then be able to select whether the report shows values above or below that value. I have a combo box that has 2 values (1=Less than or equal to, 2=greater than or equal to). There is a text box where he inputs the rate to compare against (e.g. $75). When he hits submit, the application stores the values of the combo box and text box into global variable and then I have a public function that can be used to retrieve the values. GetHRate() gets the value to compare against and GetHRateCompare() gets the value to indicate <=(1) or >=(2).
In my query for criteria for rate I have this expression: IIf(GetHRateCompare()=1,"'<=",">=") & GetHrate()
I've tried every combination of double quotes, single quotes, no quotes moving the GetHRate inside the IIf statement and nothing seems to work.
If I just hardcode <=GetHrate() into the criteria it works perfect but that does not achieve my goal of letting him select over/under at run time. Here's the full query:
SELECT qryCustomers_AverageTimeByScheduleID.ServiceName, qryCustomers_AverageTimeByScheduleID.ScheduleID, qryCustomers_AverageTimeByScheduleID.SumOfTotalTim e, qryCustomers_AverageTimeByScheduleID.PropertyID, qryCustomers_AverageTimeByScheduleID.PropertyName, qryCustomers_AverageTimeByScheduleID.MonthCount, qryCustomers_AverageTimeByScheduleID.AverageTime,
I have a query based form i use to gather data to generate a report. I have 2 forms Form A is where users open all reports from. On form A i have 2 unbound fields "Report Start Date" and "Report End Date" my queries us these from Form A as the criteria for the queries. I have like 30 reports using this method and it works great. I am now building 2 new reports, a monthly and a weekly report. These reports required and data entry point so i built "Form B"
I created my tables for these reports and went through and created the records. So when a manager needs to enter data for any given report they would us "Report Start Date" to identify what record they want to work with, so if they want to enter data for the December report the would select 12/01/2014 and then open "Form B". This works great and the report looks as good as any Access report can.
Now they are asking to add to "Form B" a comparison to the previous month or week depending on the report. for example:
This month we did 250 units Last month we did 300 units so we did 15% less "Yes my math is not exact"
They don't need to see last months data but i need a way to query the previous record to compare the data
This is the criteria code i use in the query that "Form B" is based on.
Code: [Forms]![Form A]![Report_Start_Date]
I am pulling up the 12/01/2014 in "Form B" but need the 11/01/2014 record floating behind to compare data.
How can i use the same setup but pull a 2nd record? I am thinking i can use a 2nd query but with different criteria.
I'm imported data from a csv file. The dates stored in there are in a dd-mm-yyyy time format. How can I work with this since Access's date format is just mm/dd/yyyy? I imagine I need to do some sort of conversion? Does Access provide anything for me to do this?
I have a time stamp field from an Oracle database that I want to convert to a regular date field in my Access query so that I can pull data from the table base on start and end date. The time stamp field is formatted as: 09/19/2006 03:16:00 PM
In my query I have tried the following formatting:
1. DateRcvd: Format((Left([time_stamp],10)),"mm/dd/yy") or 2. DateRcvd: Format([time_stamp],"Short Date")
Without criteria I get all the records in the following format:
1. 09/19/06 2. 9/19/06
Dates in the table are from 06/01/06 to current date 07.
Using the following criteria - I get varing results but never what I want. For example, using format #1, if I enter 09/16/06 and 01/10/07 I get everything for 07 and nothing for 06 If I use format #2, I get everything for 9/16/06 (no other records) and everything for 07
Criteria: Between [Forms]![frmDateRange]![StartDate] And [Forms]![frmDateRange]![EndDate]
My challenge is to fix a broken report that results from a query. The query is suppose to bring up all records within a date range. The problem is that the table was designed with the date field being text. Dates such as 06/12/2006 are entered without the beginning 0, so simple queries do not work, and the data is simply 6122006. I think the data needs converted to date first, possibly by extracting and converting. I do not know how to do this because of the missing digit inconsistency. The table cannot be changed directly to a date field without data loss.
I have a table with a text field to receive dates or "na" or "n/a". From this, I am trying to run a query that selects all dates that are not "na" and not "n/a" and not null. This works fine with a criteria of
"Is Not Null And Not In ("NA","N/A")"
The problem is, I also must select a date range. In the query, I have created a calculated field equal to various date conversion functions and they all seem to work at converting the text to date. However, I get a Type Mismatch error any time I try to do anything to the date field such as select a date range or to sort on it.
I have an Excel file that I want to import into an Access db table. In that Excel file is a date field formatted mm/yy. When I import that file, Access converts the date to m/d/yyyy. Is there anyway to re-format that data after it's imported into Access (like a global change?)? I even tried setting up an input mask for that date field, but that only seems to work when you are actually typing data into the field.
I have received a Access97 database which has a date field filled with numbers. The date of birth field is in the format : 19970131 And the date of birth field is a text field. The software requires the date to be in dd/mm/yyyy order and also to be a date/time field. When I try to change the text to date/time, Access deletes all dates of birth. I am not sure how to solve this as I am very new to databases. Can someone please help me soon?
