I am trying to create an IFF statement in a query that if the field HRID is populated and the space category field equals the wrong space type than my results should be "Check Occup". I have tried several combinations but I still cannot get this to work.
=IIf([HRID]>"" And [SCat Text]="BRR_Restructuring" Or "Building_Common" Or "Floor_Common" Or "Vacant_Available","Check Occup")
Can I generate an IIf statement with two-true values? I have these two statements but don't know how to combine them:
IIf((Weekday(date);1)=vbMonday or vbTuesday or vbWednesday or vbThursday or vbFriday;x;””)
IIf((Weekday(date);1)=vbSaturday or vbSunday;y;””)
I have at field with a date and will like the true statement to be “x” if the data is Monday-Friday and “y” if the date are a Saturday or Sunday and if the date field is blank, I want to have a blank field here as well.
I have tried:
IIf((Weekday(date);1)=vbMonday or vbTuesday or vbWednesday or vbThursday or vbFriday;x;y)
But it returns y in fields that should be blank.
I have the following Select Statement:
SELECTTenant.ID, Tenant.[First Name], Tenant.[Last Name], Tenant.Address, Tenant.City, Tenant.State, Tenant.Zip, Tenant.[Home Phone], Tenant.[Cell Phone], Tenant.[Work Phone], Tenant.[Rented Unit],
Tenant.[Security Deposit], Tenant.[Move In], Tenant.[Move Out], Tenant.TenantID, Tenant.UnitID, Tenant.PropertyID, Tenant.OwnerID, Owner.Company, Owner.ID AS Expr1, Property.[Property Address],
[code]....
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 have an issue that can be described as follows:
There is a textbox on a form and when I fill in its value, I would like it to lookup whether that value matches any of the values in another form and if it does present a message box. Both fields hold only numerical data, so no type mismatch issues.
I've tried the code following, but it only looks up the first value on the second form, not its entire dataset, which is problematic.
Code:
Private Sub Lot_AfterUpdate()
DoCmd.OpenForm ("Pot_Pot_ExtraLots")
If Me.Lot.Value = Forms!Pot_Pot_ExtraLots!ExtraPotLots.Value Then
MsgBox "There is a bag with extra sherds found during other analyses from this Lot! Lookup and combine results!"
DoCmd.Close acForm, "Pot_Pot_ExtraLots", acSaveNo
Else
DoCmd.Close acForm, "Pot_Pot_ExtraLots", acSaveNo
End If
End Sub
I'm now trying to speed up data entry within my database and have hit a brick wall with one part.Basically, this is within a form (for 'clauses') of which there is a one-to-one relationship with 'the Applicability' table (a series of fields with 'Yes/No' values for each and a related key field) - i.e. for each clause there are a series of circumstances when it will apply. I've set this up to create a record when one doesn't already exist using the default values (i.e. all applicable).
The method to speed up data entry is to have a pop-up form with unbound fields to list common Clause fields - including 'Applicability' as a subform. When entering a series of Clauses you tend to find they have the same 'Applicability' as their neighbour, so I would like to create a corresponding record in the Applicability table with the values set on the 'ClauseQuickAdd' form (Technically the 'ApplicabilityQuickAdd' sub form).I've edited an existing code within my database to provide two global variables (strAppFieldList and strSubAppFieldList) to put into the following statement:
Code:
DoCmd.RunSQL "INSERT INTO Applicability ( AppRelClause, "" & strAppFieldList & "") VALUES ("" & Me.Clause_ID & ", " & strSubAppFieldList & "")"
Bringing up Error 2498 "An expression you entered is the wrong data type for one of the arguments". So I presume that I haven't quite got the format right for transferring yes/no values (strSubAppFieldList). See below for an extract of strSubAppFieldList:
Code:
Forms('ClauseQuickAdd')![ApplicabilityQuickAdd]![Manufacturer], Forms('ClauseQuickAdd')![ApplicabilityQuickAdd]![Supplier], ...
I have a form that has a combobox with table names in it. I want to execute a very complicated SQL statement but I want to put the table name (from the combobox) into the SQL statement. I'm planning on making a button to do this, but I don't know how to put the SQL statement in VBS which would use the combobox value as the table name in the SQL statement.
View 2 Replies View RelatedHey there!
I've been searching about this forum and found a lot of threads on this sort of issue but haven't been able to figure it out from those alone, so feeling a bit useless :o long winded explanation by the way... I'm useless at explaining! :D
Well basically I have a query that is supposed to power a flexible search form, where you choose the fields you want to search by and enter your criteria. The form contains labelled fields with checkboxes next to them, which you tick if you want to search for something in that field. The results are displayed in a subform which reads from the query.
The problem is with the query! My solution seemed to work fine when I put my criteria in just one field when testing (that is the fields in query design builder, not the form), but when I applied the same to two fields it didn't. Instead, it would return nothing unless I specified exact and correct criteria for BOTH fields I had set up with my solution. So whereas before I could tick "Customer" and search for a customer account number and it would display ALL relevant records, now if i just ticked "Customer" and entered the same number, it would display nothing, unless I ALSO ticked "Booking" and entered a correct booking number that matched a record for that customer in the tables.
I've heard iif statements don't do too well in the criteria fields in the query design builder?? But I know nothing about using SQL really.
For example for the invoices field have used:
IIf([Forms]![frmFindInvoices]![ChkInvNo]=True,[Forms]![frmFindInvoices]![InvNo])
So:
IIf(form checkbox = true, form text box value)
However it seems to think that if the checkbox is not ticked, I want it to only display EMPTY invoicing fields. This is never the case and so no records are shown! If the tick box is not shown, there is not supposed to be criteria for this field, in other words it should allow ANYTHING through.
What I don't know how to do is tell the bloody thing that if the box is NOT ticked, it is NOT to check for anything in this field, because the user does not want to specify any criteria for this field! Things like "Is Null" produce the same results, probably because the query still thinks I want it to find records that have empty fields.
Any suggestions to specify to the query what I want? I really need to get this done! Thanks! :)
I'm using Access 2010. I need to calculate a score based on values selected in a table by looking up corresponding values in other tables. I have a "Project" form to create new entries into the Project table (see Table 1). When I create a new project record, I will select values for the Payback and Need fields by selecting options from a list. The Payback list is pointed at Table 2 and the Need list is pointed at Table 3. In the below example, I created the "ABC" project and selected "1 year" for the Payback field and "Repair" for the Need field. Pretty simple.
Now that I have the "ABC" project loaded to my Project table, I'd like to create a report that will show a "score" for this project. The score should be calculated as follows: Payback Impact + Need Impact. In this example, the score should be 30 (Payback Impact of 20 + Need Impact of 10).
I have a query with an INNER JOIN and ORDER BY that is working great. Now, using the same JOIN, I need to update values in one table with the values in another. I thought it would be simple until I learned you can't do an ORDER BY with an UPDATE. Is there another way to achieve the same result? If you remove the 'ORDER BY', the statement below doesn't produce an error but the results are not correct:
UPDATE TableA INNER JOIN TableB ON (Left(TableA.CDN,6))=(TableB.CDN)
SET TableA.HCC = TableB.HCC
WHERE TableB.HCC Like '241*' AND TableB.BBB = 'X' AND TableA.CCC = "1234" AND TableA.HCC IS NOT NULL
ORDER BY TableB.HCC, TableA.CDN;
I'm trying to determine the SQL to return only those records in a table which have duplicate values in each of two fields, but different values in a third field. Here's an example:
Code:
AcctNum FoodType FoodClass
------- -------- ---------
A123 Apple Fruit
A123 Apple Fruit
A123 Grape Fruit
A456 Potato Vegetable
A456 Potato Perishable
A789 Carrot Vegetable
A001 Banana Fruit
For the above table, I'm trying to return records which have multiple entries for AcctNum + FoodType, but DIFFERENT values for FoodClass. So for the above table, the query would return:
Code:
AcctNum FoodType FoodClass
------- -------- ---------
A456 Potato Vegetable
A456 Potato Perishable
It returns these two records because there is more than one record with for the AcctNum + FoodType (i.e. 'A456' + 'Potato'), but DIFFERENT values for FoodClass (i.e. one record has 'Vegetable' while the other has 'Perishable').
I have two tables.descriptions I'd like to relate and use to find/replace in bulk.
[Checking].[Description] (with the source data)
[Rename].[NewDescription] (with the correct data)
I'd like the values in [Checking].[Description] to be replaced with the values in [Rename].[NewDescription], including those that are "Like".
Examples:
[Checking].[Description] = Geico 12345
[Rename].[NewDescription] = Geico
[Checking].[Description] = Geic
[Rename].[NewDescription] = Geico
I have a table with following two columns
TechName Version
SQLServer 2000
SQLServer 2003
SQLServer 2005
SQLServer 2008
SQLServer 2008R2
Oracle 11G
MSOffice 2000
MSOffice 2003
MSOffice 2007
MSOffice 2010
How can i show this data as components eqch version for a techname to be one component order need not follow
TechName Component1 Component2 Component3 Component4 Component5
SqlServer 2000 2003 2005 2008 2008R2
Oralce 11G NULL NULL NULL NULL
MSOffice 2000 2003 2007 2010 NULL
I have an UPDATE query that I would think to be adequately written as follows:
UPDATE (SELECT num FROM DataSrc ORDER BY group, zone)
SET NUM = getnextcounter([num]);
The purpose of the query is to update the value in field [num], by incrementing it sequentially according to the ORDER BY sort order, using get nextcounter(), which simply adds 1 to a counter variable that is first set to 0 (before running the query).When I save the query and re-open it in design view, Access has changed it by adding an AS statement:
UPDATE (SELECT num FROM DataSrc ORDER BY group, zone, name, date, time, datasrc, id) AS [%$##@_Alias] SET NUM = getnextcounter([num]);
It does what I want, but I don't understand why the AS statement is being generated and put into the SQL statement
I have a main form with many subforms. Each subform has couple of ComboBoxes or/and TextBoxes. I want to make sure that end users dont miss completing any of required values. For example, in the subform (Clients), an end user is supposed to put either "Male" or "Female" in the (Gender) ComboBox. In another subform (Job Info), the end user will put info of the job like Wage, Employer, Date of Job Start... ete.
What I want is to get VB IF statement which tests if the textboxes "Wage", "Employer", and "Date of Job Start" in the subform (Job Info) are not null, then the CobmoBox "Gender" in the subform (Clients) should not be null too, with a warning message to complete the missing value.
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
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*;
This statement returns a "date" each time the event occurs. I want to have a parameter between [Start Date] and [End Date] so I can pull events for a specified date range. I have tried this "Expr [EventDate]" Between [start date] And [end date] but it returns "null" results. I am using access 2010
EventDate: IIf([tblEventException].[EventID] Is Null,IIf(([qryEventCartesian].[PeriodTypeID] Is Null) Or ([qryEventCartesian].[PeriodFreq] Is Null) Or ([qryEventCartesian].[InstanceID] Is Null),[qryEventCartesian].[EventStart],DateAdd([qryEventCartesian].[PeriodTypeID],[qryEventCartesian].[InstanceID]*[qryEventCartesian].[PeriodFreq],[qryEventCartesian].[EventStart])),IIf([tblEventException].[IsCanned],Null,[tblEventException].[InstanceDate]))
I have a Form that is used to print a report and the report is based off of a query.I dont like the WHERE statement in the query. I want the user to enter the employer codes and not have to continue to press enter when they are done entering 5 employer codes.
Example: The want to send letters to 3 Employer codes but after they enter the 3rd employer code the query will continue asking for 21 more employer codes. How can I provide the user a way to not be asked for 21 more employer codes? The current code for the query is:
Code:
SELECT Format([Enter Letter Date],"mmmm dd"", ""yyyy") AS [Letter Date], dbo_partfile.prt_employer_code, dbo_employer.mis_description, UCase([kn_key_name]) AS UID, Trim([prt_last]) AS [LAST], Trim([prt_first]) AS [First], dbo_partfile.prt_middle, dbo_partfile.prt_addr1, dbo_partfile.prt_addr2, Trim([prt_city]) AS City, dbo_partfile.prt_state, dbo_partfile.prt_zip_code, Format([prt_zip_ext],"0000") AS Zip4, Format([Enter Term Date],"mmmm dd"", ""yyyy") AS [Term Date], IIf([prt_sex]="F","Ms.","Mr.") AS Title, dbo_partfile.prt_status, dbo_partfile.prt_local_nbr
[code]...
I need an iif statement in my query criteria that works as follows:
Code:
iif([Field] = "FirstPossible","str1",iif [Field] = "SecondPossible","str2",*)
The problem is, I'm not sure how to display all of the records if neither the first iif or second iif returns true.
I'm trying to do a select statement and put it in a variable which i can then output to a text box. How do I get the value into a variable? I can't seem to get my syntax right
This is what I currently have
Maxvalue = "SELECT MAX[Record Num]FROM Joblog"
I am working on one access database wherein in query i need to use iif statement, i have one field called Age, in field criteria i need to use,
IIF(weekday(DATE())="2",>"3",>"1")
this if staement is not working i believe that we cant use ">" ... this type of criteria with iif, so what should i do?
my intention is if today is Monday than age filed >3 else it should be >1
I am trying to use a Like statement in a VBA dlookup, as some of the fields in a third party db i cannot manage are variations of fields in a table i need.
I currently have:
Me.Clinic.Value = DLookup("[ID]", "dbo_Clinics", "[Name] = Like '%' & [Forms]![frmHome]![subaTcTools].[Form]![subbDST].[Form]![txtClinic] & '%'")
But I get a runtime error 3075 missing operator.
Here are the criteria I am using:
Current Week: DatePart("ww", [Funded_Date]) = DatePart("ww", Date()) and Year([Funded_Date]) = Year(Date())
Current Month: Year([Funded_Date]) = Year(Now()) And Month([Funded_Date]) = Month(Now())
Previous Month: Year([Funded_Date])* 12 + DatePart("m", [Funded_Date]) = Year(Date())* 12 + DatePart("m", Date()) - 1
I am able to successfully set one of the above as a criteria just fine.I am stalled on trying to combine two or more of the above in an iif statement. Here is what I 'think it should look like:
Funded_Date_Period: IIF([funded_date] = "Year([Funded_Date])* 12 + DatePart("m", [Funded_Date]) = Year(Date())* 12 + DatePart("m", Date()) - 1)", "PrevMonth", IIF([funded_Date]= "Year([Funded_Date]) = Year(Now()) And Month([Funded_Date]) = Month(Now())", "CurrentMonth", IIF([funded_date]="DatePart("ww", [Funded_Date]) = DatePart("ww", Date()) and Year([Funded_Date]) = Year(Date())", "CurrentWeek")
In other words, in a separate field, I would like to print a period name such as above.I have not been able to find any reference to connect these long criteria together in one query expression.
My workaround is to use a union query. That works, but it is a little clumsy due to some outer joins which require a separate query. If that is my only solution, I will run with it.
I'm trying to use IIf in a query criterion, but not having any luck.
I have a field called 'ayr_code' which has values in the format:2015/6
2014/5
2013/4
2012/3
2011/2
2010/1
2009/0
2008/9
I want to return all records where in this field:if the current month is November or December, the first four characters of 'ayr_code' are between five years ago and the current year;
If the current month is between January and October, the first four characters of 'ayr_code' are between six years ago and last year;
So, in October 2015, I'd like to see records with an 'ayr_code' of 2014/5, 2013/4, 2012/3, 2011/2, 2010/1 or 2009/0. In November 2015, I'd like to see records with an 'ayr_code' of 2015/6, 2014/5, 2013/4, 2012/3, 2011/2 or 2010/1.
I've tried to do this using solution one below, but this is not working. I've gone for solution two, but I'd like to know why solution one doesn't work and if there is a way to amend it so that it does.
Solution one [preferable; not working]
Create the following field:
Code : Expr1: Left([cam_sas.ayr_code],4)
Add the following criterion:
Code : IIf(Month(Now())>10,>=Year(Now())-5 And <=Year(Now()),>=Year(Now())-6 And <=Year(Now())-1)
So this should get the first four characters of 'ayr_code' then apply one of two criteria on the results based on whether the current month is after October or not.
Solution two [non-preferable; working]
Create the following field:
Code : Expr1: IIf(Month(Now())>10,Left([cam_sas.ayr_code],4),Left([cam_sas.ayr_code],4)+1)
Add the following criterion:
Code : >=Year(Now())-5 And <=Year(Now())
This takes the first four characters of 'ayr_code' then if the current month is not after October it adds one to the result, after which it applies the criterion that the final output must be between this year and five years ago.
I have a query based on a table that has a column called Calculated Card Expiration:
IIf([Card Type Issued]="PIV",[Issue Date]+1095,IIf([Card Type Issued]="Non-PIV",[Issue Date]+1095,IIf([Card Type Issued]="Flash",[Issue Date]+365,Null)))
It worked fine when I ran the query the first time, but now when a Card Type Issued and Issue Date is added or changed it doesn't work.