Dear All,
Ok, I would like to rephrase my question :) , how do I retrieve records even when only one of the 3 text fields in my form are filled up? I have to fill up all of the 3 fields for the records to show up.
SELECT * FROM tblApplicants
WHERE (COURSE Like "*" & forms!frmFS!txtCourse & "*" OR
COURSE Like "*" & forms!frmFS!txtCourse2 & "*" OR
COURSE like "*" & forms!frmFS!txtCourse3 & "*")
ORDER BY names;
My Database consists of three tables and a linking table, tbl graves, tbldeceased,tblmemorail and tbl memorialtablejoin.
Having completed my tables and checked that the relationship data gives the correct data I decided to embark on a simple form to display all the data on a single form.
So I used the create Form option, selected add existing fields. All of my tables are shown in the fields list.
From tblgraves I selected plot and grave Id, from tbl deceased forename and surname and from tbl memorial the inscription field.
The form was created but on examination I realised that my total records was shown as 12000 records whereas tble graves and tbl deceased has 39000 records and tblememorial 17000 records
So I created the form using just tblgraves and tbldeceased and that shows my 39201 records.
If I leave form view and go back to design view and add the inscription record from tblMemorials I see the message box as per the attachment.
Not every grave or deceased has a memorial but I would expect to see my 39000 records
I've also attached a screen shot of my relationships...
In the form frmOrders of my database i have a combobox where i can choose a ClientID and the dropdown list shows me all the clients in upgoing order with their related orders in descent order.But the drop down list doesn't show all available clients. The two hundred heighest clientnumbers aren shown. However if i type one of these clientnumbers in the textbox of the combobox, the client is found.If i go to the property sheet of the combobox and activate the query that serves as the recordsource of the combobox, all clients are shown!!!I control of the records of these clients in the table Orders, doesn't show any difference with all the other records.What can be the reason for these behaviour. Is it a question of a limit of records that can be shown via a combo box?
I have a query by form that has the criteria Like [Forms]![FormName]![ControlName] & "*" for each field. However the problem is that if one of the records have at least one blank field then the whole record wont show up in the query results. For example if there is a record with Fullname, and Age filled in but Address isn't filled in then if search Adam into the query by form the record wont show up because the address line is blank.
To make records that have a blank field show I know I could use Like Forms![FormName]![ControlName] & "*" Or Forms![FormName]![ControlName] Is Null but when I have used it, it has corrupted the query because I think if you use If Is Null many times in one query it becomes too complex for it to process.
Also, The other method is to use Nz in an expression but I cant do that because I want the query results to show up in a form where you can edit the records and the error message Field is based on an expression and can not be edited comes up if you try to edit the records. Anything else I could put into the criteria to show records that may have a blank field?
I have a question on number of records displaying in datasheet view on a form. Is there a way to limit the number of records showing on open.I have a command button on a Navigation form that opens a frmEnterPartsOrder using a Macro.
I have set the Macro to OpenForm, View Datasheet then GoToRecord, Record New..Works perfectly, but it does open the form and fill the screen with records, putting focus on the "new" record at the bottom of the form. Is there a way to set it to open this form, but only display say last 10 records and then focus on "new" record.
I have a form with a sub form. The parent form has a field called 'ReportedBy'. The user enters their name in this field. I want the subform to display all records that have been created by the name in ReportedBy. I have based the subform source object on the following sql query:
SELECT * FROM tblFault WHERE tblfault.reportedby=[forms]![frmfault]![reportedby];
The sub form only shows one record even though there are more records in the table????
I have a listbox inside name of workers which is coming from table (Workersdetail) on the otherside I have report from the same table which still showing all records of worker, this report execute through the form and by selection of listbox values. look into my following codes.
Dim strWhereworker As String strWhereworker = "workername = Forms![workermenu]!workerslist"<--(Workerlist is the name of listbox) Select Case Me!reporttoview Case 3 'DoCmd.OpenReport "workerindividual1", acViewReport, , strWhereworker
Problem is, in the report all workers with related values showing while I want to get only the worker name I select from the listbox, I could not understand where I made mistake.
In the test db included the report works fine if every Rep (representative or User/Admin) has records assigned. But, for example, if I go to the table and re-assign the first record to another user/admin (pick list in the table field [Rep]), then `User01’ will have no record assigned at all. Then an error message appears: The Microsoft Office Access database engine does not recognize 'User01' as a valid field name or expression. And no report is produced.
I wanted to try solving it with the info on thread searched by: "cross tab query reports", (13th result) but to be honest I really don’t know how to implement.
I am trying to create an available list of employees to assign to an event. Basically, when I assign someone to the event and need to assign additional people, I don't want the ones who have already been selected to show up in the drop down.
Sheet1 is the linked Excel sheet where all of the employees are contained. Assigned officers is the table where those selected to work are stored.
Using the following code, I get a prompt for sheet1.[employee id]
Code: SELECT Sheet1.[Officer Name], Sheet1.[Employee Number], Sheet1.[Employee Number] FROM Sheet1 WHERE (((Sheet1.[Employee Number]) Not In ([Assigned Officer].[Employee ID]))); ;
A while back, I asked about the now-removed function of exporting Reports to Excel. I was told that there's no way to do so in Access 2007 and that the best best would be to export the underlying Query.
I did so and I found out that the query was showing the record number of some data pulled in via combo box (which gets data from a separate table) instead of the data itself. Logically, I can understand why that would happen, but is there a way to change that so that the intended data, not the record number, is shown?
hi, i have a form, which when opened, gives me a blank record. which is what i asked it to do in the OnOpen property. however, i also need a msg box to popup - but after the form has been opened. i tried placing both pieces of 'code' into the OnOpen property, however, the popup displays first, then the form opens.
so currently i have:
Private Sub Form_Load() Select Case MsgBox("Please select the Registration Number from the drop down menu or type it in", vbOKOnly, "Select Registration Number")
End Select End Sub -------------------------------------------------------------------------- Private Sub Form_Open(Cancel As Integer) DoCmd.GoToRecord , , acNewRec
End Sub --------------------------------------------------------------------------
but it does the same thing. so, is there a way to make the msg box appear after the form opens with a blank record? thank you in advance :D
I'm experimenting with the database attached (Access 2003) and on the Order Deatails subform I would like to be able to view only the products for the related CustomerID!
Any pointers on how to do this - very much appreciated :D !
Thanks,
Mary
(PS Some non-essential db components have been deleted in order to decrease size for upload)
I have a query which is supposed to search for all engines with a power rating between a user-specified range ('Rated Power'). The results should state all of these engines along with a few more related details ('System_ID_No', 'Project No', 'Rated Speed', 'Other Ratings' and 'Cylinder Capacity') that are useful to know. However, the problem arises when these other fields are empty. If empty, the related engine results do not appear in the final results spreadsheet. How do I make sure they are included aswell?
SQL:
SELECT tblProjectOverview.System_ID_No, tblProjectOverview.[Project No], tblProjectOverview.Customer, tblEnginePerformance.[Rated Power], tblEnginePerformance.[Rated Speed], tblEnginePerformance.[Other Ratings], tblEngineDefinition.[Cylinder Capacity] FROM (tblProjectOverview INNER JOIN tblEnginePerformance ON tblProjectOverview.[System_ID_No] = tblEnginePerformance.[Sytem_ID_No]) INNER JOIN tblEngineDefinition ON tblProjectOverview.System_ID_No = tblEngineDefinition.System_ID_No WHERE (((tblEnginePerformance.[Rated Power]) Between [Enter minimum power rating (kW):] And [Enter maximum power rating (kW):]));
I've created a query that runs from a form and I would like the data to be shown subtotaled on a report.
Basically, I have Year Group, Behaviour Type, Behaviour Location and I would just like subtotals and %'s for each change. So, for example, there may be 1000 records in total 200 in Year 3, 500 in Year 4, 100 in Year 5 and 200 in Year 6. I would like to show this in a table with %'s as well as figures if possible (a pie chart is the eventual aim!)
Then I would like to subtotal the same records for the changes in Behaviour Type e.g. Bullying, Verbal Aggression etc to find out how many issues of each type have occured and the percentages.
I can get this information in a report using the group feature but it's a real mess and it's broken down far more than I expected.
I have a query which is compiling monthly transaction of fee, the students paying through voucher. To every month the query is building records for students (Grouped), my problem is i have to show the arrears in the same query which would be on minus/plus of paid amount from actual fee both are in this query i can populate the arrears by simply subtracting both fields but the thing is that i am looking for a arrears which would be the adding total of previous arrears as to date transaction.
I have a form which calculates many fields in the form. However, the issue that I have is how to use a different field in the query than the field that is shown in the drop down combo box in the calculation of an expression.
In the current db there are some tables and queries, forms designed around them
With one table Test http://members.optusnet.com.au/~lukechang/access/table_d.JPG
That has 3 records in them http://members.optusnet.com.au/~lukechang/access/table.JPG
Just won't show in form anymore, at this stage http://members.optusnet.com.au/~lukechang/access/form.JPG
The Form was working previously. This is the second time this problem has came up, I thought it would go away if I made a new form and pasted all the controls and codes. It indeed went away for a while until this problem hit me again...
This db is in Access 2000 format and designed using Access 2003
I would appreciate if anyone is willing to point me to the right directions, many many thanks in advance
When I have a simple table, users pictures inside maps (jpeg,png...) defined as OLE object, when creating the form from that tables, don't have picture shown, only name of the picture? I tried all the options, package, copy, paste... nothing...always the same... just picture name but no picture.
Essentially I have a table for rooms around my school and what the rooms contain. Most of the details for the rooms are Boolean (e.g. If the room has a projector, Yes/No). So what im trying to do is have a form where i can select a checkbox(s) and if i tick one, a query that holds all the room details will only show rooms with projectors in them, which i can then display those results in another form.
My question is how do I get the check boxes in the form to narrow down the room results to only show the ones with the criteria i have selected in the form?
I am trying to create a list box that passes to the database a value that is different from what shown in the drop down list. For instance, I want to show the users a drop down list with "Yes" and "No", but then storing into the database "1" when "Yes" is selected and "2" when "No" is selected.
Mainform width is 22"(Maximum) Subreport Wdth is also 22" (Maximum)
When i run the mainform, the subreport shows correctly but the vertical scrollbar is at most right side of main form so i have to scroll main form horizontally first to access the vertical scroll bar of subreport.
To solve, changing subreport orientation to right-to-left shows vertical scroll bar at most left but still if i scroll horizontally on mainform, that scrollbar hides as its not freezed.
Consider ActiveX Control of Microsoft ScrollBars 2.0 .....
I have form where I will show the details of one table(member details table). In same form I have subform where I can get the member family details (here I am using the relationship table), now my problem is when Member detail form open based on the member id, the below subform data should be fetched...
I have a database with many Quires I want to create in the main form "welcome Form" many txt boxes cells
To show a total records in Each Quires
For Example I have the below list queries
Expired Employees PassportExpired Employees Work PermitExpired Employees VisasExpired Employees Insurance
In the "Welcome Forms" I will create a txt boxes Cells for Each one of those Queries to be Show only No "total Record in Each one". It is like a dashboard to monitor daily without opening the Report or Query...
I have built a query to calculate the expiry dates of training courses but I am trying to input a criteria so that only dates within 90 days of todays date show. I am using Date()<90 but it doesn't return the correct information. What the criteria should be for this?
