There has got to be a better way to do what I want to do. Here is the situation:
I have a weekly forecast (up to 14 weeks worth) that I want to apply to the inventory that I have on hand to generate figure out how many 'days' of forecast that I have on hand. Eg. I have 7 units on hand, next week's forecast is 7 units therefore I have 7 days of inventory.
Here is how I am doing this:
SQL = "SELECT SKU, "
SQL = SQL & "IIF([ON_HAND]<=[W1 FORECAST], [OVERSTK_POT]/([W1 FORECAST]/7), "
weeks = "[W1 FORECAST]"
days = 7
end_brackets = ")"
For n = 2 To 14
weeks = weeks & "+[W" & n & " FORECAST]"
days = days + 7
end_brackets = end_brackets & ")"
SQL = SQL & "IIF([ON_HAND]<=(" & weeks & "), [ON_HAND]/((" & weeks & ")/" & days & "), "
Next n
SQL = SQL & "105" & end_brackets & " AS OVERSTOCK_DAYS, "
I have 2 issues with this code. The first is that 12 nested iif statements is all that will be allowed in this loop before the query becomes too complex. The second is that this cannot possibly be the best method to accomplish this. I'm hoping someone might know a better method.
I've been trying to assign tours for an upcoming conference from the three options chosen by people, based on when they chose them. Now that the first tours are starting to fill up (maximum 40), some people should get shunted to their 2nd or 3rd choices. Having a hard time figuring out how to get Access to spit out that 'remaining' top choice, though.I triedSELECT IIF([TourChoice1]=10 Or [TourChoice1]=11 Or [TourChoice1]=14 Or [TourChoice1]=22,[TourChoice2],[TourChoice1]) AS TopChoice, tableRegistrants.RegistrationDate, tableRegistrants.TourFinal, tableRegistrants.PaymentMade, tablePeople.City, tablePeople.StateFROM tablePeople INNER JOIN tableRegistrants ON tablePeople.PersonID = tableRegistrants.RegIDWHERE (((tableRegistrants.TourFinal) Is Null))ORDER BY IIF([TourChoice1]=10 Or [TourChoice1]=11 Or [TourChoice1]=14 Or [TourChoice1]=22,[TourChoice2],[TourChoice1]) DESC , tableRegistrants.RegistrationDate; once I got four full tours, but then realized that it wasn't taking into account anyone whose 1st and 2nd choices were taken. There's a much larger IF statement possible here, but I'm almost certainly missing something.Factoids: 25 tour options, 40 people max per tour, about 500 people total. They're chosen based on when they registered (early registrations get their first choice, overflow on full choices get their 2nd, etc). And no, I don't have any choice on how the registration database spits them out to me.
I am trying to write a query that will search a field for a string until it discovers a comma. If there isn't a comma I want the field left as is. If there is a comma I want it to grab all strings before the comma and then take the string after the comma and flip the arrangement to another field..ie (flipname)
example if a field has [Smith, John] I want it displayed as John Smith
Here's is the code I was attempting to use below, it generates syntax errors!
SELECT Exercise1.name, Iif(Instr[name],",")=0,[Name], Mid([name],Instr([name]),+1,instr([name]),",")-1 as expr1 FROM Exercise1;
SELECT p.AcntNumber, p.Name, d.FromDate FROM Daysheet AS d INNER JOIN Patient AS p ON d.AcntNumber=p.AcntNumber
But this does not:
SELECT p.AcntNumber, p.Name, d.FromDate, c.CPT FROM Daysheet AS d INNER JOIN Patient AS p ON d.AcntNumber=p.AcntNumber INNER JOIN Charge AS c ON c.Id=d.ChargeNum
Nor this:
SELECT p.AcntNumber, p.Name, d.FromDate, c.CPT FROM Daysheet AS d (INNER JOIN Patient AS p (INNER JOIN Charge AS c ON c.Id=d.ChargeNum) ON d.AcntNumber=p.AcntNumber)
need help with looping to get a total for a specified time block. It needs to look at a time block and count the number of entries for a hour before to an hour after 7 am, 11am and 3pm on given 12 shifts. The list of shifts come from another sheet with the date and the time of the end of the shift.
I have the following which gives me a total for the 12 shift but now need to loop for the times given:
For rowz = 13 To 20 endtime = Sheets(sheetname).Cells(rowz, 1).Value begintime = endtime - 12 / 24 If IsDate(endtime) = False Or endtime > Now() Or endtime = Empty Then GoTo Exit End If
SQLstatement = "SELECT count(Field1), count(Field2),count(Field3), count(Field4), count(Field5), count(Field6)" & _ " FROM dbo.table WHERE [Date] Between '" & begintime & "' And '" & endtime & "' " rs.Open SQLstatement, , adOpenDynamic
If rs.EOF = False And IsNull(rs(0)) = False Then fieldcount= rs(0) + rs(1) + rs(2) + rs(3) + rs(4) + rs(5) Else fieldcount = "N/A" GoTo Exit End If
Hello all, thanks to Dennisk and Paul, I am able to move through my recordset and create a directory with each individual name. Now, I am attempting to "FileCopy" an excel template into each directory with the directories name.xls------got this fine.
Now I am attempting to copy and paste each individual's records by looping through the recordset RST1 and place that information into the appropriate directory/directory.xls file; close; and then save all workbooks.
Could someone point me in the right direction? Currenlty, the code is dumping all of the information into my first directory, and doing nothing for the rest of my recordsets.
Do I need another loop here? Example Do Until EOF RST1?
Here is the code:
Dim rst As DAO.RecordSet Dim DIRName As String Dim folder As String Dim Dir As String Dim strnewname As String Dim stroldname As String Dim Pause As Boolean Dim xlApp As Excel.Application Dim xlBook As Excel.Workbook Dim xlSheet As Excel.Worksheet Dim blnExcelOpen As Boolean Dim strMacro As String Dim mysheetpath As String Dim rst1 As DAO.RecordSet Dim objdb As Database
' Processing each officer and creating a directory MkDir DIRName
Sleep 10 FileCopy stroldname, strnewname 'Name stroldname As strnewname Sleep 10 mysheetpath = strnewname blnExcelOpen = IsExcelRunning() If (blnExcelOpen) Then Set xlApp = GetObject(, "Excel.Application") Else Set xlApp = CreateObject("Excel.Application") End If
Set xlBook = xlApp.Workbooks.Open(mysheetpath, False, False) Set xlSheet = xlBook.Worksheets("Sheet1")
'Transfer the data to Excel xlSheet.Range("A2:r10000").ClearContents
Hello all, i am new so please bear with me. My question is: I have this access 97 database that opens with office 07 and I am TRYING to open the database and loop through it pulling data from columns in all tables that end with the word "Data". I am lost so your help is greatly appreciated!!
I have a series of fields on a form. Each field name is a number 1 thru 32. I use a loop to set field properties
Param = 1 For z = 1 To 32 If (Me(Me.ParamB & Me.Param).Visible = True) Then Me(Me![ParamB] & Me!Param).Visible = False Me(Me!ParamL & Me!Param).Visible = False Me(Me!ParamE & Me!Param).Visible = False Me(Me!ParamT & Me!Param) = Null End If Param = Param + 1 Next z
I also have on the same form a series of field whose names are alphabets "a" thur "t"
I would also like to loop thru these fields to do the same thing. Is there any way to do this? I have tried the following code but it doesn't work. I'm thinking I need to set up temp fields to reassign each of these fields a number then loop thru it. I havn't tried it yet. but I'm wondering if there is a way to loop through the alphabet.
Param = "A"
For z = "A" To "t"
If (Me(Me.ParamB & Me.Param).Visible = True) Then Me(Me![ParamB] & Me!Param).Visible = False Me(Me!ParamL & Me!Param).Visible = False Me(Me!ParamE & Me!Param).Visible = False Me(Me!ParamT & Me!Param) = Null End If
Hi, not sure looping is the correct word so i will try to explain what i am trying to do.
i have a tbl and need to mark the records in groups of 12. the first group would be in group 1. The next group of 12 would be 2. and so on. the highest number is unknown as the data will grow.
is this posible with a qry? or do i need a for next loop and run a qry each time? if i do that how do I incriment the to the next level.
I am trying to add another record to a table. Pid and nsn are the keys. the table has 70 unique pid's. I need to add another nsn to each of the pid's. I tried the following code but no go:
Set db = CurrentDb Set rst1 = db.OpenRecordset("tblHandReceipt") Set rst2 = db.OpenRecordset("tblHandReceipt")
rst1.MoveFirst Do Until rst1.EOF rst2.AddNew rst2!PID = rst1!PID rst2!NSN = Forms!frmparts!NSN rst2!ISSUEDSOURCE = Forms!frmparts!subfrmEndItemParts.Form!cbxENDITEM 'Add like above line for each field rst2.Update rst2.MoveNext rst1.MoveNext Loop rst1.Close rst2.Close
I have a form with an UPDATE button. There is a subfrm on this form that lists all my units. I want the update button to loop through all the records on the subfrm and update the WOSD based on the information below. Is this possible?
Private Sub cmdUpdateWOSD_Click()
Call UpdateWOSDChange
End Sub
Private Sub UpdateWOSDChange()
Dim AddColor As Boolean Dim intNumDays As Integer
Select Case Forms!frmUpdateWOSD!subfrmWOSDupdate.Form.PreFin
Case "BR17", "BR28", "WH06" AddColor = True Case Else AddColor = False End Select
Select Case Forms!frmUpdateWOSD!subfrmWOSDupdate.Form.DrStyle
Case "DCREag", "DCRHWK", "DCRFAL", "RP-9", "RP-22", "RP-23" If AddColor Then intNumDays = 7 Else intNumDays = 6 End If
Case Else If AddColor Then intNumDays = 7 Else intNumDays = 4 End If End Select
Well, this should be easy... But I'm doing something wrong and can't find anything in the forum as an example.....I have a Form for creating an invoice billing for segments of time spent on a project. This form (frmInvoice) has a subform(frmHoursTimeInvoice) This subform has 2 checkboxes, one for "Billed" (HoursBilled) and one to include unbilled time into the invoice total (InvoiceHours)So... When you open the form to create an invoice the subform shows only unbilled hours.... as you ckeck the boxes (InvoiceHours) it adds the time to the invoice and calculates the total... So far so good........ Now the problem. I attached a "Close" command button to test this code....Private Sub cmdCloseSave_Click()On Error GoTo Err_cmdCloseSave_ClickIf [frmHoursTimeInvoice].Form![InvoiceHours] = True Then[frmHoursTimeInvoice].Form![HoursBilled] = TrueElseIf [frmHoursTimeInvoice].Form![InvoiceHours] = False Then[frmHoursTimeInvoice].Form![HoursBilled] = FalseEnd If End If DoCmd.CloseExit_cmdCloseSave_Click: Exit SubErr_cmdCloseSave_Click: MsgBox Err.Description Resume Exit_cmdCloseSave_Click End SubNow the problem is after it marks the first record (HoursBilled) True... it stops executing the code. I know it needs a loop here.... But I cannot find an example that works!! It needs to loop through all of the records in the subform and check the correct boxes True and leave the others False.So whats the best method? AND the correct syntax! Would a "For each... (something).... Next" work here??Thanks!PS.... Sorry for the lengthy explanation :)
I am trying to edit data in a table that has 30 fields.The fields names are W1,W2,- up to W30, and their value would correspond to a control on a form I have declared the controls in an array, so i would really like to use some sort of loop
Is There any way to use a loop to refer to fields to update them rather than having to use rst.edit ![W1]=Ctl(1) ![W2]=Ctl(2) ![W3]=Ctl(3) etc rst.update
What would the correct syntax be using a for next loop for i = 1 to 30
rst.edit !["W" & i]=Ctl(i) - this doesnt work rst.update
I've got a problem. The database-user can search through the database and the results are displayed in a subform. There's a button attachted to that subform and when that button is pressed, the files, which are linked to the found records and saved outside the database, have to be copied to an other folder. So how do I make I loop for copying those files?
I've got the following code behind the button (but it doesn't work):
Private Sub Command56_Click() Dim rst As Recordset Dim strBnaam As String
Set rst = CurrentDb.OpenRecordset("[Forms]![frmZoekVast]![qryZoekVast subform]") With rst Do While Not (rst.EOF) .MoveFirst strBnaam = rst!Tpon FileCopy "\NLFS006A110154$Stage BidteamSysteemTeksten" & strBnaam & ".doc", "C:TempOfferteTraject" & strBnaam & ".doc" .MoveNext Loop End With End Sub
I’m in the process of developing a holiday property availability form for displaying the available and free dates for a particular property in a particular period (usually 35 days). The main form allows the selection of the start and end dates of the period using a Calendar control and then completes text boxes with the days of the week for each day of the period.
The next step for the user will be to click on a property button to run a select query to find the booking records that are present for the availability period being looked at. Each booking record has the start date and the number of days booked together with the property name and other fields.
I have the select query results as a subform and can access fields on the first record using Mydate = Forms!Availability!CoiresgeirDates.Form!Revdate MsgBox Mydate However, I need to be able to access each record on the subform to be able to colour each text box for each day the property is booked so the user can see visually which days are booked for that period.
I’m struggling to find out how access the subform recordset to be able to loop through the records and pick off the days that are booked. I’ve tried defining a recordset but VB rejects the dim setting with Can't find project or library. Can somebody point me in the right direction?
I am using the code below to try to update some tables and data. My problem is the code seem to only work on one line in the recordset the last line. I have put up to 10 records in the recordset and it still only does the last record.
Any help would be great as it is the last bit of the project and I am behind my deadline;
'============================ 'Public Procedures
Public Sub UpdateQuestion() Dim UpdateAnswer As Byte Dim db1 As DAO.Database Dim rst, R As DAO.Recordset Dim volval As String Dim rcount As String
UpdateAnswer = MsgBox("Is this " & _ "work note ready to update and complete Press" & vbCrLf & _ "YES to update and complete OR " & vbCrLf & "NO just " & _ "to update and close", vbQuestion + vbYesNo + _ vbDefaultButton1, "Update and Complete") If UpdateAnswer = vbYes Then ' (Default)
If Forms![frm_Data_JobOrder_Note]![FrmDestination].Form![StatusTransfer].Value = -1 Then 'do nothing
Else Set db1 = CurrentDb Set R = Me.Form.RecordsetClone 'create a recordset of the data R.MoveFirst 'got to start somewhere Do Until R.EOF ' start loop
Set rst = db1.OpenRecordset("tbl_Data_Vessels") rst.Index = "PrimaryKey" rst.Seek "=", Forms![frm_Data_JobOrder_Note]![FrmDestination].Form![VesselID] rst.Edit volval = rst![Volume] rst![Volume] = Forms![frm_Data_JobOrder_Note]![FrmDestination].Form![FinalVolume] If rst![Volume] < 0 Then MsgBox ("Problem restoring original value"), vbCritical, gstrAppTitle rst![Volume] = volval rst.Update rst.Close Exit Sub End If rst![Status] = Forms![frm_Data_JobOrder_Note]![FrmDestination].Form![Status] rst![Updated] = [Forms]![frm_Data_JobOrder_Note]![EnterDate] rst![LastOp] = [Forms]![frm_Data_JobOrder_Note]![JobNumber] rst![ContentsOwner] = Forms![frm_Data_JobOrder_Note]![CompanyID] rst.Update rst.Close
I am currently modifying a form on my access database. More specifically the on click event of a button on the form. The form is based on a Table that only has 3 fiels, ID, GoingToCal, and Location. ID is the autonumber. GoingToCal is a multiselect list box on my form. Location is just where the user is submitting the entry from. The table is simply a way to track what went out at what facility.
The issue that I am having is 2 part. The first error I have doesn't create a runtime error, it simply doens't work. What I am attempting to do is update a recordset of the items selected in the list box. The items are in another table, and I have based my recordset on a query. When I run my code I am expecting first for the specific record to be found(I think works), then update a yes no box to yes. The Yes No field is called StillOut. It is a part of the query. However it doesn't update the Yes No box to Yes, nor does it seem to update any of the boxes. So I am confused as to what I am doing wrong that the field for that record isn't updating.
My Second issue, which I know there can be a few possible ways to work around I just can't make some of them happen. I am using the FindFirst for how I narrow down my recordset to just the one record I want to update. It doesn't seem to like "," with it. I have tried many times when setting the OpenRecordset to the specific record using SQL and a mix of SQL and the Query but no matter how hard I try I can never return any results. It constantly says too few parameters. That is why I am using the FindFirst command.
how to make the record update the Yes No box and how to specifically change just the records that have been selected in the form and a way to select those records.
If Me.GoingToCal.ItemsSelected.Count <> 0 Then For Each oItem In Me.GoingToCal.ItemsSelected If iCount = 0 Then
I have learned the basic looping technique for going through a record set from the following link. I need to know if my logic is on the right track. URL...
My question is a followup to a thread that was opened on this forum: URL....I want to do the following:
1) Use record set looping technique to fix a variety of incorrect naming conventions to a standard format 2) Update the table (or create a new table) from the updated record set values. (Is my logic going to update the table selected in the code I used to dimension the record set or will I need to do something else to make the changes available for other tasks after record set is closed ? After the naming conventions are fixed this data will be available for excel automation that I am working on and posting questions on another thread in this forum. HAHA I'm going code BANANAS) 3) rs.fields![fleetlocation] is used so many times, how can I make this a variable (what do I dimension the variable as?) 4) Use an AND statment with an if statment (how to do this with correct syntax)
Code: sub loopandfix() on error goto errorhandler: strSQL = "tblUnionQueryResults" 'table was created from a union query but has inconsistant naming conventions for the fleet location name
I have the following example of code I have previously used which loops through columns where a column is called "Page1", "Page2", Etc.This is fine when the number of columns is fairly small. But in a new case I have 585 250 columns which may or may not contain some data which is the key.If data exists in column do something and after that or of not move to the next column and do the same thing until there are no more columns?
Code: Dim rst As DAO.Recordset ' recordset for category data ' open the recordset for categories Set rst = CurrentDb.QueryDefs!QryRMA.OpenRecordset ' loop through the rows in the recordset rst.MoveFirst Do Until rst.EOF
I have a main form, lets call it FrmMain. In addition, I have a subform on FrmMain that has a tab control on it (frmSubform). What I would like to do is on form load, reference the tab control that is on the subform, loop through the controls/textboxes, lock them and change their backcolor. The code I am attempting is listed below, however, I am getting a "type mismatch error" highlighting this line:
Dim ctl As ControlDim Frm As FormSet Frm = Forms!frmmain!FrmSubformFor Each ctl In Frm.Controls    If (TypeName(ctl) = "Textbox" Or (TypeName(ctl) = "combobox") Or (TypeName(ctl) = "listbox")) Then        ctl.Value = Null        ctl.BackColor = vbYellow        ctl.Locked = True    End IfNext ctlÂ
Im still struggling with working with recordsets.What i want to achieve is to loop through a continuous form recordset, Using the OrderDetailFK from the record set and the OrderDetailPK in the OrderDetail Table
Looking at the Status FK in the OrderDetail Table. IF the OrderDetail Status = 2 then i want to make the textbox enable property = False. I have made the bellow SQL string which is placing all the criteria i just need to link it into a recordset?
Code: mySQL = "SELECT [StatusFK] FROM [tblOrderDetail] WHERE [OrderDetailPK] = " & rst!OrderDetailFK & " AND [StatusFK] = 2 "";"""
I have a looping error on this code. It all works fine until the very last row, on the last row it brings over the Claim_Number and the FacID, but leaves the Paid_Amount blank.
If I have 10 or 10,000 rows, its always leaves the Paid_Amount on the last row blank.
Code: Function RunLWSPA() Dim cnn As ADODB.Connection Dim rstUplo As New ADODB.Recordset Dim rstDiag As New ADODB.Recordset Dim rstTemp As New ADODB.Recordset
I have managed to amend records based on the user selecting multiple items in a list box by using the following code.
Set db = CurrentDb() Set rs1 = db.OpenRecordset("Usage", dbOpenDynaset, dbAppendOnly) Set ctl = [Forms]![frmsearch]![lstSelector]
[Code].....
what I need to do now, is to loop(I think) through and amend all the records in a populate listbox, with no selections, similar to above, but without selection.
I have a pretty complex database that uses several queries that all lead to a final query with a parameter (order number). I have a report linked to that query so every time I open the report it asks me for an order number. Once I enter the order number the query is run and I print the report. I am curious if I can create a query that pulls out order numbers (possibly for a date range) and then have a macro or something loop through that query and print a report for each order number. So if the query returned 50 order numbers then the report with the parameter (called Final results) would be printed 50 times.
arrParams = Split(arrLocations, ";") For i = 0 To UBound(arrParams) MsgBox (arrParams(i))
strSql1 = "SELECT branchName FROM ustax_customerBranchLocationsTBL WHERE branchName = '" & arrParams(i) & "' AND branchCustomerParentID = " & Me!customerID & "" 'MsgBox (strSql1) DoCmd.RunSQL strSql1 Set rs = db.OpenRecordset(strSql1) Dim count count = rs.RecordCount
Next
Above is my code, what i am trying to do is loop thru the array and check if the values are in a table. I am getting an error after the first loop of 'A RunSQL action requires an argument consisting of an SQL statement', why would I get this error if I am looping thru the array? Wouldn't the sql statement just be read with the new value each time?
