Case Statment To If Statement

Apr 29, 2008

Here is my CASE WHEN statement in SQL. What would the If statement be in MS access?

CASE WHEN LEN(Serial) = 9 THEN Serial
WHEN LEN(CAST(Serial as Varchar(255))) = 8 THEN '0'+Serial
WHEN LEN(CAST(Serial as Varchar(255))) = 7 THEN '00'+Serial
WHEN LEN(CAST(Serial as Varchar(255))) = 6 THEN '000'+Serial
ELSE 'Error'
END
AS NewSerial

Thanks for your help!

View Replies


ADVERTISEMENT

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

CASE Function Or IF Statement

Feb 28, 2006

Hi everyone, please help, i am in depserate need here. Am i right in thinking nested IF functions are limited to 7 variables, i need to choose from 12, so i need to use something different

it seems like a CASE function could do what i want it to, but i really have no clue where to start, shall i enter this into a query criteria box, or on the form itself ina new field box...??

Can you give a few hints as to what the code would look like, i have two fields, obviously. One is 'lesson_type' and one is 'cost' i want the cost to change depending on what is selected in the lesson type box.


LESSON TYPE ----------- COST

Beginner 1hour ----------- 15
Beginner 2hour ----------- 29
Beginner 3hour ----------- 42
Test Retake 1hour -------- 13
Test Retake 2hour -------- 25
Test Retake 3hour ---------36
Disqualified Retake 1hour -- 25
Disqualified Retake 2hour -- 49
Disqualified Retake 3hour -- 72
Advanced 1hour ---------- 10
Advanced 2hour ---------- 19
Advanced 3hour ---------- 27

Is it possible to do that, change the cost correpsonding to what one is entered in lesson_type...??? And where do i put it??? What do i do with it??

Thanks, dragon2309

View 4 Replies View Related

CASE Statement In SELECT

Mar 21, 2007

Does anyone know if its possible to perform a CASE Statement in a SELECT statement in Access and if so what the syntax is? Thanks in advance.

View 9 Replies View Related

CASE WHEN Statement Problem

Aug 8, 2007

I am trying to make a field in a query which displays a date based off of the criteria below. I tried nesting IIF statements but I couldn't get it to work so I tried a Case WHEN statement.

CASE
WHEN [Appfrom] Is Null & [Fundfrom] Is Null THEN "7/7/1999"
WHEN [Appfrom] Is Null & [Fundfrom] IS NOT NULL THEN [Fundfrom]
WHEN [Fundfrom] Is Null & [Appfrom] IS NOT NULL THEN [Appfrom]
WHEN [Fundfrom] IS NOT NULL & [Appfrom] IS NOT NULL THEN "7/7/1999"
END

Here is the IIF statement

IIf(IsNull([Appfrom] And [Fundfrom]),"7/7/1999",(IIf(Not IsNull([Appfrom]) And IsNull([Fundfrom]),[Appfrom],(IIf(IsNull([Appfrom]) And Not IsNull([Fundfrom]),[Fundfrom],"7/7/1999"))))))

Neither one works and have no idea how to fix it. Any ideas would be greatly appreciated. Thanks.

View 4 Replies View Related

Sql Case Statement In Access??!?

Jan 12, 2006

hey
Is it possible to use sql case statements in access ?
I have used them in sql server all the time and they are really useful!

I mean statements like this one:

"SELECT iOrderID,dOrderDate,iTotalBeforeVat,iShipTotal,iVa tTotal,iTotal,
CASE sOrderStatus WHEN '9' THEN 'Canceled' END FROM tTmpOrderRpt"

thanks!

View 4 Replies View Related

Not The Best At Case Select Statement Writing...

May 14, 2007

Can someone simplify my feeble scratching at a Case Select statement, please?

I have two fields in a table (True/False): IsActive and IsDefault

I would like to convert some If...Else...Then statements to a Case Select statement which details what should be done when any of the possible states of the two fields exist.

Semi-Psuedo example in the (subform) Form_Current Event:
With Me
If !IsActive = True And !IsDefault = False Then
Do Something on the Parent form
ElseIf !IsActive = True And !IsDefault = False Then
Do Something Else on the Parent form
ElseIf !IsActive = True And !IsDefault = True Then
Do Something Else Again on the Parent form
Else
Do Something Entirely Different
End If
End With

What blows my mind is the Select Case Expression bit. I can't figure out how to write this. Any help is greatly appreciated.

View 6 Replies View Related

Select Case Statement - General Q

Feb 6, 2008

If I write a select case statement for a field X i.e. 6 to 7 for example will this:1. Select any fields X where the number is greater than or equal to 6 but less than (and not including) 7B. Select any fields X where the number is greater than or equal to 6 but includes decimels of 7 i.e. 7.5 ORC. Select any fields X where the number is greater than or equal to 6 but includes but literally only includes cases where the the whole number 7 appears>I only ask because in an example in a book I am looking at says:Case 10000 to 20000......Case 20001 to 30000I would have through the word "to" means until but not including but in this case the field that equals 20000 would never be selected?

View 14 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

Modules & VBA :: Using Select Case Statement With Array?

Jun 26, 2014

I have a boolean array, foundState(3), whose 4 elements correspond to 4 variables describing conditions that will dictate what action is taken upon closing a form.

There are only 6 possible outcomes for the array, and they can be divided into just 4 cases:

Case {T,T,T,T}

Case {T,T,T,F} OR {T,T,F,F} OR {T,T,F,T}

Case {T,F,F,F}

Case {F,F,F,F}

What the proper syntax would be for this if I'm trying to create a "Select Case" statement for these 4 cases.

View 7 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

Modules & VBA :: CASE Statement - Display Specific Text In A Field Based On Value Of Another

Sep 22, 2014

I have a lengthy CASE statement in my database that displays specific text in a field based on the value of another. Simple stuff but for some reason it randomly will not work on certain values, and never the same one twice. Is there a commonly known cause for this? I have verified that the spelling and spacing etc. are correct in my code so that shouldn't be causing the problem.

View 2 Replies View Related

Queries :: SELECT CASE Statement - Populate Each Unit With Assigned Category?

Apr 9, 2013

I have a simple SELECT CASE query. I'm not sure how the syntax goes and I want to learn about it.

In the attached file, if you click on "cohort table", you will see the categorization for each unit under "field1".

how will I be able to use a SELECT CASE statement in a query to populate each unit with assigned category?

View 1 Replies View Related

Modules & VBA :: Create Disable Alphabetic Keys Function Based On Case Statement

Jul 31, 2015

Details:
I have a Profile form that tracks the expiration date for each client's various certifications. These dates are set up in the Short Date format in the table design of Access.

Problem:
When a user accidentally presses an alphabetic key while updating an expiration date, an Access error message is triggered. This is confusing to my users as these messages are written in Access lingo. I would rather that nothing occurs at all. I wrote a case statement to disable each letter of the alphabet and applied it to the On Key Down Event for each expiration date control on my Profile form to solve this problem, but this must be applied to 28 separate controls. I would rather call a function that disables alphabetic keys for each date control in my form when called.

Questions:
How do I transform my Disable Alphabetic Keys Case Statement into a function that I can call for each expiration date control? I know that when writing a function certain variables have to be declared and/or initialized.

Also, will I need to create a function to re-enable alphabetic keys or is this unnecessary because the disable alpha keys function will only be called for specific controls, not the entire form?

What I Have Tried:
I have tried copying and pasting my Disable Alphabetic Keys Case Statement into a module to attempt to create a function, but it needs work.

Below I have included 2 types of code:
(1) The original On Key Down code applied to each date control on my form
(2) The same code written as an attempt at a function

Original Profile Form Code to Disable Alphabetic Keys in the On Key Down event for each date control

Private Sub txtCert1ExpDate_KeyDown(KeyCode As Integer, Shift As Integer)
Select Case KeyCode
'All message box text is for me to test the code, not for the user to see
Case vbKeyA
MsgBox ("you pressed the A key")

[Code] .....

View 14 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

SQL Statment

Sep 7, 2006

I have typed this SQL in a query; however, access doesn't seem to like it. Does anyone have any idea why please?....

View 13 Replies View Related

If Statment

Dec 28, 2006

I am having issues with a multiple option on my If Statement. I do not want to create and If statement for each if I can combine what I want it to do.

If [cboType] = "Class II with 16K / 16K" Or "Class III with 16K / 16K" Or "Class III with 32K / 16K" Or "Class III with 32K / 32K" Or "Class IV" Then

The code just die when it is combined. Is OR and option.

Thanks

View 3 Replies View Related

Sql Statment

Oct 15, 2004

Dim mysql
mysql = "insert into [OwnerOccupierDetails] (owner_no,owner_name,owner_address,owner_phone,own er_confirmation,owner_reps,owner_reps_planner,owne r_herd_no,owner_area_status,owner_date,owner_detai ls,occupier_no,occupier_name,occupier_address,occu pier_phone,occupier_confirmation,occupier_reps,occ upier_reps_planner,occupier_herd_no,occupier_area_ status,occupier_date,occupier_details,source_name, details) VALUES ('" & Me.[txtownerNo] & "','" & Me.[txtownerName] & "','" & Me.[txtownerAddress] & "','" & Me.[txtOwnerConfirmation] & "','" & Me.[txtOwnerReps] & "','" & Me.[txtownerRepsplanner] & "','" & Me.[txtOwnerHerdNo] & "','" & Me.[txtOwnerAreaStatus] & "','" & Me.[txtOwnerDate] & "','" & Me.[txtOwnerDetails] & "','" & Me.[txtoccupierNo] & "','" & Me.[txtoccupierName] & "','" & Me.[txtoccupierAddress] & "','" & Me.[txtOccupierPhone] & "','" & Me.[txtOccupierConfirmation] & "','" & Me.[txtOccupierReps] & "','" & Me.[txtOccupierRepsPlanner] & "','" & Me.[txtOccupierHerdNo] & "','"
& Me.[txtOccupierAreaStatus] & "','" & Me.[txtOccupierDate] & "','" & Me.[txtOccupierDetails] & "','" & Me.[txtSourceName] & "','" & Me.[txtDetails] & "');"


DoCmd.RunSQL mysql


im writing this really long query in vb so it needs to go on two lines, but whats the syntax for going on the next line(theres a quote or something but i cant remember for carrying on from one line to the next)

View 6 Replies View Related

SQL Statment Failure

May 2, 2007

Hello again all... Today's problem is as follows.... I have an "after update" combo box that is coded as "CurrentDb.Execute "uno", dbFailOnError". I have an SQL Query named "uno" that is showing the following: "UPDATE [Input] SET [Input].Loc_Lng = [Loc-1L].Combo8, [Input].Loc_ID = "1", [Input].Loc_Desc = "xxxx";" I am trying to have the combo box update the values from the combo box into a blank table. When I try to run this, it comes up with the error: "Too few parameters. Expected 1". Any idea what that means, and then how to fix?

View 2 Replies View Related

Date Statment

Aug 15, 2006

Hi there!

I've got the following code on my database:

strcriteria = "[date] = #txtDay1.value# "

Set rst = dbs.OpenRecordset("tblPlanner", dbOpenDynaset)

rst.FindFirst strcriteria
The problem is when I run the code it have the following error:
Run time error '3077'
"syntax error in date in expression"

I've formated boths date field and txtday1 on short date.
Is there anyone that could tell me what wrong am I doing?

Thanks

Jonybravo

View 3 Replies View Related

SQL Insert Statment

Sep 29, 2004

I am very new to ASP

I would like to learn how we can insert a data from a web form into MS access file.

This is the situation

Well i have a db.mdb file located at c:/www/db.mdb

db.mdb contain only one table that is named as students which in turn contains only three columne i.s StudentsID , FirstName and LastName

StudentsID is autonumber field in db file and FirstName & LastName are text fields

Now i have a self.asf file where i have a form with only two input fields

i.e
<form action=self.asp method=post>
<input type=text name="first" value="">
<input type=text name="last" value="">
<input type=submit value=submit name=submit>
</form>

Please let me know how i can write a SQL insert statement and database connection so that i can insert the form data into the db.mdb file

With Regards

View 2 Replies View Related

Help With Inset Statment

Jan 12, 2005

I have a select statement that returns information into a sting
Then I try to insert this value into a table but I am not sure of the correct syntax. The SQL select statement works but not the insert into...

sRequirement1 = category1.value & ">" & subcategory1.value & ">" & Me!Label1.Caption
selectRequirementid1 = "SELECT Requirements.RequirementID " & _
"FROM Requirements " & _
"WHERE Requirements.RequirementName = '" + [sRequirement1] + "' " & _
"AND Requirements.Area = '" & Me.AArea1.value & "' " & _
"AND Requirements.Level = '" & Me.level1.value & "' "

DoCmd.RunSQL "INSERT INTO Hours ([RequirementID1]) VALUES( " & selectRequirementid1 & ");"

not sure how to treat selectRequirementid1 in Insert into statement

I tried the following yet non work...
'" & selectRequirementid1 & "'
'" + [selectRequirementid1] + "'

View 12 Replies View Related

Insert Statment

Nov 8, 2006

I was wondering if anyone knows how to insert more than one record at a time with a SQL statment? I have a form that asks the person how many records they would like to put into the system, this is a text box that they can enter a number.

My SQL for input one record is this:
[CODE]

SQL = "INSERT INTO tblTemp ([Name],[Number]) VALUES (TName,Len)"

DoCmd.RunSQL SQL

If the person wants to put in more than one I want the system to take the Number field and add 1 to it for ever record the person asks to be put into the system. the Name and Number fields will be that same except the Number field will increase by 1 for however many they choose to put in.

View 11 Replies View Related

Using Text Boxes In A Between Statment

Nov 20, 2007

Hello guys and Girls

I am trying to setup a query that will return only the records in a date range that is user specified. In the Criteria of the date field I entered this

Between [Forms]![Report Switchboard]![txtStartTrend] And [Forms]![Report Switchboard]![txtEndTrend]

But the query does not work. Is the syntax correct? Both txtStartTrend and txtEndTrend are format Short Date.

Thanks for any Help

Anthony

View 1 Replies View Related







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