I have a table which tracks material quotes. I have two fields for the quantity range called PriceBreakBegin and PriceBreakEnd. If I have a part that has contract pricing (no limit on the PriceBreakEnd), can I use a wildcard or something in the PriceBreakEnd field to represent any number, and not just the highest integer I can put in there like 99999?
View RepliesHi,
I am looking for Idea's of how to create a report.
Basically the report is a league table showing Month and YTD, this is the easy bit.
The part I need suggestions and guidance on:
The report also needs to show peoples movements in the league table i.e: whether they have moved up, down or not moved at all from the previous months position.
I was thinking of creating 2 tables: Current Month + Previous Month, in each table the persons position is indicated (an ascending record number is created throughout the table - not sure how to do this yet)
Then using code, lookup each persons position in previous month and write that position number against the persons UID and current position in current months table.
Am I on the right tracks or does someone have an easier solution?
Thanks in advance.
Darrell....
I have a data base,one of the field contain Data like "ZZZ-DEFS#UUH1234567".
There should always be 19 characters in this field including #.I want to design a query which can sort out entries less than and more than 19 characters, so that wrong entries can be corrected .
I am new to Access 2007. I have a list of companies of are constantly updated,deleted, and created which are listed on a table. I have a form with an unbound text box with a sumbit button that opens a new form with the information of the name of the company in the search box. Unfornately, if the name of the company is not perfect, the record is not found and the form opens a new record. I want search box tyo autill, or "wildcard" when I begin to input a name of a company. I tried a combo box but my boss does not like it. He wants a wildcard search.
View 1 Replies View RelatedI have a form which has a combobox called Task_Ref which looks up values in a table column.
I would like to be able to set the tickbox value of tickbox called P1 to True if the combobox contains the word "test", each entry on the combobox selection may vary such as:-
Test number 1
Yesterdays Test
As long as the word "Test" appears I would like the above to happen?
I was thinking of something along the lines of:-
If InStr(Task_Ref.Value, "Test") > 0 Then
P1.Value = True
Else
P1.Value = False
End If
End Sub
But this hasn't worked
On a bank draft I need the dollar amount spelled out in a text format. Will Access do that? If so, how?
Jeff
i have a query that returns data per week number. To obtain the week number i used this : Format([Date],"ww"). This gives me the week numbers great but when the query is sorted the week numbers are not sorted numerically, ie i get this
1
10
11
12
2
3
4
5
6
7
8
9
is there a way to sort my data to be numerical.
Here is the query:
SELECT Format([Date],"ww") AS [Week Number], Sum(Conformance.Scrap_Quantity) AS Scrap, Sum(Conformance.Rework_Quantity) AS Rework
FROM Conformance
WHERE (((Conformance.Procedure_Followed)=False))
GROUP BY Format([Date],"ww");
Hi,
Could you help me with the following.
Lets say I have table with one of the column header as notes.
The notes is of type 'text'
In the notes column information like the following is typed out
MAX O.D 3.456" Min ID 1.2" and OAL 3.4"
or
MAX O/D 5.456" Min I.D 1.2" and Min Length 4.4 inches
I want to pull out the first numerical value (3.456 - first example, 5.456 -second example) and the last numerical value (3.4 -first example and 4.4 in second example). How would I be able to do this.
So the end result should be 2 more columns with max od as one and min length as the other.
Regards
George
I have to make changes to an existing Dbase. Problem is that in this Dbase it is on one form possible to put text in a numerical field. I need to do this again, however it does not work, Access gives the message that Text will not go in a numerical field. I am talking about a combobox.
What appreciate some tips how to get this done.
Thanks,
Dirk
My problem is the amount is showing on the screen as 3.15654E+11 I need it to show the actual value which is 315,654,000,000
I already have the field set as double.
When I tried to import the field from a csv file, it created errors when I had the field set as Long Integer. When I changed it to double, I didn't get the errors but got the value of these larger amounts as 3.15654E+11
I tried to update query just those records to the value 315,654,000,000 but got another error.
Help!
Hi there
I have an ASP form that saves numerical data to an access database via an insert statement in the asp page.
The main problem i have is that i have some code that adds all the stored values together and provides a total on the screen for the user to print off.
I would like to save this 'total' value to a field in the table but cannot seem to get this to work.
So i thought of running a query in access that would add the sum total of a number of fields and store it to the total field.
Is this possible and how would i go about it?
Cheers
I am working on an app, that has a field named "Name". I have everything set up with a module, which, for example, the name "John", it assigns 1. when it sees "John" again it assigns 2. Then "Jane" It assigns 1 again. I want the module to see every instance of "John" to assign the same number, 1, then all instances of "Jane" 2, etc. Here is the code in the module I am using, but it is assigning the values wrong. My final plan is to use the numbers for conditional formatting, so all johns one color, all janes, a different color. I can't use the conditional formatting wizard because these names pop up at random, and the names populate at random.
Option Compare Database
Global GBL_Category As String
Global GBL_Icount As Long
Public Function Increment(ivalue As String) As Long
If Nz(GBL_Category, "zzzzzzzz") = ivalue Then
GBL_Icount = GBL_Icount + 1
' MsgBox icount
Else
GBL_Category = ivalue
GBL_Icount = 1
End If
Increment = GBL_Icount
End Function
I'm new to building databases and I wouldn't think that this is difficult but it has stumped me and everyone that I have approached to elegantly perform this calculation.
Essentially I have a table of meters (the best real world example is the watermeter that your community uses to measure how much water you use each month.) Each meter in the table has a "child table" of the date/time that the meter was read and the reading on the meter (stored as a number). I need to calculate the difference between the last two readings in the child table for each meter(essentially how much water each meter has used since the last reading.)
I have been able to sum, average, etc. the meter readings, but I can't seem to find a way to get a difference.
Thanks in advance,
Corwin
is there any way I can make a field with a data type that calculates numbers in the field but also allows text to be entered into the field (e.g. N/A or No Score) The non-numerical data certainly wouldn't have to be calculated and could be filtered out when calculating averages and other numerical operations.
Thanks
I am having some problem regarding the way the options in a lookup field is displayed. Currently the options are displayed numerically instead of displaying the actual text.
I have three tables which are ContactPersonnelT, OrganizationT and TrainingDonorT.
One of the fields in the ContactPersonnelT table is, Affiliation. This field is a lookup value (look up wizard) which comes from the OrganizationT table field, OrganizationName.
In the TrainingDonorT table I have a field called, ContactPersonnel. This field is a lookup value which comes from the ContactPersonnelT table field, ContactPersonName. In the drop down list of the ContactPersonnel, besides the ContactPersonName. I also want to display the Affiliation field. However, instead of displaying the text in the OrganizationName field, a numerical value is showing.
How to change the display to text instead of the numerical value.
I have a problem with a cascading form, which writes back to another table. The scenario is:
There are three tables. Users, Departments & SubDepartments.
Each table has an Autonumber set as the primary key .
The form is to write back to the user table, and within the user table there is a Department & SubDepartment field. These are linked to the relevant tables and all that works.
For the form I have two combo boxes for Department and SubDepartment. Department simply pulls from the Departments table and displays/writes back to User table correctly.
The SubDepartment is the one I am having problems with. I have got it to cascade correctly but here is the exact issue:
On the existing records the subdepartment is being displayed as a number (the primary key autonumber). The drop down list displays the text of what the subdepartment is (which is what I require) but then when that is selected it throws up the error that the value entered isn't correct for that field. I guess it's because it wants to write back the numerical record ID rather than the text from the other field.
The row source query is SELECT [qrySubDepartment].[SubDepartmentName] FROM qrySubDepartment ORDER BY [SubDepartmentName];
The table fields for the subdepartment table are SubDepartmentID, SubDepartmentName, DepartmentID and SDID.
SDID can be ignored for the purpose of this question and DepartmentID links back to the main department. Just to confirm visually the cascading form does work in terms of when you pick a department it only displays the subdepartments associated to it.
How can I convert a date format to it's numerical equivalent inside of a query? For instance, 1/1/2014 to be 41640.
View 2 Replies View RelatedWhat is the best solution to this problem? How can I automatically assign a numerical value to a field based on the information currently contained as test in another field? For example, if I have a field that ranks a film as “Poor, Fair, Good, Great, Superior”, how can I have a set value appear for statistical purposes that assigns a number to each of these values (Something like: If rating of current record = “Poor”, then ranking = “1”; If rating of current record = “Fair”, then ranking = “2”; If rating of current record = “good”, then ranking = “3”, etc. I also want to be able to have the values recalculate if someone changes their original opinion.
View 2 Replies View RelatedHave two tables: Assignment and StudentHeader - they are related by AssignmentGUID
Have the SQL:
SELECT StudentHeader.[Student ID], StudentHeader.GUID
FROM Assignment INNER JOIN StudentHeader ON Assignment.GUID = StudentHeader.[Assignment GUID]
WHERE (((Assignment.[Assignment Type])="Q") AND ((Assignment.[Assignment Number])=2))
GROUP BY StudentHeader.[Student ID], StudentHeader.GUID
ORDER BY StudentHeader.[Student ID], StudentHeader.GUID;
This returns:
Student IDGUID
al003072274
al154636303
al154636463
al595048272
al941626429
am180614426
am750033300
am750033462
I want a third field - Sequence - based on the Student ID and GUID, therefore
Student IDGUIDSequence
al0030722741
al1546363031
al1546364632
al5950482721
al9416264291
am1806144261
am7500333001
am7500334622
I've got a rapidly expanding database that I designed to do my quotes for work and now I'm trying to expand it to add up the invoice amounts when the jobs come to fruition. I've got 99% of it working well but as I'm not the only one entering data I'm trying to make it as foolproof as possible and here is my problem.
I have a multi-line textbox that receives 'vehicles' in an abbreviated format and I need to get them in order based on a sort column number in the appropriate table. This i can do but the code I have found removes the duplicates which I don't want as I can can two identical vehicles on the same job.
e.g
Vehicle Sort Order
UNT/FL 500
FL4/45 400
E7/F5 600
UNT/FL 500
I want as
FL4/45 400
UNT/FL 500
UNT/FL 500
E7/F5 600
How do I suppress the numerical values from being displayed on the "yes/no" check boxes when a user clicks directly in the field, instead of clicking the arrowhead as they should?
View 3 Replies View RelatedI've been building a small app for some friends. At present, they enter "records" line by line into a word processor, then use (archaic, IMHO) macros to produce the desired printouts. They wanted the new data entry UI to mimic that process.
Ok, so I've adopted the datasheet form as the principal entry vehicle. Based on the initial info they gave me, there were two convenient fields to sort on, so I used them in the OrderBy clause of the query which serves as the form's recordsource. I also developed the code to maintain the sort order after they inserted or deleted a record.
Now it turns out they don't require an entry into those fields, and in fact there's no logical field at all on which to order by.
So the question is: how to approach this? From my reading, I can't use the primary key or an autonumbered field, since the former won't necessarily reflect the desired incrementation and the latter only triggers for a new record, not an insertion (is this correct?).
My thought is to add a simple numerical field (which will be hidden from the user) with sequential values. I can do the OrderBy on it, and add code to adjust those values whenever a record is deleted or inserted. (That will require looping through the recordset from the point where the deletion or insertion occurred.)
For instance, is there some way to add a calculated field to the recordsource for this purpose?
I have a list of 22 soccer players with their weights sorted from heaviest to lightest in numerical order. I want to add further players and have the list reflect their standing each time it changes i.e. Col 1 Player ID, Col2 FirstName, Col 2 SecondName, Col 3 Mass:90 Kgs Col 4 Position In group: 1
A new player registers and is 95Kg.....he becomes #1 and the rest go down etc. Can this be done?
i've try and no luck... using Like "* *"
i'm tryin to create one that if i search for like let say "brisbane" it would show the results of the complete spelling, but let say if i was to just type "b" or "bris" it would show u a list of "B" towns or matching words of "bris".
any assit would be great
I have a field in a table that has the following data in it:
W-01-2005
W-02-2005
W-03-2005
W-04-2005
etc..
I created a query against this table and put a pop-up on this field so they can enter the search criteria needed, or leave blank for all.
I'd like to modify this pop-up code to ignore the W- and -2005. When the pop-up appears and asks them what week they want, I'd like for them just to enter 01 or 02 or 03 etc... not W-01, W-02 etc..
This is the code I have currently in this field.
Code: Like [Enter Two Digit Week] & "*"
Thanks, Leap!
Can anyone say what the syntax would be for the "incorrect" statement below?
Like *Mid([checking]![COMPANY NAME],1,InStr([checking]![COMPANY NAME]," "))*
As the statement above doesnt work, just returns errors.
Thanks in advance