im tryin to get this query to get data from current weeks inputs. ive googled and tried everything with no luck so far. so here i am. can someone tell me what im doing wrong? keep in mind i dont know what im doing.....
heres what i put for criteria under my date column.
Between Date() And Date()-7
doesnt work.... anybody? thanks in advance...
I am trying to create a form that searches the criteria entered and opens the query filter by the info entered.
Example:
Form opens, you type the last name parameter of client
Last Name: Smith
Click a button named Search- opens the query and filters it by the criteria (last name). You see the query with all records with the last name Smith. I may want to add the first name in the form along with the last name. Does anyone no how to do this?
thanks
Biz
Hi,
I am trying to set the criteria in a query, referencing a control on a form. The control is a combo box and I am trying to use the second column to filter data in the query.
EG. Forms!FromName!ComboBox.Column(1)
However the query doesn't accept it. I get an error saying:
Undefined Function 'Forms!FromName!ComboBox.Column' in expression.
How do I reference the control properly please.
Thanks
Red
Hi! I have a query setup that feeds off of three combo boxes on a form. It is setup to allow a user to search and calculate revenue for a selected time period by company, then department, then page. If all three or just company are selected, there are no issues running the data. However, if only company and department is selected I receive an 'ODBC--Call Failed' message. The issues seems to be stemming from the department field as I can link the department table to the main table and the query will run without error. The problem with that bandaid is that the query takes more than 15 minutes to run that way as opposed to about 2 minutes to pull more data at the company level.
Any suggestions on why this is happening or a fix that can be put in place? Any help is greatly appreciated.
I can provide more details if needed. Thanks!
Hi,
i have got a database which consists of table, queries, forms and reports.
I have one problem now is that I wan to do a query that will be able to bring the figure from another field to the current one.
ok say i have got Ref_No, Event_Date, Balance_BF, Adjustment, Balance.
so if say this is the first event for this particular ref_no (1234) it will bring the amount from another table (Principle_Table) linking the same ref_no (1234) to the Balance_BF fields,
If this is not the first event for this particular ref_no (1234) it will then bring the amount from the previous event Balance field to the current event Balance_BF field.
I am very lost how to do this. Is there a way doing this using query?
This is wat i mean
Ref No Event Date Balance_BFAdjustment Balance
06/00014 31-May-06 115140-5,757.00 $109,383.00
06/00014 31-Aug-06 109383-5,757.00 $103,626.00
06/00014 30-Nov-06 103626-5,757.00 $97,869.00
06/00014 28-Feb-07 97869-5,757.00 $92,112.00
U can see that the Balance_BF is actually brought down figure from the previous Balance starting from the second events. For the first event, the Balance_BF is actually draw down from another table (Principles_Table)
Is there a way to do this using query?
Alright people... I'm ashamed to even ask but here goes.
I have two tables:
1) Employees
2) Neighborhoods
Table Employees has a comprehensive list of all employees and their information (including phone numbers).
Table Neighborhoods has lots of information about each neighborhood we're working in and it also specifies what people play certain roles in each neighborhood. For instance, there is a field for "Field Manager" and "Sales Consultant". These two fields are exact matches of "Full Name" from Table Employees.
All I want is for my Neighborhoods Table to be updated or a new table be created that imports the "Field Manger Phone Number" into the Neighborhoods Table and the same for the "Sales Consultant".
-----------------------------
To be explain one other way, this is what I want:
Table: Employees
Field: Full Name
Field: Phone Number
Table Neighborhoods
Field: Field Manager (already equals Full Name)
Field: Sales Consultant (already equals Full Name)
Field: Field Manager Phone Number (This is the field I want pulled from Table Employees)
Field: Sales Consultant Phone Number (This is the field I want pulled from Table Employees)
-------------------------------
I've tried my best to create an Update Query or a Make Table query but I have not gotten any good results. Can someone please help me out with this being very descriptive as I am certain I'm just goofing one simple step.
Thanks in advance.
Hi everyone
I have a database that I built myself and use on a daily bases for keeping track of customers and there service records for a small sized plumbing and drainage firm. The database has a function that is designed to automatically search for upcoming boiler services that are due or over due which works fine, However we use acronym's to describe some routine jobs such as B/S (Boiler Service) or H/R (Heating repairs) which speeds up data entry. The problem is that Access refuses to search for the acronym's, I have tried basic text search critera's which brings up full words fine such as "Smith".
Is there a way of searching for the acronym's like B/S?
Thanks in advance for any help.
Hello,
I first want to thank the owners of this forum for keeping it up and the bright users who post replues I have gotten a lot of useful info thus far. I have a small issue I've been working on for a few days now. I just can't seem to find the correct syntax to accomplish it. Any I have a table which has customer contact info as well as sales numbers. I have a form which my users can search contact info. However I've been unable to allow the users to search for sales dollar volume. Basically the form sends the critera to the query. I've tried getting the between to work but I'm doing something wrong. Below is my VB code attached to the search button. The red bold line is the problem. If I just take the line out then open the query manually and type between "0" and "1000" it works fine. But if I type my form txt box names I get a message saying type mismatch in critera expressiong. So I think it's my syntax.
Option Compare Database
Private Sub cmdSearch_Click()
'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()
'Constant Select statement for the Query definition
strSQL = "SELECT tblCONSOLIDATED.ACCOUNT1, tblCONSOLIDATED.COMPANY_NAME, tblCONSOLIDATED.ADDRESS1, tblCONSOLIDATED.ADDRESS2, tblCONSOLIDATED.CITY, tblCONSOLIDATED.STATE, tblCONSOLIDATED.ZIP, tblCONSOLIDATED.CONTACT_NAME, tblCONSOLIDATED.TELEPHONE, tblCONSOLIDATED.FAX, tblCONSOLIDATED.CURRENT_YTD, tblCONSOLIDATED.PRIOR_YTD, tblCONSOLIDATED.PRIOR_TOTAL, tblCONSOLIDATED.YEAR2_TOTAL, tblCONSOLIDATED.YEAR3_TOTAL, tblCONSOLIDATED.YEAR4_TOTAL " & _
"FROM tblCONSOLIDATED"
strWhere = "WHERE"
strOrder = "ORDER BY tblCONSOLIDATED.COMPANY_NAME;"
'Set the WHERE clause for the QueryDef if information has been entered into a field on the form
If Not IsNull(Me.txtCSONME) Then '<--If the textbox txtCSONME contains no data THEN do nothing
strWhere = strWhere & " (tblCONSOLIDATED.COMPANY_NAME) Like '*" & Me.txtCSONME & "*' AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If
If Not IsNull(Me.txtCSOSLD) Then
strWhere = strWhere & " (tblCONSOLIDATED.ACCOUNT1) Like '*" & Me.txtCSOSLD & "*' AND"
End If
If Not IsNull(Me.txtCSOARN) Then
strWhere = strWhere & " (tblCONSOLIDATED.CONTACT_NAME) Like '*" & Me.txtCSOARN & "*' AND"
End If
If Not IsNull(Me.txtCSOAD1) Then
strWhere = strWhere & " (tblCONSOLIDATED.ADDRESS1) Like '*" & Me.txtCSOAD1 & "*' AND"
End If
If Not IsNull(Me.txtCSOSSM) Then
strWhere = strWhere & " (tblCONSOLIDATED.ADDRESS2) Like '*" & Me.txtCSOSSM & "*' AND"
End If
If Not IsNull(Me.txtCSOCTY) Then
strWhere = strWhere & " (tblCONSOLIDATED.CITY) Like '*" & Me.txtCSOCTY & "*' AND"
End If
If Not IsNull(Me.txtCSOST) Then
strWhere = strWhere & " (tblCONSOLIDATED.STATE) Like '*" & Me.txtCSOST & "*' AND"
End If
If Not IsNull(Me.txtCSOZIP) Then
strWhere = strWhere & " (tblCONSOLIDATED.ZIP) Like '*" & Me.txtCSOZIP & "*' AND"
End If
If Not IsNull(Me.txtSLCYYD Or Me.txtSLCYYD2) Then
strWhere = strWhere & " (tblCONSOLIDATED.CURRENT_YTD) BETWEEN '*" & Me.txtSLCYYD And Me.txtSLCYYD2 & "*' AND"
End If
'If Not IsNull(Me.txtSLLYYD) Then
'strWhere = strWhere & " (tblCONSOLIDATED.PRIOR_YTD) Like '*" & Me.txtSLLYYD & "*' AND"
'End If
'Remove the last AND from the SQL statement
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)
'Pass the QueryDef to the query
Set qryDef = dbNm.QueryDefs("qrySALESDATA")
qryDef.SQL = strSQL & " " & strWhere & "" & strOrder
'Open the Query
DoCmd.OpenQuery "qrySALESDATA", acViewNormal
End Sub
Please advise, how do I set up a form for this:
Based on one particular report style, have multi select criteria to produce user selected criteria
based on selections from a. Interest, b. State, c. Type
Report 1. sorts on Interest, 2. sorts by State, 3. sorts by Type ... and each report includes selections of the other 3 selections plus basic data common to all.
Plus, the Interests have a table where the main interest data is stored in a ContactInterest table, while the specific interest selections, per contact, are stored in the Contact table as Interest_1 through Interest_20.
In the Report, when it is sorted by Interest, while I have a concantated output for 5 of the selections, they also, want to sort alphabetically by the interest selections (which changes per Contact).
There is already a form for printing reports that are more static, and I wanted to add these selections on that form, perhaps as a subform, to allow printing of the reports, mentioned, and associated mail labels to the targeted addresses per group.
Any help, direction, guidance, or redirection would really help and is MUCH appreciated. I've tried many options, and as a newbie, have not yet found a solutions- or solutions.
Thank you so much. (time is of the essence and my skills are small so far.)
__________________
Hi,
I'm creating a new database for an investment brokerage company. The company invests clients' funds for a specific length of time and pays out a return on their investment on a weekly basis for the duration of the investment.
This is my first real database so I'm gonna need all the help I can get. The company has some pretty huge requirements but right now I'm focusing on phase 1 which will be very simple.
Right now I'm working on the database design but there's been one thing which has been bugging me for a while so I figured I'd ask you guys to help me get my head around this.
Let's say we have a table called Investments. This table will include data on the amount invested, the number of weeks invested for, and the ROI percentage. For example...
Investment Record Example
Amount: £10,000
Weeks: 10 weeks
Percentage: 10%
One of the requirements of the database is to produce a report that will list each week of the cycle and the amount paid to the client. This will require calculations in order to produce this data. Here's an example...
Payment Report Example
Week # | Payment Date | Payment Amount | Notes
1 | 1/1/01 | £100 | Enjoy your first payment! :-)
I can see how this can be easily done by producing a query and then a printing a report based on it. However, here's the tricky bit... for various reasons, the payment date or the payment amount for a certain week may need to be modified, and a note value may need to be added for particular weeks.
Of course, I could just create another table called Payments to contain this information, but I don't want to have to enter the data manually. It would be a lot more productive to have the database calculate these values, but somehow let the user modify these values, if necessary. That's what I need to figure out!
Can someone advise me how this can be accomplished? I've been reading up on the Make Table feature, will that do the job? Note that I do NOT want to create a payments table for every client so I'm quite sure.
I look forward to reading your replies.
Thanks in advance.
Warmest,
Zahid
Can someone explian to me how the max function works!
View 5 Replies View RelatedThis is probably a long shot, but I figured I'd post it anyways. We have a form that is used to input customer data. It was written about a year and a half ago and uses some programming in it. It's worked fine on the people's computers it was originally installed on, but recently we've tried to put it on some of the newer computers and when we run the form, it just freezes Access up instead of going to the form log-in screen. The weird thing is, it works on some of the new computers. They all have the same version of windows and the same version of Access, and the code in the form itself hasn't changed at all. I was thinking maybe the form was looking for something that wasn't there, and freezes because of that. Anyways, any ideas would be appreciated.
View 1 Replies View RelatedSituation: I have to seperate tables(table A and B), both have a part numbers field, manufacture field, and manufacture p/n fied. But only one of the tables(table B) has manufacture and manufacture p/n filled in. Also, some part numbers are missing from table A that need to be in B and some are missing from B that need to be in A.
Goal: My question is how do I make a query or whatever i need to tell access to find all the manufacture and manufacture p/n info(related to its designated part) in table B and put it into the fields in table A respectively. I guess i'm basically trying to join two fields in two different tables, i mean i need to the manufacture and manufacture p/n copied over to its related records in table A.
The reason for this is because we are migrating from Quickbooks to a program called Shoptech E2(access based) to do all our supply chain management. Things were correctly imported from QB except for the fact that the manufacture and manufacture p/n was not imported for some reason. Now E2 only looks at table A to pull its info and does not see the relationship, thats why i need manufacture and manufacture p/n copied over to table A to its respective parts.
Hi
This is probaly really easy, I want to identify just minus figues in a query ie -5.00. I have tried iif([TotalPrice])<0,[TotalPrice]) this shows no result. Can any one advise. Thanks in advance.
I have a query in which I need to display just the data that was entered from 9:00 p.m. the previous day until now. There is a date/time field called "QtyTime".
Thanks.
hello
i have the following expressions:
Status: IIf([Next Discussion Due]<Date(),"OverDue",IIf([Next Discussion Due]>Date(),"UpComing",Iif([LastReviewDate]>=#11/1/2006# And([LastReviewDate]<=#10/31/2007#,"Complete")))
when i try to close my query and save i get the error of the expression you entered has a function containing the wrong number of arguments??
any ideas on what i am doing wrong??
OK, this form has a series of buttons that lead to other forms or open up into reports. The problem is I can't figure out how they do that. On the click event procedure of one of the buttons it says =HandleButtonClick(1) .
This is NOT a macro.
I don't think it's a command button either usually those say [Event Procedure] in the click event.
I can't figure it out. Anyone have any suggestions please?
First time on here and I am looking assistance with the last part of this code. Can someone tell me what this is looking for thanks...
ExlFile.Application.activeworkbook.SaveAs "........Compliance Reports" & Rtn & BU & "-" & Cat & ".xls
First of all, thanks for any help in advance, I really appreciate it. This is my first big project in Access. I am trying to design a database for timesheets for all the departments at work. We have 21 departments with various numbers of employees in each department. At this point, I would like this database to be able to have users enter their own hours each week and print their individual report of hours worked then designated users add employees, look at previous payperiods and print out a master report as well as past reports. One pay period here is two weeks, with the days starting on Saturday, though I would like the database to keep track of one week at a time and then be able to group two weeks together to make a pay period. Each employee can work regular hours in a day and can also take up to 6 type of leave plus work overtime. So there will be 8 types of hours that could be entered. So far, I made tables for each different type of hour and created fields for each day of the week and created relationships to the employee table and pay period table. I made a form in datasheet design, but it didn't work out well. For lack of a better thought for them to enter their hours, I was going to design the form to look like this (but with spaces like a table);
Employee(fn) Employee(ln) Payperiod
Sat Sun Mon Tues Wed Thurs Frid Totals
Regular Hours
Vacation Used
Comp Used
Sick Time
Overtime
Holiday
Unpaid Leave
Paid Leave
The only thing is that I can't get the forms right and it seems like a lot of wasted space since employees will rarely take holiday, paid leave and unpaid leave. Is there a better way to design this?
I also have to take into account security. I eventually have to be concerned about departments only having access to their own records and employees within those departments only being able to access their own hours (except for designated users to do the master sheet). I haven't had any training or experience in access database security. Is there a way to limit records in a table or would I need to create a seperate database for every department?
I am attaching my table relationships in case I didn't describe this very well.
Thanks again for any help. If you need any further information, let me know.
Hi to everyone. I am trying, have been trying, will probably kill myself trying...to figure out the best way to make tables/relationships with the information I have already imported into my db. If someone could please PLEASE help me, I would appreciate it. I'm a visual person, and I have so many pieces of paper around my desk that I can't even see the top of it anymore!!!
Background info, I am building a db for employees and courses they MUST take. However, there are 60+ courses, only 4 of those courses are frequently taken (the others are more "train the trainer" and such). But which of those 4 courses you need to take depends on whether you're a supervisor or tech. So if you're a supv, you need to take courses 1, 2, and 4. Tech, you need to take 1, 2, and 3. My "dream" is when I later make a form, I can get the boxes to darken for the courses that don't apply to the employee.
Attached is a print screen (sorry it's .doc but I'm limited with the programs on my PC) of my current ONE table in design view so one can easily see how many fields there are.
Now, I realize to normalize, it's ideal to have only ONE subject matter per table. So, in a dream world, the tables that SHOULD be created to be completely normalized are as follows:
T_Employee
LastName
FirstName
MiddleName
SSN
T_EmpDetails
RNK
Supv
PPOCCGradeStep
EOD
DOBbasdSCD
T_Job
ParaLine
Para
T_JobDetails
Unit
DutyLoc
Activity
PositionTitle
Type
T_Courses (contains date course taken, IF taken)
NSPAE
NSPAS
.......etc..
So, hopefully you can see why I'm having such problems. I know that I could only create lookup lists for "Type", "Para", and "Rank". All other fields, although some do have repeating information, would result in HUGE lookup lists of probably more than 200 things.
Does anyone, anyone have any ideas??? Please!
I have no idea WHY I can't seem to figure out how to construct relational tables, but I just can't get it! I've built 2 other databases, but always had to come here for help on making the tables. So here I am again, only this is a bit more complicated than those I've posted about before. So I apologize for the length, but I'll try and post EACH field now so whoever may answer can possibly understand my frustration and confusion. The fields I need to have are as follows (separated by categories to shorten length here):
SoldierLastName (txt)
FirstName (txt)
MiddleName (txt)
Suffix (txt)
SSN (#)
DOB (date)
Street (txt)
City (txt)
State (txt)
Zip (#)
Ht (#)
Wt (#)
DOR (date)
PEBD (date)
APFT (date)
Blood (txt)
Meds (txt)
Allergy (txt)
PHA (date)
Mobd (yes/no)
93 (date)
8286 (date)
Tags (yes/no)
Profile (#)
FCP (txt)
MomLastName (txt)
FirstName (txt)
MiddleName (txt)
MaidenName (txt)
Deceased (date)
Street (txt)
City (txt)
State (txt)
Zip (#)
DadLastName (txt)
FirstName (txt)
MiddleName (txt)
Suffix (txt)
Deceased (date)
Street (txt)
City (txt)
State (txt)
Zip (#)
SpouseLastName (txt)
FirstName (txt)
MiddleName (txt)
MaidenName (txt)
Suffix (txt)
DateMarried (date)
DateDivorced (date)
DOB (date)
Street (txt)
City (txt)
State (txt)
Zip (#)
ChildLastName (txt)
FirstName (txt)
MiddleName (txt)
Suffix (txt)
DOB (date)
Street (txt)
City (txt)
State (txt)
Zip (#)
EmployerName (txt)
Supervisor (txt)
Street (txt)
City (txt)
State (txt)
Zip (#)
LastUpdate (date)
SoldierPhone (#)
SoldierNOK (#)
SoldierAlt (#)
SoldierCell (#)
MomPhone (#)
DadPhone (#)
SpousePhone (#)
ChildPhone (#)
EmployerPhone (#)
Ok, now perhaps someone can at least see where I'm coming from...or perhaps I'm making a mountain out of a molehill...it's possible. But the hard thing to remember is that one soldier can have more than one mom/dad (with divorces and such), more than one child that may live at more than one address, as well as more than one spouse (former and current), and last, more than one employer.
I'm pulling my hair out...strand by painful strand...trying to figure this out. Can anyone, ANYONE please please please help?!? I'd be soooo appreciative of any advice!
I finally am figuring out this Access db stuff. I have managed to produce what I believe are normalized tables, built a query or two, and now a report. However, what I need to do now is something that I don't even know is possible. Further explanation is that I need to produce a query that automatically counts how many of a particular course has been taken. So, in a sense the query would show that 500 people have taken course A. If I have to build a query for each course, that is fine (because there is a course B, C, D, etc.). The problem is that the tbl the courses are contained within has the following properties:
EmployeeCourseID [auto#]
EmployeeID [#]
CourseID [#]
CompletionDate [date/time]
I thought the easiest way to build a query would be by the CourseID. Problem? Well obviously ALL courses are contained within this field/column.
I was able to produce a query with the following SQL that produced totals for all courses...which was fine.
SELECT tbl_EmployeeCourses.CourseID, Count(*) AS Expr1
FROM tbl_EmpoyeeCourses
GROUP BY tbl_EmployeeCourses.CourseID;
But the problem is that I can't get these totals in their OWN queries. When I print the report, I want it to say in the report footer (obviously in separate textboxes) "Course A total:___" or "Course B total:___"
I know I will later be able to build on that to produce a grand total if the need should arise. but it's pulling these apart now that's whipping me.
Does anyone have any pointers or can tell me what I am doing wrong please? I would greatly appreciate it. Thanks so much!
Very new to Access and not a VB programmer. :confused:
With that in mind, I have created form for users to record address changes of members. I want to have a button that will "undo" what a user entered if they change their mind/decide an address change not necessary. My address change table has an auto-number field (ID) that is the index and I believe Access automatically creates a record regardless so how do we get rid of it?
I did a search and keep reading about a button wizard - Huh??? I don't know how to activate a button wizard (told you I was new). Also, I read about adding code but to where? and how? It also appeared the code examples might be using psuedo-names for tables or something ... how do I know a "real" name from a psuedo-name?
Can a "Delete" query be written? if so, what is the criteria to use?
I've looked at the properties of the button and all I can see is a place to add a macro or an expression. Am I in the wrong place for what I want to do?
Can anyone offer some help here?
Thanks!
hey guys i been working on this issue for a long time and still cant figure it out...please help me out if you can..
i have a inventory table that stores items, a customer sales table that stores customer information.
i have a form that i named the "sales invoice" it stores and gets the data from the customer sales table...
within that form i have a subform that gets the data from the inventory table... or at least thats what i want it to do...
here is a printscreen of what my form somewhat looks like....(see attachment)
my issue is that i cant get the subform to work properly... i've tried many different ways but i am some what new to access...
in the subform where is reads LOT# thats basically my SKU or unit product code... all this data along with everything in the subform is stored in the inventory table..
the information in the inventory table is already pre entered so what i want is to click on that LOT # and what ever # i select it will autofill the rest of the subform record..
just like a regular sales invoice... i want to select the item and have it fill up the description, price and so on....
:( :(
http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=12092&stc=1&d=1136343303
I got this problem and just don't have a clue? I'm really new to Access and I'm trying to Add-up the Values in a smiple query. I've read this Book over and tried but just can seem to get it? Can anyone walk me though this?
Thanks
DBS
Hi,
When I click this buton I get an error on one of the following lines.. I can't figure out why and how to fix it.
the error I get is
run-time error '3464':
Data type mismatch in criteria expression.
I made the part where the error occurs in bold and italics
'submit for approval
Private Sub Command22_Click()
Dim rs As Object
Dim rs2 As Recordset
Dim db As Database
Dim name As String
Dim x As Integer 'will be used as flag for do while loop
Dim cnt As Integer 'this will contain the number of records in the recordset
'MsgBox (Me.projno)
Set db = CurrentDb
Answer = MsgBox("Are you sure you want to submit this timesheet?", vbYesNo)
'if cancelled
If Answer = vbNo Then
Else
x = 0 'initialize flag
Set rs = Me.Recordset.Clone
rs.MoveLast
cnt = rs.RecordCount
rs.MoveFirst
Do While x < cnt
If rs!statusPM = "pending" Then
MsgBox "This timesheet has already been submitted. You can't submit this again."
x = cnt
End If
If rs!statusPM = "approved" Then
MsgBox "This timesheet has already been approved by your supervisor. You can't submit this again."
x = cnt
Else
MsgBox (rs!projno)
Set rs2 = db.OpenRecordset("SELECT projmanager FROM Projects WHERE projno =" & rs!projno)
Do While Not rs2.EOF
name = rs2!projmanager
MsgBox (name)
rs2.MoveNext
Loop
rs.Edit
rs!statusPM = "pending"
rs!status = "pending"
rs.Update
x = x + 1
'MsgBox (rs!projno)
rs.MoveNext
End If
Loop
'clear variables
Set db = Nothing
Set rs2 = Nothing
End If
End Sub
Thanks in advance,