I have tried searching and haven't found an answer to this question.
I have a calculated field in a query that returns a aging date to an invoice. I want to take that aging date and apply it to a multiple If statement. I tried a joining field, but it's not working. A simple multiple If statement should do the trick though.
If AR is:
0-10
Then return:
"Current AR"
If AR is:
11-40
Then return:
"001-030"
If AR is:
41-70
Then return:
"061-090"
There are more, but I can figure that part out once these are solved. Any help would be appreciated.
Hi Guys, How can I change the format of a table using a query? basically after the query runs the resulting table all needs to be in a text format even though the fields I am querying are in numeric?
I have a field which shows numbers 1-8 or is null. I want to group this to show if its 1,3,5,7 then it shows as "BOOK" else if its 2,4,6,8 then its "CHAIR" and if its null then show it as null Can anyone help - I am using Access 2003
I'm new to programing so if this is not possible please tell me! Because this is going to be hard to explain I attached an example.
I have a Main form that a user will open, this is the only window that will ever pop up in front of them so it has a subform that will open differnt froms by picking buttons on the left.
One of the sub forms is the Member Information form, this form will show member information than at the bottom has another subform that will allow a user to pick a button above it to open another subform (this is so member info is always show and a member can be registered, pay...all different subforms)
on the Event Register subform the "Class" pulldown is based on the event that was pick just above it.
Issue: when I am in the main form, I can not get the class pulldown to work it always prompts me for "enter Parameter Value". I have tried the following SQL statements in the row source and noting work.. what am I missing???
SELECT SubCatList.SubCatName, SubCatList.EventName FROM SubCatList WHERE (((SubCatList.EventName)=[Forms].[MainFRM]![MemberInfoFRM].Form]![EventRegFRM].[Form]![EventName]));
and
SELECT SubCatList.SubCatName, SubCatList.EventName FROM SubCatList WHERE (((SubCatList.EventName)=[Forms]![MainFRM]![MainSubFRM]![Form]![memberinfoFRM]![MemberSubFRM].[Form].[EventName]));
I thought the whole point of the ; sign at the end of a sql statement in access was so that you can run more than one query at a time? if this is the case, could you tell me why this doesn't work? Code: sqlStr = "UPDATE tabControlFeeType SET FeeAmount = " & txtFirst.Text & " WHERE KeyFeeType = 1;" & _ "UPDATE tabControlFeeType SET FeeAmount = " & txtSecond.Text & " WHERE KeyFeeType = 2;" & _ "UPDATE tabControlFeeType SET FeeAmount = " & txtThird.Text & " WHERE KeyFeeType = 3;" Dim comm As New OleDbCommand(sqlStr, conn) comm.ExecuteNonQuery() the query doesn't work if I put it straight into access either?
I have a form with multiple controls (textboxes) named: Father Mother Child1 Child2
On the form there are also two checkboxes (check1 and check2).
I am using the following statement for each control and placed in in the OnCurrent event of my form: If Not IsNull (Me.Fater) Then Me.Check1 = "YES" (or -1) Me.Check2.Value = False End IF
Is there a way I can write one If statement saying: If all controls Father, Mather, Child1 and Child2 or at least one of them has data, then Check1 should be selected and check2 emply. If all controls do not have data then ckeck1 should be empty and check2 selected.
I have the following code on an After Update event:
If Me.Program_Type.Value = "(1) 45 Minute Formal" And Me.Cost_Category = "Full Price" Then Me.ProgPriceTxt.Value = "85" End If
This works fine. When I add another "And" to the statement, however, it no longer functions:If
Me.Program_Type.Value = "(1) 45 Minute Formal" And Me.Cost_Category = "Full Price" And Me.PavRentCheck = False Then Me.ProgPriceTxt.Value = "85" End If
Is it possible to put three conditions into an And statement? This thread seems to imply so (it's a different situation, but it seems close enough).
I believe it doesn't have anything to do with my text boxes or fields because this same issue has occurred in other places when I tried to have three conditions in an And statement.
I've "inherited" someone else access DB and I need to interpret this selection logic:
IIf(IsNull(a.[Lifecycle Stage 4 Sub-Stage]),IIf(IsNull(a.[Lifecycle Sub Stage 2]), a.[Lifecycle Stage], a.[Lifecycle Sub Stage 2]), a.[Lifecycle Stage 4 Sub-Stage]) AS [Sanitized Phase],
What I think it's saying:
If [Lifecycle Sub Stage 2] is null, use [Lifecycle stage], if [Lifecycle Sub Stage 2] is not null use [Lifecycle Sub Stage 2]. If [Lifecycle Stage 4 Sub-Stage] is not null, use [Lifecycle Stage 4 Sub-Stage].
I am not 100% sure if this is the correct interpretation as I have not had much previous experience interpreting multiple IIF statements in access. My aim is to migrate this process to Oracle, so I am just trying to get a handle around all the Access logic so that I can replicate the same behavior in another environment.
First off thanks for this forum it has gotten me this far. 2nd I have a question on how the best way to accomplish this. So I have a table that has customer info in it, Account #, Name, city, state, Zip. I have a form that allows users to type in fields to query for particular info. My select statement is below.
Private Sub cmdSearch_Click() Dim strSQL As String, strOrder As String, strWhere As String Dim dbNm As Database Dim qryDef As QueryDef Set dbNm = CurrentDb()
If Not IsNull(Me.txtCSONME) Then strWhere = strWhere & " (tblCONSOLIDATED.COMPANY_NAME) Like '*" & Me.txtCSONME & "*' AND" End If
If Not IsNull(Me.txtCSOSLD) Then strWhere = strWhere & " (tblCONSOLIDATED.ACCOUNT1) Like '*" & Me.txtCSOSLD & "*' AND" End If
If Not IsNull(Me.txtCSOSSM) Then strWhere = strWhere & " (tblCONSOLIDATED.REP_NUMBER) Like '*" & Me.txtCSOSSM & "*' AND" End If
If Not IsNull(Me.txtCSOARN) Then strWhere = strWhere & " (tblCONSOLIDATED.CONTACT_NAME) Like '*" & Me.txtCSOARN & "*' AND" End If
If Not IsNull(Me.txtCSOCTY) Then strWhere = strWhere & " (tblCONSOLIDATED.CITY) Like '*" & Me.txtCSOCTY & "*' AND" End If
If Not IsNull(Me.txtCSOST) Then strWhere = strWhere & " (tblCONSOLIDATED.STATE) Like '*" & Me.txtCSOST & "*' And" End If
Everything works but I can only search for one state. So now I want to search for two or more states. I've added multiple text fields on my form and have tried approaching it that way. Unfortunily if I add txtCSOST2 for example then add
If Not IsNull(Me.txtCSOST2) Then strWhere = strWhere & " (tblCONSOLIDATED.STATE) Like '*" & Me.txtCSOST2 & "*' And" End If
tblCONSOLIDATED.STATE has to contain both state codes. If I put an Or instead of And I get both states and all other search critera is ignored. Basically I need to be able to query by two or more states and it still be an and I guess. For example we may have a Rep A (Me.txtCSOSSM) that goes into TX & OK but Rep B also has a peice of TX. If I'm looking all the accounts that are in TX & OK and are Rep A and I use the below code I get all of the TX & OK accounts as well as Rep A accounts.
If Not IsNull(Me.txtCSOST) Then strWhere = strWhere & " (tblCONSOLIDATED.STATE) Like '*" & Me.txtCSOST & "*' Or" End If
If Not IsNull(Me.txtCSOST2) Then strWhere = strWhere & " (tblCONSOLIDATED.STATE) Like '*" & Me.txtCSOST2 & "*' Or" End If
I know that has to be simple I just wrap my brain around it. Let me know if this needs further explaination.
I'm trying to create a report that does the following:
If the term "Other" is selected in the Time1 field, then the Time1 field will not be visible, but the field Other1 field will be visible and if the term "Other" is not selected in Time1 field, then the Time1 field will be visible and the Other1 field will not be. This is what I have for VBA code, but it is not working.
If Not IsNull(Me.Time1) Then If Me.Time1 = "Other" Then Me.Time1.Visible = False Else Me.Time1.Visible = True
I am creating a report that has a filter based on 3 separate listboxes. The user has the option of choosing one or more filter criteria from each listbox. The trouble I am having is if the user only chooses one filter, I need to adjust my filter string. This is the code that generates the filter:
I need to get this syntax right. I have something similar that worked before to open a report but now I am using the same code structure on opening a form and I can't get it.
[prikey] is an autonumber and that has given me trouble before with the syntax. [EstimateFlagCleared] and [WarrantyFlagCleared] are Yes/No fields.
Dim maxFlag As String Dim flagCriteriaWarranty As String Dim flagCriteriaEstimate As String
Look at the below SQL 'INSERT INTO' statement ? I'm trying to insert multiple variable values into an 'INSERT INTO' statement. I'm getting the below error message. The code is listed below. I started out with two (2) variables, but will have thirteen to insert into a table. Also, in the code below is the VBA statement to retrieve the variable data. I'm getting the data, but cannot insert the data into the table.
Private Sub Test2_Click() Dim strSQL As String Dim strSalesman As String Dim strContentArea As String DoCmd.SetWarnings False
[Code] ....
Error Microsoft Visual Basic popup Run-time error '3061'
I have a problem that I can't seem to solve in SQL for my access 2010 query.
Let's say I have a the price of and for every year.
In the tables I have A building number, a building type, and electricity and water predictions for this year and many upcoming years (up to 40 years)
I need to apply a change to any building with the building type BRT to show only 10 percent of the electricity and water for ALL years (up to year 2052). So building 5 would show 8.5, 5.4, 9.5, 7.4, and so on.
Following is the one liner Update statement which, works perfectly
strsql = "UPDATE tblCurr SET tblCurr.Currencyname = [forms]![updatecurrency]![txtcurname] WHERE (((tblCurr.Currencycode)=[forms]![updatecurrency]![txtcurcode]));
I am trying to get a Case Statement to evaluate multiple conditions. Example: below when I get diagnosis code 20400 and the age_at_diagnosis is 40 the code is basically ignoring the second condition of the Case "And rs![Age_At_Diag] < 18". How do I get the code to recognize both conditions?
Code:
Private Sub cmd_Update_Conditional_Codes_Click() Dim rs As DAO.Recordset Dim rs2 As DAO.Recordset
I'm trying to construct an SQL crosstab query that will output data like the picture I've attached in the .zip file.
The four variables from the data table would be [Client Accounting].[Marketer] (the left vertical column), [Client Accounting].[Closing Date] (the higher level horizontal column grouped by month), [Client Accounting].[Write Off] and [Client Accounting].[Refund] (the lower level horizontal columns as sums)
The totals column at the bottom and the two vertical columns at the right would be made in the report and wouldn't need to be in the query.
This is what I have so far but I don't know how to add a second TRANSFORM statement to be included and grouped by month!
Code: TRANSFORM Sum([Client Accounting].[Refund]) AS SumOfRefund SELECT [Client Accounting].[Marketer] FROM [Client Accounting] GROUP BY [Client Accounting].[Marketer] PIVOT Format([Closing Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
I'm working on a class assignment and I am in desperate need of help. I am trying to set up a table... the fields are as shown: Type of Registrant, Registration Fee, Number...
I've created another table that has a list of names, telephone numbers, amounts paid, and registration status...
I'm creating the table I talked about in the first paragraph to simply list the status of registration, the amount per registration status, and the total number of registrants per status...
The available registration types are as follows: Academic ($285), Student ($200), Executive ($300), Manager ($225)
What I would like is this: When entering my information in this new table, I would like for the registration fee to automatically appear... almost like a set value... So, if I entered "Academic" in the "Registration Type" field, the amount $285 would automatically appear. I've tried a few "if" statements, and they're not working in my favor... Is there any kind of code that will allow this to happen? Please keep in mind, the reigstration types from which the information would be pulled are in the form of a list box in another table... Thanks!
I have created a database that tracks employee adherence. We have employees monitor adherence and when someone is out of adherence we track the time they are out and also there shift time. I am needing Access to know to change a record from 0:00 (midnight) to 23:59. I know it is probably done with an IF statement but dont know how to write it correctly, or where to place it.
Hi, I am writing ddl statement for creating tables in access, i would need to provide a default date for a date column, how can i do this in a ddl statement ?
In the table design window in the gui, i can do it by providing the default as "=format(now())" but it seems not to work in the ddl statement.
also, is it possible to generate the ddl sql statement for an existing table in the database, i would need to know how can this be done too.
I am making a default value statement in a table that looks like this IIF([company name] IN(“name1”, “name2”, “name3”), Date()+14, Date()+30);
this statement says that if the companys name is any of the names in the list, then the date outputed will be todays date +14, otherwise, the date will be todays date +30.
whenever i try to use the statement, access keeps giving me the error... "you omitted an operand or operator, you entered an inalid character or comma, or you entered text without surrounding it in quotation marks"
Is it possible to use a value of a table row in a "Like" statement of a query ? Below is query code that I want to modify to accomplish what I need. I want to search the "BudgetIn (Description) table for records that are like "ExpensesIn (Accounts) - table. I thought that if I entered the value in the "EXpensesIn" (Accounts) table, surrounding by asterisks, would give me what I needed. Unfortunately, the query criteria placed quotes around the critrea and the query looked for text (i.e. "*[ExpensesIn]Accounts*") rather than the value in the table.
SELECT BudgetIn.Description, ExpensesIn.Expenses, BudgetIn.Previous_Budget, BudgetIn.Budget_Year, ExpensesIn.Accounts FROM BudgetIn LEFT JOIN ExpensesIn ON BudgetIn.Description = ExpensesIn.Accounts WHERE BudgetIn.Description Like *[ExpensesIn]Accounts*;
I am using Access 2010 and Excel 2010. I need to have VB script to export the access table 502 records by 38 fields into Multiple Excel workbooks each having multiple tabs. In the Access table each record has two fields: Div and Tab that will be used to name each workbook and each tab (sheet). There are 6 unique "Div"'s to name the 6 workbooks and there are several "Tab" names for each Div (workbook).
Note: These 6 workbooks with multiple tabs were originally imported into Access from one common folder on my desktop by this routine:
Option Compare Database Option Explicit Private Sub Command1_Click() Dim blnHasFieldNames As Boolean, blnEXCEL As Boolean, blnReadOnly As Boolean Dim lngCount As Long
I am using Access 2010 and Excel 2010. I need to have VB script to export the access table 502 records by 38 fields into Multiple Excel workbooks each having multiple tabs. In the Access table each record has two fields: Div and Tab that will be used to name each workbook and each tab (sheet). There are 6 unique "Div"'s to name the 6 workbooks and there are several "Tab" names for each Div (workbook).
Excel workbooks would take names from the "Div" field and the tab names would come from the "Tab" field in the Access table. First need to find workbook name (Div - Field) then the look for each sheet name (Tab - Field) to create 1st Excel workbook with all the sheets (Tab) and repeat the process. I think you need to approach of read the Access table one record at a time keying on the "Div" and "Tab" fields in creating each Excel workbook with the associated multiple tabs (sheets) that are written to a common folder.
Note: These 6 workbooks with multiple tabs were originally imported into Access from one common folder on my desktop by this routine.
Option Compare Database Option Explicit Private Sub Command1_Click() Dim blnHasFieldNames As Boolean, blnEXCEL As Boolean, blnReadOnly As Boolean Dim lngCount As Long Dim objExcel As Object, objWorkbook As Object Dim colWorksheets As Collection
I HAVE A DATASETS cSV TEXT FORMAT WHICH HAS A SELECTION OF FIELDS THAT NEED TO BE IMPORTED INTO A TABLE. USING THE ADVANCED IMPORT FACILLITY I HAVE BEEN UNABLE TO IMPORT THE DATA PLEASE HELP AS THIS IS FOR MY A-LEVEL STUDENTS.
THE FIELDS REQUIRED IN THE SESSION TABLE ARE, Booking ID, Customer ID, Date Booking Made, Pickup Point, Payment Made
