Modules & VBA :: Field Update Using Function With Case
Jun 10, 2015
I'm trying to update some field value depending two string field using a function with if and case, function below:
Code:
Private Function checkDATI(tipotransazione As String, tipovendita As String) As String
Dim r As String
r = ""
If tipotransazione = "VENDITA" Then
Select Case tipovendita
[code]...
And then when I call this function in a command button event as:
Code:
MsgBox (checkDATI(Me.CausaleMov, Me.txt_tipomov))
It's not update those field.
View Replies
ADVERTISEMENT
Aug 17, 2014
My problem now is about evaluating the result of that query in order to calculate quarterly and annual performances. The query returns correctly:
1) NULL - when I don't have values in the given month
2) 0 - when one of the components of the performance formula is 0
3) value (positive or negative) which can include any positive or negative value
In order to calculate the quarterly values I need the performance of the last month in the quarter and the first month in the quarter. My monthly values are stored in columns so say for Quarter 1, I would need something like this:
Code:
[mar]/[jan]-1
As you might have thought, the problems come when I have NULL or 0 values. Let me give you a couple of examples.
[jan] is NULL
[feb] is a value
[mar] is a value
Than my formula would have been
Code:
[mar]/[feb]-1
Second case:
[jan] is 0
[feb] is 0
[mar] is 0
In this case I would like the formula return '-' because I want to be able to spot the case in which the quarterly performance is 0 because the initial values were 0 from the case in which the performance was actually 0 (i.e., say [jan] = 101 and [mar] = 101 then performance is 0 which is different from having [jan] and [mar] = 0 thus performance '-').You can combine the three states (NULL, 0, value) with the three months and find many combinations (I have found 27 relevant ones).
I initially thought to use SWITCH in SQL to evaluate the 27 combinations but I found out that SWITCH would evaluate non more than 15 conditions. So I guess the solution should be use a function in VBA which does what I was about to do with SWITCH in SQL.
I have built a test function which evaluates the numerator of my formula using only two cases (CASE 1: all variables have a value <>0, and CASE 2: the first month is null, the second is 0 and the third a value). Here the code:
Code:
Function evaluate_s(Var1, Var2, Var3) As Double
Select Case Var1
Case Is <> 0
[code]....
My main question here is what is wrong in the syntax of my function. Why is not correctly evaluating at least the two cases in my function? I always get 0 for all records.
View 8 Replies
View Related
Jun 4, 2014
I have an issue with this case select below. The DelayStart is time so lets say I put in the debug window
?DatePart("h", #04:00pm#)
The result would be 16 which is correct BUT the issue is my second shift starts at 04:01pm and the result is still 16 untill 5pm. How do I fix this so I get the correct shifts? Im guesing use something other than DatePart but what?
'Daylight 6:00am - 4:00pm
'Afternoon 4:00pm - 2:00am
'Midnight 2:00am - 6:00am
Code:
Public Function getShiftForRecord(DelayStart As Variant)
On Error Resume Next
Select Case DatePart("h", DelayStart)
Case 6 To 16 'Daylight 6:00am - 4:00pm
[Code] ....
View 11 Replies
View Related
Jan 3, 2014
I would like to select a case depending on the output of a function.
This function tests the syntax of the reporting month.
If the syntax is fine nothing should be done further in the main sub else it should return to the Input window for the reporting month.
Somehow it doesn't work out.
Code:
Public Function RepMonthCheck(rep_date As String) as Boolean
If Len(rep_date) <> 6 Or Left(rep_date, 2) > 12 Or Left(rep_date, 2) < 1 Then
MsgBox ("Reporting rep_date is not in the correct format = mmyyyy")
Return False
ElseIf Right(rep_date, 4) > 9998 Then
MsgBox ("No forecast available for year 9998")
[Code] ....
View 3 Replies
View Related
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
Feb 4, 2014
I'm trying to use select case on the 1st two digits of a field. I f it equals 1- then the case is meet but its not working. Will this work or no?
[CODE Case (Left(Me.Step42, 2) = "1-")][/CODE]
View 10 Replies
View Related
Oct 1, 2014
I am trying to add various values based on Select Case to the value of field. The problem I face is that each time when I get different Case in select statement, the value of the field rather changing adds the value on top.
Code:
Private Sub ProductID_AfterUpdate()
Dim qflPrice As Variant
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sqlQry As String
Dim instID As Integer
[Code] .....
View 5 Replies
View Related
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
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
Jun 16, 2013
I have this code below that is working however the calculation are updating on my form late.
Basically, I have some calculation that are performed on a "After Update" event on some controls on my form. I wanted it to do the calculation after I update the control on the form.
The code is in a module and I just call the function after update on the control But the form is not updating when I change the value in a field. I have to change the field again for it to update.
Control
Code:
Call GeraAuditCalc
DoCmd.RefreshRecord
Function
Code:
Function GeraAuditCalc()
Dim fHrs As Double
Dim Ttl As Double
Dim Ttl1 As Double
Dim Ttl2 As Double
[Code] ....
View 4 Replies
View Related
Nov 21, 2013
Would I use an update query or function to complete my task?
Task: Automatically update [Status] based on DateDiff calculation of [RequestDate] and [DueDate]
Issue: [Status] can be one of 6 values, the user can manually select Review or Completed.
[Status] values: Review; Completed; Due in 24; Due in 24-48; Beyond 48; Over Due
Query: Below is the query I just started to ignore the calculation if either Review or Completed exist.
Expr1: IIf([Status]="Review",[Status],IIf([Status]="Completed",[Status],DateDiff('d',[RequestDate],[DueDate])))If the value from above DateDiff equals to the below, I want to update the [Status] field to the [Status] value.
Due in 24 = (0-1)
Due in 24-48 = (2-3)
Beyond 48 = (greater than or equal to 4)
Over Due= (-1)
View 14 Replies
View Related
Dec 16, 2013
I have a small issue where I do have names written in UPPER CASE, I would like to transform them in Capitalized (each word), I understand that I cannot use the same function as in excel (PROPER) but something like StrConv, but as I do have a ‘comma’ in between the names I have something like that
Code:
StrConv(Left(Tbl_Telephony_Temp.Agent, InStr(Tbl_Telephony_Temp.Agent, ",")-1), 3)&", "&StrConv(Mid(Tbl_Telephony_Temp.Agent, InStr(Tbl_Telephony_Temp.Agent, ",") + 1), 3) AS CompleteName
Which as you can see is quite a complex statement, how can I simplify it?
Example of a name: VITOR, FONSECA, preferable output, Vitor, Fonseca
View 14 Replies
View Related
Jun 25, 2013
I'm still learning Access 2010 and having issues getting my buttons to work. I'm working on a simple address database.edit/save button. On form load, my fields are locked and my button will read "edit". After clicking, my fields are unlocked, my search features are locked, and my button reads "save" just how I need it to. The issue happens when I try and save the field edits, lock fields, return search features, and get button to read "edit" again. I know I need to add some code into what I already have, but I'm running into a wall as I have tried many options to get it to work. Here is the code for this button:
Code:
Private Function Lockdown() 'locks controls at load
Dim tb As Control
Dim cb As Control
Dim subf As Control
[code]...
Second issue is with my report button. I have not been able to get this to work once. I have done many searches on single record reports, and have found the same code every time. I added that code into my database, but can't seem to get it to work. In my database there are two address (shipping and work location) which I would like to print out together. I have the work location on the main form and the shipping on a subform. There are and upwards of 150 locations I will have in my database, Here is the code I'm working with:
Code:
Private Sub cmdrptadd_Click()
Dim strReportName As String
Dim strCriteria As String
[code]...
View 3 Replies
View Related
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
May 11, 2015
I have the following fields in an A2010 form
PaymentMethod
Total
InstalmentAmounts
OurFee
InstalmentAmounts field is populated based on the results of PaymentMethod which is a combo box
Here is the current code
Select Case Me.Payment_Method.Value
Case "Three post dated Cheques"
'If three payments by cheque
Me.InstalmentAmounts.Value = Round(Me.Total.Value / 3, 2)
In some cases there will be a few pennies that the client "overpays" because the instalment amounts will not add up to the Total. I need to add up the TotalInstalmentPayments to find out what the client actually pays and then add the difference between TotalInstalmentPayments and Total to the OurFee field
So
Total = 500.00
Payment Method = 3 instalments so
IstalmentAmounts = 166.67 (rounded) so client pays 500.01 so the 0.01 needs to add to the OurFee field.
View 4 Replies
View Related
Jan 22, 2014
I have a check for lots of different data in a string and wondered if i can use CASE or similar.Sample code reads...
If InStr(1, Me.txt_sp, "Give & Take", 1) Then TM = "Standard"
If InStr(1, Me.txt_sp, "Give Take", 1) Then TM = "Standard"
If InStr(1, Me.txt_sp, "give and take", 1) Then TM = "Standard"
If InStr(1, Me.txt_sp, "Give and", 1) Then TM = "Standard"
If InStr(1, Me.txt_sp, "priority working", 1) Then TM = "Standard"
If InStr(1, Me.txt_sp, "priority boards", 1) Then TM = "Standard"
If InStr(1, Me.txt_sp", 1) Then TM = "2 Way"
View 12 Replies
View Related
Dec 16, 2013
Basically I want to check if the Reporting Month already exists in the Months Table. If it exists it should ask the User wheter to overwrite or not. If the User clicks on OK it should delete one record from Months Table if the User clicks no, nothing should happen.However it doesn't delete the record, when I click on OK.
Code:
Public Sub IMPORT_Click()
Dim filelocation As Variant
Dim f As Object
Dim Message As String, Title As String, Default As String
Dim sql As String
Dim MsgReply As Integer
[code]....
View 1 Replies
View Related
Oct 22, 2014
How to Make an empty field schedule database ACCESS default text in case the field is empty ....
Default text like 12345
View 12 Replies
View Related
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
Jul 31, 2013
I want to know if it's possible to make a dynamic select case. In my form I have 3 separate combo boxes. What I want to happen is depending on what was selected in one the options in the other two change and if you select something in the second the option in the third narrows down again. The user can select these in any order. They can use one by itself or all three independently. In my code I can do this with many select statements, e.g
Select Case Me.combobox1.Value
Case "choice1"
Forms!CurrentForm!combobox2.RowSourceType = "Table/Query"
Forms!CurrentForm!combobox2.RowSource = "SELECT fieldname FROM tablename WHERE fieldname = '" & Me.combobox1.Value & "'"
End Select
In the place I have "choice" is it possible to write something along the lines of,Me.combobox1.value = "SQL Code"..The idea that this code would still work if the user adds more data to the tables which these combobox choices come from.
View 6 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
Nov 20, 2014
When I try to run a simple update query written in VBA I get no errors but the field that I am trying to update just gets a 0 instead of the appropriate ID that I am passing.
Code:
Function ExtractProjects()
On Error GoTo ErrHandler:
Dim db As Database, rs As DAO.Recordset, rs2 As DAO.Recordset, var() As Variant, i As Long, qdf As DAO.QueryDef, ii As Long
Set db = CurrentDb
[code]....
The funny thing is if I run the query itself it works by using a parameter query but when I try and run it like this all I get is 0's in the field that it's supposed to be updating.The field [(SDSK) Charges Master].PID properties are as followed and is a linked table:
Type: Integer
Indexed: Yes (Duplicates Allowed)
A debug.print of the results of the query trying to be ran is the follows:
Code:
UPDATE [(SDSK) Charges Master] SET [(SDSK) Charges Master].PID = 1 WHERE ((([(SDSK) Charges Master].[IBB Date]) Between #10/24/2014# And #11/19/2014#) AND (([(SDSK) Charges Master].[Charge Num]) Like '*BAA*' And ([(SDSK) Charges Master].[Charge Num]) Is Not Null));
As you can see it is providing a number to be set to but instead it just fills it in with a 0.
View 4 Replies
View Related
Apr 25, 2005
I have a drop down list box on my form called FieldA. I would like for FieldB to update with a number depending on what is in FieldA. For example, if FieldA has Red, I would like FieldB to return a 2. I know this needs to have a case function done, but how do I get it linked to FieldB?
View 2 Replies
View Related
May 29, 2007
Hi
I have imported a large contacts table from an old database. The Name field has had all the names input as JOE BLOGGS etc. I can force the table to read joe bloggs etc using < in the format box in properties for this field. But I want the names to read in the Joe Bloggs format, first letter in each name being upper case. Is there an easy way of doing this.
Just got back in to databases after 15 years so wee bit lost at the moment.:o
regards
TP
View 1 Replies
View Related
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
Feb 2, 2014
I have two queries called "Query_match_AND" and "Query_match_OR". I want to show the total number of records for either of these queries in a form called "form_candidates_result" depending upon the combo box value (either "AND" or "OR") in a form called "form_match".If I put
Code:
=DCount("*","Query_match_AND")
in the control source of [candidatecount] in "form_candidates_result", the value is shown in the form and it works fine.If I use the following code in either of the "on load" or "on open" events in "form_candidates_result"...
Code:
Select Case [Forms]![form_match]![ANDOR]
Case "AND"
Me.CandidateCount.ControlSource = DCount("*", "Query_matching_AND")
Case "OR"
Me.CandidateCount.ControlSource = DCount("*", "Query_matching_OR")
End Select
it doesn't work and I get
Code:
#NAME?
View 7 Replies
View Related
