I have a form that using a combo box to select specific record, sometimes we need to see all records, i would like if the combo box if left empty to return all records. I typed this expression but it returns no records when combo box is empty.
If i use this expression i get the wrong results when a Loan Officer ID is enterd;
LIke([Forms]![FrmReports]![LoanOfficer] & *
Example of wrong results, if Loan officer Id is 1, then it returns loan officers 1,11, 111
Hi All. I'm Sure this is simple enough, but i just cant seem to figure it out.
I want to use a Combo dropdown box with Codes FC000 - FC900. to return records in a second drop down box that begins with the Letters selected in the first dropdown box.
So When a Record is Selected in ComboBox1 eg: (FC0**), ComboBox2 brings up the record to match (FC000).
When the SSN field is null I would like this query to return : 000000000, is this possible and if so could you show me how? Thanks
SELECT [CASEHEADS RECEIVING NON WELFARE MONEY].IVA_MEMBER_ID AS [IVA #], [CASEHEADS RECEIVING NON WELFARE MONEY].MEM_SSN AS SSN, Sum([CASEHEADS RECEIVING NON WELFARE MONEY].AMT_DISBURSE) AS AMT, [CASEHEADS RECEIVING NON WELFARE MONEY].DT_DISBURSE AS [DATE] INTO [CASEHEADS RECEIVING NON WELFARE MONEY TBL2]
FROM [CASEHEADS RECEIVING NON WELFARE MONEY]
GROUP BY [CASEHEADS RECEIVING NON WELFARE MONEY].IVA_MEMBER_ID, [CASEHEADS RECEIVING NON WELFARE MONEY].MEM_SSN, [CASEHEADS RECEIVING NON WELFARE MONEY].DT_DISBURSE ORDER BY [CASEHEADS RECEIVING NON WELFARE MONEY].IVA_MEMBER_ID;
I have two tables: tblStudents and tblEnrollments. The query I have designed shows the StudentID and counts the number of Enrollments that each student has. I want the students who don't have any enrollments to still show up and have a 0 by their ID. Right now, only the students with enrollments show up in the query results.
Here is my SQL Statement:
SELECT tblStudents.StudentID, Count(tblEnrollments.EnrollmentID) AS CountOfEnrollmentID FROM tblStudents INNER JOIN tblEnrollments ON tblStudents.StudentID = tblEnrollments.StudentID GROUP BY tblStudents.StudentID;
What do I need to do to it to have null values display as zero?
I have a bound form that is used to enter company info (address, name, category...etc). When the user closes the form, if Company name, province or category are left blank, I warn them with a message box asking if they want to exit and undo changes, or return to the form to fill in the missing info.
When they choose the option of returning to the form, I get 2 errors. You must enter a value in "tblCompanies.category", and "You can't save the record at this time, do you want to exit without saving?". If I click "No" on the second warning, focus is set to the missing data control and I can continue working.
How can I prevent the record from being saved when I choose to return to the form to fill in the blank records?
Code: Private Sub Form_BeforeUpdate(Cancel As Integer) On Error GoTo Form_BeforeUpdate_Error Dim Response As Integer ' Determine if required fields are populated. If IsNull(Me.txtCompanyName) Then Response = MsgBox("Company name is a required field. Do you wish to discard changes and exit?", vbYesNo,
A bit of a weird one, I've got a query and the criteria for showing records is that one particular field is null. However the query is showing records with the values in the field chosen for the Is Null.
Not sure why this is happening, has anyone come across this problem before?
I have a search from that has an option group, text fields, and a checkbox where the users selects a variety of option that generates a query. If the query return at least on record a split form (form on top/datasheet on bottom) is displayed and the form has an Edit and Close button. If the query returns no records the form is still displayed except the buttons are not visible.
If I knew the result of the query and then made a decision whether to open the form or release control back to the search box that would be great. To open the form I am using VBA with the DoCmd.OpenForm(,,"MyQuery","criteria") command.I thought about using the DLookup command and evaluate the return value for null to be used to control the program flow.
I am having problems with setting up a set of combo boxes.
What I am trying to do is if combo Productline is empty then in combo PartNumber would show all products but if combo Productline has a value selected then in the combo partnumber would only be able to select the partnumbers in that productline.
I have a query where data is first sorted by user input; first field's criteria: [fieldname], then by another field's criteria: Is Null.
I know there are records containing null values in the second field, as I have run a select query with the criteria: Like "*", to make sure they are null, and not zero-length-strings.
Trying to run a query using criteria to populate the query by looking at information from a field on a form, if from is closed I need that criteria to look at the table and return all date in table.
I have a query where I display the [OPEN DATE] and [CLOSE DATE] of my cases. However, when I run this query sometimes the cases are not closed yet, therefore there are null values. However, I also have a field to calculate the datediff between these two dates. I need the [CLOSE DATE] field to display today's date when it is a null value so that I can still get a count of the days using datediff when I run the query.
My combo boxes are not returning the actual combo box values back to my table, instead in the table it is displaying the combo box data list number i.e 1,2,3,1,5,1 etc where it should be displaying a property address.
I have field in my table, which is required (in access, or not allow null sql server), and have defaults of 0.
I have a form with a combo box bound (thru query) to this field - how do I let the user choose something from the list and then decide they actually didn't want to choose anything - and so clear the list.
Hear I get an error saying the field cannot accept null - how do I just force it to accept 0 - ie no choice??
I have a database called LettersDatabase this databse holds all the letters that have been made including the path to the doc. I use SSN to ID the letters to customers on the Contacts Database.
Contacts database also uses the SSN to id the contacts
I have a form that creates new letters for customers in this form I have listbox that queries the LettersDatabase for all matching records based on the forms contact SSN to see how many letters have been made for that customer.
The problem is that my listbox only shows the first record matching that SSN but there are more records in that LettersDatabase with the same SSN that I need to have diplayed on the listbox as well.
I may be writing the query incorrectly. Here what I have for the query on the listbox
Like[Forms]![LetterMaker]![txtSSN]
I try adding (&"*") to the end of the query but that does not help.
If anyone out there has the solution to this problem it would be greatly appreciated
I am wandering if it possible to check programatically whether a query returns a NULL result?
I am writing a program(VB) that involves an IFF statement where if there are records in the query it does a specific calculation & if there are no records returned in the query it does something else. Is this possible to achieve?
As today being the 1st October, I would like the query to return the values for Oct and Nov. The same values would be returned on the 31st Oct, but then the following day, only the Nov value would be returned...so, basically, it only returns the values of the current month and following months..
I am assuming I put something in the criteria of the query under the month? I have tried a number of things, with none working!!!
Looks like a grate forum for access topics and thats exactly what I need now! I'm new to Access and are going grazy!!
I'm trying to add some functions to an excisting database that someone else has done.
The Form creates an report from an db containing a one per day status information from water and electrical meters. Filling in start and stop dates gives a nice report over all meters and their status.
Now I want to add a combo box so the user can select one meter or leaving it empty to get all meters. I can make it work when selecting one meter in the list, but leaving it empty shows none. Or selecting any don't help all of the meters shows up anyway.
I used the search engine on this forum and found similar problem, but couldn't figure it out. Tried many combinations of the IIF statement, but didn't help.
May I know some to construct any functions or query string that can track the no. of records that mean certain criteria in the run time.
Basically I want to track the records in a subform (in datasheet view). Should the no. of records is 0, then I can disallow user from saving it onto the table for better record keeping.
My issue surrounds retrieving the last (based on most recent date) set of records based on the most recent date. I have query, containing 2 tables as the sources for the query results. Currently, the query yields:
Field A Field B Field C 123456 AAAA 1/8/13 123456 BBBBI 1/8/13 123456 CCCC 1/8/13 123456 DDDD 1/8/13 123456 EEEEEE 3/10/13 123456 FFFFFF 3/10/13 123456 GGGG 3/10/13 123456 HHHH 3/28/13 123456 IIII 3/28/13 123456 JJJJ 3/28/13
The desired results would be to return all records with the last/max date, so yield:
I am looking for a query that will return records from a table that have related records in another table. Opposite to the Unmatched Query Wizard.I have two tables: tblSupplier and tblSupplierProducts.The two tables are related by the field "SupplierId".I need the query to only return Suppliers that have Products.
We just upgraded to Office 2010 from 2003 and I am now getting 'invalid use of null' after choosing a valid combo box option. When I debug, the line that highlights has to do with a text box on the form rather than the combo box. Here's the code highlighting:
frmLines = Me.WorkLoadUnit
This is the code in it's entirety.
Private Sub fsubProductivityInput_Calculations() On Error GoTo err 'Calculate Breaks and Net Hours Dim frmLines As Double Dim frmHours As Double Dim frmLPH As Double
