I've been looking for this one for a while. In my query I'm using a date field to extract dates using the between And function. I'm refrencing to txt boxes within a dialog form within the query.
For example:
Between [Forms]![frmViewByCatagory]![TxtStartDate] And [Forms]![frmViewByCatagory]![txtEndDate]
The field actually only lists years rather whole dates .
I want to find a way to add an expression so that I can leave the txt boxes blank and access will ingnore the txt boxes and give me all the records.
Does anyone have an idea what I can do here. I thought the IIF function along with Null might work but I don't know the syntax.
I have 2 tbl's one is class and the other is professor... in my logic i create a new class for each student however, I am trying to make label for the professor and since a professor can teach more than one class i need only one label. How do i do this
Hi guys, is there any code u can write that ignores this hidden command that closes the form when you press the X button in the top right corner of the form.
I have a form where the user can choose from a drop down menu, values like Name= James.90 Date=1/10/07.
Then it would return the record with James.90 And That date in it
But my problem is you have to choose a value for both boxes. Is there a way to ignore it if the field is left blank.
My current code is
SELECT timesheet.WeekEnding, timesheet.FName, timesheet.Company, timesheet.Project, timesheet.CTR, timesheet.Comments, timesheet.Total, timesheet.Project_Title FROM timesheet WHERE (((timesheet.WeekEnding)=[Forms]![FormBeta]![Week]) AND ((timesheet.Company)=[Forms]![FormBeta]![Company]) AND ((timesheet.Project)=[Forms]![FormBeta]![Project]));
In my database I have module that checks if tick box is True and then closes all the forms and kicks the user out of the database. However in most of my forms, there is a custom Save command in the On Click of cmdSave button and the Form_BeforeUpdate. The save requires certain things to be true so naturally if a user has made a change to a record, the form won't allow you to close it if you haven't saved your changes or undone them.
Is there anyway I can get my module to bypass certain events in the form?
I have some expressions built to calculate totals in a query. Here is the first
Engineer Total: [Engineer Hrs]*[Engineer rate].
This works when a user adds hrs to a table and the rate is a default number.Then a total from 4 totals similar to the Engineer total are added together here:
Total Rate: [Designer Total]+[Quality Total]+[Coordinator Total]+[Engineer Total].
Works perfect if all 4 Hrs fields have data. The problem is not every entry will have hrs in each field..when this happens the Total rate does not calculate. So long story short, how do I make the Total rate bypass the blanks or do I need to make the Engineer total force a zero on blanks?
In a database am building, I want to run a query with the criteria dependant on which field the user populates in a form.
The form has a number of fields that the user can select from including our reference number, the client's reference number and the site address.
I would like the user to be able to select the site address using a wildcard so that they can enter a part of the address such as "This Street" instead of "45 This Street" and the user be presented with all of the records matching "This Street".
I tried using the criteria:
Code: Like "*" & [Forms]![SearchJobs]![SearchAddressLine1] & "*"
Which works perfectly as long as this field is populated. If this field is not populated, entering details in any other field bring up every record in the database.
I have 3 queries populating 3 subreports on a report, i have a checkbox on my main form i launch the report from - i reference it in a filter on the form to ignore certain error types - id like it do to the same in the queries that populate the reports
So the reports atm give - an over all count of error types, a count per person and then a breakdown on type per person
Thats all fine but id like the option to ignore one type of error (as we sometimes look at it and sometimes dont).
The problem I am having is I am using a =Sum([SumOfCountOfUseData]) statement to get a number of instances data is used from a table this works with one exception it counts deleted instances and sums those as well?
I'm using access 2007 and have some date fields in my forms, i'd like to be able to blank out weekends and public holidays from the calender/date selector in these fields so theres no way those dates can be selected. Is this possible? Thanks.
I have been tasked with completing an audit of approximately 10,000 items, to which I have generated a list of 40 questions (fields) for each record. I began collating the information in Excel, but found myself getting lost and even experienced major data loss on at least 2 occasions. I have now decided to ditch Excel and use Access 2003 instead.
I have imported my master spreadsheet, however as I have various contacts sending in their respective information in Excel spreadsheets with same types of fields, and also need to import data that has already been sent in. I’m thinking that it would be better for me to create update and append queries, especially as there is going to be a stage 2, where I will be requesting additional (field) information.
There is a big chance that some of the contacts will send in info for the same item (record), which means that there is a risk of duplication, which I have removed as I have a unique identifier which will be the primary key! Is that right or should it be indexed?
The main problem that I have is that I want Access to ignore the target cell if it has a value in it! Meaning that I would not like Access 2003 to overwrite the cell with valid data in it, with a blank cell! As I need the database to grow!
Can anyone suggest a way that I can do this please.
So I have a report with two pages. One page is about product exchange information and the other one payment. This report can either be printed or saved as a pdf (one pdf for both pages) and e-mailed.
This works fine, however if the item is under warranty then the exchange is for free and I do not want to print/e-mail the 2nd page (payment).My trigger field for this is "Warranty" which can be "Yes" or "No".How can I prevent that 2nd page from printing/e-mailing? Can it be simply skipped when the report is being created based on my field?
In my table I have a numerical field for case numbers. Our case numbers are in the format of YYMM000000 (YY = 2 digit year, MM = 2 digit month, remaining 0's are consecutive case numbers). In order to properly list the case numbers in descending order, I have
caseyr: Left([CaseNo],2) (which pulls the first 2 digits being the year) officeno: Right([CaseNo],3) (which pulls the last 3 for our part of the case number)
This was working fantastic until I had to enter cases from 2009, ex: 0911000587
I have set the format for the CaseNo field to 000000000 everywhere it is. It displays the 09 cases perfectly, retaining the zero, however, my left function ignores it. In the case number of 0911000587, it pulls 91 as the first 2 digits. So, in the form that I list my cases in order, the 2009 cases are showing up first (because they're pulling as 91 instead of 09).
Is there anything I can do to force it to NOT ignore the leading zero?
I have code written which imports excel data to a access table but after the first import it fails due to duplicates, how can i tell it to ignore duplicates in the table and only copy new records?
code is below.
Code: Function SyncEmployes() Dim lngColumn As Long Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
I have created a ribbon for the users on all forms, and named that ribbon on each form. While I can bypass the Main ribbon (hiding backstage and most other tabs) by holding the shift key on open, the minute I open a form I am stuck with the very limited form ribbon I've named. Is there a programmatic way I can ignore this call, either when holding the shift key or some other avenue?
Suppose I have a table with a variety of fields; one autonumber, some text fields and some date fields.
I need code to calculate the number of fields that are NOT date fields and then code to count how many of those fields (the ones that are not date fields) have data in them.
Following is the code to count how many fields in a table had data and this works well, but now I need to find a way to ignore the date fields. How can this code be modified to do the same but ignore the date fields?
Code: Public Function PercentCompleted(Table As String, IDProject As Long, TotalFields As Single) As Double
Dim sampO As DAO.Recordset, iCtr As Long, strSQL As String, Percent As Double 'Takes the fields based on the projectID strSQL = "SELECT *" & _ "FROM " & Table & " WHERE IDProject = " & IDProject Set sampO = CurrentDb.OpenRecordset(strSQL)
Wonder if someone can help please. I'm quite new to Access so please bear with me.
I have a data field in my database consisting of both a date and time.
I then have a form containing two fields where the user can type a 'To' and 'From' date to extract the records that they are interested in. The query behind this uses the 'Between[Enter The Date] And [Enter The Date]' coding.
The problem is that because the field contains a time it doesn't return any records when I run the query.
Can anyone offer a bit of guidance on how I could ignore the time part of the field perhaps by adapting the above.
I have a form whose data source is a select query, q3, that is built from 2 other select queries. I'll call them q1, q2, and q3. q1 is a parameter query where I enter a "Cutoff Date" that the 3 queries manipulte and generate the desired results that appear in the form. The problem is that I don't know how to capture the parameter "Cutoff Date" from q1 to display on the form.
I have a query that requires a Start-Date and an End-Date to be input by user for the Where clause. It is asking for both over and over. I've had it ask from 1 up to 4 times! :eek: Shouldn't it store the input and only ask for it once? I'm thinking that the way my query is arranged may be causing it to have to loop through that section more than once to find the data, but that's just my theory. Any help would be great!
Here is my code (abbreviated slightly):
SELECT DISTINCTROW C1.*, C2.* FROM Pen AS C1 INNER JOIN Jobs AS C2 ON C1.subno=C2.[Jobs Acct] WHERE ((C1.typ="SS" Or C1.typ="CC" Or C1.typ="PP" Or C1.typ="TT") And C1.stdate>=[Enter Start Date] And C1.stdate<=[Enter End Date] And C2.[Type]<>"EE" And C2.[Type]<>"QQ" And C1.entdate<=C2.[ChangeDate]+60);
I'm selecting rows from "Pen" and "Jobs" that have the same subno/Jobs Acct numbers (text), then there are criteria for "Pen" types, user inputs criteria for date range (Start Date and End Date) and there are criteria for "Jobs" types. Finally, there's a cross-table criteria based on a date field ("Pen" entdate should not be more than 60 days past the "Jobs" ChangeDate). Tables are in quotes in my explanation here.
So running the above, it asks for user input "Enter Start Date", then again for "Enter End Date"...but then it asks for each again...and again...and sometimes again!
Help! :confused:
P.S..I didn't notice this repeating until I made it user input (parameter query) because it was using whatever dates I hard-coded in there before.
I am creating a database for work and I keep getting a message that pops up when I opn my form that says "tblClientBFinfo.ClosedReason". Then there is a place to enter a value and and it says "ENTER PARAMETER VALUE." I know it is a problem with one of my tables, specifically the "closed reason" field in the table. But I odn't know why it is asking me to enter a parameter value. Could anyone offer help?
I need a new parameter in my query. When I open the query i need the parameter to be asked in a combo box or drop down box or any way to select the value from the groupo other than typing.
I want to run a query automatically in the gotfocus event of a field within a form. Prior to the gotfocus, a value is entered in an unbound text box on the form and I want to use that value for the maximum value in the parameters. I will set the minimum to zero.
My code errors in the where, it has no problem with the serial number but I can't seem to set the deliv_no to pull the value entered on the form.
SELECT Sum(VOLTEST.unc_del) AS SumOfunc_del FROM VOLTEST WHERE (((VOLTEST.SERIAL_NO)=[Forms]![largeVolume1]![serial]) AND ((VOLTEST.DELIV_NO)>0 And (VOLTEST.DELIV_NO)<=([Forms![largeVolume1]![No_del])));
I figure this is a syntax problem. Can you guys see where I have gone wrong?
I need the value to write to the table, not the form when this runs.
In my chart I have a yes/no field that I would like to set up a query to for. I want the user to be able to input yes or no and then the data be displayed. Whenever I enter yes or no when the prompt comes up, I get an error saying the expression is typed incorrectly or the data is too complex. How do I set it up so the user can select yes or no in some way? thanks
Hi all.I have a form that links to another form using the following macro "[homerid]=[Forms]![Homer Input]![homerid]" so when I click on form 2 the homerid from form 1 is automatically displayed. However on my switchboard I have a direct link to my Form 2 but when I click on this i get a box come up which is titled "enter parameter value" and then the subtitle is "[homerid]=[Forms]![Homer Input]![homerid]", this is fine, but I would like to change the subtitle to something i write myself such as "please enter homerid". Is their a way of doing this? Thanks.
