General :: How To Use IsNumeric Function
Jul 30, 2012
I want to make my Courseid field visible in my form based on whats entered in my StudentID Field.I've used the following code but i'm getting an "Else Without If" error:
Code:
Private Sub Form_AfterUpdate()
If Me.studentID = "Pending" Or "Rejected" Or "pending" Or "rejected" Then Me.Courseid.Visible = False
Else: Me.Courseid.Visible = True
End If
End Sub
If the studentID is a number then i want the course to become visible but i don't know how to use the isNumeric function this way.
View Replies
ADVERTISEMENT
Jun 13, 2013
I want to run a function when my application closes. But it must run in all cases, so running it on the last form, or in a hidden form, is not good enough. Even if I'm in design mode with no forms loaded, I want it to run.
For background info - my app is locked down tight against clients poking around. I have got a way for me as developer to get into the "back door", and what I'm trying to do is make sure when I'm finished doing my stuff that the app is locked again without me having to remember to do it.
View 8 Replies
View Related
Dec 4, 2012
I'm trying to add a couple of buttons to the QAT to run public sub's saved in modules within the DB but for some reason they are not showing when I try to customise the toolbar?The sub's are fairly straightforward (they just open forms) and are saved in a module within the project :
Code:
Option Compare Database
Option Explicit
Public Sub NewEntry()
DoCmd.OpenForm "DataEntry"
End Sub
[code]....
But when i try to add them to the QAT, they are not visible?
View 1 Replies
View Related
Jun 4, 2015
I want to make a loop like this:
Dim var1 As Variant, var2 As Variant, var3 As Variant, var4 As Variant
DIm var5 As Variant, var6 As Variant, var7 As Variant, var8 As Variant
Dim var9 As Variant, var10 As Variant
Dim i1 As Long
i1 = 1
Do Until i1 > 10
var & i1 ??? = "0" & i1 & "." & txt1 & "." & txt2
i1 = i1 + 1
Loop
How to make concatenate var + i1 to make loop function?
View 10 Replies
View Related
Oct 10, 2014
Is it possible to compare rows, ie in excel I could do =A1=A2 to compare certain fields then if they are different/the same highlight.Is there a way of doing this in access?
View 1 Replies
View Related
Oct 5, 2012
The following search operation have to search some operation in the database, but there is a bug and I don't know why..
View 4 Replies
View Related
Oct 28, 2014
I now try to rework on an old project but when I try to run some queries I get an error message: Undefined function 'Date' in expression . The same happen for the Format function.Both are built in Access functions
View 5 Replies
View Related
Dec 24, 2012
I have a ICT exam on databases in January for A2 ICT. It is based on a sixth form college. I need to add new units for the courses via a form, with the unit being uniquely identified using a Unit Code
The Unit Code consists of the first 3 letters of the Subject Code, the first 2 letters of the Description of the unit and a number.
Eg Subject ID : ARA001 and Description: Culture will have a Unit code of ARACU1
I'm trying to find a way to automatically create the unit code on the form. The first two bits have been solved fine,however, i have had difficulty of putting the number of the end. It seems easy in practice as i need to find a Unit Code like the one i have on the form but find the maximum number of a identical code and add one to it. I thought if i did
Like "[Forms]![frmaddnewunit]![txtUnitCode]*" as the criteria on a query with the field being Unit Code from the Unit table it would find data like the one on the form but it doensn't.
I thought i would use the data from the query and use Max(Right([UnitCode],1))+1 to get the number and add this to the code in the unit
This formula on the unit code text box on the form solves the first two bits of the problem
=Left([cboSubjectID],3) & UCase(Left([txtDescription],2))
but i need to find a way to find unit codes thsat exist similar to the ones that are created and then find the maximum number and add one to it so i have a valid unit code
e.g
if on the form i select Subject ID ARA001 and Description Culture
The unit code will display ARACU.
I then need to find unit codes similar to this (There is one that already exists ARACU1) and find the max number on the end and add one to it
So ideally the code that will be automatically generated in this case would be ARACU2
View 2 Replies
View Related
Jun 27, 2012
I have a function which I want to return the value of a field.
Public Function fieldValue(tblName As String, fldName As String)
Dim drs As Recordset
Set db = CurrentDb
Set drs = db.OpenRecordset(tblName)
fieldValue = drs.Fields(fldName).Value
drs.Close
Set drs = Nothing
End Function
I need to return the value obtained, for a specified User identified with a numeric variable.What is the best way forward? - Some sort of filter or DLookup and how to code this?
View 14 Replies
View Related
Aug 20, 2014
I am working on recreating an excel file in access. My excel files uses the Vlook up function to get data from a table for calculations.I am calculating Fruit Solids.
=VLOOKUP(BRIXVALUE,'Brix Table'!$A$1:$F$752,6)*QUANTITY
(BRIX VALUE references a cell, and quantity references a cell just used the names instead of cell references).The Brix table has 751 entries. The headers for the following tables are "Brix, Gravity, Weight, GFL, LBS, FS/Gal".So the look up table takes the BRIX value, finds it in the first column then takes the value in the 6th column(FS/Gal) then multiples that by the Quantity inserted in the other table. I didnt create the Vlook up access form so I am trying to get an understanding and recreate it in access.
In access i made a table called BRIX to store the Brix table information like the one in excel.Then i have fields in a different table where you enter the quantity, and brix value of the product. I now need to make the Vlook Up function in excel work for my access data.
View 4 Replies
View Related
Apr 23, 2015
I have created inventory databases for both my job and personal use. I do not understand coding. I either need a good hand-holding with simple, detailed instructions or I need to hire a code person.
A button in my form that can be coded to ... When clicked ...
___ open a browse window of hard drives,
___ allow user to choose any single image for selection,
___ copy & paste selected image's location to a table field.
NOTE: Table field will be used to connect to image place holder on form with control source to make the image seen.
View 8 Replies
View Related
Aug 14, 2013
Which function in access return the the last value in a string.
Period 1 Period 2 Period 3
10 20 30
I need the function to return 30.The reason behind this I have different periods for categories and my formula I'm using needs the ending value.
View 1 Replies
View Related
Jun 10, 2015
I need an Access 2010 function to enable/disable screensaver on W8.1
View 1 Replies
View Related
Jun 12, 2014
How to amend the below, so that instead of doing 4 days, it does 4 working days?
<=DateAdd("d",4,Date())
View 5 Replies
View Related
Jul 13, 2012
I want an example of choose function to write in expression builder in access 2007 ..
I have a size as 1/4,2/3,4/9 etc to be converted to ABC,DEF,GHI etc . i have nearly 40 entries so i think choose statement would do it .. moreover i cant find switch statement in expression builder .......
For example one could be to replace the text ..... or any other method to change the size to alphabetical grade.
View 4 Replies
View Related
Aug 19, 2013
I have loads of projects in a table, they all have a status (eg Red, Green, Amber).
I have a query for each so if you run qry_RagRed, it will show in a sheet all Red projects and obviously have a total in there.
So I have a form which has buttons on it for navigation, what I want is to display various things on there so for example, a field that simply has the number of red projects, or green etc.
All I want it to do is tell me a number based upon number of items in a query.
View 5 Replies
View Related
Nov 8, 2012
I'm relavtively new to databases but after a steep learning curve (being left with many databases created by my old manager with no instructions on use) I'm getting there.What I am trying to do is pull out the date from records in a field called "Model_ID". The Model_ID field contains this kind of information:
Endscopy20120726JSmith
GISurgery20120521JDoe
I want to bring back "20120726" or "20120521". In excel I can do this with the mid and find functions but find doesn't work in access. I've tried combining the mid function with instr function but it comes back as too complex!
View 10 Replies
View Related
May 25, 2013
I looked for missing references after I had received this message. List of available references had 4 selected items:
1) Visual Basic for Applications;
2) Microsoft Access 15.0 Object Library;
3) Microsoft Office 15.0 access database engine library
4) OLE Automation;
I tried to check off the 3rd and the 4th one, but it came to nought (error didn't dissapear). What else can I do ?
View 6 Replies
View Related
Sep 17, 2013
I created a Public Function called fNetWorkDays in my access database, however, when I try to use it in an expression I get "Undefined function 'fNetWorkDays' in expression". The Public Function is in a standard Module in my vba project. Why I cannot call it in my queries?
View 3 Replies
View Related
Jul 18, 2014
I have a string where alphabetical and numerical characters are mixed up. I need a string function where I can find the index of the first non-numerical character. I tried with the mid function but could not get it to work.
View 13 Replies
View Related
Sep 2, 2013
I have a problem in the copyfile function. It says "CompileError - Expected =" and I am not sure if I am even setting this correctly. This is my code:
Dim fso As Object
Set fso = VBA.CreateObject(filesystemobject)
fso.copyfile(me.txtPath,me.txtarchivepath & me.txtnewfile)
I think the problem is the "filesystemobject bit in the code ....
View 10 Replies
View Related
Dec 3, 2012
I am using VBA for MS Access 2003. I left this project since 4 years and I know back to it as mass needs forced me to use it again.
I use trim function. it raises the compile error: can't find project or library. I know that the solution is by adding the library in the References item from the Tools menu. Thus What are the minimum libraries that I should add so that these basic problems solved. By the way: I already added the Microsoft Visual Basic for Applications Extensibility 5.3.
View 1 Replies
View Related
Jan 10, 2013
I have created user defined function for lastdayofweek, lastdayofmonth, and firstdayofmonth. When I apply this function to an expression in my query, it returns a julian date. How do I apply the format for the calendar date?
Maybe I am not calling my function correctly. Here's my functions below as well as my expression..
Function LastDayThisMonth()
LastDayThisMonth = DateSerial(Year(Date), Month(Date) + 1, 0)
End Function
Function FirstDayThisMonth()
FirstDayThisMonth = DateSerial(Year(Date), Month(Date), 1)
End Function
[Code] ....
View 3 Replies
View Related
Apr 3, 2014
I am maintaining an Access 2003 application that is running on Windows 7 64Bit Enterprise OS setup.
I have a form in the application that displays a number of records with each record having a unique id and a field called ClockHrs which is stored as a Number (Long Integer) type.
I have a textbox that displays the sum of the ClockHrs field.
The textbox contains no code behind.
The Control source property of the textbox is set to '= SUM([ClockHrs])'
ClockHrs
10
10
30
15
10
15
25
===
115
The textbox displays the result of the sum 115 in scientific notation as 1.15 +02 .
View 3 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
Jan 30, 2014
I have a project at hand and it's been a predecessor of mine and client has asked me to do some work on it and extend functionality - but I have not really delved into Access before and I have had to worked my way through to this final snag :/
The Main Form has one sub form. This sub form allows the user to add multiple order items i.e. qty, stock, description from records within the system - fairly straight forward.At the last column of each row is the sub total of those particular items i.e.
Qty Unit | Item ID | Total
-----------------------
2 | 1234 | 80.00
------------------------
1 | 43526 | 20.00
------------------------
> | |
So the total is a function of =[Qty Unit] * [Unit Price].Then in the Footer of this SubForm is the Sub Total
=SUM([Qty Unit] * [Unit Price])
All fine and well..... However, the additional functionality kicks in.
Lets add the additional customer_id from the Main Form. Each Item bought is dependent on the customer_id i.e. they get special prices depending on who they are.So a New table is made which has the Item ID and SpecialPriceID (of a table to define as a specialPrice) and the Price linked to this Item and Special Price category. So say that there are two groups of users "wholesale" and "nonwholesale" these would be SP_1 and SP_2 and each client is defined either one of these, and each stock item has a Price for each SP_1 and SP_2. Hopefully I've explained myself there.
Back to the SubForm. So now the Total needs to calculated differently with needed the external customer_id from the Main Form.
Code:
Function CalculateSpecialPrice(ItemID As String, CustomerID As String, Unit As Integer)
Dim SPSelect As String
SPSelect = "SELECT Price FROM [Items_SpecialPrices] WHERE"
SPSelect = SPSelect & " ItemID = '" & ItemID
SPSelect = SPSelect & "' AND SpecialPriceID = (SELECT SpecialPriceID FROM Customers WHERE customer_id = " & CustomerID & ") "
[code]....
its the sub total I just keep on getting #Error on. I have even watched (using alerts) that the correct return variable is the same as the individual rows. This is the equation I used for the SubTotal within the footer.
=SUM(CalculateSpecialPrice([Item ID], [Form]![FormName]![CustomerID], [Qty Unit]))
#Error
View 2 Replies
View Related
