Hi all,
I'm building a quick database, but some of the data to go into the database is quite... odd...
Anyway, the point is, I have one sheet that says
"Job Number 1200-1245" which would have all the same details, dates, etc.
Is there a quick and easy way to put in a range, say J1200-J1245, enter the data once, then be able to query... for example J1212 would return the same data.
Cheers :)
I have a report based on a query that returns all info from the query which is fine, I now need to amend this so that individual users can specify the date range to be queried and the person for whom the results are required (one of the query strings)...
View 1 Replies View RelatedI am creating a query which determines the number of days a payment is delinquent. Is there a function I can use in a query field that would look at the numeric field and if the value of the subject field was:
< 60 Days "30-59 days"
< 90 Days "60-89 Days"
<120 Days "90-119 Days"
>=120 Days "120 Days +"
It was my understanding that the IIF function tests for a condition and can only return yes/no results. I seem to remember that SQL had something like a least or most function that would allow me to set maximum value for range of days. I apologize that my question is confusing and hope you can understand what I am trying to accomplish.
Thanks for any help
I am trying to make a query that finds all records by the "Month Number" and have problems.
What I did:
Month(DateField)
I got month numbers like I wanted but when I typed in the criteria below, I got February thru December Records, but when I search on anything "up to" 9 it works fine.
I want:
Between{month1] and [month2]
Month1= 9 (example)
Month2= 12 (example)
Need results to be from the requested months but any year
I have to imagine it's how the date is converted to an integer, but not sure.
Any help would be great!
I have this formula for counting age groups
TOTAL 29 - 31: Count(IIf(DateDiff("yyyy";[DATE_OF_BIRTH];Date())>=29<=31;1))
But id does not work, if I use between 29 and 31 also do not work
I need to get count (number) of all students that have 29,30 and 31 year of age.
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.
When using the Format:'yyyy mm dd' in access queries.
When trying to show a week is there a way to show the
date range instead of week number.
Week 01 = Jan1-7. If the results are only week numbers
that makes it hard for people to understand when it
actually is. Bottom line is: I would like to convert
Week number to actual date range. Can anyone help with
this?
I was wondering can MS Access do a range compair look up. I have 2 tables. One is the Info table and the other is Rate table. The Info table has 3 columns like this:
[code]...
I want access to read the Info table and pick up the values in the FICO, LTV and Type and compair it. Like with the above example. In the Info table FICO = 622. So then I want it to carry the number 622 down into the Rate table and compare that the number 622 is in FromFICO and ToFICO column to find where does 622 falls in between. In this case there are nine rows in the Rate table that 622 is between 620 and 629. The next criteria is the LTV number in the Info table. It's 76. So back into the Rate table looking for the number 76. And 76 is happen to fall between 75.01 and 80.00 in the FromLTV column and ToLTV column. Next back to the Info table is the Type column which is 2. Now back to the Rate table to look up the Type column for Type = 2. So from FICO = 622 , LTV = 76 , Type = 2...So the rate I want to populate is 0.25...So is this possible to do in Access?
I am looking for a way to use vb, or any other way, to allow my users to enter a railcar initial and then a number sequence and add new records to the end of the table.
For example in a form the user can enter the car initial and the number sequence
Railcar initial: GATX
Number sequence : 290001 - 290100
I would like a way to create a temp table that then has 100 records
GATX 290001
GATX 290002
GATX 290003
etc etc.
I can then use this temp table in an append query to add them to my main table.
Ok, not sure if this is even possible or where to even start..
I've got a form that has all the info for a client, eg..
Client First Name
Client Last Name
Client Hours
Client WE/CS/EE
Client Day And Times
Client Phone
Client Comments
Begin Date & End Date (2 boxs) on there for date input..
and what i'm trying to do is limit that any date entered between begin date and end date, it'll check and make sure there is only ever 20 clients on any 1 day..
i thought Datediff would be the way to go, but then again how do you get it to search each day and make sure theres only 20 clients on there..
I have a table called StockTable with the following fields Location, Status, Serial, Make, Model, LastDate, DotNumber
I also have a table called FCDateRange with three fields
DateStart DateEnd and DotNumber
For example
3/7/13 - 3/13/13 - 1
3/14/13 - 3/20/13 - 2
Im trying to figure how to write a query that if the lastdate from the StockTable falls during the DateStart and DateEnd fields it will assign it the number in the DotNumber field
I have two tables (one is a query)
Table 1 (query based)
EMP_ID
Prev_Emp_ID
EMP_ID_DDSK
332-123
1
500
332-133
1
501
332-144
0
332-156
1
502
332-654
1
503
332-456
1
504
332-967
0
Table 2
Res_Numbers
Num_Of_Employees
500
10
505 - after 5 numbers are placed
Will be added for next hire
My goal: to place employee numbers into new employee field "EMP_ID_DDSK" (table 1), Numbers will come from (from "Res_Numbers" field (table 2)
Example: If field "Prev_Emp_ID" = false
Get the next number in line from table 2 "Res_Number" and place it in table 1 field "EMP_ID_DDSK".
Example: if "Prev_Emp_ID" = false for 5 new employees. Take the next set of numbers and place into field "EMP_ID_DDSK".
Code:
' count records in query
Dim rs As DAO.Recordset
Dim db As Database
Dim strSQL As String
Dim beginDatum As String
Dim eindDatum As String
Set db = CurrentDb
[code]....
Hi,
Would you humour me for a moment while I explain my query problem.
I work in Air Freight at Heathrow where all consignments are processed under an Airwaybill (AWB), an eleven digit number where the first three digits (prefix) denotes which airline the freight has travelled with. For security reasons a lot of the freight is X-Rayed and I am designing a database to log these shipments. Sometimes freight will arrive at Heathrow on one airline and then travel on to its' destination (this is called a transhipment) on another having first been X-Rayed.
What I want to do is produce end-of-month reports for each airline detailing which of their freight has been X-Rayed. I have created queries which filter the data for each airline according to the first three digits of the AWB, simple. On the data entry form I have a combo box which allows the operator to select which airline the freight has transhipped on. My problem is in the query where I have used the Or operator to "catch" these records e.g in the United Airlines report where the prefix is 016 I have all the records that begin with this prefix plus those that went on this airline but also those that went on a different airline but have the 016 prefix. It is these last records that I only want to appear once on the report of the airline it finished its' journey with.:o
Hi all,
I have a UNION query of multiple tables:
Select * From tblCal_SlsAB
UNION
Select * From tblCal_SlsBC;
Both of them have Unique IDs as PK, such as: AB001, AB002, etc for tblCal_SlsAB and BC001, BC002, etc for tblCal_SlsBC. When I do the UNION query I get: 1, 2, etc for both IDs instead. What's causing the ID to change and is there a workaround it? I need to get the same exact IDs I have in those individual tables. Thanks in advance!
Max
Hi all,
I have a UNION query of multiple tables:
Select * From tblCal_SlsAB
UNION
Select * From tblCal_SlsBC;
Both of them have Unique IDs as PK, such as: AB001, AB002, etc for tblCal_SlsAB and BC001, BC002, etc for tblCal_SlsBC. When I do the UNION query I get: 1, 2, etc for both IDs instead. What's causing the ID to change and is there a workaround it? I need to get the same exact IDs I have in those individual tables. Thanks in advance!
Max
Here is my code...if I take out the where statement..it copies the remarks all the way down my table to all the records...with the where statement..it's prompting in for parameters..
Private Sub Remarks1_Click()
Dim strMsg As String, strQry As String, strRemark As String
strMsg = "Update All Comments in the Current view. IMPORTANT!!! Will update a the Viewed records."
[Code].....
I have a continuous form with an SQL query as its record source, it is a dynaset. Each row on the form is for a specific picture, containing a unique ID, a pre-generated caption field, and an official caption field. The idea is that users will edit the pre-generated caption field, hit a button, then their changes to the pregenerate caption will be made to the value of the official caption field.
My VBA code for the button works fine, in that it doesn't return any errors. The problem is that there is some sort of delay between when the button is hit and when the official caption field is updated. After the first press, the form requeries and the official caption field is the same, but after a second press the official caption field will display the user's changes.
First, a recordset clone is created using an SQL query, the pre-generated caption column is selected and then the results are filtered to the single record that shares the picture ID of whatever row the user was working in. Next, an SQL update runs, replacing the value of the official caption field with the value of the pre-generated caption that is contained in the recordset clone. Then the form is requeried.
I've tried adding a change of focus at the beginning of the code and a 15 second pause between when the update statement runs and when the form is requeried, neither solves the issue. The problem can't entirely be my code, because a second press of the button will make the appropriate changes.
I know what you're thinking, why have divide the captions into pre-generated and official in the first place - why not use pre-generated caption as the caption source? There is a separate feature that allows a user to mass edit captions and I decided to retain the original pre-generated caption as a field, in the event that the user makes a mistake or decides to reference the content of the pre-generated caption.
I have one memo per change (user input)...I need to have the comment the user inputs to copy into other records that were queried. I tried insert. My coding does one of the records or all of the records ..I need it to copy just to the queried records ...and I have a query but no filter set.
View 4 Replies View RelatedLet's see how well I can explain this. I'm sure the issue is much simpler than I know but my knowledge is limited as this is all self-taught.
I have two tables:
Table 1:
Category Name
Part Name
# used per house
Table 2:
Part Name
MFR Name
Part Number
Initial Cost
Rebate %
Final Cost
In my form table 2 is the subform as there are multiple manufacturers that we get price quotes from for every part that we use. I'm trying to query for the minimum price for each part but I cannot get it to give back the MFR name that has the minimum price. I currently can only get it to show the minimum price for each part but that doesn't tell me which MFR it is that provides that price.
Also, Final Cost is just the Initial Cost multiplied by the Rebate %. Is there anyway to make it be auto-populated within the table. Currently I'm just running a text box on my form that multiplies the two fields but that leaves my Final Cost field in the table blank obviously.
I have a few other things but figure I'll start here lol. Any help would be appreciated
I have a client that wants to enter a range of dates in a query of when they will call that person back. Then they want to be able to type in a range of dates and have a make table query show them all the people that fall in between these two dates....is this even possible???
Ex.
Joe March 3 to March 8
Mary March 4 to March 9
John March 5 to March 10
So if they type into the query March 3 to March 6 all three people should show up because one of the dates specified lies within the parameters they are asking for.....man I am out of ideas
Anyone.....
I have a form [IUDATA]
I have a add record button.
I have a date field [DATEIN]
I have a text field [DRPNO]
If the [DPRNO] field is empty, I would like the user to have the [DPRNO] field be automatically populated after the user enters a date.
I'd like the format of [DPRNO] to be "dpr YY-XXX"
Where:
YY is the year of the [DATEIN] field and
XXX is number of records in that year.
So for example, if it was the 4th record with a 2013 date the [DPRNO] would be dpr 13-004.
I have an Access2007/SQL Server 2012 system with 20 users for an insurance company. The company does most of its business via a network of vehicle dealers around the country. If someone comes in to buy a motorcycle, boat, or recreational vehicle at a dealership they need insurance to take it home, and our dealers send the quotes to us.
The dealers, in turn, receive payment from us each month for their efforts. Some are paid a % commission on the premium, some are paid for each quote they send regardless of whether the policy actually sells or not, and some are paid a set amount per sold policy. (Yes, that is relevant information!)
We already have reports that tally the amounts due each dealer based on their payment scheme, but last month our bookkeeper had to write about 650 checks manually because the check writing is not automated. She'd look at the report, and then enter name, address, and amount (in digits and words) into Quick Books and print the checks from there, a horribly tedious process. I've been asked to print the checks from Access. Basically one click would print all 650 checks.
I've opted to use a Make Table query to move the commissioned dealers amounts to a single location, and then to run two append queries to add the records from those paid per quote and those paid per policy. At the end of the day, one table contains all the information necessary to print the checks...except one.
The check number.
I need a way to sequentially number each record in the new table with a user generated starting point, the first check number.
By the way, the check blanks are on standard letter sized paper, three to a page, with tear-off perforations to separate them, in case that information has any relevance.
I think the best way to accomplish this is from the report itself. I've created a blank field on each record for the check number, and what seems most logical is that the sequential number is generated on print and written back to the table, rather than just generating all the numbers at once. That way, should print ever be interrupted, it will be easy to take up where we left off.
Date of Birth (DOB) field etc. in one program are text - how do I make another file with the same data into number fields for Date of Birth field etc? When I copy data to file that has number fields the 09252004 is changed to 9252004. Can I get reports with the correct Date of Birth in them by moving data from text file to number file?
There is data entered monthly in file and formula has been set up for January, February etc as ---quarter: Int(([month]-1)/3)+1. I would like formula for the fiscal year for April to be counted as month 1, May - month 2, June as month 3, July as month 4, August as month 5, Sept as month 6, October as month 7, Nov as month 8, Dec as month 9, Jan as month 10, Feb as month 11 and March as month 12.
Thank you
I have a form where we fill in information for supply of equipment to employees.
Each item must be signed for on a printed report.
I am encountering problems trying to create enough rows in my report detail for each signature of the items supplied.
For example, on the form I will select the "equipment" - 4 hats supplied and 3 boots. On the report I want the equipment set as the group and the detail to be a number or rows which equals the number of selected items. therefore under the Hats group heading I want 4 blank rows which are made up of 3 text boxes - Print Name, Signature & Date and another group heading for boots but with 3 lines.
I would like to know if there is any procedure to restrict/stop auto number increment for certain number of record count (say 50), then increment by 1 for next 50 records.
View 8 Replies View Related