I am using a predefined query to define the sql statement that will serve as recordsource to a form. Syntax is fine. The predefined query does not have the primary key of the table. The selection in the recordsource is done using this key. So I add this field to the design of the query. I save the query. It says the query is saved. But it is never saved. Run the query again it is not changed.
View RepliesWhy is this update query not working, I'm trying to update a date field stored in a table.
The new date is passed from a txtbox on a form to the update query!!!
SQL code
UPDATE TblDietPlantemp SET TblDietPlantemp.MealDate = [Forms]![FrmSwitchBoard]![txtCusDate];
I have a table with a list of different government programs that products can take advantage of. Each of these programs has criteria such as "must use less than 1000W" or "lasts for 100 hours". Rather than have a column for each possible condition, I've created 3 fields that will accept any type of condition; Var1Condition (example: watts) , Var1Requirement (example: >=), and Var1Value (example: 50). I figured this would be the more efficient database design than to add 15-20 columns.
I then built a form that would where I could enter product attributes and would hopefully query my database and only return the programs for which the product would qualify. So I would have a field named "Watts" in which I would put the wattage of the product and then I would see which programs it would fall in.
In theory, it should be simple. I figured I could just find a way to combine my 3 criteria fields into one string "Forms!Search!watts >= 50" and then use that as a query filter, but I can't find a way to do it.
I have several projects with different tasks for each. I have 3 fields [fkProjectsID], [TaskOrder] and [Duration] in a table for project tracking with that structure:
[fkProjectsID] [TaskOrder] [Duration]
1 /1 /5
1 /2 /8
1 /3 /15
1 /4 /6
2 /1 /8
2 /2 /30
2 /3 /25
I want to calculate cumulative values stored in [Duration] field (represent a number of days). I'm using the field [TaskOrder] to order different tasks within each project. With some testing, I was able to calculate cumulative [Duration] with 1 project using the DSum fucntion as following:
CumulDuration: DSum("[Duration]", "[tblProjectTracking]", "[TaskOrder]<=" & [TaskOrder])
I was having the sequence: 5, 13, 28, 34 for respectively Task 1,2,3,4. However, when I add a second project (and then a third...), I need to be able to filter based on [fkProjectsID] as well (i.e. a specific DSum by ProjectsID). I add this criteria but I get the sum of [Duration] on each row instead of the cumulative:
CumulDuration: DSum("[Duration]","[tblProjectTracking]","[TaskOrder]<=" & [TaskOrder] And "[fkProjectsID]=" & [fkProjectsID])
Is there any way to count how many lines of text are stored in a particular table field? How about counting how many times was the "enter" key pressed?
Thanks
Mafhobb
I am running into an issue when trying to compare data stored within the same table.
tblParcels - holds data relating to an individual parcel of land; unique identifier is ParcelID. No repeating data.
tblValues - holds data relating to the value(s) of each parcel; new values entered annually; unique identifier is TaxID; has field to link to tblParcels. One parcel can have multiple entries for each year tracked.
I have two unbound text boxes on frmMainForm where users can enter Tax Years to compare (txtYear1 and txtYear2). Theory being that users will be able to select any two tax years and compare the parcel values.The report (rptMaster) is built using three queries and is used to show the change in value between txtYear1 and txtYear2:
qryYear1 - pulls all values from tblValues where the TaxYear equals the year typed in txtYear1.
qryYear2 - pulls all values from tblValues where the TaxYear equals the year typed in txtYear2.
qryCompare - joins the results from qryYear1 and qryYear2, along with other common fields, and populates rptMaster.
My problem lies when a parcel does not have any values for one or both of the years entered into the text boxes. I completely understand why, qryCompare can't show any records if there are no results coming from either qryYear1 or qryYear2. What I am not getting is how to work around this issue.I would like for the report to show "$0.00" for the value if there is not a value for that Parcel in tblValues for the year entered.
tblValues example data:
TaxID ParcelID TaxYear Value
1 5 2011 100,000
2 5 2012 200,000
3 6 2012 75,000
In the example above, when comparing 2012 values to 2011 values, my report shows the values for ParcelID 5 but not for ParcelID 6 as it does not have a 2011 value (and won't since information about that parcel didn't exist in 2011).
With microsofts article, I have made to store multi options value of a list box in a text box with comma. However, since these are IDs being stored, I want these values to run a query and get results also.
But I am confused since have never used comma like in query
I have a question regarding the display of a list of numbers that are stored into a field of my database. Currently the numbers are comma-delineated which is fine with me, but I was wondering if there is a way to change the display of those numbers on the form so that it is easier to read.
Example:
current format - 1,2,3,4,7,8,9,10
desired format - 1-4, 7-10
SQL stored procedure which is simple INSERT statement on a single table 'tblSOF'
Code:
-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter values below.
[Code] ....
I am stumped with the following error.
Error: Msg 102, Level 15, State 1, Procedure InsertINTO_tblSOF_sp, Line 80 Incorrect syntax near ')'.
What are the benefits of each a stored query or sql? I suspect that the stored query would run quicker>
View 1 Replies View RelatedI have developed a database but have had difficulty with sorting data within subform of a main form. The subform displays the related tasks that correspond with main form that has been selected. Within the main form I have also created a text field that defines a particular sorting sequence of the tasks found within the subform which is titled Task Sequence. What I would like to do, is use the Task Sequence field to sort the order of tasks within subform. I have tried: IIf([ID] Is Null, 0, Val([Task Sequence])) within the Advanced filter/sort but either it shows only the first task defined in the Task Sequence or it wants to filter the main form and not the subform.
View 14 Replies View RelatedHello - am hoping someone can help with the below query.
I have a database with two linked tables. Both of them contain a 10-digit ID number. One table (Table 1) has this ID stored as text; the second (Table 2) as a double.
I need to find records in Table 2 that are not in Table 1. I first created a stored query that converted the Table 1 ID into a double using the function CDbl([ID]). I then created a new query left-joining the stored query on Table 2 against the ID. The query displays the stored query's converted ID.
I run the new query. In the results - if there's a match, the ID successfully appears. But where there's no match - instead of a Null or an empty string, "#Error" appears. Can anyone advise why this is, and how I can work around it?
Thanks!
I this is a simplifed version a saved query called "qryTest"
PARAMETERS [gUserID] Long;
SELECT [Id],'Complaint' AS IncidentType FROM tblComplaints WHERE tblComplaints.[Id] IN (Select Incident_ID from tblNotification where Dept_ID IN (Select Dept_ID from tblUserDepts Where User_Id = [gUserID] ) AND Incident_Type = 'Complaint');
UNION ALL SELECT [Id],'Fall' AS IncidentType, FROM tblFalls tblFalls.[Id] IN (Select Incident_ID from tblNotification where Dept_ID IN (Select Dept_ID from tblUserDepts Where User_Id = [gUserID] ) AND Incident_Type = 'Fall');
how to i call the result from code or from the query manager and include the parameter so there is no prompt that comes up?
like
Me.ListNew.RowSource = "qryTest " & gUserID &";"
or
Select * from qryTest , 31 - where 31 is the value fed to the parameter
??? does anyone know? I know it can be called in APS like this:
Dim Conn
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open strConn ' strconn is my connection string
set rsP=createobject("adodb.recordset")
conn.qryTest 31
.....
I can't believe I can't do it from the query string!
Heeeellpppp!
I have created a stored procedure parameter query and using access created a report that runs the procedure and creates a report based on a parameter entered:
1) I wanted to know if I can specify a default paramter so if I do not input it returns all records?
2) Can I create a stored procedure so it asks for month, for example if I had a createdDate field of data type datetime and wanted to return records for a specific month?
I have a parent form that has the following
[Category],[Event],[Name]
In the sub form i am also storing the same values
[Category],[Event],[Name] & [Contestant ID], [Score1],[Score2]
I do not want to enter the
[Category],[Event],[Name] each time i enter a record into this sub form i would like the value that is in the parent form to be picked up and stored as the values for these fields.
Is there a simple way of doing this?
I currently import data into a table and it it has a text field which looks like this: 12,345.67 GBP...I need to use a query to make this a number field so that I can sum it's contents. I've managed to remove the 'GBP' part but can't seem to get rid of the comma?
View 4 Replies View RelatedI have two tables that are joined - called A and B. A has two fields, "PLACE" and "RAND" and is joined to B via field "RAND". Other than "RAND", B has several fields named 01 TOTAL, 02 TOTAL, 03 TOTAL, etc...for about 60 fields.
The "PLACE" field in table A has data that is 01, 02, 03, ect.... How do I structure a query so that whatever "PLACE" is, I can match the XX TOTAL value? In other words, i need to have the query field header be somehow dependent on the value in "PLACE".
Is this even possible?
I’m developing a claim tracking database that tracks dates of events that occur in the course of processing a claim; such as, Loss Date, Report Date, Estimate Date, Payment Date, etc. There are 16 different “Events” in all.I currently have the following tables set up:
tblClaim
ClaimID
ClaimNumber
fkEmpID
tblEmployee
EmpID
EmpName
[code]....
What I need to do is create a form where management can choose two or more events, and calculate the average number of days between two of any of the events, for an employee, or all employees.I have created a crosstab query to change the values in the EventName field in tblEvents to field names, and the EventDate as values for the related EventNames. I created another query based on this query to do the DateDiff.
I created combo boxes on my form with the Row Source Type set to Field List, for a list of fields in my crosstab query. I’ve tried to use the following DateDiff function to get the days between the two fields selected in my combo boxes:
Code:
DateDiff("d",[Forms]![frmReportBuilder]![cboEvent1],[Forms]![frmReportBuilder]![cboEvent2])
But I get an error about unrecognized field name or expression for my combo boxes. So I added my combo boxes in the query parameter window, with a data type as both text and value, but with both I get an error “This expression is typed incorrectly or is too complex to be evaluated.” I also specified the column headings in the crosstab but I still am getting the “too complex” error.I’m pretty sure it’s trying to do a Datediff on the literal values in the comboboxes and not recognizing that I’m trying to specify field names.Is it possible to assign field names in DateDiff this way?
I have a query where I prompt for a Report_Date to create a new field; Report_Date:[ Enter date for report]
Is it possible to use the result from this prompt as a criteria in a different field?
for example, Order_Date based on the criteria of <=[Report_Date]
I have a field that is giving me the number of business days between a period of time and then I want to subtract that number - the person's PTO time to see the actual days they were available...when I simply type the number in (see below) it works great but I want to set up a prompt that will ask me how many PTO Days to calculate as it will be different for each person I am quering...is this possible?
View 9 Replies View RelatedHi,
I have a table that looks something like this:
Company Product Sales
1 A 5
1 B 2
1 C 7
2 A 8
2 B 1
2 C 2
I want a query to produce a table showing Products A though D which is
the sum of Products A and B. Result:
Company Product Sales
1 A 5
1 B 2
1 C 7
1 D 7
2 A 8
2 B 1
2 C 2
2 D 9
I have many rows and many summations and the current union query and
supporting queries are pretty messy and not easily changeable. What
I'd like to do is have a third table which would hold the summed
product names and the formula used to get to it (Product D, A + B) and
have a query that can be dynamically based off of that table. Do I
need to use VBA? Does anyone have a better idea? Any help or even a
good search term would be appreciated!
Thanks!
Nathan
I have a main data table and my users will input their information on a main form. One of the fields is called "IsSkyBlue", which is a Yes/No checkbox field. On the form (which has multiple tabs), it asks the question "Is the Sky blue", with instructions to check the box if the answer is "Yes". I have a "Summary" tab, and on it I want to provide the user's answer in written form. I want it to say: Do you think the sky is blue? YES
So, I have a text box on the Summary tab with the question, and for the answer, I tried =IIf([IsSkyBlue]=YES,"Yes","No"), but I get a #NAME? error in that field. I have tried using YES, TRUE, 1, 0, -1, Not Null.... nothing works in the IIf statement.
Does anyone know how Access stores a "YES" answer to a YES/No field?
I want to make a query that would show ID 1 once based on the criteria of having both Home and Office. I will apply the concept on a bigger database.
ID is from table 1
Date and Location from table 2
It might be a simple question but I don't know how to solve this:
First: I've created a simple query which gives me the result for a given user input "p".
Code:
SELECT TOP 1 val1
FROM tblPerson
WHERE( (val2)<=[p] )
ORDER BY val2 DESC;
Result looks like:
...|..val1...
...| 12345
Now I've a second query. I would like to get the value for discount from the query above by "putting in p".
Code:
Field.......level......... discount
Table.......adbCases...... qryDiscountGet
Total.......expression.....Group By
Criteria...................[p]=[level]
I have made a database to catalogue my coin collection, I have a table with various fields for logging data about each coin.I want to try and make a query that can easily filter the coins that I have the same year so I can easily see which sets of coins I have in each year, without having to type in each year.
So basically I want the query to detect that I have a 3 pence, 2 pence and shilling all dated 1887 and crown and half crown dated 1945 etc.
This is not the normal calculated fields in query's. What I want is different, I want it to be like
IFF(ShowID="A",[TotalSales],0)
Thing I have is, I want to know all the Total sales in on row that has Show A in it and then Total sales for Show B. Problem is, I have over 130 shows. How would do that?