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 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,
[Code] ....
Is it possible to have operators (<, >, <=, >=) in an IIF statement like this:
iif([field a] is null, > [field b], < [field c])
This is in the criteria for a currency field.
I have been trying different combinations without any luck for the last couple of hours or so. :confused: :confused:
Any help would be greatly appreciated.
Thanks in advance,
Brian
Hello,
I'm gonna try to explain my problem.
In fact, I have a form where I have a list where the user can select the following signs :
<, >, >=, <=, = (greater than, less than etc...)
Also, the user has the possibility to set the value that will be go with the operator above.
I tried the following expression in the query :
=[Forms]![Open_Form]![Sign CL AMT] & [Forms]![Open_Form]![CL_Amt]
the sign CL Amt is > and the CL AMt is 500'000 and I want to have >500000 in the criteria of the query in order to have only the amount above 500'000.
But is does not work.
I get the message : "The expression is typed incorectly or is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables"
If some one has a solution would be very appreciated.
Thanks in advance.
Cheers
Dan
I am trying to store binary values (as decimal long integers) and then use bitwise comparisons to update the values. MS help suggest that the standard logical operators (And or etc) can do bitwise comparison as opposed to comparing boolean logical values for expressions; but I have found no way of making this work.
Is this possible? Any ideas. Doing it mechanically, by splitting the decimal into the relevant parts for any bit is very tedious!
Gibbo
Hey
Can someone answer me if Like "Red" and Like "Blue" Or Like "Red" is a logical operator?
Thanks for the help
Ihow to formulate a calculated field properly that would look for records that has at least 'annual report' in a specific field.
For example, a record with a field ([DocumentType]) with "2013 annual report", another record with a field ([DocumentType]) with "2012 annual report" and then I would like to count in those records and exclude the other records.
Here is the formula I wrote:
Code:
Report: IIf([TABLE]![DocumentType]<>"Annual Report",[TABLE]![Name],"")
Maybe I am using the wrong operator or something....?
I am currently learning the Totals in Access 2003, using the Group By and it's drop down menu which has all the Min,Max,Sum Count etc Expressions.
I have a table of which I need to create a query, the table contains info with Mechanics names and jobs that they have done, often a mechanics name appears more than once in the table.
Which Expression do I use to query the number of mechanics in the table? When I use the Count the query contains all of the mechanics names including the the ones whose names appear more than once! :confused:
Any help would be gratefully appreciated I look forward to any replies thank you.
Sarah
I am preparing a database application that will be used by one person in an organization. Frequently , (every year or two) the program will be given to another (usually a laptop) user in the organization as that person assumes the task of the program’s operation and responsibilities.
I would like the user to load the program from the installation CD (made with Wise Installation System and SageKey softwares) on each computer each time there is a brand new user and each time a subsequent user takes over. However, each successive user needs to have the data files passed from the last user.
At face value it seems that having a front end/back end arrangement might be the most desirable. Can anyone jump start me on the best way to accomplish this process to EZ data transfer?
Thanks in advance to all,
JQ
Is there a way to look at two tables to compare records and add missing records from table to the other?
Thanks!
Terry
Hey all,
I've got to show records from a table that are 45 days old or newer.. im not quite sure how to do this in SQL ...
Select field from table where date > (todaysdate-45).. as you can see..clueless lol any help is _greatly_ appreciated
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
View 5 Replies View RelatedI really have looked everywhere
I have 2 fields and I want a query to pull out the records with a lower or equal to quantity value than the stock.
ReorderValue
3
5
Quantity
2
6
As you can see it shuold pick out the top record because its lower, I just need a way of telling it to look at each records Reorder Value
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.
What am I doing wrong?
Thanks,
Forrest
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.
Thank you!!!
Simple, I'm sure, but I am having one of those days...
I just need to compare two currency fields and do something if the difference is greater than one cent.
Any help with that incredibly simple expression, please? :D
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.
Anyway, here is what I am looking to do:
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'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?
Code:
MsgBox DCount("*", "[healthcheck]", "[check_date]=#" & Format([Forms]![MAINTENANCE_FRM]![MAINTENANCE_DETAIL_TBL subform].[Form]![FIRST_HEALTH]) & "#")
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 am having some "fun" at the moment trying to work out how to compare two times on different rows. The current setup I have is:
StartDTM EndDTM VisitID
09:00:00 10:00:00 1
11:00:00 12:00:00 2
12:15:00 13:00:00 3
etc etc...
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 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)
The output I want should be:
over 30 days:
GA 2
WV 1
over 60 days:
SC 1
WV 1
over 90 days:
GA 1
WV 1
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 need to have the comparison variable (< or >) to be read from a field in the form.
Namely:
WHERE (((([PackagesCosts].[TotalCost]) & [Forms]![F_Reports]![Increased] & [C].[TotalCost])
Where [Forms]![F_Reports]![Increased] is the filed on my form.
What is wrong in the syntax (as it is not working properly)?
I have a form with a textbox, where one inputs a number and then I run a query with the form criteria. The query is
Code:
Select Blah blah from dbo_temp where A>textbox value OR B>textbox value OR C>textbox value OR D>textbox value
The columns which are linked to the textbox some times contain null values. So, A, B, C, D columns do contain null values.
The expression in the "Criteria" column of Columns A, B, C, D (all on different lines - to make sure OR criteria is fullfilled) is as follows
Code:
>IIf(IsNull([Forms]![MainForm]![Criteria]),-100,[Forms]![MainForm]![Criteria])
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 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],
[Code] .....