I am trying to calculate [current status] based on multiple conditions. for example
Current status = A , if (w>0 and x=0 and y=0 and z=0)
Current Status =B , if(w>0 and x>0 , y=0 and z=0)
Current Status =C , if(w>0 and x>0 and y>0 , z=0)
Current status =D , if(w>0 and x>0 and y>0 and z>0)
Where A,B,C,D are text values and w,x,y,z are dates
Is it possible to set multiple conditions across multiple tables using OR in a where clause? For instance, can you run where......
(table1 = 'test') or (table2 = pass) or (table2 = "fail) AND (class = 'SQL');
G'Day:
I have a form that I created that allows the user to input an "ExhibitNo" and then a "PlacingNo" to allow each entry to receive a set monetary amount (based on values in a table).
The "ExhibitNo" has two references linked to it. They are "ClassNo" and "ExhibitorID".
My supervisors want me to make it so that when the "ExhibitNo" is entered on the form that it will not allow a monetary amount.
This is where I started, but it does not work. Does anyone have a better idea?
Private Sub Combo14_AfterUpdate()
Me.Refresh
If [ClassNo] >= 0 >= 5000 Then
If [ExhibitorNo] <= 0 >= 499 Then
If [Placinggrade] = "Blue" Then
[Premium] = [Blue]
End If
If [Placinggrade] = "Red" Then
[Premium] = [Red]
End If
If [Placinggrade] = "White" Then
[Premium] = [White]
End If
End If
End If
If [ClassNo] >= 5000 Then
If [ExhibitorNo] <= 500 Then
If [Placinggrade] = "Blue" Then
[Premium] = [Blue]
End If
If [Placinggrade] = "Red" Then
[Premium] = [Red]
End If
If [Placinggrade] = "White" Then
[Premium] = [White]
End If
End If
End If
If [ClassNo] >= 5000 Then
If [ExhibitorNo] >= 500 Then
[Premium] = 0
End If
End If
End Sub
Thanks
how to do multiple conditions, Nested....I have 6 conditions that i like to check,
Code:
if (condition-1) And
(condition-2) And
(condition-3) And
'[code]....
I'm trying to create an If/Then statement that needs to match to multiple values. My initial thoughts were to create a list but that doesn't seem to work the way I thought it would.
For example:
If Me.Field = 1,2,5, or 8 Then
Do 'X'
Else IF Me.Field = 3,4,6 or 7 THen
Do 'Y'
Else Do 'Z'
I haven't been able to get the syntax to work right. I'd rather not have to do 'Me.Field = 1 or Me.Field = 2, etc.' but if that's the only way to have it work right, then I guess it'll have to do.
For some reason, this one is driving me nuts so i would really appreciate some help.
This is kind of complicated to explain but here goes.
I am trying to create a query so that, based on certain conditions certain rows from the Form/Table are either visible or not visible on the report.
I have four fields involved:
Antic Dep$ (Anticipated Deposit Amount)
Antic Loan$ (Anticipated Loan Amount)
Date Dep Booked
Date Loan Booked
Basically i am trying to hide any records that in which the Date Dep Booked and/or Date Loan Booked are older than the current month...
I can think it through logically but cannot get my head around the query.
If Date Dep Booked or Date Loan Booked is not older than current month then show the row;
If Date Dep Booked is older than current month and Date Loan Booked is older than current month then hide the row; unless
If Date Dep Booked is older than current month but Date Loan Booked is not older than current month then show the row; except
If Date Dep Booked is older than current month and Antic Loan$ is zero then hide the row; but
If Date Dep Booked is older than current month and Antic Loan$ is not zero then show the row;
Then i also need to do the reverse for the Date Loan Booked, i.e.
If Date Loan Booked is older than current month but Date Dep Booked is not older than current month then show the row; except
If Date Loan Booked is older than current month and Antic Dep$ is zero then hide the row; but
If Date Loan Booked is older than current month and Antic Dep$ is not zero then show the row;
Phew! That probably makes no sense but i had to try.
Once again, i appreciate any help so that i can keep the remainder of my hair.
Switters
I have a Customer Issue form that writes the following into one table named Table1: Date, Customer Name, Ticket number, Agent, Issue and Comments. This is very simple.
All of this is filled in from a form that has links to 3 other tables for drop downs; Customer Name, Agents, Issues. This is working perfectly.
What I want to be able to do is generate a report based on a date range for a particular agent. Say 9/1/2013 - 9/30/2013 for John Doe.
For the report portion, on the form I have to combo boxes, one for start date, one for end date. I also have a drop down for selecting the agent. When I click a button on the form named Report, It will generate a preview of the report.
Here is the problem. I can get this to generate a report based either on the date range, which gives me all of the agents, or by agent, which gives me all of the dates. I can't get it to do both.
Here is some code that I have on the Report button:
DoCmd.OpenReport "AIReport3", acViewPreview, , "[Agent]=" & Me.Agent
This is the code that will let me choose the agent, but gives me all dates.
If I change this code to this:
DoCmd.OpenReport "AIReport3", acViewPreview, , "[DateRptd] Between #" & Me.cboFrom & "# And #" & Me.cboTo & "#"
It will display all issues in the date range, but gives me all agents.
I was thinking I should be able to combine them with an AND or an & to get it to use both the agent and date fields, but I can't get this to work.
Something like: DoCmd.OpenReport "AIReport3", acViewPreview, , "[Agent]=" & Me.Agent And "[DateRptd] Between #" & Me.cboFrom & "# And #" & Me.cboTo & "#"
or
DoCmd.OpenReport "AIReport3", acViewPreview, , "[Agent]=" & Me.Agent & "[DateRptd] Between #" & Me.cboFrom & "# And #" & Me.cboTo & "#"
Been trying to crack this one for a while hoping someone on here might be able to help me. ;)
I have a table with a list of required software and a table with a list of computers and the software installed software. I made a query that displays the machines with the required installed. My problem is if a machine has more than required then it does not display the record. If it needs Office, Photoshop but has office, Photoshop and quark installed then it does not display that machine.
This should be quite a simple thing, I have played with Like, NOT, Where and others but with no luck...
Any help would be greatly appreciated.
Graeme
Hi All,
My database has two tables:
1. Complete Address Data
2. Address conditions.
Table 1. has verbose customer [address] data like 11 smith st, 14 Kent Rd etc.
Table 2. has [std] address conditions, ST, RD, Street, Road etc.
How can I query ALL records by field [address] in table 1 by using ALL [std] address conditions in table 2?
The address conditions can occur anywhere in the [address] string, so I was thinking about maybe a CONTAINS condition, but I'm not sure
I want to open a filter form with mulitple conditions.one of them is a date condition.
I'm using access 2013 and the code below:
Code:
DoCmd.OpenForm "Edit_Mission", acNormal, , "[Report_Date]= " & Me.Date & " And [Supporter_Name]='" & Me.Supporter & "'"
it's opens the form but with no data. I also tried the # and it's still didn't work.
is there any way to put into the calculated field (in expression builder) conditions? What I need is something like
Sum If (Table1.Field1="Y" And CurrentTable.Field2=Table1.Field3)
I means sum how many times there is "S" value in the field1 Table1, but only for records where the field3 in Table1 is equal to the value in the actual table in Field2 (in the actual row).
I have a main form which has couple of subforms bound to one main table. One of the subfomrs is about employment info. It has 3 TextBoxes: (Job Start Date), (Employer Name), and (Wage).
It has also 3 ComboBoxes: (Job Type [Full Time or Part Time]), (Hire Status [Permanent or Temporary]), (Quarter [1st, 2nd, or 3rd]).
I want to force users to fill all these 6 fields if they put any value in any one of them. I tried to set a Validation Rule in the TextBox/ComboBox’s property including IIF statement condition, but I did not succeed, and these rules did not work.I put the following code in the subform’s (After Update), (Before Update), and (On Current) events which works very well when I keep the (Wage) value 0 and move to a new record or to another subform:
Private Sub Form_AfterUpdate()
If Me.[Start Date] > 0 And Me.Wage.Value = 0 Then
MsgBox "You did not put how much is the wage."
Cancel = True
End If
End Sub
[code]....
how to make a similar validation rule for the other TextBoxes and ComboBoxes.
I am trying to make a query that outputs the minimum "Need Year" AND ALSO if the need year was equal to 9999 it shows "NO DATA".
This is what I have so far for checking the minimum value:
field: Need Year: MinofList(PMS_output!pqi_ny,PMS_output!iri_ny,PMS_ output!sdi_ny,pms_output!sai_ny)
I am not sure if I should be putting it in the criteria to check whether this minimum value (need year) equals to 9999 or not and if it does, it says "NO DATA" instead of 9999.
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]...
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] .....
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]....
I would like to know which way is the best way to import excel data from multiple sheets in to multiple tables in access.
For example data from Sheet1 -> Table1, Sheet2->Table2, Sheet3->Table3 etc...
I have tried using this:
Cmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel5, "Table1", "C:Importedfile.xlsx", True, "Sheet1!"
Ironically, data from Sheet2 and Sheet3 seem to be properly imported in to table2 and table3, but some of the data from Sheet1 seems to be missing in Table1 after import.
Any other ways to import the data?
I am trying to write a code that will enable an event to happen if 2 conditions are true, something like:
If [DueDate] >= [CurrentDate] And [EndDate] is Null Then
Event ..... take place
I tried above and did not work
it works only if write one condition of above
how can I set it up correctly
I currently have a query which takes its criteria from a form called search. This all works fine, unless the user leaves a field in the search form blank. Then the query has no results.
What I'd like is for a blank field to act as a wildcard.
I guess the reason this doesn't automatically work is that Access (effectively) puts " & " around the criteria taken from a form, so the criteria ends up as " " not (there's a space there!).
I tried entering a criteria in the query that says:
IIF([Forms]![Search]![Date] Is Null,*, [Forms]![Search]![Date]
this doesn't work. For one thing Access edits the * into "*", so it is no longer a wildcard. Secondly, I've never used IIF before, so it's probably wrong anyway...
So, can anyone help me do this?
Even if a blank field cannot be used as a wildcard, any character would do...
Hi
Hope someone can help with this. Had a search through the forums but nothing quite the same (although I'm guessing it's a simple one to solve.)
I need to calculate a figure based on 2 criteria. The permutations are as follows
If condition A is less than 20 and condition B is No then return 9
If condition A is less than 20 and condition B is No then return 12
If condition A is greater than 20 and condition B is No then return 13
If condition A is greater than 20 and condition B is No then return 15
Any suggestions?
Hi, I have created a Yes/No attribute. Im just wondering if a text boxes visibility can be dependent on the result of this. E.g. If a row is set to yes then the text box will be shown and if it is set to no the text box is made invisible. Thanks for any help
View 7 Replies View RelatedHi again :)
Since I am new to Access I am having a problem figuring out how to set the participation conditions (optional or mandatory) for my relationships. I have looked through MS access online and forums, google etc. and found nothing.
I would be very grateful for any help :)
Kind Regards
Marie
The following SQL statement with 1 where condition works fine, but the Next SQL statement with 2 where conditions does not work, a error message says Run-Time error ‘13’ type mismatch , what is incorrect in the statement when the AND MinID = 15 is added. The table data type in set as number
SQL = "UPDATE MinistryInv2 Set MinID = " & 14 & " WHERE PID = " & Me!ID & ";"
SQL = "UPDATE MinistryInv2 Set MinID = " & 14 & " WHERE PID = " & Me!ID And MinID = 15 & ";"
Phil
I have set up a querry that gathers information from different tables:
ID | Name | StartDate | EndDate | NbOfDays |
Then, i am using a form to fill up this querry. Normal stuff.
1- How and where can i set a condition so the input of EndDate could not be smaller than StartDate?
2- How and where can i set up and write the formula that calculates the NbOfDays? (meaning number of days worked form start date to end date)
Thank you for helping!
What I need to do is, not have a form pop up when all the conditions are met. I have 30 some users that log into the db.
I have a form that pops up when the date of the project is past due. to certain users when they log in. I am using the Environ("username") theory, which works very well. But when the user has COMPLETED all the necessary dates, the form still comes up, (but of course it’s blank).
How can I eliminate that problem?
Hi,
i am trying to open a form if all conditions are met. if the conditions are not met, then they get a flash message and then the user should return to the open form. i have the condition checking working but cannot seem to open the form if ALL conditions are true. i also need to close the form when the new form opens.how could i achieve this? here is my code
Private Sub MidVerifyContinue_Click()
FullNameChk.SetFocus
If FullNameChk = False Then
MsgBox "You Need to have a Full Name to Verify", vbOKOnly, "Message"
End If
If CompNameChk = False Then
MsgBox "You Need to have a Company Name to Verify", vbOKOnly, "Message"
End If
If TradeNameChk = False Then
MsgBox "You Need to have a Trading as name to Verify", vbOKOnly, "Message"
End If
If UTChk = False Then
MsgBox "You Need to have a number to Verify", vbOKOnly, "Message"
End If
If CRChk = False Then
MsgBox "You Need to have a C R number to Verify", vbOKOnly, "Message"
End If
If NIChk = False Then
MsgBox "You Need to have a N I number to Verify", vbOKOnly, "Message"
End If
If PostCodeChk = False Then
MsgBox "You Need to have a Postcode to Verify", vbOKOnly, "Message"
End If
Exit Sub
DoCmd.OpenForm "New Verification Qry"
End Sub
many thanks,
Nigel