After a hiatus of a few years I find myself back writing Access applications, so I need to get caught up a bit.
First of all help me out with the whole ADO, DAO RunSQL thing. In the past whenever I needed to do any database operations I almost always used straight SQL with DoCmd.RunSQL, e.g.:
lsSQL = "INSERT INTO tblUsers CenterID, WorkerID..." DoCmd.RunSQL (lsSQL)
Most other coders seem to use some recordset approach, e.g:
The only time I ever used recordsets was when I needed to loop through each record and apply some logic that was too convoluted for SQL or at least too convoluted for me to write in SQL.
So, what's the advantage of using recordsets - whether ADO or DAO - over RunSQL?
I have a table with one entry- just a date that holds the last day of the month for a function that reminds the user to do something. I have some code that's supposed to change this entry via an update query- but it's not working- literally, no errors, just nothing. I tried running it in an actual query- but still nothing. The query should update the table endCurrMonth to the last day of the month. I've tried hardcoding various dates- still nothing DoCmd.RunSQL ("UPDATE tableMetrics SET tableMetrics.endCurrMonth = DateSerial(Year(Date()), Month(Date()) + 1, 0)") anyone know what I'm doing wrong? thanx
i have two tables table1,table2 each with one same field Country.
In my RunSQl query, i want to delete all records in table1 and table2 simultaneously from one button with a certain country. The problem is how can i do this from may be one RunSQL statement i tried to use two deletion runSQl commands, but only one is executed. Actually the one that comes second. If i put doevents in the middle of these statements, only the first is executed.
Here is my code:
Private Sub RemoveCtry_Click() docmd.setwarnings false Docmd.RunSQL "DELETE * FROM table1 WHERE (Country='" & USA & "')" Doevents Docmd.RunSQL "DELETE * FROM table2 WHERE (Country='" & USA & "')" docmd.setwarnings true End Sub
if I use One Statement:
Docmd.RunSQL "DELETE table1.Country,table2.Country FROM table1,table2 WHERE ((table1.Country='" & USA & "' ) AND (table2.Country=' " & USA & " '))" I get an error that i have to specify the table to delete from!!!!
im creating a small database and am extremely new to vba, ive got stuck on running a select query from within the vba code itself, the book im currently reading while learning about this stuff suggests my code whould work but after looking into it i believe its wrong as ive read various threads saying you cannot use docmd.runsql with a select query, just wondering if anyone could help and throw some light on how to get this little bit of code working. below is the part of my code that falls over,
basicaly it should lookup the weight based on what the parcel type is and find the price, the parcel type is worked out earlier in my code and is held in strParcelType, theirs probably an easier way to do this as well but have'nt got that far in my book :)
intWeight = Me.txtWeight
Select Case intWeight Case 0 To 100 intCost = DoCmd.RunSQL "SELECT pricetable1.[Postage Name], pricetable1.[0-100g] FROM pricetable1 WHERE (((pricetable1.[Postage Name]) = strParcelType))" Case 101 To 250 intCost = DoCmd.RunSQL "SELECT pricetable1.[Postage Name], pricetable1.[101-250g] FROM pricetable1 WHERE (((pricetable1.[Postage Name]) = strParcelType))" Case 251 To 500 intCost = DoCmd.RunSQL "SELECT pricetable1.[Postage Name], pricetable1.[251-500g] FROM pricetable1 WHERE (((pricetable1.[Postage Name]) = strParcelType))" Case 501 To 750 intCost = DoCmd.RunSQL "SELECT pricetable1.[Postage Name], pricetable1.[501-750g] FROM pricetable1 WHERE (((pricetable1.[Postage Name]) = strParcelType))" Case 751 To 1000 intCost = DoCmd.RunSQL "SELECT pricetable1.[Postage Name], pricetable1.[751-1000g] FROM pricetable1 WHERE (((pricetable1.[Postage Name]) = strParcelType))" Case 1001 To 1250 intCost = DoCmd.RunSQL "SELECT pricetable1.[Postage Name], pricetable1.[1001-1250g] FROM pricetable1 WHERE (((pricetable1.[Postage Name]) = strParcelType))" End Select
Me.txtEstimate = strParcelType & intCost
many thanks to any gurus who can point me in the right direction or show me a easier way to do this
I have a VBA application i am writing. I am trying to use an sql statement with docmd.runsql to insert a value into my table that matches a particular record. (which will be the one open). For testing purposed i have stripped down my code.
When running the code it works, however it puts chinese symbols in all the other fields of the record and dose not insert the record into the CapExFileName Field. Then when you try and delete the record it comes up with no search index found.
Any idea on why it would be doing this ?
here is the code.
.......
Dim SQLstring As String
SQLstring = "UPDATE Assets SET Assets.CapExFileName = 'Test' WHERE Assets.Barcode = 'Testies1234'"
Now, I know that something in the UPDATE statement does not match my select statement.What should my Update Statement be, in order to update all the columns in the joined tables?
I have a query with the following criteria in one of the fields:
>=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom())
fom is a function for first of the current month. I need this query to be specific to what month it is when its ran so i want to only have this criteria if the month is > = october. If it isnt October or greater, i want the criteria to reflect this.
>=DateAdd("m",-12,fom()) And <=fom()
Which also works by itself. But when i add it to an iif statement it always produces no results. Below is the iif statement.
Iif(month(date())>=10, >=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom()),>=DateAdd("m",-12,fom()) And <=fom())
I have also added the column name to each expression and it still doesnt produce any results.
I am creating a multi-search form for a student database, where after I enter my search criteria I hit a "Run Query" command button and then it opens a query form with all of my criteria.So far I can search using last name, first name, and middle name. When I try to search with a start date and end date I am have issues.The start date and end date is for the class date. In the query form under the field, class date, for criteria I wrote:
Between IIf([Forms]![Search Form]![Start Date]="",1/1/10,[Forms]![Search Form]![Start Date]) And IIf([Forms]![Search Form]![End Date]="",4/25/15,[Forms]![Search Form]![End Date])
I want it when I write a date in the start date and end date I want it to give me a list of all the students who took the course between those dates. Also, if I leave the dates blank I want it to search all dates. The dates 1/1/10 and 4/25/15 are just the dates I gave because that is far back as my database goes.
I have a text box that I'm trying to control the color of based off an IF then statement. Based off to If's
If [Reports]![Rpt-Paths]![subreport].Report![ModuleCh] = "Ch - A" And [Reports]![Rpt-Run Paths]![subreport].Report![Status] = "0" Then
This part works but I need to add a second one
If [Reports]![Rpt-Paths]![subreport].Report![ModuleCh] = "Ch - B" And [Reports]![Rpt-Run Paths]![subreport].Report![Status] = "0" Then
So I want the text box to be RED if the status of both Ch A and Ch B is 0 but if either one has a status greater than 0 then the text box would be green.
I'm using this code to do some calculations on a form. This code works fine as long as only one IF statment is true. My problem starts when the one of the values is equal (meaning qual is 4) so I try to add < or = to and then I end up with more then one IF statement being true. Is there a better way to do this?
If Me.[qual] < 4 And Me.[completed] < 61 Then Me.[GtoG] = [GtoGtotal] Else End If If Me.[qual] < 4 And Me.[completed] > 61 Then Me.[GtoG] = Me.[qual] + 61 Me.[temp1] = Me.[completed] - 61 Me.[Delayone] = Me.[Delayone] + [temp1] Else End If If Me.[qual] > 4 And Me.[completed] < 61 Then Me.[GtoG] = Me.[completed] + 4 Me.[temp2] = Me.[qual] - 4 Me.[Delaytwo] = Me.[Delaytwo] + [temp2] Else End If If Me.[qual] > 4 And Me.[completed] > 61 Then Me.[GtoG] = 65 Me.[temp1] = Me.[completed] - 61 Me.[Delayone] = Me.[Delayone] + [temp1] Me.[temp2] = Me.[qual] - 4 Me.[Delaytwo] = Me.[Delaytwo] + [temp2] Else End If
I am trying to do something really simple, but my lack of experience in Access has bitten me. I have a report... there is a field called payor_code and one called bill_time. These are both based on tables.
The variable is payor code... it could be 2 different things.
All I want to do is make a calculation for TOTAL that multiplies the bill time, times the correct rate. (which is determined by the payor code)
I want to basically do this:
If payor_code = "02" Then total = Bill_Time * 1 Else total = Bill_Time * 2
Question..... i have a report in access on which grades of student are shown, like the following example:
Lifescience 6 Brainstorming 7 learningdev. 9 communication 5
My goal is to, next to the grades, also to put the grades as words...
Lifescience 6 six Brainstorming 7 seven Learningve. 9 nine Communication 5 vife
I have no clue actually how to do that, i of course thought of an IIF statement, the only thing i know is the crystal reports syntax, but in this case i am not sure how to 'translate' it to the expression on the field. (that is: i think i have to use the field where the grade comes from?)
Thanks for this, i think, relatively simple question........
Ok guys, I am stuck. If I have the following codes that works fine when I separate each "IF" statement, but I was trying to combine it all into one code. When I combine the code, I continue to get the msgbox even when the criteria has been met. Please tell me what I am doing wrong. Just trying to simplify the codes.
If Me.cboFat = 1 And Not IsDate(ClosingDate) Then MsgBox "Based on your selection in the Final Action Taken, Closing Date is a required field!" Cancel = True End If
If Me.cboFat = 2 And Not IsDate(ClosingDate) Then MsgBox "Based on your selection in the Final Action Taken, Closing Date is a required field!" Cancel = True End If
If Me.cboFat = 3 And Not IsDate(ClosingDate) Then MsgBox "Based on your selection in the Final Action Taken, Closing Date is a required field!" Cancel = True End If
If Me.cboFat = 4 And Not IsDate(ClosingDate) Then MsgBox "Based on your selection in the Final Action Taken, Closing Date is a required field!" Cancel = True End If
If (Me.cboFat = "1") Or Me.cboFat = "2" Or Me.cboFat = "3" Or Me.cboFat = "3" _ Or Me.cboFat = "4" And Not IsDate(ClosingDate) Then MsgBox "Based on your selection in the Final Action Taken,Closing Date is a required field!" Cancel = True End If
I'm trying to calculate the value of a field based on the other columns in the table. It's working in a form but I need to save the value in the table. I'm selecting the default value of the field and entering the following:
IIf(DateDiff('m',[Vesting Start Date],Date())>12,0.25*[Number of Options]+(DateDiff('m',[Vesting Start Date],Date())-12)*0.03*([Number of Options]-(0.25*[Number of Options])),0)
It doesn't recognize any of the column names like Vesting Start Date. Is it possible to calculate the value of a field based upon the other columns?
I have a nested IIF statement in one of the parameters of my query that checks for the value in an option box and returns the results for a specific record based on the value. This works great for what we need and we've been using it for over a month without a problem.
I just found out that there may be instances where management might want to see a group of records with combined data. I've racked my brain and searched this forum and have not come up with a solution yet.
In the first line is where I tried expanding the criteria to a group but I either get blank results or an error depending on how I've tweaked it.
This is an example of what I'm after:
IIf([Forms]![frmRejectopt]![optgrpWC]=2,([tblProcess]![ProcessID]=1 Or [tblProcess]![ProcessID]=9 Or [tblProcess]![ProcessID]=10 Or [tblProcess]![ProcessID]=11),IIf([Forms]![frmRejectopt]![optgrpWC]=3,2,IIf([Forms]![frmRejectopt]![optgrpWC]=4,5,IIf([Forms]![frmRejectopt]![optgrpWC]=5,9,IIf([Forms]![frmRejectopt]![optgrpWC]=6,10,IIf([Forms]![frmRejectopt]![optgrpWC]=7,11,IIf([Forms]![frmRejectopt]![optgrpWC]=12,4,IIf([Forms]![frmRejectopt]![optgrpWC]=8,3,IIf([Forms]![frmRejectopt]![optgrpWC]=9,6,IIf([Forms]![frmRejectopt]![optgrpWC]=11,7,IIf([Forms]![frmRejectopt]![optgrpWC]=10,8,[tblProcess]![ProcessID])))))))))))
I hope I was clear in my explanations. Thanks in advance for your help.
I am using a database that holds (amongst other things) payments received from a membership. This info is held in tblPayments. When payments are received I enter a new record against the membership number and in a field named PdForYr I enter the year for which they are paying, i.e. current year is 2005/6. I need to be able to extract members who have NOT renewed, i.e. this info is not in the database. So I think I need a SQL statement asking for records that paid in 2004/5 but not in 2005/6. Can't quite get it. Any help please?
