I want to write a code for a calculated text field in a datasheet. Right now the calculated text field is just set to ="view runs" but I want it to say "0 runs" if there are no runs in that Lot (for every lot there could be 0 to many runs). Im not sure how to make my IIf statement... every run has a runID and LotID_FK which is the LotID foregin key which is where the LotID is assigned.
how I can make an Array and For Each statement work together. I normally don't use either, but it would be good so I could write less code where plausible. Here is the example I have:
Code: Dim Named As Variant Dim Ctl As Control Named = Array(Me.Namee, Me.ID, Me.Title)
[code]....
I am unsure how I include the array in this statement. I have tried replacing "Me.Controls" with the array name, but I get a 424 runtime error (no object). What I am trying to accomplish is for each item in the array I want to make it not visible if it has a null value.
I've been using a SELECT INTO statement to import data from a linked text file into a temporary table in Access. Something along the lines of :
SELECT [tblLink].[fld1] AS Field1, [tblLink].[fld2] AS Field2, [tblLink].[fld3] AS Field3 INTO [tblTemp] FROM [tblLink]
(There's an INNER JOIN in there and some Nz / CLng functions but just want to keep it simple...)
Now - I've just realised I also need to create a couple of extra 'dummy' fields in my temporary table (for later on in the show) and I need them to be Yes/No format (will set them to False at first, then run some separate queries later to update them)
I tried this :
SELECT [tblLink].[fld1] AS Field1, [tblLink].[fld2] AS Field2, [tblLink].[fld3] AS Field3, False AS Field4, False AS Field5 INTO [tblTemp] FROM [tblLink]
But this sets Field4 and Field5 as Number fields, with each record given a value of 0. What syntax is required in the SQL to make these fields Yes/No rather than Number?
I'm trying to make a nested if then statement in a query field, and I can't figure out why I can't get my formula to work:
Volume: IIf([MethodCode]="K",[total]*12.54*0.026873,IIf([MethodCode]="S",([length]*[width]*[depth])/2,IIf([MethodCode]="M" And [Location]="SH",[total]*5.08*0.026873,IIf([MethodCode]="M" And [Location]="C",[total]*18.58*0.026873," "))))
I keep getting the "data type mismatch in criteria expression" error. If I separate out all the individual if then statements individually, they work. But if I connect them all as a nested if then it doesn't work.
I have a combobox [CBreason] that reads from a table to show me why someone is gaining access to the building.
One of the options is "Incident".
When incident is selected I want a textbox [TBIncidentNo] and a button[BIncidentDup] control I made to appear.
I thought that the code:
If Me.CBReason.Value = "Incident" Then Me.CBIncidentNo.Visible = True Me.BIncidentDup.Visible = True Else Me.CBIncidentNo.Visible = False Me.BIncidentDup.Visible = False End If
Placed in the after update action of the CBReason box would sufice but it doesnt work
I'm guessing the value is not recognised fro some reason but i cant work out why. There is no error message or issus, it makes the box dissapear when there is no entry but no change for selecting incident.
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 am creating a multi-search form for a student database, where after I enter my search criteria I hit a "Run Query" command button and then it opens a query form with all of my criteria.So far I can search using last name, first name, and middle name. When I try to search with a start date and end date I am have issues.The start date and end date is for the class date. In the query form under the field, class date, for criteria I wrote:
Between IIf([Forms]![Search Form]![Start Date]="",1/1/10,[Forms]![Search Form]![Start Date]) And IIf([Forms]![Search Form]![End Date]="",4/25/15,[Forms]![Search Form]![End Date])
I want it when I write a date in the start date and end date I want it to give me a list of all the students who took the course between those dates. Also, if I leave the dates blank I want it to search all dates. The dates 1/1/10 and 4/25/15 are just the dates I gave because that is far back as my database goes.
I'm currently working with a form, which is in datasheet view. I have many rows which are combo boxes (yes/no), and the name is rather long. So each line (each row) spreads on to 2-3 pages to the right.What I would like to do is make the namebar, on top of every column, a little bit higher, so the name would be split into two lines, or three. Allowing me to make the width allot smaller.Here is an example of my problem:http://213.213.137.96/~terminal/columns.jpgSo my question is, can I change the height of the column name? Or is there some trick I can use?regardsFrímann Kjerúlf
Would it be ok just to make a copy of the BE file (every so often) rather than to make a copy via code?The user can then just paste over the original if it becomes corrupt.
I have a text box that I'm trying to control the color of based off an IF then statement. Based off to If's
If [Reports]![Rpt-Paths]![subreport].Report![ModuleCh] = "Ch - A" And [Reports]![Rpt-Run Paths]![subreport].Report![Status] = "0" Then
This part works but I need to add a second one
If [Reports]![Rpt-Paths]![subreport].Report![ModuleCh] = "Ch - B" And [Reports]![Rpt-Run Paths]![subreport].Report![Status] = "0" Then
So I want the text box to be RED if the status of both Ch A and Ch B is 0 but if either one has a status greater than 0 then the text box would be green.
I'm using this code to do some calculations on a form. This code works fine as long as only one IF statment is true. My problem starts when the one of the values is equal (meaning qual is 4) so I try to add < or = to and then I end up with more then one IF statement being true. Is there a better way to do this?
If Me.[qual] < 4 And Me.[completed] < 61 Then Me.[GtoG] = [GtoGtotal] Else End If If Me.[qual] < 4 And Me.[completed] > 61 Then Me.[GtoG] = Me.[qual] + 61 Me.[temp1] = Me.[completed] - 61 Me.[Delayone] = Me.[Delayone] + [temp1] Else End If If Me.[qual] > 4 And Me.[completed] < 61 Then Me.[GtoG] = Me.[completed] + 4 Me.[temp2] = Me.[qual] - 4 Me.[Delaytwo] = Me.[Delaytwo] + [temp2] Else End If If Me.[qual] > 4 And Me.[completed] > 61 Then Me.[GtoG] = 65 Me.[temp1] = Me.[completed] - 61 Me.[Delayone] = Me.[Delayone] + [temp1] Me.[temp2] = Me.[qual] - 4 Me.[Delaytwo] = Me.[Delaytwo] + [temp2] Else End If
I am trying to do something really simple, but my lack of experience in Access has bitten me. I have a report... there is a field called payor_code and one called bill_time. These are both based on tables.
The variable is payor code... it could be 2 different things.
All I want to do is make a calculation for TOTAL that multiplies the bill time, times the correct rate. (which is determined by the payor code)
I want to basically do this:
If payor_code = "02" Then total = Bill_Time * 1 Else total = Bill_Time * 2
Question..... i have a report in access on which grades of student are shown, like the following example:
Lifescience 6 Brainstorming 7 learningdev. 9 communication 5
My goal is to, next to the grades, also to put the grades as words...
Lifescience 6 six Brainstorming 7 seven Learningve. 9 nine Communication 5 vife
I have no clue actually how to do that, i of course thought of an IIF statement, the only thing i know is the crystal reports syntax, but in this case i am not sure how to 'translate' it to the expression on the field. (that is: i think i have to use the field where the grade comes from?)
Thanks for this, i think, relatively simple question........
Ok guys, I am stuck. If I have the following codes that works fine when I separate each "IF" statement, but I was trying to combine it all into one code. When I combine the code, I continue to get the msgbox even when the criteria has been met. Please tell me what I am doing wrong. Just trying to simplify the codes.
If Me.cboFat = 1 And Not IsDate(ClosingDate) Then MsgBox "Based on your selection in the Final Action Taken, Closing Date is a required field!" Cancel = True End If
If Me.cboFat = 2 And Not IsDate(ClosingDate) Then MsgBox "Based on your selection in the Final Action Taken, Closing Date is a required field!" Cancel = True End If
If Me.cboFat = 3 And Not IsDate(ClosingDate) Then MsgBox "Based on your selection in the Final Action Taken, Closing Date is a required field!" Cancel = True End If
If Me.cboFat = 4 And Not IsDate(ClosingDate) Then MsgBox "Based on your selection in the Final Action Taken, Closing Date is a required field!" Cancel = True End If
If (Me.cboFat = "1") Or Me.cboFat = "2" Or Me.cboFat = "3" Or Me.cboFat = "3" _ Or Me.cboFat = "4" And Not IsDate(ClosingDate) Then MsgBox "Based on your selection in the Final Action Taken,Closing Date is a required field!" Cancel = True End If
I'm trying to calculate the value of a field based on the other columns in the table. It's working in a form but I need to save the value in the table. I'm selecting the default value of the field and entering the following:
IIf(DateDiff('m',[Vesting Start Date],Date())>12,0.25*[Number of Options]+(DateDiff('m',[Vesting Start Date],Date())-12)*0.03*([Number of Options]-(0.25*[Number of Options])),0)
It doesn't recognize any of the column names like Vesting Start Date. Is it possible to calculate the value of a field based upon the other columns?
I have a nested IIF statement in one of the parameters of my query that checks for the value in an option box and returns the results for a specific record based on the value. This works great for what we need and we've been using it for over a month without a problem.
I just found out that there may be instances where management might want to see a group of records with combined data. I've racked my brain and searched this forum and have not come up with a solution yet.
In the first line is where I tried expanding the criteria to a group but I either get blank results or an error depending on how I've tweaked it.
This is an example of what I'm after:
IIf([Forms]![frmRejectopt]![optgrpWC]=2,([tblProcess]![ProcessID]=1 Or [tblProcess]![ProcessID]=9 Or [tblProcess]![ProcessID]=10 Or [tblProcess]![ProcessID]=11),IIf([Forms]![frmRejectopt]![optgrpWC]=3,2,IIf([Forms]![frmRejectopt]![optgrpWC]=4,5,IIf([Forms]![frmRejectopt]![optgrpWC]=5,9,IIf([Forms]![frmRejectopt]![optgrpWC]=6,10,IIf([Forms]![frmRejectopt]![optgrpWC]=7,11,IIf([Forms]![frmRejectopt]![optgrpWC]=12,4,IIf([Forms]![frmRejectopt]![optgrpWC]=8,3,IIf([Forms]![frmRejectopt]![optgrpWC]=9,6,IIf([Forms]![frmRejectopt]![optgrpWC]=11,7,IIf([Forms]![frmRejectopt]![optgrpWC]=10,8,[tblProcess]![ProcessID])))))))))))
I hope I was clear in my explanations. Thanks in advance for your help.
I am using a database that holds (amongst other things) payments received from a membership. This info is held in tblPayments. When payments are received I enter a new record against the membership number and in a field named PdForYr I enter the year for which they are paying, i.e. current year is 2005/6. I need to be able to extract members who have NOT renewed, i.e. this info is not in the database. So I think I need a SQL statement asking for records that paid in 2004/5 but not in 2005/6. Can't quite get it. Any help please?