I have table with filed with jobs that our IT’s have done for that day, the table is updated daily, based on my table I have created my query, I have set some criteria for some fields though, as follow:
Month = [Forms]![DailyWorkForm]![commonth]
Year = [Forms]![DailyWorkForm]![comyear]
ITName = [Forms]![DailyWorkForm]![comit]
Day = [Forms]![DailyWorkForm]![comday]
on my form I have commonth, comyear, comit, comday and a command button that will open a report based on the query which will be based on my 4 combo boxes on my form, ok I hope I didn’t make you all really confused, this is the question what if the user inputs on 2 combo only, meaning that if month = may and year = 2005 and left ITname and day empty, I still want the query to run, how can I do that?
I am endeavouring to filter a form based on the position of an individual. I would like the users to be able to select their position and then any record where there is a match in the "Primary Responsibility" field or the "Secondary Responsibility " field will be displayed.
I have an underlying query that is populated by a combo box on an unbound form.
This has worked when generating reports for individuals but I cannot get the required result when using the form.
Thanking anyone in advance who can assist me with this.
Maybe it's the day's 'brain drain', but I need to set a criteria in a query whereby it will select answers in a field that are a specific number of characters in length. i.e., answers that are 5 digits long (without knowing any of the digits)
FTA # Date Attended Last name First name assigned person......
Each week I need to generate a report that shows the people that attended for that week, grouped by the assigned person. I only want to show the data for a specific "date attended" (i.e. that days date). I figured I first need to generate a query that only returns the data for that specific date in the "date attended" column. I did that but it is manuel by using the criteria field.
Is there an easy way to type in the date that I want the query to use as the criteria?
Below is the SQL I have on a Union Query. Each Query is based on a date range. The first Query date is Planned Immplementation Date. The second Query date is Revised Planned Implementation Date. If the Date in the Revised Planned Implementation Date is higher than the EndDateTxt Date Range I do not want the results to appear for that ECN. Any suggestions on how to accomplish this?
SELECT ECNBCNVIPtbl.[ECN Analyst], ECNBCNVIPtbl.[ECN Number], ECNDetailtbl.[ECN Description], ECNDetailtbl.[Planned Implementation Date], ECNDetailtbl.[Revised Planned Implementation Date], ECNBCNVIPtbl.[Serial Number Break Required?], ECNBCNVIPtbl.[Implementation Reporting Required?], ECNBCNVIPtbl.[Do Not Process] FROM ECNBCNVIPtbl INNER JOIN ECNDetailtbl ON ECNBCNVIPtbl.[ECNBCNVIP ID] = ECNDetailtbl.[ECNBCNVIP ID] WHERE (((ECNBCNVIPtbl.[ECN Number])<>"sample") AND ((ECNDetailtbl.[Planned Implementation Date]) Between [Forms]![EcnVisualStatusFRM]![StartDateTxt] And [Forms]![EcnVisualStatusFRM]![EndDateTxt]) AND ((ECNBCNVIPtbl.[Do Not Process])="yes")) ORDER BY ECNBCNVIPtbl.[ECN Analyst], ECNBCNVIPtbl.[ECN Number] UNION SELECT ECNBCNVIPtbl.[ECN Analyst], ECNBCNVIPtbl.[ECN Number], ECNDetailtbl.[ECN Description], ECNDetailtbl.[Planned Implementation Date], ECNDetailtbl.[Revised Planned Implementation Date], ECNBCNVIPtbl.[Serial Number Break Required?], ECNBCNVIPtbl.[Implementation Reporting Required?], ECNBCNVIPtbl.[Do Not Process] FROM ECNBCNVIPtbl INNER JOIN ECNDetailtbl ON ECNBCNVIPtbl.[ECNBCNVIP ID] = ECNDetailtbl.[ECNBCNVIP ID] WHERE (((ECNBCNVIPtbl.[ECN Number])<>"sample") AND ((ECNDetailtbl.[Revised Planned Implementation Date]) Between [Forms]![EcnVisualStatusFRM]![StartDateTxt] And [Forms]![EcnVisualStatusFRM]![EndDateTxt]) AND ((ECNBCNVIPtbl.[Do Not Process])="yes")) ORDER BY ECNBCNVIPtbl.[ECN Analyst], ECNBCNVIPtbl.[ECN Number];
I have a table(Product Change) with these fields: Tracking Number Approved (a check box) Engineering (text box that represents department) Purchasing (text box that represents department) Quality (text box that represents department) Production (text box that represents department) Customer Service (text box that represents department)
I want my query results to show all records that have the "Approved" check box...checked, and then only the records that have one of the Department fields with a null value. So I'm looking to see only records that are "Approved" and out of those....only the records with at least one department field empty(Null).
Any help doing this is SQL view would be great...or even design view.
Hi all, I have a query which populates a form called EditPatientFrm, which asks the user to enter the patient UniqueID in order to pull up the record they want to edit from the TblPatient. However, I want the user to now be able to input just one of three criteria in order to pull up the record to be edited:
UniqueID ChartNumber PatientLastName and PatientFirstName (two fields)
since they may not have the UniqueID readily available to them.
Can anyone show me how to do this? I have tried to find answer to this one under queries and forms- no avail. Sure it's a simple thing...
I sure am having difficulty with this. I'm trying to have the criteria in my form refer to a text box within one of my forms which is the text as I would write it myself within the query design view.
[calcDestZip5]'s value in QueryWizard form = "55422" OR "55343"
Basically, I just want to be able to enter the text string within a form instead of within the query. Sure this can be done!
Oh, and for what it's worth... If, [calcDestZip5]'s value in QueryWizard form = "55422" Then the query runs just fine. It's as if the query doesn't want to accept multiple criteria from another source.
What I want to do is set the criteria of a query to the value on a form if the form is open, and set it to a different value if the form is closed. I tried using a function on the OnLoad event of the form to set a variable called IsOpen to 1 if the form is open, and reset that variable to 0 when the form closes, but when I tried using the variable in an IIF statement in the query criteria, I got a "Its too complex" error.
Here's what I really want to do. I have a very complex form with multiple tabs and subforms. The subforms populate based on a query of what is selected and loaded into a textbox control on the first tab of the form. The first tab has a subform that is based on the main table. Rather than recreate that form, I want to copy it and change the rowsource on the first tab to a subset of the main table, and tell the query to use the textbox on the new form so I don't have to go and replicate all the other subforms. Is there a way to do that or am I just screwed?
On I form I have a combo box called cboMobileStatus.Its row source type is "Value List".The row source is: 1;" All";2;"Has";3;"None".I have a query based on table "tblCustomerContacts" and I want to limit the records returned based on the value of the combo box.So if the user selects:
* All (1) I want all records returned. * Has (2) I only want records that have a mobile number returned * None (3) I only want records that do not have a mobile number returned
The field mobile is text (to keep the leading 0)I thought of using the Switch function in the criteria of the query for field Mobile like this:
Code: Switch([Forms]![frmPrintCustomer]![cboMobileStatus]=1,([tblCustomerContacts].[Mobile]) Like "*",[Forms]![frmPrintCustomer]![cboMobileStatus]=2,([tblCustomerContacts].[Mobile]) Is Not Null,[Forms]![frmPrintCustomer]![cboMobileStatus]=3,([tblCustomerContacts].[Mobile]) Is Null)
but the query does not like it!Am I on the right track, and if so, how should I modify the code?
I am trying to pull the sum of document count from the SumTotalPerf query where the datereceived in the query matches the date on the form and the BookedInID in the query matches the BookedInID on the form, at the moment Text27 just displays as blank with no error messages displayed so I am lost as to what im doing wrong, Ive double checked all the spelling for my column names etc and all is correct.
I have a form with a check box. A query is run that looks at that check box and decides what the criteria are based on that. So, if the check box is checked, it should pull in all data in the field that is a Y. If it is not checked, i want it to pull all data (Y's and N's and blanks).
I have a temporary table with Yes/No fields (F101,F102,F103,....etc). In another table i have a field called ProductCode with values (101,102,103,....etc) Is it possible to append(or update) the records of the temp table with criteria on "F" &ProductCode field? (For example if ProductCode=101 then F101 sets to yes)
Please except apologies if I sound a bit ignorant with this but I'm complete novice with Access...
Basically I have a table with the fields Name - Date - Location I need to extract info from the table based on the location field.
i.e. return Name if a specified location exists and another specified location does not. This was real pain in itself and in the end I had to run two seperate queries which returned NAmes where each of the chosen locations exist. I've then created an additional query which compares the two sub-queries and iliminates Names that appear in sub query 2.
I do hope this make's sense I'm starting to confuse myself.....
Anyway, my problem is with the date field... I have to specify a date in order to get the correct info as the results may change on a daily basis... i.e. somebody may use both locations one day, but just the one location the next. However the eventual report which I need to generate is based on criteria over the previous 7 days, although if I use this criteria in my query in completely messes up the results.
So, what I was hoping that I could do is somehow automate my query to run 7 times (once for each of the previous 7 days) and then to combine the results of the 7 queries into a single query or report...
Failing that, could anybody think of any simple way of achieving this... or will be a case of having settle for a seperate report for each day?
Any help would be hugely appreciated... once again apologies for the explaination... It porobably makes no sense at all.
What I am trying to do is fairly simple i just dont have the ability to correctly code what i want to do.
I want to filter my query based on some criteria in multiple columns. But i only want the query to filter based on the specific criteria if a checkbox has been selected.
Basically i want the criteria for one of the columns criteria to read
IF a check box "Check0" is selected THEN filter the column to only records that = 1 and if "Check2" then filter all records that = 2
I have a SubForm "assignments" based on a Query, which has criteria to filter dates and also to filter 0 and 1 of the checkbox ...
The question is:
How do I put in that SubForm one or more Checkbox to "enable" and "disable", only the criteria of such query? So, toggle, for example, those jobs that are not completed (Checkbox of the query=0) and those that do ...
Is there a way in access, through vba or any other means, which would allow me to either enable or disable criteria in a query based on the value of textbox??
Like i have a query that displays bookings customer has made between two dates, so i made a query and in the starting and ending date fields i get the value from form. In case if the use does not enter ending date, i would like to run the query with starting date only that displays booking made after the starting date.
Currently the starting date criteria is set to a textbox of form, and so is ending date. So if customer selects 1st April 2015 as starting date and 30th April 2015 as ending date the query should display the bookings between 1st and 30th of April. And if the user enters only 1st April in starting date it should display bookings starting from 1st April onward.
I have a SubForm "assignments" based on a Query, which has criteria to filter dates and also to filter 0 and 1 of the checkbox ...
The question is:
How do I put in that SubForm one or more Checkbox to "enable" and "disable", only the criteria of such query? So, toggle, for example, those jobs that are not completed (Checkbox of the query=0) and those that do ...
I want to create a query based on 3 combo boxes but have it so that if the field in the second or third combo box is not populated the query still runs.
Right now i have the Criteria set for the three columns that i wish to sort by as seen below.
This gives me the correct query result but im forced to make a selection from each combo box. Is there a way to progamme it so that if I only make a selection from the first combobox and leave the others blank i can still get results in a query?
My form-based search mechanism uses controls to set the parameters for the query data source.
I have one field call quantity in stock. I could you a Between and And method to allow the end user to input the stock quantity they want.
HOWEVER, i would love it for the user to first select the Comparison Operator (e.g. > , <, >=, <=) from a combo box and then in an adjacent text box, enter the quantity.
The expression i entered in the query goes something like this.... Forms![frmSearch]![cboRange] & [Forms]![frmSearch]![txtQuantity]
When i try and run this, i get the message "THe expression is too complex to be evaluated".
I have a form which lets the user enter a contract ID and then a combo box that has a list of reports which run against whichever ID is entered.
What I want to know if I put the ID's in a table if theres a way to allow the user to select multiple ID's in one go and run the report against them the range rather than 1 at time?
I'm thinking about utilising the check/tick boxes would this be possible? or another way?
I have a very simple query on an accounts form to show a running transaction history.
Identifying from the TransactionID (shown for display purposes only - normally hidden) three or four postings make up one transaction.
Using TransactionID 10 as an example, I'd like to have a sum of total [Credit]-[Debit] and have the query display on one line (either at the top of £1,429, or at the bottom of £16,995) to identify this is in fact one transaction, having three posts.
Transaction 9 will have obviously have one total, as this is a single post.
11 the same as 10, by having one total Transaction Value either at the top of the row or bottom.
I am currently working on a project to develop an access database to manage a roster of calls to clients on a daily basis based on two general criteria:
1. Pre-determined days selected by the client. (e.g. Call Mon, Wed, Fri only. This can change as client requirements change.)
2. Ad-hoc changes based on the client’s circumstances. (e.g. No call from 27/7/2015 to 29/7/ 2015)
I have managed to successfully deal with the second of these with the following expression in a query:
CallToday?: IIf((Date()>=[NoCallFrom] And Date()<=[NoCallTo]),"No","Yes")
However dealing with the first is a little more difficult to work out. I have tried a multivalue lookup field with multiple days selectable, but constructing an IIF query to deal with these multiple values is proving quite a challenge.
I am thinking of using a table with days of the week and a junction table to allow the multi-selection, but I may need constructing the relationships and the query here.
I have an access database in which I have a table A and table B. Table A has a list of 200 website URLs. Table B has one column ID and another criteria.
I want to create a query to filter websites list which does not have values or characters from table b.
I have these values in table B that I want to be filtered out or not shown in my URL Select Query
.org .gov .du .pk .dk
I would keep on adding more criteria into this so criteria table so adding new criteria into table B should not disturb our filtering.
Below is what I have tried but in vain and it says atmost you can atmost one criteria row in sub query
SELECT tableA.WEB_ADDRESS FROM tableA WHERE ((([tableA].[wEB_ADDRESS] Not Like '*'+(SELECT * FROM tableB)+'*')=True));
