i have a query that does a search for a string in 1 field right now in the table. it works great, but then i found out that the string may also appear in another field.
for example, my query looks for "abc" in Field 1. then i found out i also want to search for "abc" in Field 2. In Field 1 and Field 2 there's no way to tell where the string will appear. Field 1 and Field 2 are text fields too. So I was thinking it should be a Like "*" & abc & "*" type match.
so basically i want a query that will return results if "abc" is in Field 1 or Field 2.
I have main form and that has one sub-form , this Main form governs/determines the data in the sub form.
This Sub form ( DataSheet Mode) has approximately 130 columns and based on the Main form criteria only ~ 20 columns has to be filled.
What I want to do is based on the main forms criteria I want to show only the columns that are applicable to main form criteria.
If I use Columnar or Tabular single form for the Sub Form I am able to hide the fields that are not required BUT IT LEAVES A SPACE/GAP on form ( for the hidden fields that are not required)
Private Sub Form_Load() If Forms!frmShowPIforActiveAndCanAddNewPI!FrmSubFrmFi lterProductInformationPerFMT!CASETIF = True Then
Me.CASETIF.Visible = True Else Me.CASETIF.Visible = False End If End Sub
And If I use DataSheet and hide ( visible = no) a particular filed it still shows up in Sub Form
Is there a way to Auto-Re Arrange all the fields in the sub form so that the hidden ( visible = no) fields no not leave gap
Or is there a way by VBA program to select fields ( Columns) from a table to be displayed on a sub form based on a criteria
I have an inquiry about using dlookup to retrive all fields from a table that are using the same criteria. Here's the table structure
Table1: ID (Autonumber) Number Code Grade
I have the following data: Number Code Grade 4 15 68 4 52 78 4 17 69 5 85 83 5 28 17
I am using this to retrive values for number 4 but I only get the first row DLookup("Code", "Table1", "[Number] = 4") DLookup("Grade", "Table1", "[Number] = 4")
I get only as a result 15 and 68, how can I retrive all the rows that have Number 4?
hi. i'm a novice with Access so any explantions need to be pretty simple. I'm trying to create a query that searches 2 fields when 1 word is entered into a parameter value box..... would any1 hav a sugestion. please post a reply if you don't understant what i'm trying to achieve.
May I know what is the easiest way to search for records using 2 fields wich are not primary keys? and then return a boolean value whether it is found or not...
These 2 fields are of integer type.
Recordset.Find can only find record with one field and not two.
Hi, I just started doing something in access and need your help. I have created a database and need to create a query which will sort all important fields within a certain time period. I have a date field and in the criteria field I wrote an expression:">Forms![Insert_date_form]![starting_date_field] and < Forms![insert_date_form]![ending_date_field]"
Its purpose is to show all fields which date is between this to dates. User will have to enter this dates in a separate form(Insert_date_form) which will store it in a small database containing only these two fields, e.g. generate report of all employees that are started working within a period between starting_date and a ending date. When I try to create and open a report based on this query I've been prompted with a small dialog which says: Enter parameter value.... for starting_date_field and same for ending_date_field. I don't need that. These dates are allready entered by user in a insert_date form and stored in its databese.
Is there a beter way to do this?
Thanks! P.S. Sorry for my poor english. It's not my native language.
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.
I have a query that is set up to join two name fields...for example:
[ELIGVENDORS.LSTNAM] & [ELIGVENDORS.FSTNAM]
The issue is that this is perfect for names that are entered in the table like this:
Lstnam: ABC HOSP Fstnam: ITAL
They are entered like that for groups, but when individual doctors are entered, the data entry folks are entering them like this:
Lstnam: SMITH Fstnam: BOB Titlcd: MD
So when I join them the first displays correctly as ABC HOSPITAL, the second displays as SMITHBOBMD and I have to manually go in and add a comma.
Is there a way to do an if/then statement or something to tell the query to display results If the titlecd is NOT NULL, display as [ELIGVENDORS.FSTNAM] & [ELIGVENDORS.LSTNAM]&", "&[ELIGVENDORS.TITLCD], else display as [ELIGVENDORS.LSTNAM] & [ELIGVENDORS.FSTNAM]
I'm sure there is, I am new to IF/THEN statments though and was hoping to get some help setting it up.
Thanks in advance and I hope my description is clear enough.
I am trying to figure out how to build my query that returns equipment reservations where "date out" OR "date In" are between 2 dates that are specified on my form using [Forms]![Myformname]![StartDate] and [Forms]![Myformname]![EndDate].
The purpose of the query is to find the amount of companies that have had a piece of equipment reserved between 2 dates, which could be a calendar year, or a fiscal year for example.
This is my current SQL statement that returns records where only DateIn falls between the criteria. My objective is to also return records where DateOut falls between 2 dates.
We have a PHP website that utilizes an Access 2003 SP3 database. This website is used as an authorization process for a business process in our company. On the website, 8 different people must "sign off" on the process. This sign off process simply consists of typing your name and date into two different text boxes, as well as a third drop-down menu for "APPROVED" or "DENIED". These, of course, correlate to their respective fields in the Access database.
Within the past year or so, the 8th approval section was added. Since then, there have been sporadic issues where all of the approval fields would simply be erased and everyone has to go back to the form and "re-approve" their fields. I believe we have narrowed the issue to when the 8th person signs off before the 7th person. However, the approvals are not cleared every single time this happens, but this condition exists every time data is cleared. So my question is, would this be an issue related to access, or with the coding of the website?
With a form with two fields one is a date field formatted as short date and the other is a lookup combo box that I use to lookup predefined times and is formatted as short time. The date and time are stored in the same table as date/time in separate fields. I attempted to use Dcount, and get syntax error missing operator. Below is one of a hundred that i have tried after scouring this forum and the net. I ended up converting the Appt_Time field to a text field in the table to eliminate one date field but still got no where.
Code: Private Sub Appt_Time_BeforeUpdate(Cancel As Integer) 'Check table for duplicate If DCount("[Appt_Time]", "tblexams", "Appt_Time= & Appt_Time & And [Sch_Date] = #" & Sch_Date & "#") > 0 Then 'Message box warning of duplication
I am creating a query with criteria for a dates column. The column cotaining dates in my Table has many empty fields. I want to limit the criteria for my dates to >#1/1/2004#, but I don't want Access to exclude all the empty fields because I want all the data displayed for the purposes of my report.
What do I do? I'm not terribly familiar with Access, so please explain as completely as possible. Thanks in advance!
I have a command button that opens a report with a where filter. The report has a main section and a detail section. Its a Organisation and its Contacts. The Contacts are in the detail section. How can I suppress the detail section (and a label) if there is no records to show in the detail section?
I have a table [maintable] that holds print records, with the following fields :
id,userlogon,printername,pagecount,color(yes/no),duplex(yes/no),timestamp and papersize.
I want to create a query that will show the following:
Sum of pagecount where papersize like 'a4' and between 2 dates but FOR EACH USERLOGON.The dates will take them from 2 fields in a form so i will use this
"WHERE (((maintable.Timestamp)>=[forms]![reportsform]![frmdate] And (maintable.Timestamp)<DateAdd("d",1,[Forms]![reportsform]![todate])))" and some more..
So i want the query to show,first the userlogon then a field to show sum of a4 then a4 color prints etc..(remember,i want the between 2 dates criteria to be global,to the whole query)
here are all of the criteria fields i want:
1) - papersize=A4 2) - papersize=A4 and Color=True 3) - papersize=A4 and Color=False 4) - papersize=A4 and Duplex=True 5) - papersize=A4 and Duplex=False 6) - papersize=A4 and Color=True and Duplex=True 7) - papersize=A4 and Color=True and Duplex=False 8) - papersize=A4 and Color=False and Duplex=True 9) - papersize=A4 and Color=False and Duplex=False
Windows XP MS Access 2007 VBA experience is very limited
So in my forms 'BeforeUpdate' property I want to validate whether a condition is met. The condition is whether 'any one of 4 fields contains something (text or number)'. I have written this code for validating a single condition and it alerts if the field is left blank. This code returns a warning message when the field is blank.
If Me.[txtObjective] & "" = "" Then MsgBox "A 'Problem Objective' is required, please complete this field.", vbOKOnly Me.[txtObjective].SetFocus Cancel = True
[code]...
how to write the SetFocus part. And I dont know if this is correct anyway.
i made a query to display students results after an exam. there are 12 subjects but a student chooses 8 from that. After an exam, the results are keyed in and a result slip is printed later. The problem is that the result slip displays all subjects including those not done by the student. I typed a criteria >0 in one of the fields and no records appeared. Is there a way that i can use to display only the subjects chosen and marks attained by a student and leave out the rest?
I have a query criteria where the data in the query field are numbers.
The following works as long as I have a value in the textbox otherwise I get an error message stating, this expression is typed incorrectly.
Code: Like [FORMS]![FONMain]![txtTest15] Or [FORMS]![FONMain]![txtTest15] Is Null
Also tried
Code: [FORMS]![FONMain]![txtTest15] Or [FORMS]![FONMain]![txtTest15] Is Null
Yet I use the following script for another column which has identical data and it works fine either way and I can use it in the other column and it works with or without data in the textbox
Code: Like [Forms]![FONMain]![txtFact1] Or [Forms]![FONMain]![txtFact2] Or [Forms]![FONMain]![txtFact3] Or [Forms]![FONMain]![txtFact4] Or [Forms]![FONMain]![txtFact5] Or [Forms]![FONMain]![txtFact6] Or [Forms]![FONMain]![txtFact7] Or [Forms]![FONMain]![txtFact8]
The Table1 holds data that is a request for a task.
ID, Request, Task_tms (the number of times the task is required to be done)
1, Make a cup of coffee,15
Table2 is my allocation of people to the tasks.
ID, Person, Task_done (the number of times this person has done the task)
1,Sally,10 1,Eddie,5
What I want to do is to limit the amounts of allocation in some way (ie in this case if I have already put 10 for Sally then I would be unable to put anymore than 5 when entering the amount for Eddie.
Additionally if I were to allocate all 15 to Sally then no additional people would be able to be allocated to this task (ID 1)
The two table are in datasheet format linked on a form.
Using sql or access query I would like to create an expression that aggregates the first field and I would like to see all records grouped by the relationship with another field. Let me show an example.
My query shows:
field1 field2 apple a apple b banana a carrot a carrot b dog b elephant b
I would like my query to now display a third field and group field :
field1 field3 apple both banana a carrot both dog b elephant b
I would like to only display certain fields on a form depending on a criteria in another cell. For example if some enters 4 in 'Schemes' it would display 4 data entry boxes scheme1, scheme2, scheme3 etc - is it possible?
I have a report which I would like to apply conditional formatting on multiple fields. I would like the conditional formatting to be based on two types of criteria
First criteria would be contracts that start with the year 2014. I used the following expression which worked - InStr(1,[ContractNr],"2014" AND [DirectorInCharge]= "Joseph Steinbok" or "Adam Godson")
To this expression I would also like to highlight fields which contracts start with 2014 AND have a particular Director assigned to it. For this I used the following expression - And [DirectorInCharge]="Name"
On their own, both expressions are working but I want to combine them. How do I do this? I've tried the following - InStr(1,[ContractNr],"2014" AND [DirectorInCharge]= "Name") but then nothing is highlighted. I also tried InStr(1,[ContractNr],"2014") AND [DirectorInCharge]= "Name" - in this instance EVERY record was highlighted.
