Elseif Type Function In Query
May 31, 2006
i have the following query statement:
intext: IIf([calllogs]![InternalCall],"0","External")
in "InternalCall" there is either a "0" or a "1", these represent external and internal calls
So I want to turn the "1" and "0" into "internal" and "external"
I've done half of it, but I can't use an "else if" statement in the query, the only other option is to have one field for internal and one box for external with seperate iif statements which I want to stay away from...
View Replies
ADVERTISEMENT
Nov 6, 2005
I have been using the following in AfterUpdate event of "MyField1". This has worked ok enabling and disabling one field which in this case is named Myfield2. Now I want to enable and disable multiple fields based on text content of "MyField1" and am not sure what to do. MyField1 is a combo. I've searched about but can't find anything to point me in the right direction. Any suggestions?
If Me.Myfield1 = "mytext" Then
Me.Myfield2.Enabled = True
ElseIf Me.Myfield1 = "someothertext" Then
Me.myfield2.Enabled = True
Else
Me.myfield2.Enabled = False
End If
End Sub
View 5 Replies
View Related
Nov 4, 2004
Can someone please look at my code and help me understand why it isn't working?
Code:Private Sub Text113_Enter()If [current retailer] = "One" And ([peakusage] + [offpeakusage] + [controlledloadusage]) > 40000 And ([one] - [total]) < 0 Then meter_type = "B Meter"ElseIf [current retailer] = "One" And ([peakusage] + [offpeakusage] + [controlledloadusage]) > 40000 And ([one] - [total]) >= 0 Then meter_type = "5 Meter"ElseIf [current retailer] = "Two" And ([peakusage] + [offpeakusage] + [controlledloadusage]) > 40000 And ([two] - [total]) < 0 Then meter_type = "B Meter"ElseIf [current retailer] = "Two" And ([peakusage] + [offpeakusage] + [controlledloadusage]) > 40000 And ([two] - [total]) >= 0 Then meter_type = "5 Meter"ElseIf [peakusage] + [offpeakusage] + [controlledloadusage] <= 40000 Then meter_type = "B Meter"End IfEnd Sub
Everytime I try to run this code to store it in my field(meter_type), I get the "Else without If" error, but I can't see where I'm going wrong! Anyone??
View 8 Replies
View Related
Jan 24, 2005
Heelo all:
Code:
Private Sub GPA_AfterUpdate()
If Division = "GRADUATE" Then
Honors = ""
ElseIf Division = "UNDERGRADUATE" And GPA < 3.2 Then
Honors = ""
ElseIf GPA >= 3.2 And GPA < 3.5 Then
Honors = "CUM LAUDE"
ElseIf GPA >= 3.5 And GPA < 3.8 Then
Honors = "MAGNA CUM LAUDE"
ElseIf GPA >= 3.8 Then
Honors = "SUMMA CUM LAUDE"
End If
If Division = "GRADUATE" And School = "SCHOOL OF EDUCATION" And GPA >= 3.7 Then
Honors = "HONORS"
MsgBox "PLEASE MAKE SURE STUDENT HAS ONLY A'S AND B'S ON TRANSCRIPT!"
End If
End Sub
Does anyone know hoe to convert the above statements into using SELECTCASE statements?
Any help is most welcome.
Thanks in advance,
Dee
View 1 Replies
View Related
Sep 23, 2014
Question for Documentation purpose: Should the Public Type be declared in its own module?
Or should it be declared in a standard module where non-public functions use it? It is not for a Form module use.
For a Rule Engine, a function is calling one record on 4 different SQL Views (as linked tables) that have the same field format.
For speed, the recordset should only be opened once. However, there are multiple values that must be returned to the result table multiple fields.
One way to return multiple values is an Array. That has over head too.
Another way is to create multiple public variables. Not my choice for documentation. Another is to create a string.
This is a pure code module with several non public functions / subs. What is the documentation preference? List a Public Type close to the function, or place it in the Global module?
Background: A function can only have one return value.
By creating a public Type, multiple values can be returned.
Code:
Public Type Income
Wages As Currency
Dividends As Currency
Other As Currency
Total As Currency
End Type
Use this structure as the return type for a function. In a real situation, the function would look up your database tables to get the values, but the return values would be assigned like this:
Code:
Function GetIncome() As Income
GetIncome.Wages = 950
GetIncome.Dividends = 570
GetIncome.Other = 52
GetIncome.Total = GetIncome.Wages + GetIncome.Dividends + GetIncome.Other
End Function
To use the function, you could type into the Immediate Window:
GetIncome().Wages
(Note: the use of "Public" in the Type declaration gives it sufficient scope.)
Important Notice The way this function is called will work, but is wrong from the aspect it re-calls the recordset over and over.
See the proper way to use it submitted below.
View 5 Replies
View Related
Dec 3, 2013
I ahve declared custCount as an integer and id as a string that comes from the user form (id = me.CustID) and trying to count the number of matching records in the recordset (rst) using the following:
custCount = DCount("[customerId]", rst, "[customerId] like '" & id & "'")
I'm getting a type mismatch error on the DCount statement?
View 3 Replies
View Related
Oct 17, 2013
Anyway I've got a type mismatch that I can't figure out.
Code:
dMaxLstReq = DMax("reqNumb", "FlightLog", "Month([txtDate])='" & frmMonth & "'" And "Year([txtDate])='" & frmYear & "'")
In plain english: Select the highest value in the field named reqNumb from FlightLog where the Month of txtDate is equal to the variable frmMonth and the year of txtDate is equal to the variable frmYear.
txtDate is a Date/Time field in the table FlightLog
frmMonth and frmYear are both integer variables that take the system time (sysTime) and determine the month and year: I.E.
Code:
frmMonth = Month(sysTime) & frmYear = Year(sysTime)
Am I correct in thinking that the fact that my variables are integers and not times, that this is the cause of the mismatch?
View 2 Replies
View Related
Dec 11, 2013
So basically I need making a function that will count the number of records from another table/query based on a field from the current query.
View 2 Replies
View Related
Sep 21, 2004
hi friends,
i have tried had to connect sub type tabels (Saving, Checking, Loan... they have their own ids...) with super type (Account...it has account id...) on the condition of account_type (either "S","C" or "L") attribute in ACCOUNT entity.
how to joint them??? with query or with expression??
i expect help from you.........please.
........thanks.
View 5 Replies
View Related
Sep 16, 2005
I have a table products
product id - autonum, key
ref - txt
serial - txt
typeId - num (gets type from types table)
and a table types
typeId - autonum, key
type - txt
I want a query to allow my form in a choicelist to pick one of the types and display all available products on that type.
I got :
SELECT types.TypeId, types.Type, products.Reference
FROM types, products
WHERE (((types.TypeId)=[products].[typeid]));
But it doesn't give me that result when I make the form based on that query.
View 2 Replies
View Related
Oct 26, 2007
I'm not really looking for code, just a clue as to what to read up on as I have been beating my head against the wall with this one. I can do it in about three steps but I am trying to do it in one. :confused:
The first table1 is like this:
id Field1 Field2 Field3
1 TagNo44 Y Yes
2 TagNo5 C No
3 TagNo127 Q Maybe
4 TagNo4 T Perhaps
5 TagNo88 Z Probably Not
6 TagNo17 P Could be
The second table2 is like this:
id Field1 Field2 Field3
1 TagNo44 Outfit6 30
2 TagNo44 Outfit14 70
3 TagNo127 Outfit14 100
4 TagNo4 Outfit3 100
5 TagNo88 Outfit3 50
6 TagNo88 Outfit8 50
7 TagNo5 Outfit7 100
8 TagNo17 Outfit92 100
Output table to be like this
id Field1 Field2
4 TagNo4 Outfit3&100
2 TagNo5 Outfit7&100
6 TagNo17 Outfit92&100
1 TagNo44 Outfit14&70&Outfit6&30
5 TagNo88 Outfit3&50&Outfit8&50
3 TagNo127 Outfit14&100
Table1Field1 is in a one to many relationship to table2.field1
All fields are strings.
View 3 Replies
View Related
Aug 11, 2005
This is a bit confusing, so I'll try to explain it well.
Two tables. One has a list of metals. The other has a list of submetals, and what basic type of metal it is. It's grabbing the second column from the first table. Makes sense, right?
Now, I have another table, with links to both of those fields. What I want it to do is when it picks the metal type, to filter the subgroup to only the types it has associated with it to the basic metal type. Not sure if that makes sense. I'll supply an example.
Table 1
Aluminum
Brass
Copper
Table 2
Subgroup Basic group
License Plate Aluminum
Iron Aluminum Aluminum
Yellow Brass Brass
Copper Wire Copper
Now, if in Table 3, they choose Aluminum from the drop down box for basic metal, I want the dropdown box for the subgroup to have only License plate, and Iron Aluminum. For Brass, only Yellow Brass, and the same for copper.
Any ideas on how to implement this?
View 3 Replies
View Related
Jun 4, 2007
Hi
Hope someone can help
I have a query that is trying to match two tables, one in MSSQL2000 and the other in Access 2003. The matching field in MSSQL is a Text field and in Access the field is a long integer. When I try to run the query it gives a Type Mismatch error.
I've tried all the functions that I can see to convert either of the fields data types but to no avail.
Does anyone know of a way to force a match?
Thanks
Mark
View 2 Replies
View Related
Jun 9, 2005
I want to say update set field1 = field2
But field1's data type is Number and Field2 is Text. (Field1 is fed by a lookup table). Am I doomed to go through the 700 or so by hand, gurus, please?
View 5 Replies
View Related
Jun 15, 2006
I am trying to do a basic query and I keep getting a "Type Mismatch" error and the query will not run. If I only do a query on one table, it works no problem so I know it must be related to my Join between tables.
For the two tables that are joined (it is one-to-many)- the first table is a clients table and I created a field called ClientNumber that is an AutoNumber. The second table is called TrainingRequests. This will store the training requests for each client and each client can have multiple training requests. I created a field called ClientNumber in it as well (this is what field I linked the tables by). But I set it to text instead of AutoNumber.
Is there a way to do a query with the two tables? Or will I have to change something in table design? I already have some data in the tables so I am not sure what direction to take.
Thanks for any help someone can provide. It would be greatly appreciated.
View 1 Replies
View Related
May 31, 2007
I'm sure this must be an easy one, I haven't used access for years and I've forgot how to do everything.
I'm designing a query based on a linked table which belongs to someone else. Unfortunately they appear to have stored a cost value (eg. 12030.30) as text.
I need to group the table records together and sum the cost value but I can't because its a text data type. Can anyone help me convert the data type within the query so that I can sum the costs, I've tried to build the expression using the cdbl() function but got stuck when it asked me for a parameter.....
Would appreciate the help :)
Thanks
Lloyd
p.s using Access 97....
View 1 Replies
View Related
Feb 7, 2006
I've been struggling with this for awhile now. I've been trying to research a solution but I'm not even sure what to search for.I have the following table structure:tblContactsContact_ID (PK)First_NameLast_NametblBidPackBid_Pack_ID (PK)Bid_Pack_DescriptiontblDistributionListList_ID (PK)Bid_Pack_ID (FK) (1-M)Contact_ID (FK) (1-M)What I would like to do is create a query to display all of the contacts from tblContacts that are not associated with a certain Bid_Pack_ID. My end goal is to create a form where I enter Distribution List Information. I want a listbox on the left with all of the contacts from tblContacts and a listbox on the right with contacts that have been selected for a particular Bid_Pack. When the user double clicks an item on the left it should show up on the right and be removed from the left. I'm looking for the same functionality as when you use the design wizard and you choose fields to include/exclude.For example, let's say I want to add contacts for distribution list "A". My thought is the box on the left would show all the contacts from tblContacts that aren't selected for distribution list "A" and the box on the right would show all of the contacts that are part of distribution list "A". As you double click an entry it would perform the appropriate record creation/deletion and each box would be required.So back to my original question, what is the correct method (relationship, join, querydef) to select all of the contacts from tblContacts that haven't been assocatied with a particular bid pack?Thanks for any help you can provide. Even a nudge in the right direction would be appreciated.
View 2 Replies
View Related
Nov 4, 2006
Query1:
Src: Table1 joined Table2
ID (Type Text)
Title (Type Text)
Remarks(Type Text)
Formatted: FormatTitle([title],[Remarks])
Expr1: InStrRev([Formatted], "~")
public functionFormatTitle(ByVal sTitle as String, ByVal sRemarks as String) as String
'do process code here very complicated an long, but works find in the end
'creates a Multi-String delimited by | (pipe)
end function
The above works, and Expr1 does give an accurate value for the position of a "~" (tilde) in the string Created by the FormatTitle() function.
However, If I put a Criteria >0 on Expr1 it asks for the value of the [Formatted] field as if it was a parameter. If I put a criteria for Formatted: Like "*~*" I get a Data Type Mismatch in Query Criteria
Query2:
Src: Query1
Title (Type Text)
Remarks (Type Text)
Formatted(Type Text)
Exr1 (Type Number) criteria >0
This Query Also produces the Data Type Mismatch in Query Criteria
pardon me, but WTF? If it isn't a STring, than InStrRev() should produce an error, not an accurate response, and if InStrRev() produces a number why can't i compare it to 0 (zero)? This is indubitably messed up that I'm getting this error. There is no data type mismatch, on either of these tests, one is a string and I criteria-limit it by a string operation, the other is a number and I criteria limit it by a number, WHAT IS GOING ON!!!
Thanks
Jaeden "Sifo Dyas" al'Raec Ruiner - The Frustratedly Confused
View 2 Replies
View Related
Oct 6, 2006
Good Afternoon Everybody,
Apologies for imposing on you all but I was wondering whether or not you could help me out??
Would you be so kind as to kind as to show me a practical example of how to incorporate a facility into my attached Database which serves to select an equal ratio of my field “SectionHeadings” such that out of the 30 “random” questions that it currently generates it will provides me on completion with the following:
5 From Set1
5 From Set2
5 From Set 3 …..etc etc
It currently generates the 30 questions that I need without a problem; however it routinely selects any number of one particular type, sometimes more from one subject and less of another.
All subjects headings are of equal importance to me and as a consequence would be very grateful if you could look at what I’ve already done and show me what needs to be done in order to achieve my aim.
I hope my request is possible - Any help you could give me would be very much appreciated. Thank you very much.
Best Regards
CarolW
View 5 Replies
View Related
Aug 27, 2014
Working with two tables - tbl_A_OrdData & tbl_B_ShipData tbl_A have all the orders placed, but tbl_B only have the orders that got shipped. I'm trying to create a query that will show me all orders that got placed for a particular criteria and if the order find a match in tbl_B to give me the ship date too. A left joint query get me the data I need. But my dates are in YYYYMMDD number format so in the query I am putting the DateValue function. And that error out as "data type mismatch" because it cannot find the shipdate for certain records. I tried this but didnt work. ShipDt: IIf (IsNull ([PSHPDT]), "", DateValue(Mid([PSHPDT],5,2) & "/" & Right([PSHPDT],2) & "/" & Left([PSHPDT],4)))
View 2 Replies
View Related
Aug 19, 2013
Is there a way to retrieve the value of a user-defined type in a query?
Here's the type:
Code:
Public Type ClassRank
Rank As Integer
ClassCount As Integer
End Type
I have a function with the following excerpt:
Code:
Function GetRank(strDOD) as ClassRank
...
GetRank.Rank = intRank
GetRank.ClassCount = intCount
...
End Function
In my query I expected to be able to put the following:
Code:
GetRank(strDOD).Rank & " " & GetRank(strDOD).ClassCount
However, Access didn't like the period in .Rank or .ClassCount.
Should I just write two different function to get 'rank' and 'classcount'?
View 2 Replies
View Related
Nov 3, 2012
I have a table with a field with names set to text data type and i want to change it to number data type but when i do it in design view the data get lost. I want to know if there is a way to convert the data in the field as number type and keep the data in the field.
View 7 Replies
View Related
Apr 14, 2008
I have a list of PC SN#s in tbl1;
I have a list of other PC SN#s in tbl2
I want to display ALL records of tbl1, AND ALL records of tbl2, matching up the SN#s (where there is a match) - but I want to display all records whether they match or not.
How can I accomplish this?
View 1 Replies
View Related
Jan 16, 2014
I have vba code that creates the following SQL:
SELECT SubscheduleID, EventID, WeekOrder, DayID, StartTime, EndTime, Priority, CanJoin, PatientTitle, PatientNickname, IncludesPatient, IncludesAftercare, Letter1
FROM [qryScheduleCombinedDetails]
WHERE (SubscheduleID = 1 AND IncludesPatient = -1 AND DuringAftercare <> "AC only" AND (WeekOrder = "All" OR WeekOrder = 3 OR (WeekOrder = 1 AND Letter1 = "XYZ")) AND DayID = 2 AND StartTime <= #8:00:00 AM# AND EndTime >= #8:30:00 AM#);
When I try to run it, I get a "data type mismatch" error. When I put the same code into a query, I get the same error. However, it will run if I delete either condition from within the (WeekOrder = 1 AND Letter1 = "XYZ") pairing. I can't figure why it can run with either of those, but not both together.
WeekOrder is defined as String. Letter1 is calculated as Cstr(Nz(IIf(Letter,"XYZ","ABC"))) within [qryScheduleCombinedDetails], because I wanted to make sure that it would be recognized as a string.
View 4 Replies
View Related
Jan 18, 2014
Is it possible to limit the type of data that can be entered in a query that has a LIKE criteria?
( Like [Enter Data] & "*")
limit to two digits or any number of digits, or limit to numbers only or letters only. .
View 2 Replies
View Related
Apr 2, 2013
I run a physical therapy office and patients come in for treatment either 3, 4 or 5 times per week. My database is used to track these frequencies (among other things).
I have 3 queries which count how many patients come in 5, 4 and 3 times/week.
In my main table I have fields called "how many 5's", "how many 4's" and "how many 3's".
I have tried to design an update query which will update those fileds in my main table to reflect the counts in the 3 queries mentioned above.
(I'm not using SQL view, I'm using the query design view)
In the "update to:" row, I use the Build function and locate the count I'm looking for.
Problem: when I run the query I get the error: Operation must use an updateable query.
View 3 Replies
View Related
