I have to give a three paramters queries. But i have to handle null responses also in such a way that in one parameter or two parameters i don't want to enter value and the result should bring all possible values with the parameters I entered
Like customer, status and order number in different fields
when i don't enter order number the result should bring all values in status and customer
I have a button on a Tab that opens an input form to allow user to add a records. The record is indexed to relevant option from the combo bos on main form, so if no choice is made and user clicks add button megbox 'Invalid Use of Null' pops up. However I can't seem to prevent it, even when I've removed the code completely from the method/function. What's going on there? I just want the message box to say, please make a selection or something. Any ideas?
View 1 Replies View RelatedI have a client database that has null values in issue received, there are as many as ten issues per client. What I need to do is make a list of clients that have only two issues outstanding and a list of clients that have three or more issues outstanding. There are two tables that are connected correctly. The client table(one) and the issues table(many).
View 14 Replies View RelatedIn Expression Builder, I have the query(in the Criteria row) set to run based on values in different fields on a form. When one of the fields is empty however, the query will not return any records.
How can tell Access to ignore the fields when those fields on the form are empty and only look at fields that are not null.
I have a form which users can toggle whether they want to only see entries that has data in a certain field. Previously I have set up a separate query with a hardcoded 'Is Not Null' in the criteria, and set the form to call the different queries based on the status of a toggle button. This time there is 22 queries that need to be modified so I'm hoping there's a better way.
I have a hidden textbox on my form that has value "Is Not Null" or Null based on the position of the toggle button.
In my query for the field criteria I have [Forms]![MainForm].[txtCriteria] where txtCriteria is the textbox previously mentioned.
When I run the query no data is shown.
I had an issue with writing LIKE statements in query criteria yesterday [URL]....
The answer they gave worked perfectly when I only used a single table in the query. But as soon as I did an INNER JOIN with two other tables, now I get parameter value prompts when I open frmSearch, and instead of seeing ALL my records when the controls are left null, I get only the first record in the table.
Here's the SQL of the query, can you point out what I messed up? NOTE that this SQL was 'written' by Access.. as I used the Query builder to set all the 'Like or Is Null' statements, then clicked SQL and sorta formatted the code so I can see what I'm looking at (instead of superthick wall-o-code):
Code:
SELECT tblPeople.name, tblPeople.num
FROM (tblPeople INNER JOIN tblAddresses ON tblPeople.name = tblAddresses.name)
INNER JOIN tblPets ON tblPeople.name = tblPets.name
[code]....
Basically, this is a searchable database of participants in a pet-adoption program, along with the participants' pets history and address history (hence the linked tables as opposed to additional columns in one single table for pets and addresses... there are more than one in some cases). The frmSearch allows a person to run quick searches based upon ANY item in the database, such as name, pets, addresses, pet age, pet type, county of residence, etc. I need to be able to pick ANY field on frmSearch and type a value, and have the qrySearch return records for ANY record's related column wherein any part of it matches what I typed.
The statements as written worked PERFECTLY right up until I added the INNER JOIN. Now I get a set of parameter value prompts for every field on frmSearch that's referenced in the SQL for EACH table I linked to tblPeople, and if I leave everything null and click Search, I want to see EVERY person, but I'm only seeing the very FIRST person in tblPeople.
I am trying to alter this parameter to bring back all records if either beginningsalesrange or ending salesrange is left blank. I can't quite get it right.
Between [forms]![frmState]![BeginningSalesRange] And [Forms]![frmState]![EndingSalesRange]
i'm creating a search form giving the end user a range of controls to use when filtering/searching data. See the image.But, i think my range search (using the textbox) to put in a lower and upper limit...is preventing this from working. In fact, when i put data into all the controls, no data pops up in my subform.
My query data source can also be seen...showing you how i've handled teh null entries. (i need to put in a null 'handler' for the two textboxes?)
I'm trying to split out the first name from a GivenNames field which contains first and middle names. Some values in the GivenNames field have just a first name. I've tried the below formula, but still get some #Error responses where only a first name exists in the field.
My formula:
Given Name: IIf(IsError(Left([GivenNames],InStr([GivenNames]," ")-1)),[GivenNames],Left([GivenNames],InStr([GivenNames]," ")-1))
(I'm not sure if this belongs in the macro or query forum)
I have a macro that opens 15 queries and ends with opening a report.
The final query asks the user to type in beginning date, then an end date. Now when there's a data mismatch (i.e., he spells feburary), the macro halts. No harm no foul, they can just click the command button again, but I'd rather not.
What I'd like to do is place a msgbox (i.e., "Check Your Spelling") and then reroute them back to the query.
I have built a contacts manager based on an Outlook export (I know I can link the two programs, but I wanted to customize the contacts a bit further). I built the database in a way that phone numbers, addresses, etc. allow for multiple entries for each person under the same column using a foreign key. The only difference is a field called "Type" which designates the entry for Home, Work, etc. The issue I ran into is when I queried the data I get multiple entries for the same record. As you are all likely aware, Access repeats the data in a query for each different case. For example if Bob Jones has a personal and work phone number, Access will show the results in a query like this:
1. Bob Jones 555-555-5555 Personal
1. Bob Jones 555-222-2222 Work
2. Jerry Smith 555-111-1111 Personal
If I kept the data in Access it wouldn't necessarily be an issue. However, I want to be able to export the data in an excel file. How do I get Excel to return the data so it looks like this:
1. Bob Jones 555-555-5555 Personal 555-222-2222 Work
2. Jerry Smith 555-111-1111 Personal
Hello all,
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?
Thanks.
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.
View 2 Replies View RelatedHi,
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 trying to do the good 'ol sales report (query) to include customers with no sales.
I have a customers table, account number table, sales table & sales (line) detail table. (all linked in that order)
If I run a query to show customers (in the customer table) with account numbers, that works
An unmatched query to show customer without an account number works (but of course the unmatched account number field isn't shown).
How can I get the two two be shown together with the "unmatched" having a null or 0 for their account number?
I am guessing in principle, the resulting solution can be modified to show customers without sales alongside those with sales?
I have recently upsized an Access database to SQL Server. The .adp is for the most part working fine.
However, when an end user tries to create a duplicate record in one of the Primary key columns of a Form he gets a SQL Server error message as follows:
"Violation of Primary Key constraint. Cannot insert duplicate key in object myTable".
This is obviously not a very useful error message. What would be the best way to ensure that a more useful error message is returned?
I have considered adding code to the "After_Update" event of the cell in question. This however seems long winded. Is there an easier option?
Thanks
Kabir
Hi
Have the following code that works on Access 2000
Dim myForenameBefore As String
Dim myForenameAfter As String
With CurrentDb.OpenRecordset("Names", dbOpenDynaset)
Do Until .EOF
myForenameBefore = .Fields("Forename")
myForenameAfter = Replace(.Fields("Forename"), "Elizh", "Elizabeth")
MsgBox myForenameBefore & " " & myForenameAfter
.MoveNext
Loop
.Close
End With
So that I can replace any abbreviated names in a table, however I cannot figure out how to put the value of myForenameAfter back into the table replacing the existing data.
Must be particularly thick today
I have a form with a number of check boxes. Clicking on the checkboxes carries out a count function which is then displayed. If the count goes above 20 then a message box is displayed telling the user of it. My problem is that once the number is above 20, the message box diplays everytime. I want it to display just once, regardless of how high the number goes. The checkboxes can be ticked in any order.
At present i'm using
On click
If Me.Text1 > 20 Then
Msgbox "The number is graeter than 20"
End If
Hi all
The following code only half works. I want to stop an ugly error occuring when primary keys are duplicated. In the following code the error handler picks up and displays a simple message when a user attempts to add duplicate values. BUT if the users adds a record, the record is successfully added followed with the same error message. I think I've put the error handler in the wrong place, but I can't figure out where it should go. Any ideas are much appreciated.
Damon
Private Sub cmdUpdateGoMaths_Click()
'update Go Maths
On Error GoTo error
Dim adors As ADODB.Recordset
Dim sSQL As String
' sSQL = "Select * from datGoMaths;"
' Set rsADO = New ADODB.Recordset
' rsADO.Open sSQL, CurrentProject.Connection, adOpenKeyset, adLockBatchOptimistic
sSQL = "dataGoMaths"
Set adors = New ADODB.Recordset
adors.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
With adors
.AddNew
.Fields("EQID") = Me.lstGoMathsStudentName
.Fields("IDGoMaths") = Me.lstUnitNo.Column(0)
.Fields("Score") = Me.txtScore
.Update
End With
Set adors = Nothing
MsgBox Me.txtStudentName & " Added"
Me.lstGoMathsStudentName = ""
Me.txtScore = ""
error:
MsgBox "test"
DoCmd.CancelEvent
End Sub
I do not understand what is happening here. I have foll0wing line in a calculated query field:
m: Switch([EmpID]<5,1) ' run Query 18 in attached example, A2007/2010
this produces 1 for all EmpID<5 and Null for all other EmpID's. All as expected.
But if I do this:
m: Switch([EmpID]<5,1,[EmpID]>=5,Null) ' run Query 19 in attached example
then the entire column is set to Null
I currently have a macro that sets the value of several fields on a "lost focus " event; however there is an error that I would like access to ignore, is there any way to have this macro process this function "On Error Resume Next" ?
Carbo
When I export my access database to XML I noticed that the XML elements for the table keys contain the key numbers themselves, not the table name (or a chosen field in the table). For an application I am working on, this presents a problem. What I have is:
One table called 'Process' that looks like:
Process instance_idSystem instance IdProcess name
26 24 Two_thread_process
I have another table called "Thread":
Thread _instance_id Process instance_Id Thread instance name
30 26 T1_instance
XML export looks like:
- <process_INSTANCE>
<process_instance_id>26</process_instance_id>
<system_instance_name_id>24</system_instance_name_id>
<process_instance_name>two_thread_process</process_instance_name>
- <thread_INSTANCE>
<Thread_instance_id>30</Thread_instance_id>
<process_instance_id>26</process_instance_id>
<thread_instance_name>T1_instance</thread_instance_name>
</thread_INSTANCE>
</process_INSTANCE>
What I would *like * the XML to look like is:
- <process_INSTANCE>
<process_instance_id>26</process_instance_id>
<system_instance_name_id>24</system_instance_name_id>
<process_instance_name>two_thread_process</process_instance_name>
- <thread_INSTANCE>
<Thread_instance_id>30</Thread_instance_id>
<process_instance_id>two_thread_process</process_instance_id> (**name instead of key here**)
<thread_instance_name>T1_instance</thread_instance_name>
<thread_instance_type>periodic</thread_instance_type>
</thread_INSTANCE>
The access XML export writes out the key values as they appear in the tables. What I would like is for it to put in the ‘process instance name’ instead of the key that points back to the base (process) field.
Any pointers on how I could do this?
Thanks
John
Does anyone have a recommended way to deal with images (ie. linked to file, OLE object embedded, others I'm not aware of)???
I have a report which summarizes the history of a project. In this report I have a subreport which has task information. As a user enters task information they have the option of associating pictures with the task (ie. prototypes, design images, test setup, etc).
So my data structure is based on:
- many projects
- each project can have many tasks
- each task can have many pictures
My report for each project lists some typical project information and a subreport shows the task details associated with that project along with as many images that are associated with it.
My current system links to files on a network to avoid database size. This works well for storage and form use, until I try to print or preview a report. Then some of my pictures don't show up and I get a message that says images may be too large. My report could conceivably have up to 100 images, but I'm images stop showing up when I have around 15-20 pictures. Pictures files are typically 300 KB.
Is there a better way to show images on a report?????
I have a form where the user enters a year then clicks on a command button to open a report.
If a year that there is no data for is entered an error 2427 occurs.
I have tried putting the following code in the on error event of the form with now success:
Select Case DataErr
Case 2427
MsgBox "There are no records for the year you entered" & Chr(13) & "Make sure you entered the correct year”
Response = acDataErrContinue
Case Else
Response = acDataErrDisplay
End Select
Does anyone know how to trap this error?
Thanks
Hi All,
I am rather new to this and I must admit I am getting everything completely wrapped around my head!
I am developing a database that stores technical support requests received and their solutions. I have three main tables :
Problem - stores all the details of the problems received
Client - stores the address details of the client which is then associated with the 'Problem' table
Company - stores the name of the company which is then associated with the 'Client' table. This is a separate table as different clients can work for the same company.
At present, I have a 'problem data entry' form in which I enter all the details of the problem. At the top there is a section where I can do a search for a particular client and company and the details appear in the respective text boxs on the 'problem data entry' form. If the client does not exist, I have a button that I use to open another form which allows the entry of the new client's details ('New Client'). The user can choose which company this client works for by using a combobox. If the company is not already in the database, I have another button for opening a 'New Company' form!
Its all a little convoluted and it does not work particularly well to be honest. When I add a new company, I need to move forward a record and then back in the 'New Company' form in order that the new company information is saved correctly.
The company selection combobox in the form 'New Client' is then updated with the new company name BUT I need to actually open the combobox and select the company name myself for it to work properly! Once again I also need to move forward one record and then back again in order to properly save all the information before I can close the form.
What would be nice is to do a search for the the company in the combobox on the 'New Client' form, if the company is not found, it automatically adds the company to the database whilst updating all the necessary links. Hence getting rid of the 'New Company' form. I would also like to get rid of the problem of having to move forwards a record and then back again.
Any ideas? I find my method to be rather poor, there must be a simpler way of doing this!
Many Thanks,
Lee
On a form i have a textbox to enter a date and multiple command buttons to open other forms.
After entering a date value, followed by either a tab or clicking on one of the command buttons, i want to validate the entered date value. If the date is invalid i want to display an error message and the cursor positioned on the textbox field.
The LostFocus event of the textbox is as follows:
Private Sub txtDateField_LostFocus()
If Not IsDate(txtDateField.Value) Then
MsgBox "Date invalid."
txtDateField.SetFocus
End If
End Sub
If entering an invalid date is followed by a tab, the error is displayed but the cursor is not positioned on the textbox field.
If entering an invalid date is followed by clicking on one of the command buttons, the error is displayed but the expected form is opened.
How to position the cursor appropriate and how to prevent opening the form?
Who helps?