How do I use a query to "add" two text fields together?
Convert field to General Number
I have a query that displays my yes/no check boxes. In the query I have fields that would indicate if field = "Yes" set to 1,
This works great. The results shows one field as a check box and the next as a 1. Just as I need.
I now use this query in a report and the 1 show up as "Yes" not a 1. oops
I was trying to print a list where I could show what was flaged 1 then total the field and add that field to other fields.
I can get a sum of the "Yes" field but I can't add two "Yes" fields together as they are text fields.
I tried to set the Queries field to General Number and the report to General number but when I save them they convert back to text fields
Counting Text Fields in a Query
I have created a tool for our audit team to use when reviewing the work of team members. In total there are 54 questions that they have to answer and each one has a possible outcomes of Yes, No, N/A.
The field name is txtQ1Outcomes, txtQ2outcomes etc. and all data is stored in a table tblDataStore.
Once all of this data is collated I then want to run a report that shows the audit results by counting the number of times a particular handler for example, has had an outcome of Yes/No/NA for Q1, Q2, etc.
If I create a query and use Q1 as an example then I have two columns, one to group by and one to count and this works really well. However as soon as I add Q2 to the query I get a "summary grid" that shows how many times Q1 has been answered "Yes" and then how many times Q2 has been answered "yes", then how many times Q1 has been answered"Yes" and Q2 has been answered "no" and so on.
As I add more and more fields so the matrix becomes more complex and does not give me what I want.
All I want is "Q1 has these outcomes", "Q2 has these outcomes", and I am ending up with a response with numerous permutations on it.
This what I am getting when I add other columns to the query and this is only for 2 of the 54 fields.
How can I simply get the totals for each individuals question without creating a query for each question and then probably creating a sub report in the overall report that goes back to the team leaders for review?
Do I need to re-arrange the data? I thought about assigning numerical values to each outcome, but really need a count of Yes, No, N/A. For example as an insurance company if there is noinjury to a claimant then several questions would be N/A so I can't ignore them as we need to know if a handler has missed the injury out entirely if it should have been applicable.
Queries, Expressions and Text Fields
I have created a query where a field uses this expression:
IIf([Field 1]"" Or [Field 2]"","Answer B") and this works fine.
When I created a new query where I am joining one Query (Query 1) to another Query that has the expression above (Query 2) I used the relationship Include records from Query 1 and only those records from Query 2 where the joined fields are equal (the joined fields are numeric and the are no null values in the joined fields).
When I use the field that has the expression, it applies that expression to all of the records in Query 1 as well as Query 2.
.Body Property very long
I would like to make a quick question. I have a code in a form of my database that sends an e-mail to the people in it.
In the code I have ".Body="; I have written the following:
.Body= "Please read information attached."
I would like to know if there is any possibility to add a more extensive verbiage including space between paragraphs. I have tried to do this by adding the following:
But the vba says there is an error, first it says "_" is an invalid character.
Is possible to add an extensive verbiage with this property
Help with forms and subforms
I'm putting together a job costing form (with subforms in tabs such as Materials, Labour, etc.). This example will be just for a single "Job" form with a "Labour" subform. If I can get this I can just add more subforms
First, my tables are Clients (ClientID, ClientName, Address), LabourRates (LabourID, Type, Rate) and Jobs (JobID, CustomerID, date). Do I create the "Jobs" table first and then create a "Jobs" form off of it?
Next: for the subform, I assume my best course of action is to create a "Job Details" query for my subform. What fields should I need? I'm thinking JobDetailsID, ClientID, JobID, LabourID, Rate, Hours (entered by user).
Basing the subform record source on this query is the best option, right? Do I need JobID in this query? I assume I do, even if for the future.
Ideally, I'd like to select the "Type" of Labour (eg. Overtime: $40.00) in a combo box on the subform and have the "Rate" field in the subform automatically populate with the corresponding rate.
Some people seem to say to add some code to the OnUpdate or OnDirty options (Me.TextBox = Me.ComboBox.Column(3)). Others seem to think that my query will allow me to do that if I set the record source of the subform to the query and the source for the "Rate" text box to.? I'm not sure.
but it only involved a form and not a subform. Both the "LabourID" combo box and the "Rate" text box are in a subform. Does LabourID come from the "Job details" query or from the "Labour" table? What about "Rate".
I assume it's best to avoid lookup fields in tables and use queries instead; Base forms on those queries when possible, especially when you're looking to bind fields.
Query too Complex Error
I jst made this database for some official work n it includes abt 10 tables and 25 queries. I have queries in few levels . it means that a query is created from some queries and such queries are used to create another.
So likewise as I add on queries. and in a later stage when I try to add more fields into a one of these queries, I get an error message sayin "QUERY TOO COMPLEX" I searched for online help and found MS ACCESS help.
Followed what they said. but stil it appears. I have to add more fields to this query and aslo some more queries.bt they wont allow it.
the speciality is, when I add a single field additionaly to the existing fields in the query only this appears and once I delete that field no error is given and in all above, I can keep onadding more queries without this prob, but with only lesser (2 or 3 ) fields.
Can I refer to a "*" in a query criteria as text and not a wildcard?
I don't know if this is possible.
I need to filter on whether the value of a field is "BIN/" or "BIN*" or various other things.
The data is from a mainframe system, and whoever created that was wonderful enough to allow these characters in text fields
Why am i getting Null fields with this query?
Like "*1083*" Or Like "*4048*" Or Like "*1083*" Or Like "*4049*" Or Like "*4050*" Or Like "*4052*" Or Like "*6001*" Or Like "*6464*" Or Like "*8602*" Or Like "*8603*" Or Like "*8609*" Or Like "*8627*" Or Like "*8628*" Or Like "*8629*" Or Like "870*" Or Like "871*" Or Like "872*" Or Like "873*" Or Like "874*" Or Like "876*" Or Like "*,870*" Or Like "*,871*" Or Like "*,872*" Or Like "*,873*" Or Like "*,874*" Or Like "*,876*" Or Like "*8751*" Or Like "*8752*" Or Like "*8753*" Or Like "*8757*" Or Like "*8758*" Or Like "*8759*" Or Like "*8770*" Or Like "*8776*" Or Like "*8800*" Or Like "*8805*" Or Like "*8806*" Or Like
Prevent user from "Adding new record" if mandatory fields are null
I have some fields namely.
and so on.i want that : " if required fields are null " the "add record " button is disabled, if all required fields are filled in then only the "add record" button should be enabled.
can someone guide me in detail.what event and what
Reference to a Query
My form only has one text item:
Name: Michael Jordan
And I have a query, of only Basketball players, now I want to add on my form:
Does he play basketball?: YES or NO
I added a text box and tried: =IIF([Name]=[Query]![Name], "yes", "no")
but of course I get a #name? error
Build Query in VBA using List Box to Select Fields
The user would like to be able to create queries dynamically. ie. She would like to be able to select which fields are in the query. Each of the "variable" fields to be selected are Yes/No Checkboxes.
I thought the best way to do this is to create a query with the basic fields needed and then add the "variable" fields by allowing the user to select them using a list box.
How do I transfer the listbox selection to the SQL statement
Query excludes blank fields
Found a number of Googles from this, all unsolved! I have what should be a simple query. I have, let's say 1302 rows. In column Dog their are 12 unique values. My query should return only items that have the value "Doberman," "Mutt," "It's a Cat," and blank fields.
If I exclude anything (one or all the unwanted values) the query stops returning the blank fields. I've tried some NZ and "" and "*" constructions, but the exact formula eludes me.
With the odd values, like "It's a Cat" I am using "*Cat*" to avoid any issues with characters/symbols. This query looks at values in two columns. The other is a simple requirement that "Purina" be in the field.
Best way to show multiple tables on one form?
I typed a long message earlier and got a database error,
I have three tables. Each heading is a criteria. Each row is a customer account number. On each row I can assign one letter to each criteria, which are "B", "W" or "S".
What I need is a form that shows this data and allows it to be modified. Usually I would use "Add existing fields" and design my form around those fields. Why can't I now? Because the user can add a new field in using an "Add field" button I made.
So, how do I make the form automatically show any new fields that users have added?
Append query issue
So I have a form that user fills out and when they click on the "save" button, this runs an "append query" that adds what the user wrote to a table on the back end. theproblem is that when it tries to run the query it can't add the record and the reason it gives me is:
"can't add record due to key violations"
I designed this table to be able to hold multiple records of the same person by having the "person id" and the "date" in which they came in as primary key fields. Thatway, a person can be in the table multiple times if the dates are different, so why is the query complaining and why is it not working as intended?
Set a Field Value when using a make table Query
I am pulling data fom SQL which makes a base table, when it runs I also add in 2 fields which are CreatedOn:"" and ModifiedOn:"", I then run another procedure using the newly made table to update these two fields from another table, My problem is that in the original table (lets call it base1) these two fields are set as text which really screws things up later when I try to use them,
The Make table query gets run once a week to capture any outstanding data etc so the table is over written then, I have tried setting the values in the property fields in the query but it dont seem to work, I want to avoid opening the table after each run and manually setting the field to DateTime.
Is there a way that I can set thsi so it does it each time I run the process
Paasing parameters to a query
I can pass criteria that is simplistic e.g.
strAttending = "Attended"
SetResultAttending strAttending (this passes my information to a global variable)
And I then get a query result returned based on this criteria, no problem, with a GetResultAttending method in the query. Success.
But when I try to add more than one criteria, The query return is blank.
strAttending = """" + "Attended" + """" + " Or " + """" + "Confirmed" + """"
"Attended" Or "Confirmed"
in the query criteria box, in the query and this does not work. I have also tried passing it without the quotation marks without any success.
I have also tried the query criteria in another query that is identical, without passing variables, and the criteria returns the result I am looking for when I do that.
A query with Sum() and how to add more fields to it.
Do I have to use a join? If so what would the structure have to look like?" result in a functional query but "FullCityName, ZipCode"result in an "aggregate function" error? I need to get this down.
How to disable edit for query
I have a search query form, where I have command control to run query, he is my code for it:
Option Compare Database
Private Sub Command12_Click()
Me.qfirstname1.Value = ""
Me.qlastname1.Value = ""
Me.qnickname1.Value = ""
Me.qnextofkinname1.Value = ""
Me.qnotes1.Value = ""
Private Sub Command13_Click()
DoCmd.OpenQuery "mainquery", acViewNormal
My mainquery query has the fields with in the criteria for example for one of the fields in:
Like "*" & [Forms]![searchform]![qfirstname1] & "*" Or Is Null
Also, what I want done is, when I run the query, I shouldnt be able to change/edit anything in the result records
New record button
I would like to add a button that when clicked will add a new record with specific text.
For example, when the button is clicked it will atomatically add the text "auto note" + whatever is in a field on the form.
Say I have a field named "note" and it is not stored in a table, it is just for inputting text. Then once the text is typed, you would click the add button and it would automatically add a new record with text: "<Auto Note>" & [note]
I hope this makes sense. I have tried a bunch of things, but I don't know what I'm doing to be honest. I have never tried this before
Pivot Statement - SQL - Force a column when there is no data
I have two Cross Tab Queries that Pivot on Text fields and return a Count for each category. Occasionally, one of more of the categories may have no data/activity during the period/date range of the query's criteria.
What I want to do is to force a column for those categories. In as much as they are text fields, I would like a missing category to return "No Data" or N/D. If a Category is missing it causes other subsequent queries that are expecting these columns to fail and generate errors, which cause automation code to crash.
The categories for the first query are: [Type] - "CD", "OD" or "PD".
The categories for the second query are: [Campus] - "MK", "NH", 'SY" or "KG".
The Pivot Counts the number of occurances under each category based on another Text [field] [SNum] or [WCode]
Both queries run on the same tmpAtt_DateRange table. This table is created based on a user input for [StartDate] & [EndDate]. (Specific Period for Comparison)....