Multiple If Numberic Statement From A Table
Jul 20, 2006
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.
Thanks,
Jason
View Replies
ADVERTISEMENT
Nov 6, 2007
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?
View 1 Replies
View Related
Jun 6, 2007
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
View 2 Replies
View Related
Oct 5, 2005
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]));
View 3 Replies
View Related
Aug 15, 2005
Hi all,
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?
do I just have this wrong?
Thanx
View 2 Replies
View Related
Jul 14, 2006
Hello,
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.
Thank you.
View 2 Replies
View Related
Oct 1, 2013
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.
View 11 Replies
View Related
May 20, 2015
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.
View 1 Replies
View Related
Mar 14, 2006
Hello,
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()
strSQL = "SELECT tblCONSOLIDATED.ACCOUNT1, tblCONSOLIDATED.COMPANY_NAME, tblCONSOLIDATED.CUSTOMER_TYPE, tblCONSOLIDATED.ADDRESS1, tblCONSOLIDATED.ADDRESS2, tblCONSOLIDATED.CITY, tblCONSOLIDATED.STATE, tblCONSOLIDATED.ZIP, tblCONSOLIDATED.CONTACT_NAME, tblCONSOLIDATED.E_MAIL, tblCONSOLIDATED.TELEPHONE, tblCONSOLIDATED.FAX, tblCONSOLIDATED.REP_NUMBER, tblCONSOLIDATED.PROMOCODE, tblCONSOLIDATED.SALESCODE, tblCONSOLIDATED.CURRENT_YTD, tblCONSOLIDATED.PRIOR_YTD, tblCONSOLIDATED.PRIOR_TOTAL, tblCONSOLIDATED.YEAR2_TOTAL, tblCONSOLIDATED.YEAR3_TOTAL, tblCONSOLIDATED.YEAR4_TOTAL " & _
"FROM tblCONSOLIDATED"
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.
Thanks in advance.
Bryan
View 3 Replies
View Related
Apr 14, 2015
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
[Code] ......
View 6 Replies
View Related
Feb 25, 2015
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:
Code:
strWhereFinal = strWhere1 & "AND " & strWhere2 & "AND " & strWhere3
You can see that if strwhere1 is NULL, the string will start with "AND [ApplicationStatus] = 1" and will obviously cause a failure.
Is there a simple way to build this string based on the number of search strings that are not null?
View 2 Replies
View Related
Dec 10, 2014
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
[Code] .....
View 13 Replies
View Related
Sep 28, 2013
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'
Too few parameters. Expected 1.
View 5 Replies
View Related
Oct 15, 2013
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.
View 2 Replies
View Related
Mar 7, 2014
I am unable to break the UPDATE statement into multiple lines and now I am getting "Syntax Error in UPDATE statement"
Following is the multiple line Update Statement
Dim strsql As String
strsql = "Update tblcurr" & _
"SET tblCurr.Currencyname =" & _
"[forms]![updatecurrency]![txtcurname]" & _
"WHERE (((tblCurr.Currencycode)=" & _
"[forms]![updatecurrency]![txtcurcode]));"
DoCmd.RunSQL strsql
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]));
View 4 Replies
View Related
Sep 8, 2014
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
[code]...
View 1 Replies
View Related
Feb 24, 2015
I'm trying to have a single or multiple query criteria based on what the user checks on a form.
I can't get the True condition to work at all, I get no records. Here is what I'm using
IIf([Forms]![FrmAttendanceLogsRpt]![BlkFilter]=-1,[TempVars]![EID] Or 86,[TempVars]![EID])
If I just put
[TempVars]![EID] Or 86
in the Criteria it works just fine.
View 14 Replies
View Related
Mar 24, 2015
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");
View 2 Replies
View Related
Oct 31, 2007
Hello:
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!
View 3 Replies
View Related
Jun 6, 2007
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.
View 1 Replies
View Related
Jan 26, 2008
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.
Thanks for the help
Joy
View 8 Replies
View Related
Dec 28, 2005
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"
what is wrong with my statement??
View 5 Replies
View Related
Oct 22, 2014
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*;
View 5 Replies
View Related
Dec 13, 2012
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
[Code] .....
View 7 Replies
View Related
Dec 13, 2012
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
[code]....
View 12 Replies
View Related
Nov 16, 2006
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
The CSV data set attached
tHANK YOU
SIMON
View 2 Replies
View Related
