How To Do Multiple IIF / CASE Query

Jul 6, 2005

Hello

I was wondering if anyone knows how to do the following;

I have the number of years staff have been working for the company which is extracted from the startdate.

Based on the number of years service employees are entitiled to extra days leave, so I need to do the following:

IIF([YEAR]<5, 24) and IIF([YEAR] between 5 and 9, 25) and IIF([YEAR] between 10 and 14, 26) and IIF([YEAR] between 15 and 19, 27) and IIF([YEAR] between 20 and 24, 28) and IIF([YEAR] > 25 , 29)

The above syntax does not work...

HOw do I achieve the above? Any suggestion would be gratefully recieved.
Ta :rolleyes:

View Replies


ADVERTISEMENT

Multiple Select Case

Mar 30, 2006

Hiya is there an easier way of grouping together 'Select Case' statements rather than this way I have done them. They work but I still have more to add and feel this way is a little long winded!
Private Sub Form_Current()

Select Case DatePart("w", Tape_Date)
Case 1, 3, 5
Me.Datapulse.Visible = True
Me.Label86.Visible = True
Me.Label87.Visible = True
Case Else
Me.Datapulse.Visible = False
Me.Label86.Visible = False
Me.Label87.Visible = False
End Select

Select Case DatePart("w", Create_U607ST50)
Case 6
Me.Create_U607ST50.Visible = True
Me.Label88.Visible = True
Me.Label89.Visible = True
Me.Label129.Visible = True
Case Else
Me.Create_U607ST50.Visible = False
Me.Label88.Visible = False
Me.Label89.Visible = False
Me.Label129.Visible = False
End Select

Select Case DatePart("w", U607ST94)
Case 6
Me.U607ST94.Visible = True
Me.Label90.Visible = True
Me.Label91.Visible = True
Case Else
Me.U607ST94.Visible = False
Me.Label90.Visible = False
Me.Label91.Visible = False
End Select

End Sub

View 8 Replies View Related

Modules & VBA :: Trying To Get Case Statement To Recognize Multiple Conditions

Sep 8, 2014

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]...

View 1 Replies View Related

General :: Input Data In Lower Case And Automatically Change First Letter Of Word To Upper Case

Mar 16, 2013

Is there any way of making data that is inputted in lower case to automatically change to the first letter of each word being a capital ...

View 4 Replies View Related

Change Case To Title Case

Aug 24, 2004

hi

help is needed yet again :-)

I know when creating a text field in the format option
you can use the > or < sign so that when text
is entered it automatically changes it to uppercase or
lowercase - but i need it to be Title Case, any one know
how I can do this....

View 11 Replies View Related

Modules & VBA :: Case Sensitive Query

Jul 12, 2013

I want to make this bit of code below case sensitive :

Code:
SQL_Entire = "WHERE [" & ReplaceStringSelectedField & "] LIKE '*" & SearchedString & "*';"
SQL_Beginning = "WHERE [" & ReplaceStringSelectedField & "] LIKE '" & SearchedString & "*';"
SQL_End = "WHERE [" & ReplaceStringSelectedField & "] LIKE '*" & SearchedString & "';"

View 14 Replies View Related

Query Returning Case-sensitive Results!?!

May 22, 2006

I am using the following:

SELECT CPL_CLIENTS.CODE, CPL_CLIENTS.FULLNAME FROM CPL_CLIENTS
WHERE (((CPL_CLIENTS.FULLNAME) Like "*" & Forms!frmSafeKeep_AddClient_SearchDesig!txtSearchT ext & "*"));

As the control source of a listbox, so that the user can enter something in "txtSearchText", and the listbox will be narrowed to only those those clients that have a matching part of their name.

The problem is that the user enters, for example, "duncan", and the list returns nothing, when in actual fact there are 5 matching records. If the user enters "Duncan" (note the capital letter), they get all 5 records as they should.

If memory serves, access should only be case sensitive if you tell it to be - and this is working fine on other forms.

The only difference between them is the data source. Other forms are getting data from linked tables in the backend. This form is getting it from a linked Oracle table.

View 4 Replies View Related

Syntax Error -- Case Statement In Query

Jan 16, 2008

Hi,

I received syntax error for the following statement

SELECT
CASE [File Type]
WHEN 'Security' THEN 'SEC'
WHEN 'Admin' THEN 'ADM'
END AS FT
FROM [tbl_Core Non-Core]


please advise

thanks

Alice

View 3 Replies View Related

Queries :: Using Combo Box Case To Run Query And Generate Report

Jun 30, 2015

I am create a database to track leave of my team.I have create the tracker and few reports, however now I want to create a report by applying filters in 2 places, for associate and for leave type or for associate and for joint leave or for associate and for Informed.I have SQL code which does not work. how can I select the other paramater with associate name using combox box in form.

Code:

SELECT Leave_Records.[Absent Date], Leave_Records.[Associate Name], Leave_Records.[Absent Day], Leave_Records.[Leave Availed], Leave_Records.[Joint Leave], Leave_Records.[Informed TL], Leave_Records.[Leave Type], Leave_Records.[Leave Applied], Leave_Records.Comments
FROM Leave_Records
WHERE (((Leave_Records.[Associate Name])=Forms!Report_Form!AssociateName) And ((Leave_Records.[Leave Type])=Forms!Report_Form!Leave_Type)) Or
(((Leave_Records.[Associate Name])=Forms!Report_Form!AssociateName) And ((Leave_Records.[Joint Leave])=Forms!Report_Form!Joint_Leave)) Or (((Leave_Records.[Associate Name])=Forms!Report_Form!AssociateName) And ((Leave_Records.[Informed TL])=Forms!Report_Form!Informed));

View 1 Replies View Related

Modules & VBA :: Open In Main Form The Oldest Case Taken From Query

Jul 7, 2015

I need to make a query that will show the oldest cases and then open this one in the main form. It will be possible that there could be several cases with the same date. For example the query runs and the results are 7, how could I get one of those cases in the main form.

The idea is that a person will click on a button called "next case", the query will run and then it will open the case in the main form.

How to get this started?

View 7 Replies View Related

Forms :: Query Using Table - Indicate Previously Input Data In Case Of Duplicate Entry

May 30, 2015

table name : Schedule
Field 1 = Vessel code
Field 2 = Voyage
Field 3 = ETA
Field 4 = berthed
Field 5 = Sailed

there is a query by using above table and data entry form based on that query.

need to add following facilitate

While data entering, if given voyage number is already exist for the particular vessel code, msg should be pop up immediately at that time saying " This voyage number is already exist"

How could this be manage ?

View 4 Replies View Related

Multiple Fields Of Multiple Tables To One Table Query Or Report

Apr 12, 2013

I have 10 tables, 30+ fields on each table (every table has the same 'account number' field). I only need from 5 - 20 fields from each table. How do I get the certain fields from each table and put them in a table, query or report?

View 1 Replies View Related

Multiple Joins In Multiple Table Search Query

Sep 21, 2004

I am trying to create a simple Search form in Access where a user can select a desired record and query multiple tables using the inputs.

I would like them to be able to query Retailers, Distributors and Products.

The 6 tables are linked as follows:
Although some of these tables are not included in the query, they are required to ensure relationships.

Retailers -- Uses (RetailerID,DistributorID) -- Distributors
Retailers -- Orders (RetailerID,ProductID) -- Products

All retailers have at least one distributor BUT a retailer may or may not have ordered any products.

I have created my form but the query linked to the form is having some trouble. It is only selecting those records that have ordered products. For example, if I query a retailer name only and it does not have any ordered products, it will not display. Is there a problem with the table joins? The SQL for the query is displayed here:

Code:

View 5 Replies View Related

IF OK, Case Not OK

Apr 7, 2006

I have an Option Box that contains three Report Choices, Report1, Report2 and Report3. I want to run the Reports. I coded using If-Then and it worked out fine. See code below:

Private Sub Frame0_Click()
If Me.Frame0 = 1 Then
DoCmd.OpenReport "Report1", acViewPreview
ElseIf Me.Frame0 = 2 Then
DoCmd.OpenReport "Report2", acViewPreview
Else
Me.Frame0 = 3
DoCmd.OpenReport "Report3", acViewPreview
End If
End Sub

I would like to get the same result using "Case." However, when I use the Case format, it does not show all the reports as it does in the IF -Then format. See Case format below:

Private Sub Frame1_Click()
Dim Test As Integer

Select Case Test

Case Me.Frame1 = 1
DoCmd.OpenReport "Report1", acViewPreview

Case Me.Frame1 = 2
DoCmd.OpenReport "Report2", acViewPreview

Case Me.Frame1 = 3
DoCmd.OpenReport "Report3", acViewPreview
End Select
End Sub

Something simple is missing at the top. Please advise as to how to correct this?

View 3 Replies View Related

Case To Iif

Sep 28, 2004

Someone was helping me out and sent me the attached code to connect to a SQL server from Access2000 in a query. Access doesn't use the CASE statement, but the IIF statement instead. Can any of you help in deciphering the below CASE statement into an IIF statement?....

View 2 Replies View Related

Using Select Case

Jul 24, 2005

Can I use a [system number] value stored in a table in a select case?

Select Case [system number] "Tble system numbers"

Case 42144
do this

Case 88754
do this

???

Jon

View 6 Replies View Related

Need A Little More Help With A Select Case

Jul 26, 2005

I have this code in an event property on a report and it is working great. However, I now need to do a second case based on which modules are down. Is it possible to have a select case within a select case? I have the select case for the systems now with in each system I need a select case based on modules?


Dim dbs As DAO.Database
Dim rst AS DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Select * From YourTable")
While Not rst.EOF And Not rst.BOF
Select Case rst![System Number]
Case 42144
' do this
Case 88754
' do this
Case Else
' do this
End Select
rst.MoveNext
Wend
rst.Close
Set rst = Nothing
Set dbs = Nothing

View 4 Replies View Related

Case AutoNumber

Jan 1, 2007

How can I set up a table so that as we assign a case it generates a new number with the year in front of it. I would like for the case number to be generated by the program and when someone enter a new case the program generates the next case number and assignes it to that case. The format we use for our file numbers are year-XXXX (ex: 07-0001). With the new year I'm trying to set this up so that as we start the year the computer will generate the new number.

View 1 Replies View Related

Upper Case

May 2, 2007

Any ideas how to Capitalise data in a table.

View 14 Replies View Related

Case Statement

May 23, 2005

Hi,

I am trying to run the following query and getting an error. It looks like its because of the Case Statement.

SELECT Role.RoleID, Objects.ObjectID,
CASE MID(Objects.ObjectName, 4, 2)
CASE 'SR'
0
CASE ELSE
2
END AS AccessType
FROM Role, Objects
WHERE (Role.RoleID > 2115)

Can you give me suggestions plzzz

Thanks,

View 2 Replies View Related

CASE Instruction

Jun 28, 2005

Hi everybody,
I'm having quiete a problem with the case instruction. I have the folowing query:

Select station, count(case poc_1 when 1 then 1 end), count(case poc_1 when 2 then 1 end)
From poc
group by station


Well Acces tells me there is a syntax error in the case instruction???Can't find!!!Can anybody help? Tanx

View 4 Replies View Related

Case Keyword

Jun 29, 2005

Hi,

I'd like to know if the keyword CASE, or IF... is recognized in Access (sql) queries.

If not, wat should I do to implement a condition, in a query or in the source of a textbox in a report ?

The problem : I have a report that use this query in a calculated field, and I want to avoid results that are equal to zero (i.e.divising by zero).

Thanks :)

Twinpath

View 3 Replies View Related

Converting Iif To Case

Oct 10, 2006

Hi here is the original code

MAX(IIf([Book]='C',[Score]," ")) AS CScore,
Max(IIf([Book]='C',[PercentileScore]," ")) AS CPercent,
Max(IIf([Book]='C',[PassFail]," ")) AS C,
Max(IIf([Book]='D',[Score]," ")) AS DScore,
Max(IIf([Book]='D',[PercentileScore]," ")) AS DPercent,
Here is what I thought may work, but it seems like there should be an easier way
Case when Max ([Book]) = 'C' then [Score] else null as CScore,
Case when Max([Book]) = 'C' then [PercentileScore] else Null as CPercent,
Case when Max ([Book]) = 'C' then [Passfail] else Null as C,
Case when Max([Book]) = 'D' then [Score] else Null as Dscore,
Case when Max ([book]) = 'D' then [PercentileScore] eslse Null as DPercent,

Thanks
K

View 3 Replies View Related

Matching Case

Oct 24, 2007

I have two tables that have fields set to a text so that the ClientID is their name.

When I query, my queries don't take into account the case. So "K Smith" is the same as "K SMITH" as "k sMIth"

I am trying to write an unmatched query between two tables based on this ClientID but it will turn up no unmatched because it is not taking into account the case.

Any suggestions on how to match the cases, othere then changing the table?

Thanks.

View 1 Replies View Related

Case Statement

Dec 19, 2007

Hello, I'm attempting to use a case statement in order to select fields from a column that have names and addresses mixed. I'm wanting to select only the fields that contain names. For example some fields start with an address of "1998 Sky Rd" or PO BOX, or Suite at the beginning of the field. I would like to first select all fields with names in the field and then use an update statement to move/switch fields to another column. The only problem is that I can't seem to get this query to work. Please help!!!

Thank you!!!!!!!!!


SELECT address1, name3=address1 as expr1

Case [address1]

When mid([address1],1,1) Like [A-Z] then [address1]
When [address] is null then ""
When [address] = "" then ""
Else 'Null"
End,

FROM Exercise1;



:confused:

View 9 Replies View Related

Title Case

Mar 21, 2005

i have a form, and i would like that on one of the fields, as soon as i type something in it when i leave the field, i want it to automatically convert it to title case. can this be done please?

Cheers

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved