Ask - Update Query With Criteria Numeric
Apr 15, 2008
hi all,
I'm newbie here. please forgive me if I make mistake / wrong "room"
here what I need. I want to make update query in access 2000/2003. let say I want to update field "COST" to "0", which have condition field "ID" is 070.... (070. and something). the matter is type of field ID is numeric not string, so I can't use LIKE function. any suggest what I can do without change data type for field "ID" for permanent (I mean, if we can change data type for temporary with code, and turn it back with code too, that's no problem). I don't care, either use macro or update query (if update query can't do this task).
thanks before
View Replies
ADVERTISEMENT
Nov 29, 2006
Ok, i have a question about update queries.I have two tables (I'll call table 1 and table two for simplicity) and an update query. I want to get some data from table one to table two (via an update query). But in table two there is a field that isn't in table one but i want to add a value to that field via the query.My question is, can i manually put into the query what data to add to a field instead of/aswell as using data from other tables.I hope you understood my questions.Cheers
View 3 Replies
View Related
May 10, 2015
I have this payroll system:JJAEPAYROLL.accdb
In the "qryEmployeeWTax" i need to Lookup the [InitialTax] from the "qryreftbl_WTAX" based from the [ETPES] and [SalaryAfterStatutoryDeductions], so i am using this function:
Code:
InitialTax: DLookUp("[InitialTax]","qryreftbl_WTAX","[ETPES]=" & [ETPES] & "AND [ValueRangeStart]<=" & [SalaryAfterStatutoryDeductions] & "AND [ValueRangeEnd]<" & [SalaryAfterStatutoryDeductions])
But it doesn't work
I am able to use DLookup successfully using 2 criteria s in my "qryEmployeesStatutoryInfo"
View 14 Replies
View Related
Feb 25, 2015
I have a small problem with dlookup multiple criteria. Vba code looks like this:
Code:
Label34.Caption = DLookup("[Spent_Hours]", "249_1_CHours", "[Date_Added]= " & Me.Text27 & " And [Shift] = '" & Me.Text29 & "'")
This gives following error:
Syntax error in number in query expression '[Date_Added]=4.02.2015 And [Shift] = '2'.
[Shift] column is a numeric field.
View 14 Replies
View Related
Jan 28, 2008
All -
I have an update query built that checks to see if a date is entered for when a document is mailed as well as a date for when that document was received.
I'm trying to setup this query to check and ensure that if multiple documents (Form8) were mailed that each one was marked as having a received date before the Update query add's a "Graduated" mark to the student. Screenshots are attached.
Anyone have any idea on how I can set this up?
Basically the tblFormEight can have multiple entries assocaited with each EnrollmentID (aka Student enrolled in a class) which represents multiple Form's set out in the mail. I don't want this Update query to run unless all entries for each EnrollmentID have Received Dates.
Thanks!
View 11 Replies
View Related
Mar 31, 2006
What I want to do is from several records find the Max() date and update that one record with my stuff. However, I get an error message cannot use aggregate in update query or something to do that effect. Is there a way to get around this?
View 1 Replies
View Related
Mar 2, 2007
Hello, i was wondering if you could help.
I want to run an update query, taht updates prices in a table. But i would like to be able to choose the criteria when the query runs, for example a message box appears, that will allow me to enter a certain amount for the prices to change by. Could someone guide me on how to do this? thankyou, its much appreciated
View 3 Replies
View Related
Jan 12, 2007
Hello,
Was wondering if there is a way without building individual update queries, to update info in one field that has multiple criteria ?
Basically I need to change/update daily multiple ID numbers to new ID numbers, long story on why this needs to be done but for now I need to do it this way.
Example: 12345 update to ABCDE, 6789 update to FGHI, etc. These ID's are all within the same field in the table.
It works fine running each ID one at a time but was wondering if it is possible to do all these updates within one query or code ?
Thanks for any assistance
View 6 Replies
View Related
Mar 20, 2015
I am developing a Task database.
Table: TaskT
Fields include; Description, Action, DueDate, Priority, Precinct, Recurring and Employee.
Query: TaskQ
Form: TaskF and TasklistF
The forms are fed by the TaskQ query.I have sort and filter on all fields except date field which i would like to have 3 buttons which will dynamically update the criteria in the TaskQ DueDate field. Is that possible or the way to do it??
The buttons would include 'Due today' 'Overdue' 'Next 7 days' and they update the criteria in the TaskQ query and hence filter with the following;
Date()
<Date()
Between Date () And Date()+6
View 7 Replies
View Related
Jan 10, 2013
I have a form that run a query to select all the students taught by a given faculty member. Once the records are loaded, the faculty enters attendance data, selects a date from a combo box and hits a save button.I change my relationship and now need to be able to store the key for the field in the combobox and not the text.
How do I set the field, table and criteria in the update query to do this. The UD query uses the FacultyStudents query as its record source. Currently the FacultyStudents querry does not have the Key or the date fields since the date is selected by the faculty once their records are loaded. As I see it there is now way to make a join, so I think I either need VBA or SQL, which I am not very good (really bad) at wriitng. Can the necessary Sql be written in the query design view?
View 4 Replies
View Related
Apr 21, 2013
I need to update the periodtype field in my table depending on different values in the field Formtype- I am looking to do this without having to use VBA. I have the following fields in a table - I want to update the value of the field Period_type as follows -
When Formtype is 10-Q, update Periodtype to "Quarterly"
When Formtype is 10-K, update Periodtype to "Annual"
The current value of Periodtype for both formtypes is "Semiannual"
Can I do this using only update query?
View 2 Replies
View Related
Jul 17, 2013
I'm trying to update a record in a table, from a query that is run as part of an event from a command button on a form.
I have a table called 'Assets', a table called 'Disposals', and a form called 'Disposal Entry'. I would like the user to select an Asset ID from a combobox on the form, then when the button is clicked it adds a record to the 'Disposals' table, and updates the Status for that specific Asset in the 'Assets' table to "Disposed".
It adds to the Disposal table fine, but I can't get it to update the Asset table.
My query looks like:
Field: Status Asset ID
Table: Assets Assets
Update To: "Disposed"
Criteria: [Forms]![Disposals Entry]![Asset ID]
I've checked the spelling and everything looks ok.
The [Asset ID] control on the form is bound to the Assets table. If I edit the control and clear out what is in Control Source, then it updates the table and works fine.
However, I want to keep it bound as I have a subform on my home page showing the latest disposals.
How I can get the query to use the Asset ID on the form as the Criteria?
View 14 Replies
View Related
Oct 18, 2013
I have 2 tables that must be involved in this query. Both have the same fields; one is a temporary table created from an Excel import which will be deleted later, the other is the permanent table.
tblStoreProducts is the permanent table. It has a 2-field Unique index; there can only be 1 of any ProductKey for a StoreKey (there can be multiple ProductKeys within a StoreKey, and multiple StoreKeys for each product, but there can only be 1 entry for each unique combination of ProductKey/StoreKey).
tblImportToStoreProducts is the temporary table. It holds all of the ProductKeys for one StoreKey.
My query needs to identify any ProductKey in the tblStoreProducts that does not exist in the tblImportToStoreProducts for that StoreKey, and then it must set the MaxUnits field to 0.
Basically, the temporary table is this years' inventory for a particular store. If a product was in that store last year but it isn't there this year, the quantity must be set to 0 because the ultimate aim is reporting hazardous materials.
View 1 Replies
View Related
Jul 25, 2013
I have an update query for tGLCashAccount where it adds a value from another table with the BeginningBalance to arrive at CurrentBalance.
Here's what it looks like in design view:
Field: CurrentBalance
Table: tGLCashAcct
Update to: [tMakeNewCashBal].[TotalPrice]+[tGLCashAcct].[BeginningBalance]
Here is SQL code:
UPDATE tGLCashAcct, tMakeNewCashBal SET tGLCashAcct.CurrentBalance = [tMakeNewCashBal].[TotalPrice]+[tGLCashAcct].[BeginningBalance]
WHERE (((tGLCashAcct.GLCashAcctID)="102"));
I get the error: data type mismatch in criteria expression when I run it.
View 3 Replies
View Related
Mar 11, 2014
I have a text field having data i-e HO-1, HO,2, ACW-25 and so on. The field name is nBadge_num and is Unique. The data in this field is sorted automatically like 1, 10, 11, 12, 13, 2, 3, 4, 5...because this is the text field.
The number on the form is automatically generated, when the user type HO- for example on field exit event. The last number will generate like HO-5.
Code for automatic number generation is:
Dim dbs As Database, rst As Recordset, Response
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT Max(Right([nBadge_Num],Len([nBadge_Num])-" & Len(Me.NBadge_Num) & ")) AS MaxNo " _
[Code]....
My problem is when the number is generated it give HO-5 instead of HO-14, How can I sort the numeric part of the field ?
View 2 Replies
View Related
Mar 3, 2008
I am adding fields with numeric values in design view of my query. The only issue I am having is that the query is not showing totals when one of the fields does not have a numeric value in it. How can I show the total numeric value regardless of the null value? Thank you:cool:
View 1 Replies
View Related
Apr 26, 2007
Hello,
I am not sure what cateogory to choose for this question but I would greatly appreciate your help.
I have 5 buttons in my option group:
1. very often
2. often
3. occasionally
4. rarely
5. never
I have to produce a report using description of each button in my report, not numeric value. I heard something about global variable but I am not sure how to do it.
Many thanks,
Debbie
View 1 Replies
View Related
Jan 25, 2008
Hi all,
I have looked everywhere for a solution to this and found none. Its an annoying issue which I would like to get around.
Here is what happens.
I have a union query that gathers rows with different criteria together. The query works execept that the yes/no feild which is set to display as "Yes" or "No" is displayes as 0 for No and -1 for Yes.
I understand that the numeric values are the underlying values of yes/no feilds in access, however they are displayed as yes/no in all my queries and reports execept my union query.
I will include the union query here in case.
The following code is a function that I use to build the union query on the fly based on some parameters. If you are asking why I am doing it like this is becasue I need the rows that have a value for the feild schFeilds(0) to be on top of the final results followed by the rows that don't have any values for the above mentioned feild, and I want them sorted. I found this method to be the only one that gives me the result in the order I need.
' ----------------------------------------------------------
Public Function makeMainPanelQry()
On Error GoTo Err_makeMainPanelQry
Dim db As Database
Dim qd_window As QueryDef
Dim qd_nowindow As QueryDef
Dim strSQL As String, sql_window As String, sql_nowindow As String
Set db = CurrentDb
Set qd_window = db.QueryDefs("Q1")
Set qd_nowindow = db.QueryDefs("Q2")
strStatus = cmbStatus.Value
strReboot = cmbARStatus.Value
strFilter = makeHostFilter(strStatus, strReboot)
schFeilds = Split(getRSchechuleFeild(), ",")
sql_window = "SELECT " & pbl_ReleaseTable & ".Hostname, " & pbl_ReleaseTable & ".Status, " & pbl_ReleaseTable & ".Excluded, " & _
pbl_ReleaseTable & ".AutoReboot as Auto, " & HEAT_PROFILE_NODECOM & "." & schFeilds(0) & ", " & _
HEAT_PROFILE_NODECOM & "." & schFeilds(1) & _
", Technician.FirstName as Assigned, 1 as Priority FROM (" & pbl_ReleaseTable & " LEFT JOIN " & HEAT_PROFILE_NODECOM & " ON " & _
pbl_ReleaseTable & ".Hostname = " & HEAT_PROFILE_NODECOM & ".DeviceName) " & _
"LEFT JOIN Technician ON " & pbl_ReleaseTable & ".TechID = Technician.TechID " & _
"WHERE ((" & pbl_ReleaseTable & ".Excluded) = False) " & _
strFilter & "and " & HEAT_PROFILE_NODECOM & "." & schFeilds(0) & " Not Like """" "
'" ORDER BY " & HEAT_PROFILE_NODECOM & "." & schFeilds(0) & ", " & pbl_ReleaseTable & ".Status"
qd_window.sql = sql_window
sql_nowindow = "SELECT " & pbl_ReleaseTable & ".Hostname, " & pbl_ReleaseTable & ".Status, " & pbl_ReleaseTable & ".Excluded, " & _
pbl_ReleaseTable & ".AutoReboot as Auto, " & HEAT_PROFILE_NODECOM & "." & schFeilds(0) & ", " & _
HEAT_PROFILE_NODECOM & "." & schFeilds(1) & _
", Technician.FirstName as Assigned, 2 as Priority FROM (" & pbl_ReleaseTable & " LEFT JOIN " & HEAT_PROFILE_NODECOM & " ON " & _
pbl_ReleaseTable & ".Hostname = " & HEAT_PROFILE_NODECOM & ".DeviceName) " & _
"LEFT JOIN Technician ON " & pbl_ReleaseTable & ".TechID = Technician.TechID " & _
"WHERE ((" & pbl_ReleaseTable & ".Excluded) = False)" & _
strFilter & "and " & HEAT_PROFILE_NODECOM & "." & schFeilds(0) & " Like """" or " & _
HEAT_PROFILE_NODECOM & "." & schFeilds(0) & " is NULL"
'" ORDER BY Priority, " & HEAT_PROFILE_NODECOM & "." & schFeilds(0) & _
'", " & HEAT_PROFILE_NODECOM & ".Status;"
qd_nowindow.sql = sql_nowindow
strSQL = "SELECT * FROM Q1 " & _
"UNION ALL SELECT * FROM Q2 " & _
"order by Priority, " & schFeilds(0)
Set qd_window = Nothing
Set qd_nowindow = Nothing
Set db = Nothing
makeMainPanelQry = strSQL
Exit_makeMainPanelQry:
Exit Function
Err_makeMainPanelQry:
MsgBox Err.Description
Resume Exit_makeMainPanelQry
End Function
' ----------------------------------------------------------
I woudl really appreciate any assitance as its my last hope for a solution.
Thanks and regards
Sep
View 2 Replies
View Related
Feb 25, 2013
I am writing a sports database and have a query that displays a seasons fixture list in a form. One of the fields shows the points from each game played. (either 3 for a win, 1 for a draw etc). I can not get these points to be added up and displayed in a form along side the fixture list.
View 9 Replies
View Related
Dec 30, 2013
I currently have records that end with a letter and 2 numbers. For example, A1, A2, ... , A10, A11. When I try to sort my table/query by these values, A10 & A11 come before A2. It seems that it is sorting by the first digit shown. Is there any way to fix this quickly within table/query properties so that this can be displayed in proper numeric order?
View 7 Replies
View Related
Aug 18, 2014
I have a table that has several fields including CallID (autonumber) and SKU (text)
SKU can be anything up to 9 characters, sometimes numeric sometimes alphanumeric. For example: 24300, AA23145, G58d444, 24999, 89332,...
Based on the Count of CallID I can easily get the top20 calls on each SKU. This is the query I use for that:
Code:
SELECT TOP 20 Count(Calls.CallID) AS CountOfCallID, Calls.SKU
FROM Calls
GROUP BY Calls.SKU
HAVING ((Not (Calls.SKU) Is Null))
ORDER BY Count(Calls.CallID) DESC;
The problem is that now I have been asked to create two different lists. One that has the top 20 SKU that range from 24520 and 24599 and another one that does the res tof the SKUs.
Obviously my problem is that the SKU field is text, not numbers so I can't just limit the results in the query by using "Between 24520 and 24500" in the query criteria.
View 7 Replies
View Related
Jun 13, 2014
I am creating a table, using an INTO statement.
I require a blank numeric field, which users will be updating via a form.
How do I make the field numeric, as my current script makes it a text field
'' as Ticket_No
And you can't cast/convert text to numeric ....
View 2 Replies
View Related
May 25, 2014
Have a Make table query that needs to create (add) several new fields where each field must be numeric design.
Have tried:
Score1: Not Null - does not seem to work (results in a Binary field)
Score1: 0 - which does give me the numeric field designation but every field in table contains a 0.
Would like to show Blank field (makes data input easier at a later time) but still have the Numeric designation.
View 6 Replies
View Related
Jun 13, 2014
I am creating a table, using an INTO statement.
I require a blank numeric field, which users will be updating via a form.
How do I make the field numeric, as my current script makes it a text field
'' as Ticket_No
And you can't cast/convert text to numeric ...
View 1 Replies
View Related
Jun 22, 2005
Hi all,
I've tried searching the forum, but no luck.
I have a Field say Field1, with data like this:
AB, AC
AB
AC
AB, AD
AA, AB
Where the field contains "AB", I want to replace the data with say "AB - Alpha Beta". But I do not want to change any of the other data. So the outcome should look like:
AB - Alpha Beta, AC
AB - Alpha Beta
AC
AB - Alpha Beta, AD
AA, AB - Alpha Beta
I'd then go on to change AC to say "AC - Alpha Charlie".
All help appreciated,
Jempie
View 2 Replies
View Related
Jan 28, 2008
All -
I have another update query that is basically looking at all of the Enrollment ID's that are associated with a ClassID. If each EnrollmendID has a Graduation date and the Graduated check = yes, then I want to place the current date in the Closed Date field for the Class.
I can get a select Query that shows all of the classes and all of the EnrollmentID's but I'm not sure how to tell the query, "For Each ClassID, If All EnrollmentID's have a Graduated Date and Graduated = Yes, Then Class Closed Date = Date()"
View 1 Replies
View Related
