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.LastEditDate
FROM PTSData
WHERE (((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 Expr1
FROM PTSData
WHERE (((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 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'm using Access and VB for the first time, trying to develop an application for hobby use. I built and populated my database, and I'm building my first module. Unfortunately, one of the most simple actions is tripping me up. I'm attempting to perform an action only if a 255-character text field in a file doesn't contain data, but comparing it to " " or "Space$(n)" doesn't yield the actions I'm expecting.
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;} } }
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],
: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'm having difficulty coming up with a way to exlcuded a group of dates from my Query. I am trying to have a set of dates come up without the dates, between 7/1/2006 and 7/31/2006.
I've tried <> Between #7/1/2006# And #7/31/2006#, and a few others with quotes and such. Any help would be greatly appreciated.
I'm trying to pull a date from a table into a text box on a form tab control using DLookup and I just can't figure out what is wrong with my DLookup expression:
=DLookUp("DateOrdered","tDateOrdered","PrNumber=" & [PrNumber]) ----- (DateOrdered is short date, PrNumber is text, db is split Access 2013)
Whats missing in this expression? I've tried every criteria variant I could find but to no avail.
I'm running a query to find records over one year old. When I use Date()-"365" or DateAdd (365 days, 12 months, 4 quarters, or 1 year) the records from one year ago today come up just like they are suppose to. The problem comes in when I put the comparison operator out front.
If I put < in front of either Date() or DateAdd minus one year, I get all records from yesterday and earlier as if it is ignoring the year and only looking at the month and day. If I put > in front, I get today's records.
Date() and DateAdd by themselves are working as advertised. Things only get weird when I put < or > out front.
I have a scheduling database that my company uses to manage our customers deliveries. We would like to make this database more intelligent by adding some logic and I am not quite sure how to proceed.
On the form frmDelivery, the user inputs all the header information for the delivery, in the subform subfrmLotInfo, they input the houses that will be delivered, the delivery date, and some other miscellaneous information.
When the user inputs the delivery date, the system should count back a number of days based on other criteria and set a build date (when we should start building the cabinets, typically 5 days before the delivery date). Then I need the database to run a query against this date and determine how many boxes we have scheduled to be built already on that day, if it exceed a certain number than I need to it to search an available date close to that date that has not exceeded our production capacity and schedule the lot for that date.
This needs to happen as well if the user changes the delivery date at a later time. Our customers change delivery dates constantly. This will help us schedule our manufacturing plant better and reduce the possiblity of scheduling more than we can actually accomodate.
I have a table tblPrdCapacity that holds how many boxes we can build on a given day. It hold Date, PrdCap (number of boxes). In the subfrmLotInfo, the field BoxesBuilt tells how many boxes will be built for that Lot.
Any info would be greatly appreciated!! I have been tasked to accomplish this as soon as possible.
I need to make a query to compare equality of two pieces of data, one is a 6 character fixed-length text string (where the first character can be ignored) and the other is a 4 or 5 digit number (long integer, if 4 digit number can be assumed to be 5 with a leading 0).
I do not have the ability to change the way I receive the data, they are on linked tables of different customers.
I tried using various SQL functions to convert the data in the query but they don't seem to work in Access, so I am getting "Type incorrect in the expression" errors.
How do I do this in ACCESS? I would prefer a SQL query solution to a VBA solution if possible, the data is updated VERY often and the application is already doing too much processing on the form_timer() event.
1.) Generate a random number between 1 and 100 and assign the result an identifier, like a person's name. 2.) Generate a second random number between 1 and 100 and assign the result a different identifier, like another person's name. 3.) Compare the two randomly generated numbers. 4.) Have the identifier of the LARGER random number displayed on the screen.
I would like to have this linked into a button click.I am hoping there is an easy way to accomplish this task.
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'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 ....
Hi there. I'm just jumping into Access and have the following question. In my Purchase Order Table, I have a date field calcualated as text, i.e. "01/12/04". I need to convert this to a date format in my queries so I can do calculations, i.e. 01/12/04 - 01/05/04 = 7 days. Can someone help me with how to convert this text date to a date format. Sorry if this is an easy question. - AJS
I am wondering if there is an efficient way to compare two fields from one table to another two fields from another table. So basically
Code:
If targetTable.Field1.Value = sourceTable.Field1.Value And targetTable.Field2.Value = sourceTable.Field2.Value Then targetTable.Field3.Value = sourceTable.Field3.Value
The problem is that I need to run this for all entries in targetTable. The only I could think of was to use 2 nested for loops (one for target table and one for source table) as outlined in the following (my data is currently in Excel, but I want to import it to Access)
Code: For i = 2 To 5754 For j = 2 To 3500 If targetSheet.Range("I" & i).Value = sourceSheet.Range("AR" & j).Value And targetSheet.Range("K" & i).Value = sourceSheet.Range("AS" & j).Value Then targetSheet.Range("I" & i).Value = sourceSheet.Range("AT" & j).Value
The above code works but it is really slow (takes about 12 mins on a high-end CPU).
I have a small clinic database. I've got tblAppointments to show AppointmentID and AppointmentDate among various other data, but only the dates matter for what I'm trying to achieve.
I'm trying to show a chart on a report that shows the number of appointments by months for this year and previous year. How can I do this...without using SQL, hopefully?
Here's what I tried: I made a cross-tab query to successfully show the years 2013 and 2014 in the rows, months in the columns, and number of appointments as values. But then, I didn't know how to graph it to compare the number of appointments for the two years by months.
I am a newbie, so please forgive me for such an easy question, but I am stumped. I attached a text file that shows the data I am working with. What I need to do is take each product (labeled Prod) and do a weekly sum on the quantities and compare against a set number to see if the quantity is lower or higher. For instance, I need to take column 12, regardless of value and compare it against set number. If the quantity is less, then I need to add the value of column 12 to column 13 and compare the summed value against set number. Again, if the sum is less than set number, I then need to take the value of column 14 and add it to the summed value of the previous step (sum of 12 & 13), then compare this new sum to set number. This process keeps taking place until I reach a summed value that is greater than set number. Once that happens I need to identify the column that sent me over the set value and hold that data. For instance, if column 33's (out of 52) summed value takes me over the set number, I want to know that it was column 33, so I can run further calculations against that value. The column header's are week numbers and I need to identify order points based on lead times and when I will run out of material. Is this beyond queries? I think so, but if it is, I don't know how to exactly begin the code in VBA either. I think I would use an If then Else stucture with a counter switch set from 1 to 52, unless comparison exits function, but not certain. HELP?????:confused:
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,
