I cannot get the correct syntax for this. The punctuation for the second where condition is incorrect.
Code:
Me.cboDose.RowSource = "SELECT Label FROM tblLookup WHERE tblLookup.[Med]='" & Me.cboMed & "'" AND tblLookup.[Notes] = "Swallowed"
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 do not want to allow the main form record to be submitted unless there is at least one subform entry.Initially I thought it would be as simple as a "Before Update" event which would test the record count of the subform. This works... kind of... not really. It does check successfully, however the conundrum is that if the user has completed the main form and tries clicking INTO the subform to add records, this fires the Before Update event and Cancels the update event.
How can I force a user to complete the subform without running into this issue? I dunno how easy this is to do considering you kind of HAVE to exit the main form to enter the subform, at which point the record HAS to be saved...
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.
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.
Can't we give conditions on Validation Rules? (Like IIf)
IIf(getusername()=[Log_Resource],Between DateAdd("d",-1,Date()) And Date(),Between DateAdd("d",-2,Date()) And Date())
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.
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 RelatedI have an sql that runs....
UPDATE ProblemLog SET ProblemLog.Status = "Pending"
WHERE [Problem Number] IN (([Forms]![frmPendEm]![txtProbNo]));
Problem Number is autoGenerated primary key.
When I put in say...26, 27 it updates 0 records.
Put 26 or 27 by themselves and it updates 1 record at a time.
Anybody have any ideas of how this in statment can be accomplished to update multiple records?
Thanks.
Hi 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!
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?
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
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
Hi friends,
I had two tables parent(f1) , child(f1,f2,f3).
I had to display f3 value for parent(f1) when f1 has only one f2,
if f2 values are more than one for single f1 and if all f3 values are 0 then
i should display f3 as 0, else if some values for f3 are nonZeros and some are Zeros then
is should display f3 value as '-'.
How to do this?
please any suggestions?
Thanks.
I am so frustrated right now that I could scream. I have a database which tracks attendance and referrals for a networking organization - it meets weekly on Thursdays. It has a report which displays totals of both attendance and referrals for each weekly meeting for an entire month. The problem I am having is related to the fact that some months have 4 Thursdays and some have 5. If I get my queries to display the meeting dates correctly for the 4-Thursday months, then it displays incorrectly for the 5-Thursday months, and vice versa.
Is there any way to put a criteria on a missing record? If I could have it just populate the 5th week with spaces or null when it doesn't exist for that month, it would make my life a lot easier.
I can send a copy of the database to anyone who thinks they can help. It's too big to post.
Greetings,
I am a little stumped in logic again
What I am trying to do is the following
If the value is on field is say "OA" then I need an input code of LL0000 or LL00000 in the next field
If the value is say "OT" then I need an input code of LL etc
What is the best way of going about this.
The code is for stock locations in a warehouse.
~rbinder
I have a filed and I want to find out its length then in the next column i have to add the data like if filed 1's length is 1 then it should be 000+field1.value, if it is 2 then 00+field1.value, if it is 3 then 0+field1.value and so on...any help is appreciated , thanks
View 2 Replies View Relatedhow 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.
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