I have 3 fields on a form PU_POINTS, SU_POINTS & 2_MILE_RUN_POINTS. The user enters a number 1-100 in each of these fields. If the user enters a number in any of the 3 fields of anything less than 60 then I want it to check a fail box. If the user enters all numbers 60 or more then i want it to check a pass box.
View RepliesI need to create a very simple database that would just store records and produce a couple of reports. I have three tables: one with the roster , one has records of the inventory items people from the roster receive and another one contains types of inventory we have. Everything is very simple except for one part. Every time we make a record of an inventory item given to someone, it requires not only employee id and inventory type from the existing tables. It needs us to enter a serial number of an item. This serial number contains a letter and a number.
Looks something like this - M100. Many people receive a consecutive set of inventory items. For example, from M100 to M150. There is no way to have a separate table with all serial numbers because they constantly change. That is why we need to have two text boxes that would allow us to input a range of serial numbers or just one number. Then the program should separate numbers from letters, evaluate the range, create new records of numbers and then put new numbers and a letter back together into one field in the table where we have all inventory records.
I found the following code online that allows me to find numbers within a range, but it only works for numbers.
Dim varRange As Variant
Dim lngLow As Long
Dim lngHigh As Long
Dim lngCounter As Long
DoCmd.Hourglass True
[code]...
I then found a piece of code that is supposed to separate numbers from letters, but I can't find a way to make it work.
Public Function FindNum(strName As String) As String
Dim strTemp As String
Dim i As Integer
For i = 1 To Len(strName)
strTemp = Mid(strName, i, 1)
If (Asc(strTemp) < 91 And Asc(strTemp) > 64) Or (Asc(strTemp) < 128 And Asc(strTemp) > 96) Then
FindNum = Right$(strName, Len(strName) - i)
End If
Next i
End Function
how to alter the code to make it work for my specific situation.
I am trying to update the range for the graph automatically and I'm calling this from Access because my data exists in Access. I've attached a sample file.what I am trying to do:
1) click on the button
2) button will update the usedrange in "trending" tab
3) have the graph's data range based on the usedrange.
Code:
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
xlApp.Workbooks.Open "C:Documents and SettingsgjiaDesktopmetric_DEV.xlsx", True, False
[code]...
note: the columns will grow bigger to the right, which is dynamic, so I've used usedrange method, but the code fails on the usedrange line saying the object or method is not being supported...
I'm trying to create some code that will create a new folder depending on the ID that is currently being added to the database.What happens at the moment is a new ID is generated which in turn is job reference.When this is added to the database a folder is created with a prefix of this ID number and a 20char test specified in a text field by the user.
What I am trying to achieve is this:If the ID = 57...Then a folder is create called 1 - 500 (and ignore if one has already been created, which it should have at ID number 1)...When the ID Number 501 comes along another new folder is create called 501 - 1000 etc etc.The idea is not to have 3000+ folders in just one folder making it look a bit messy and lengthy to look through.
I need to automatically generate a 5-character value for my Business Key. Without any user interaction.
2 character -> from 01,02,03,04 to 05
+
3 character -> Sequential Number (001,002,003).
The Sequential Number must reset on each new day.
I have some code that filters job raised I have 2 text box's txtdatestart and txtenddate after entering. date range between the too text boxs it shows me all job raised with in the period.i have entered what I would like is filter it again by client field using combo box cboclient so if the user enter's client name in cboclient combo box and date range in txtdatestart and txtenddate it will only show jobs raised with in the date range of the client enter in the combo box but if the combo box is empty show.
Code:
Private Sub cmdPreview_Click()
'On Error GoTo Err_Handler 'Remove the single quote from start of this line once you have it working.
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "#mm/dd/yyyy#" 'Do NOT change it to match your local settings.
[code]....
I have a form (Project Form) with (Project_ID,Applicant_ID,Project_description, etc). To make it easier for the user who may not know the Applicant _ID when he/she is adding a new project for the applicant, I want to put a combo box with the Applicant Names in it, and once the Applicant Name is chosen, the Applicant ID will be filled out automatically and be saved in the Project Table.
My Approach so far was adding a combo box with two columns(Applicant ID, Applicant Name), and basically adding the following code:
Private Sub Combo36_AfterUpdate()
Me.Applicant_ID = Me.Combo36.Column(0)
End Sub
This approach works well, when I select an applicant, the Applicant ID will pop up correctly. However, This ID is not being saved in the Project Table.
Hello:
I have 15 text boxes on a form. After the last text box is filled in, I can get the average okay, the sum okay, but would like to caculate the difference between the highest and the lowest numbers (Range). I've tried a ton of "If" statments, but did not seem to work. Any ideas would be helpful. I searched the forum for "Range" but could not locate an answer.
thx
My dates are stored as numbers (long integers), because they need to be in the form YYYYMMDD, and I couldn't figure out how to enter them and keep them in that format as Date/Time.
Anyhow, I have a form with a textbox and a button. When the user enters in a date range, I need totals to show up for only dates that are in-between and/or equal to the date(s) typed in the textbox.
For example when the user types in 20050904-20051004, and pushes the button, a display of totals will be displayed for only those dates and those in-between. If the user leaves the textbox blank, and pushes the button, I want totals from all dates to be shown.
As you may know from my previous question, I am a relative newbie to Access. Suggestions about how to do this maybe even how to derive and present the totals, and especially specific code will be very helpful and VERY much appreciated.
I am working on a small database for a school to track their live and silent auction. Currently I have a table set up as follows.
Silent Auction table :
Item Number
Short Item Description
Long Item Description
there are more fields that I am not interested in right now.
Mainly what I am after right now is to do some decent reporting for generating the catalog and posting the silent auction items on the web site for viewing before the night of the event.
I need to generate a report that contains the Item Number, short and long description. I need to group on Category which corresponds to the item numbers. Example:
Item Number 100 - 110 = Class Projects
110 -199 = Live Auction Items
200 - 299 = Sports and Recreation
300 - 349 = Entertainment
and so on......
I realize that had they numbered them in the 100 range I could group in my report by 100's and everything would be pretty good however, I did not have the opportuinity to be involved in the numbering scheme.
Even if the numbers had been in the order of 100s I still would need a way to say that the 100 -110 range is Class Projects.
I think I need another table and link it but I am not sure how to set up the range of numbers for the category.
Sorry to be so wordy.
Thanks in advance for the help.
Lee
Network admin trying to learn Access.
I need some help please. I am working on a performance management database. In this database we keep track of daily overtime and how that overtime is broken down into sub categories. For example, my staff worked 14 hours of overtime yesterday. 8 went for weather related overtime and 6 went to cover vacations. I have a report that shows all of the supervisor's overtime in a continuous list of running totals. But it's getting to big. I am trying to create a query for each supervisor that pulls the daily totals for the previous week and gives me that total sum. So far, my individual supervisor queries look like this. DATE, SUPERVISOR, DAILY TOTAL. They click on the report button and then they just count 7 days worth of numbers.
How do I create this?
TOTAL FOR SUPERVISOR X FOR THE WEEK OF 00/00/00 through 00/00/00 that pulls the daily totals out of my "overtime table"? and gives me a weekly total?
Thank you
Shannon
How can I check if a table contains blank fields/values.
If there are blank fields I want to replace them with 0.
I am very new to database building.I need to create a registeration database for a shooting sport competiton event that can either automatically assign each new registerant a target number when they are entered into the database, or allow me to enter different number if I wish.
View 3 Replies View RelatedI want to write an IF...THEN statement that checks the value of a control against a set of values. It would look something like:
If value = In(10,21,34,55,62,104) Then
It would be the equivalent of writing:
If (value = 10) Or (value = 21) Or (value = 34)...
Is there a function like 'In(a,b,c,d)' ???
Thanks,
David
/dmset
I have 2 fields that I would like to automate if possible
One field is called "p/o number" and another field called "line no"
These fields are part of an ordering database
Let say I have 200 items to purchase form 10 suppliers
And form example 20 items from each supplier
What I do at present is put the order number on each line item and the line number
example
p/o number line no
1 1
1 2
1 3
2 1
2 2
2 3
2 4
What I want to do is just put the first po number in the required line . Put the first line number in i.e. "1" and the macro will complete all the p/o numbers and line numbers for me as per the ones marked in red.
Example
1 1
2 2
3 3
2 1
2 2
2 3
Good Day!
I have a simple table where the user enter expiry date for visa. User also enter return date of visa holder.
I am looking for a way whereby, every time I start my database a report is automatically sent if Visa Expiry daye is close to departure date.
I already have the query and the report based on it, but how to have this checked and sent?
This is in my query
VISA_CHECK: IIf([Visa_LOI_end_date]-[Return Date]<7,'Fail','Pass')
If it fails this should trigger the report being sent.
I hope I have been clear and that someone can help.
Thank you in Advance
Enrico
I have a form (Access 2010) that we use to keep track of client data. When I enter a number equal to 6 or above in one field (Client Rating and this field is actually formatted as text because it could be 6 or 6a, b, c or 7 or 7a, b, c, etc), I want anything =>6 to automatically check a box in another field which is the Watch Indicator box (a yes/no box), how would I do this?
I tried to create an expression like the below on the Watch Indicator check box and it does not work - not sure why - it did not say it was incorrect, I think I may have it in the wrong place?
I used "After Update" on the Yes/No Box
=IIf([R Rating]<=6,Yes,No)
I have a table holding the number of each task planned each month, with a field for the name of the task and a field for each month stretching of for a number of years, and would like to be able to open a form or run a query on the present months field and the following six months without having to have a seperate form or query for each starting month.
any help or a point in the right direction would be apreciated.
I am using Access 2007. This is the problem; I have a warehouse divided into four departments North, East, South, and West. Each department is assigned its own storage location range North is N1-N1000, East is E1-E1000, South is S1-S1000, and West is W1-W1000. On the access input form of my database are two fields, one combo box field for Department and one combo box field for location stored.
When the user is updating the data base for parts received or shipped, the user must enter the department and location stored to correctly debit or credit that department. The location stored entered must be assigned to the department. (Example: dept. is north and storage location is N65, the user enters D65 instead, error message is displayed, "you have entered the wrong location").
What I need is instructions how to write the code to check the fields to verify location stored matches department, and display a warning message. How do I do this? I have a table with department names and storage location ranges.
I am building Access (2013) database for 100+ employees to keep track of their expiry dates of visa/passport/work permits, for that to work I need to be able to search between date range to generate a report to work on. please check out the image attached as it will explain better where I am stuck, with current settings access keeps giving me empty query table as I think it searches for exact same result in all 3 fields at the same time. But I want it to search between all 3 fields and display if any of the 3 fields falls into that range.
View 3 Replies View Relatedhello all, any help greatly appreciated.....
I have two tables in the same Access 2003 database.
Table1 is called "Clients" and has address type fields, e.g. street_name, postcode, etc. It also has a field called CYPAN_area.
Table 2 is called CYPAN_Postcodes and has two fields; postcodes and the CYPAN area that postcode belongs to.
I want to create a function so that when someone enters the postcode in table1, the database searches Table2 and identifies which CYPAN area that postcode belongs to and automatically populates the "CYPAN_area" field in Table 1.
any tips greatly appreciated
Harry
PS I'm only a novice so I don't know anything about VBA etc, but I understand excel type formula
I'm kinda new at ACCESS, and I've accepted an assignment that is giving me some trouble. For beginners, I could use some help as follows:
I am constructing a data entry form that is keyed on PID (Personal ID). I would like to be able to have certain fields in my form (Name, Title, Department, etc.) fill in automatically from information already stored in a different table, also containing the PID (which I can set as key). I would like this action to occur either when the PID field has reached 3 characters or when the PID field has LostFocus.
Thanks for any help in advance.
MAE:
Is it possible to have some fields filled in automatically as soon as a certain value has been entered into another field. e.g. if a town is entered in one field, then the postcode is automatically entered into the next field.
View 1 Replies View RelatedI have a table, tblDailyCalls, that contains agent_name, date, calls_ answered, and talk_time. Ideally on a form, the user will select an agent, enter the date range in txtStartDate and txtEndDate and a report opens to show what the total amount of calls and talk time is for that date range.
All I've managed so far is doing a simple expression on the report itself to sum the fields I want. But my method returns every date in the range. I would like to only display the total.
I've been trying with Totals in the query and crosstab queries but am not familiar with them.
I have Two tables one table has a Master list of products (ProductMaster) with a Unique product code (ProductMaster.UQ_ID).
The second Table has Order data. The Second table (OrderTable) is where I will enter the product code (UQ_ID) which is the unique Identifier which is also in the ProductMaster. As soon as I enter the Prodt code in the OrderTable I should have all the related fields filled in from the Product Master.
Columns in The ProductMaster = Product_ID, Product_Manucaturer, Product_Weight, Product_Color, Product_Size
Columns in The OrderTable = Product_ID, Product_Manucaturer, Product_Weight, Product_Color, Product_Size, Order_Date, Ordered_By
The columns marked in red are the ones that will have to be automatically fetched from ProductMaster
The data in the second table should be populated from the ProductMaster as soon as I enter the Product_Code. The rest of the details Can be filled manually filled in.
Is it possible to do this with Access. I have tried different expressions in the Fields without much use. The Documentation from Microsoft is very weak. If someone knows a solution or where the documentation is, please help.
Thanks
I have a table in my Database that uses the feilds Location and partnum. I use a barcode scanner to generate each of these fields. The problem I am having is that when our part number is scanned it contains data such as "des--. 123456 --aa". I have been doing a find/replace on this data to remove the "des--." and "--aa" but I was wondering if access has a function to do this for me so that I can get a macro to check automatically for this type of problem. Any ideas?
View 1 Replies View Related