I think I read somewhere that DCount will not count blank cells, but when I try to do it - it counts everything.
Here is my formula:
=DCount("[E-Mail]","Detail","Not IsNull([E-Mail])" & " AND [Primary] = [Products]![Product Code]")
Basically, I want to count the number of email addresses in the E-Mail column of my Detail table IF the Primary column (also in the Detail table) equals the product code on my form AND if there is something in the E-Mail column for that line. When I did the above formula - and I've done a bunch of different variations - it keeps counting all lines that match the product code.
I have an Excel file that I use to enter data. About half the columns are validation cells to minimize human error. The validation cells are in rows 2-200. I then import the data into Access.
The problem is that, even if I only enter or select data in one row, Access imports all 200 rows that contain validation cells. I only want the rows in which I've actually entered/selected data to be imported.
I've searched this forum, but may not be using the correct search criteria. I've also searched Google for "Access Imports Blank Validation Cells from Excel" and other variations of the same words, and switched empty for "blank".
Can someone recommend a thread or on-line article that will give me an idea how to work around this?
Thanks in advance for any assistance.
PS. Sorry, I wasn't sure under which category to post this. Again, thanks for your time.
I am building a database to store student assessment information. I have a table with student information, a table with teacher information, and tables which will store the student's scores on a certain assessments.
Q1: I have a field in the assessment table for Teacher ID and Student ID which are linked to their respective tables. What I want to do is create a form for inputting the assessment data but the fields for teacher ID and Student ID to auto-complete or be combo boxes, I don't care which. That is, when a person is entering a student's scores, and go to enter their ID, they won't have to type the whole number out and hope they dont make any mistakes, they will essentially have a list of choices to choose from. Or as they type, the corresponding number will filter down. I already have refferential integrity on so that they can't enter an invalid ID in either field but I want them to have the choice.
Q2: In an assessments table, there is a 1-1 correspondence btw student ID's because only one student can have one score on a particular assessment. However, clearly one teacher can be tagged to several student's assessment scores. Any better way to tag the teacher to multiple student's scores without having fields in the assessment table for teacher ID which is repeated multiple times.
When entering information into a blank form, I would like to be able to continue entering information to another additional blank form after my last entry. Is there a way to continue to a blank form after entering information into the previous blank form? I would just like to continue without having to close the entire form and then reopening another form.
How can I prevent duplicate records from being added from a form, the dcount in the text field property, trigged before update is where this should be used is what I know but having trouble with the syntax.
I've got the table tblInvnetoryDetail with InvID(AutoNumber), and SerialNumber. The form text field is txtSerialNumber. I've managed this far with the expression
DCount([InvID],"tblinventoryDetail","[txtSerialNumber]=&"'") but this did not work.
basically i'm new to access, i am not a programmer and have been asked (told) to do an access (a few) for our office,
OK what I have is a very very simple database, we add in the post that we post out every day. I have two fields called Postage Paid and Stamp Required. I have used a Dcount to count how many have Postage Paid and how many require a stamp (Stamp Required).
What I want is to add the totals for the day that i enter them, i don't need to store that data, just to tally up the totals for the day i enter them...
I have a form with a text box "txtSdate" and a text box "txtEdat" and a text box "txttotal". I have a table that is called "Call Logs" and a field in it called "Date" which is shown as mm/dd/yyyy for each record.What i want to do is have "txttotal" display the # of records for the specific date range when the user enters it into "txtSdate" and "txtEdate".
I am working with MS Access 2003. I have a form (frmCalendar) with a textbox (tbDay) and a combobox (cmbLineName)
I want to count the number of records based on the criteria from cmbLineName, and show the result in tbDate. Inside the Control Source of tbDate I put in the following:
The problem is that the result in tbDate is always 0; regardless of what is chosen in cmbLineName. I know that the record count should not be 0 for all criteria.
I would like to have a text box display the number of records for a selected title that fall within a selected date range. Been looking around for a while and have this so far;
=DCount("Discussion_Title","Discussions","[Discussion_Title]='" & [cboType] & "' And [Discussion_Date] = Between ([txtStartDate] And [txtEndDate])'")
I get an #Error message. I just threw that together because it describes what I want, but I know there are syntax problems.
I am trying to count the number of records in a subform where the text box (txtFinal) = RPR-RPR & RTN. The subform is called PartNumbers. The table name the subform is bound to is named Completed.
I tried using =DCount("[Part Number]","Completed","[Final] = 'RPR-RPR & RTN'"). That is counting the total number of records in the table, not the total number of records in the subform. How do I get it to count the total number of records in the subform?
I'm building a customer management database. Part of which is a table to record details of all members of the main customers family or the household compliment.
I'm currently using a DCount to work out how many people on the table are related to my client, to work out the size of the family.....
Could I also then count the members of the household that are under 16 years old by looking at the DoB from the same household members table?
I realise that I'd need to ask access to calculate age at the same time as working out if they're under 16 and then count them if they have the same Customer ID - which might need a little more than this single function!
I've been working on trying to get this code to work as expected for days. I'm trying to find duplicates (I can't use primary keys or indexes alone to weed out duplicates due to the structure of the tables involved) in a subform as a user enters data. As soon as a project number is added, the code is supposed to count the number of records that contain that particular project number as well as a category number (there can be multiples of the same project numbers as long as their category numbers are different). This is the code I am using in the "Before Update" event of the field in the subform:
Private Sub ProjectID_BeforeUpdate(Cancel As Integer)
Dim strCriteria As String Dim RecCount As Integer strCriteria = "([ProjectID] = " & Me.ProjectID & ") AND ([CatID] = " & Me.CatID & ")" RecCount = DCount("[ProjHrsID]", "tblProjHrs", strCriteria)
[Code] .....
What could I be doing wrong? Nothing about this code seems to work properly - even the Undo and Cancel=True is a problem (I get the "No current record" error).
I currently have a database with a few search forms. I recently attempted to add a box on one of my search forms to search 3 cells of a record for a key word. What happens is when I go to search, say for P0442, it does not bring up all of the records that contain that keyword in the 3 cells I have outlined. If I step down the code in the OR boxes of the query, it seems to work better but then for some reason my other search criteria , (Tracking number etc) does not work. I will upload the database for reference. I am currently working on the Search(View Only) and that is where you will find the query to work on.
I have a couple different reference files that get updated each week. Sometimes there are missing data elements, so I'd like to structure a select query to show me those records that have blank elements but I'd like the similar records to be pulled in as well, so I can make a determination as to how to populate the blank records..
See attached example: I have a client ID reference table that gets populated with forecast owner names (individuals responsible for the customer) from a couple of different sources. Sometimes there are names attached and sometimes the field is blank.
How can I structure a query to show me just those Client ID's that have multiple entries with blank AND non-blank forecast owners? I'd also like to exclude single/multiple records where there are only blank records...
OK, I have been searching around on the fourm for over an hour now...I give up. If this is some where else, I am sorry.
I have a database of maintenance data. There are several columns that are usually filled in, some records have some columns blank. They are formated text because they hold letters and numbers (see pic).
I am using a form to query the table...no problem. The form has text boxes the user filter down the data
The problem comes are with the results of the query. Any record that has a blank column is not retuned. I am using "Like" so that the user can enter in partial codes. I know "like" won't return "null" records.... Help!
Things I have tried: 1)IIF(form field is blank, return table field, else use like command to filter) - returns nothing! 2)Like "*" & [Forms]![Fleetwide_data_Request]![MAL_CD] & "*" returns all records without blanks (i.e. missing data)
When the Next button at the bottom of the page is clicked I want all the values of each table recorded even if there were no values put in the boxes.
For example if there is no value added to the field boxes in say half the tables I want the default value to be recorded as blank or zero so that each table has the same Autonumber associated with the other tables in the form.
I have created a form based on a query that prompts the user to enter a persons name. Once entered, the persons data appears on the form and some other related data appears in a subform. However, I have found a problem that occurs when a name is entered that doesnt exist on the database... the form opens completely blank!! even text and graphics disappear!
