Modules & VBA :: Update SQL With Multiple Where Clause

Sep 29, 2014

I have an update sql statement that isn't quite working properly.

My where clause has 3 criteria.
*print = -1
*stDocCriteria (project_num and client_id)

Below is what the code looks like.

Code:
updateSQL = "update tblTimedTasks set printed =-1, invoice_date = Now() where print = -1 and " & stDocCriteria

Add watch: so you guys can see "stDocCriteria"

Code:
updateSQL = "update tblTimedTasks set printed =-1, invoice_date = Now() where print =-1 and ([project_num]= '140012' And [client_id] = 87)"

I want to only updates records that meet all three criteria. The above sql not only updates all records that satisfy stDocCriteria regardless if print is -1(true) or 0(false).

View Replies


ADVERTISEMENT

Queries :: SQL Multiple Where Clause AND / OR

Feb 6, 2015

I have a form with 2 cbo. I want query to show all data if nothing has been selected, or show specific data to whatever has been selected in cbo1 AND/OR cbo2.My query was working ok for couple of days and then stopped. I don't know why, as no changes has been made.

if both cbo are blank then it works OR if both cbo have a selected value then it works, ok. But it doesn't if one of the cbo has a value and the other one hasn't.

Code:
WHERE (dAreaFK=Forms!F_MainMenu.cboStatsArea
AND dShiftFK=Forms!F_MainMenu.cboStatsShift)
OR (Forms!F_MainMenu!cboStatsArea IS NULL
AND Forms!F_MainMenu!cboStatsShift IS NULL)

View 7 Replies View Related

Open Reports With Multiple Where Clause

Apr 19, 2007

Hi again! :)

I can't figure this out myself.....so if anyone likes to help, I would be really glad!

Ok, here is the problem:

I got a form with 7 listboxes and a Command Button.

Box1 chooses the report
box2 the clientname
box3 to 7 choose different variables depending on the choosen report.

So i want to do something like
DoCmd.OpenReport "rpDetails", acPreview, , "[clientname] " & strclientnames & "" And Box3-7

I already have done this for every box:

If IsNull(Me.cboGAclientnames.Value) Then
strclientnames = "Like '*'"
Else
strclientnames = "='" & Me.cboGAclientnames.Value & "'"
End If

I tried to build the OpenReport String with one more box, but i get a mismatch error and don't know why :(

View 7 Replies View Related

Modules & VBA :: Update Multiple Records With Value From Inputbox?

Sep 11, 2014

Instead of using an update query, so my Audit Trail will continue to update correctly, I'd like to use an input box and run a function that performs much like an update query.

My problem is, only one out of 4 records updates correctly.

Code:
Me.txtTranTo = InputBox("Enter Location Transferring To:", "Location Transferring To", "")
Me.txtTranFrom.Value = Me.Location 'Old location
Me.Location.Value = Me.txtTranTo 'New location

The txtTranTo and txtTranFrom are unbound text boxes.

EDIT: Forgot to mention that this is in a continuous form.

View 1 Replies View Related

Modules & VBA :: Update Access Database (multiple Rows)

Jan 7, 2014

I have a table called "Mov" and its columns are:

Code:
Number | Link | Name | Status
1899 | htto://example.com/code1 | code1 | Done
2 | htto://example.com/code23455 | code23455 | Done
3 | htto://example.com/code2343 | code2343 | Done
13500 | htto://example.com/code234cv | code234cv | Deleted
220 | htto://example.com/code234cv | code234cv | Null
400 | htto://example.com/code234cv | code234cv | Null

So I want a way to update Status of my rows according to numbers list. For example I want to update Status column for multiple numbers to become Done .

Simply I want to update "Null status" to become "Done" according to its numbers according to this list

Code:
1234
53
546
767
2135
6657
43
34

Something like this

I tried "update query" but I don't know how to use criteria to solve this problem. In Excel I did that by "conditional formatting duplicates" -with my number list which I wanted to update - Then "sort by highlighted color" then "fill copy" the status with the value...

View 4 Replies View Related

Modules & VBA :: Add WHERE Clause To Query (queryDef)

Dec 1, 2014

I have a TRANSFORMED query:

Code:
TRANSFORM nz(count(T_qa.qaQAPK),0) AS SumOfQAs
SELECT month(qaDate) AS QAmonth, Count(T_qa.qaQAPK) AS QAs
FROM Q_ALL_qa
GROUP BY Month(qaDate)
PIVOT month([qaDate])
IN (1,2,3,4,5,6,7,8,9,10,11,12);

This query is record source for a report, then this report show all calculations in a form.

I have 7 of each (query + report) all showing on the same form.

All those queries calculate data for all departments.

In the form, I have placed a combobox.

What I want is to create a vba code which will add clause WHERE to all queries at the same time and then run it.

Code:
WHERE qaDeptFK=Forms!F_CompLvl.cboDeptStats

However, if nothing has been selected in the combo, I want the queries to calculate data as normal, for all departments.

Where do I place the vba statement? Is it under combobox AfterUpdate event?

I am planning to use this: (As I never done it before)
[URL] ....
Modifying SQL On-The-Fly section

View 5 Replies View Related

Modules & VBA :: SQL Statement To Take A Value From Combo Box In WHERE LIKE Clause

Jun 6, 2013

I'm trying to get an SQL statement to take a value from a combo box in a WHERE LIKE clause.

For example:

INSERT INTO tblInspectionTempp (BuildingID, DoorNumber) SELECT tblDoorData.BuildingID, tblDoorData.DoorNumber FROM tblDoorData WHERE tblDoorData.BuildingID LIKE = '[Forms]![fmInspectionColumns]![cmboBuildingID].Value'"

The errors I'm receiving are either Missing Match or incorrect Syntax, depending on my trial and errors methods regarding the WHERE clause.

View 2 Replies View Related

Modules & VBA :: Using Where Clause To Max Of A Table Field

Sep 18, 2014

I am trying to open a form using the where clause to max of a table field.I cannot get the syntax correct.

Code:

DoCmd.OpenForm "frmInput", , , Max(tblClients.ClientID) AS MaxOfClientID FROM tblClients

View 4 Replies View Related

Modules & VBA :: Manage IN Clause Using Parameters

Dec 10, 2014

I am trying to dynamically change the IN sql from within VBA using parameters. for some reason i have no luck, no errors shows up, but it's actually not picking up the criteria.

Code:
THE SQL IN STATEMENT

In (select RemID from [ReminderAssignees] Where RemDate between [Date1] and [Date2] And [sDismiss] )));

Code:
THE VBA CODE

qdf.Parameters("date1") = Date1
qdf.Parameters("date2") = Date2
Select Case iDismissed
Case 0, 1
qdf.Parameters("sDismiss") = "1=1"
Case 2
qdf.Parameters("sDismiss") = "(not isdate(Dismiss) or Dismiss > #" & Now & "#)"
Case 3
qdf.Parameters("sDismiss") = " isdate(Dismiss) and Dismiss < #" & Now & "#"
End Select

qdf.Execute

View 6 Replies View Related

Modules & VBA :: Open Form Where Clause With Date

Jul 21, 2015

I would like to open a form to a specific date that is taken from another form. Date field format is short date and looks like yyyy.mm.dd

I have figured it out that it only works if date format is yyyy/mm/dd

I know format function can do this, but i do not know how to write the code with correct syntax.

For example i have tried:
DoCmd.OpenForm "TreatmentsTB1", , , "DOV= #"& Format(2015.01.11,"yyyy/mm/dd")&"#"
it does not work.

View 3 Replies View Related

Modules & VBA :: OpenForm Command Where Clause With 2 Criteria

Oct 31, 2013

I am having trouble opening Form 2 to the same record as the record in Form 1. Form 1 is a continuous form of questions. When certain response is given, I want to be able to add more information to the "additionalcomments" column for that record. I want to have the additional comments pop up in a new form.

Form 1 is based off of a query with no unique ID. I need the second form to open on Eval_Number and Question_Number. I have tried...

Code:
If Me.Response = 0 Then
DoCmd.OpenForm "frmadditionalcomments", acNormal, , "[Eval_Number] =" & Forms!ESVWL1Trainee!subfrmreponses.Eval_Number And [Question_Number] = " & Forms!ESVWL1Trainee!subfrmreponses.Question_Number "
End If

and this tells me the object doesn't support the method. Is the SQL incorrect? Is it the way its setup?

View 5 Replies View Related

Modules & VBA :: Between Dates In Where Clause - Query Using Parameters

May 30, 2014

I have this code below which pulls a report based on the current date, I wanted to be able to pull the same report by entering between 2 dates as is done in a query using parameters.

Code:
reworkWhere = "ReworkTech = '" & Me.txt_tech_by_date_techid & "' And ReworkTimeOut = Date()"
repairWhere = "RepairTech = '" & Me.txt_tech_by_date_techid & "' And RepairTimeOut = Date()"
qcWhere = "QC_Tech = '" & Me.txt_tech_by_date_techid & "' And QC_TimeOut = Date()"
strWhere = reworkWhere & " Or " & repairWhere & " Or " & qcWhere
DoCmd.OpenReport "RPT_RF_TECH_REPORT_UNIT_DAILY", acViewReport, , strWhere

How to make a combo box with the months listed so they can pull this report by the month selected but is a side tangent.

View 3 Replies View Related

Modules & VBA :: InStr - Use Clause In Opening A Datasheet Form

Jun 16, 2015

I want to that the WHERE clause for a SQL statement that I am using options on a form to build. I intend to use the clause in opening a datasheet form.

This is the code I have for getting the substring

Code:

Dim intPos As Integer
Dim tempString As String
Dim BaseQueryFormStr As String
'BaseQueryFormStr is used to reopen the BaseMasterQueryFrm with the specified parameters
tempString = "WHERE"

intPos = InStr(1, strSQL, tempString, vbTextCompare)
BaseQueryFormStr = Left(strSQL, intPos - 1)
MsgBox (BaseQueryFormStr)

The value of intPos remains=0 and when the program hits the second to last line I get "run-time error 5"

View 10 Replies View Related

Modules & VBA :: Using A Custom Aggregate Function With GROUP BY Clause

Dec 20, 2013

I am trying to calculate annual percentiles of a large set of data and I have only been successful at retrieving the percentile of the entire data set (and not by the grouping). See provided example database for code/query. Query1 is what I want to happen to make the Percentiles table.

View 14 Replies View Related

Modules & VBA :: Invalid Column Error In SQL Server ODBC Where Clause

Jun 26, 2014

Connecting Access FE to SQL SERVER BE Connection is fine. I can open and close it and other queries work fine. I have only one problem with the SQL in one query

Code:
With rstRPT
If FirstRecord = True Then
.Open "SELECT Min([" & SourceTbl & "].[3Order ID]) AS MinOf3OrderID " & _

[Code].....

why it wants to see the variable as a column name?

View 1 Replies View Related

Modules & VBA :: SQL Where Clause - Open Form To Correct Work Order?

Aug 25, 2014

Windows 7
Access 2013

I've been trying to work up a where clause that is generated by a button click event on a report. The workflow that i'm trying to obtain is as follows:

1) A report is run to determine the remaining work orders that need to be processed.
2) A button that is placed on that report is to be clicked, taking the user to the form associated with that work order, so it can be processed.

What i've been able to do so far is capture the unique ID for the work order and then print that in a message box. I can then open the form.

What i haven't been able to accomplish thus far is to open the form to the correct work order.

Things I've tried : I started trying to use the macro with the search for record option and using the where clause. Not successful. I am a little more comfortable in using vba so i switched to that pretty quickly.

Code:
Private Sub btnJobEntry_Click()
'GOAL: open the work order form to the correct entry
'METHOD: store the uniqueID to a variable, then use that in the open command's where clause
Dim strJobID As String
'store the unique ID in the variable

[Code] ....

I've put the strJobID variable in both the filter and where clause sections of the DoCmd but it just opens the form to the first entry. I'm fairly confident i'm not applying the filter/where clause correctly by using the incorrect syntax.

View 3 Replies View Related

Forms :: Inventory - Allow Users To Update Multiple Fields For Multiple Assets

Sep 25, 2014

I want to create a form that allows users to update multiple fields for multiple assets. Below is what I came up with:

Ideally, I'd like the subform to be filled in by having the user select multiple Assets from the S/N combobox field which would then auto-populate the "Type" field. Then they would fill out the appropriate fields they want edited in the top part of the form. They hit save and magic happens. This would also be nice because only assets they want edited would be displayed (easier on the eyes) and no distinguishing would be necessary. To do it this way, I know I would need to use a temp table but I wanna avoid using temp tables.

I know I can do this by adding a Yes/No field in the "Asset" table, setting the "Asset" table as the subform's recordsource, and then putting a checkbox in the subform and allowing them to check the assets that they want to edit (which would also allow me to sort it instantly so that checked Assets are at the top of the datasheet for easy viewing), but I would like to know if there's a way of accomplishing this without the use of checkboxes.

I know I could also use a listbox and that allows them to multi-select items, but I'm not sure if that allows me to group all selected items at the top of the listbox for easy viewing of selected items. Plus it would involve a lot of scrolling (there are over 2k assets).

View 2 Replies View Related

Modules & VBA :: Inserting Multiple Records From Multiple Unbound Text Boxes

May 6, 2014

I have a form with 15 unbound text boxes (daily temperatures) and what I am trying to do after entering the temperatures into the text boxes the user clicks an add button which will add 15 new records into the temperature table

the code I have started off with is

Code:

CurrentDb.Execute "INSERT INTO ColdTemperatures (ProductID, ColdTempDate, Temperature) VALUES (" & Lettuce & ", #" & Me.RealTime & "#, " & Me.Lettuce & ")"

which adds 1 successfully however if i repeat the code above for all 15 this Im assumming will create a potential bottleneck and slow the system down

is it possible to add all 15 records at once? do you think Im going at this the right way

View 5 Replies View Related

Modules & VBA :: Import Multiple Files To Multiple Tables On Button Click

Sep 20, 2014

I made a database that in one of the forms, I like by clicking on a button the user be able to select 5 excel files with different file names (in the same directory) and then based on the imported file's names, it be stored in 5 different tables.

At the moment by using the bellow code, I can import multiple files (with the same formats) only into one table . My vba code comes as follow:

Function GetAllFiles()
Dim fd As Object
Dim strFilter As String
Dim lngItems As Long

Const msoFileDialogOpen As Long = 3
Const msoFileDialogViewDetails As Long = 2

[Code] ....

View 4 Replies View Related

Modules & VBA :: Import Multiple Fix Width Text Files - Eliminate Multiple Headers And Footers

Aug 1, 2013

I am trying to build a newer database 2010, based on an older one,2000, that has been locked tight and I cannot see the modules to kinda get a reference of where to start. I am trying to find a VBA code that will allow me to import a several text files to one table. The text files are all in the same format but I cannot remove the page headers and footers to get the table to look right. I have attached an example of the text file i am trying to import but it is a stripped down version for information protection.

Also, it appears in the old Database Table once imported as:

J.Smith 1234 01 ABCD ABCD HGJV 2345 ABCDE ABC6 Qual Date Date
J.Smith 1234 01 ABCD ABCD HGJV 2345 ABCDE ABC6 Qual Date Date
J.Smith 1234 01 ABCD ABCD HGJV 2345 ABCDE ABC6 Qual Date Date
J.Adam 1234 01 ABCD ABCD HGJV 2345 ABCDE ABC6 Qual Date Date
J.Adam 1234 01 ABCD ABCD HGJV 2345 ABCDE ABC6 Qual Date Date
J.Adam 1234 01 ABCD ABCD HGJV 2345 ABCDE ABC6 Qual Date Date

If I could import the text files and end up with a table like this, it would be all i need as i could run all the queries i need from this.

View 3 Replies View Related

Modules & VBA :: SQL - Select Multiple Fields From Multiple Unrelated Tables

Oct 28, 2013

A small issue I was wondering of for a few day . Is it possible in SQL query to SELECT multiple fields from multiple tables ? Example for the question is

Code:

dim my_var as String
my_var = "SELECT Emp_FName , Emp_LName , Emp_Adress " _
& " FROM Table1 " _
& " AND Emp_Date_Of_Payment , Emp_Sum_Of_Payment " _
& "FROM Table2 " _
& " WHERE Emp_ID = 3 "

Is this code actually valid in SQL gramatics , and is it usable if passed to a Recordset variable ( rs = CurrentDB.OpenRecordset(my_var) ) ? Just FYI - The two tables are not related and I want to keep them that way (If possible relate their records just via SQL/Vba )

View 7 Replies View Related

Multiple Update Using Dao

Jul 5, 2005

ok, here is the code i've got...

Code:Dim DAOdb As Database Dim DAOrs As DAO.Recordset Dim sSQL As String Set DAOdb = CurrentDb() 'select the record corresponding with the value in the hidden textbox sSQL = "Select * from table where field =" & "'" & Me.textbox.Value & "'" & ";" Set DAOrs = DAOdb.OpenRecordset(sSQL) With DAOrs .Edit 'update this field in the table .Fields("Status") = "Complete" .Update End With DAOrs.Close DAOdb.Close
i want to be able to update all of the fields that match the textbox. as it is now, it only updates one. how can i loop through all of the records and update all of the ones that meet the criteria??

thanks for your help. It's greatly appreciated!

*j

View 2 Replies View Related

Multiple Update To Same Field

Sep 6, 2006

Hi, I have created a field called age, where I have a formula that takes the date of birth out of the IDENTITY NUMBER and calculated the peoples age, I have then created a field where I would like to allocate groups depending on their age, i.e, 18-25 = Group1, 26-35 = Group 2,etc... I am having trouble with this, firstly, I cannot get the formulas to work individually and secondly, I get an error that says I have duplicate fields to update.

PLEASE HELP!!!!

View 2 Replies View Related

Update Multiple Fields At Once

Sep 6, 2007

Hey guys-
I have 2 identical tables. I want to update the data from Table1 to go into Table2. Each table has well over 70+ fields in them. Instead of handwriting out each [Table].[Field] in either SQL or the Designer- is there a shortcut to tell Access to grab all the fields from Table1 and update all the fields in Table2 (all the fields have the same name)? I just don't have the energy to type it all out- I figure there's got to be a way...

I know when you do an APPEND query in the designer- it will do this for you- but not the UPDATE query...
Thanks!

View 6 Replies View Related

How Can Update Multiple Records??

Sep 13, 2005

Hello,
I would like to update multiple records in one query.
The scenerio is that i have 3x3 (3 in row and 3 in column) text feilds and i want to update it once, in one query after entering the data in text fileds.
Can anybody help me please......????

Waiting of your quick reply.

View 3 Replies View Related

Update Multiple Product IDs

Feb 21, 2012

I've got an order detail form which has

product id, product description, customer, quantity and below the form a Total quantity

When I enter the same product id, and when i press the button to update, the table of stock quantity deducts from total quantity

If I use different product ids, it doesn't work, but updates only the current product id, where the mouse current record is set by mouse.

By the way i use dlookup to check if the stockquantity has enough items.

View 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved