A child in our company can have up to 4 different service providers offering different services at a time. I have to do a report for each individual service provider and there delivered services. The problem is, all the providers and delivered services are listed on one record (the child's record). How do I make a query that will only pull up their delivered services in a record and not the services of the others attached to it? Should I use IIF? If so, what would be a good statement to use? If anyone can help me out, I'd be thankful. Let me know if additional information is needed.
I run a service related company and I am trying to develop a service database that will automatically calculate the next service due based on the previous service date, the catch is that the service need to happen on the same week day. i.e. if the last service was on Friday 02-08-2013 and it is a monthly service, the next service is due on Friday 06-09-2013. Is there any way that this can be done? PS there are various service periods such as bi-weekly, weekly, 2 weekly, monthly, 2 monthly etc.
I have developed a MS Access tool to be used in multiuser environ. In some computers, the tool is working while in some, there is an error message-"Provider not found. May not installed properly". Is it problem with the script or is it that the provider was not installed with MS Office. I have used ADODB connection.
Error Type: Microsoft OLE DB Provider for ODBC Drivers (0x80040E37) [Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot find the input table or query 'Results'. Make sure it exists and that its name is spelled correctly. /Access_Mem/verifypassword.asp, line 18
I have a table called providers with the following 3 fields:
Provider ID Cancellation Reason Visit ID Date
It is possible to have multiple instances of the same provider ID, cancellation reason and visit ID on the same date. I'm trying to count just 1 instance based on visit ID if there are multiple instances of the visit ID and reason on the same date. Select distinct doesn't seem to do the trick.
Microsoft OLE DB Provider for ODBC Drivers (0x80004005) [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key 'Temporary (volatile) Jet DSN for process 0x818 Thread 0xa74 DBC 0x5f5c1024 Jet'.
I get the above error when I run my login form can anyone help? it points to the code below
<% Response.Buffer = true %> <% Session("DatabasePath") = "MoneygallGAA.mdb" If Request.Form("btnLogin") = "Login" AND Request.Form("txtName") <> "" _ AND Request.Form("txtPassword") <> "" Then
'-- Declare your variables Dim DataConnection, cmdDC, RecordSet Dim RecordToEdit, Updated, strUserName, strPassword
'-- Create object and open database Set DataConnection = Server.CreateObject("ADODB.Connection") DataConnection.Open "DRIVER={Microsoft Access Driver (*.mdb)};" & _ "DBQ=" & Session("DatabasePath") & ";"
Set cmdDC = Server.CreateObject("ADODB.Command") cmdDC.ActiveConnection = DataConnection
'-- default SQL SQL = "SELECT * FROM Login"
If Request.Form("txtName") <> "" Then SQL = "SELECT Login.* FROM Login " & _ "WHERE Login.userID='" & strUserName& _ "' AND Login.password ='" & strPassword & "'" End If
cmdDC.CommandText = SQL Set RecordSet = Server.CreateObject("ADODB.Recordset")
If Not RecordSet.EOF Then Dim struserLevel struserLevel = RecordSet.Fields("userLevel") Session("userLevel") = struserLevel Else 'The user was not validated... 'Take them to a page which tells them they were not validated... Response.Redirect "default.htm" End If End If %>
Recently I came to know about a wonderful website providing cheap magazine subscription. One can find any popular magazines here, that too on a price less than 35% newsstands price. You can subscribe for all popular Sports magazines, lifestyle magazine, health magazines, and magazines related to popular music genres and almost any kind of publications.
The website is http://www.magazineland.com. I hope you’ll get benefit through this latest news.
I am a newbie in Access. I am running Access 2003 and would like to be able to create links to Visual Foxpro9 tables using the VFP OleDB provider and not the ODBC drivers.
A few of my clients want to use the VFP9 tables in their reports but so far I have not been able to find a way to do it using OLEDB (except in a module with VBA code which I don't want).
I can do it using the ODBC but the problem is that ODBC driver does not recognise the VarChar fields in the tables.
I tried creating an Access project and then 'File --> Connections' but it does not display the 'Provider' tab to select which provider to use and by default assumes that SQL Server is the source database.
How can I get Access 2003 to allow me to select the OLEDB Provider? Any help will be appreciated. Thanks.
Hi, I'm trying to make a database where a Date and a Service Number is entered into a form.
I have 2 buttons, one to automatically enter the Current Date and the second to automatically enter the Service Number.
The service number is incrimented by 1 each time and reset for every day.
eg.
Date Service Number January 1, 2005 1 January 1, 2005 2 January 1, 2005 3 January 2, 2005 1 January 2, 2005 2 January 5, 2005 1 January 5, 2005 2
I already have the button that enters the Current Date. However, I am having trouble with the second button.
How can I code the button so that it searches the table for the Current Date and finds the record with the greatest service number for that date? After this is done, I want to create code that will automatically enter the next service number into the Service Number Textbox on the form.
eg. Using the Example Table Above The date January 5, 2005 is entered into the Date Text Box and the Search Next Service Number button is pressed. The number '3' should show up in the Service Number Textbox
Sorry if its hard to understand, but thanks anyways.
I just realized that I placed this in the wrong forum, Sorry for the inconvenience.
When I open a query in design view it can take anything upto 20 seconds to open. Tables are also slow to show, with a marked "slowness" over all. I tried the same database on an old laptop & the tasks were almost instant.
My main machine links to a file server, but the database files are not server based.
After much troubleshooting I have dicovered that it is the XP Workstation Service that is dragging it down. If I stop the service, then Access is rapid!
I have read all I can find on the subject, but nothing that helps. With all the parameters in the Workstation Service in the registry, is there a setting that I can change to speed things up?
I'm trying to build a table structure for this database. Heres some background information about the business:
The business is a service business. We visit the customer's location and run tests on whatever water systems they have. Each customer is unique in that they could have any combination of systems at their site. They can also have more than one of the same type of system. The test results are the data that I need to record and store for future access. Each customer is visited on average once or twice a once a month. So there should not be more than 1-2 entries of data for each system for each customer per month.
For example customer RUTGERS might have two systems labeled HWS and CHWS. customer BMSQUIB might have three systems labeled HWS1, HWS2, and CHWS.
What I need to do with this information is go into the records of service visits, and retrieve for example, the last 4 visits of a specific system and prepare that information to be printed in a report along with their contact information.
I have come up with three tables to do this:
Customers Table contains: Customer ID (pk), contact information data.
Systems Table contains: System ID (pk), Customer ID, System Name
Service Records Table contains: Record ID(pk), Date, System ID, Data
My thoughts were to have a table to contain customer information(each customer with a unique ID), a table to contain system information for each customer (each system has a unique ID), and a table to store the results of every service visit for each system(each individual visit has a unique ID)
Please critique this table design. If you think its sufficient, perhaps you could lead me in a direction pertaining to how to retrieve data on the most recent 4 visits (last 4 entries) for a specific SINGLE system from the Service Records table. I would assume that you would need to use a query and then get the data from that and put it into a form.
Hello, I'm totally new to this forum. I have a database with the vehicle information in one table. The Vehicles' Odometer reading stored weekly in another table. I have the vehicles' service history with the Odometer reading of the last service in another table. The first two tables are linked by parent, child relationship. I now want to forecast the approximate vehicle service due date based on this information. All I want to do is extract the last 10 Odometer Readings from the Weekly Odometer Readings Table. Find the Average Kilometers with those 10 values. Come up with a figure. Determine the last value entered in the Weekly Odometer reading along with the date. Pull the last service history for the vehicle, with the odometer reading and date. Compare these 2 readings to see if the vehicle is due for service, and if so based on the average Kilometers run every week, determine the approximate date the vehicle is service for due. I badly need help on this one. So if someone could help me on this please let me know. Thanks
Simply put, I have a front end that does a bunch of file moving and if it encounters an error and stops processing (which is intentional) I am the only that can login to the machine and fix it.
Is there any way to run it that would allow others to login on their own user profile, solution the problem, and start the processing again or am I stuck to a "per profile" instance?
I've been trying to solve this problem for the past month and at the verge of destroying my PC! I've trawled through numerous web sites but just cant seem to figure this one out.I'm trying to get access to tell me what the next service moth is based on the start date, end date, and the frequency of service.
For example:
Start date: 01/01/2014 End date: 31/12/2015
service frequency: Every 4 months
Using the above information the service months are: 1 - April 2014 2 - August 2014 3 - December 2014 4 - April 2015 5 - August 2015 6 - December 2015
WindowsNT 4.0 Service Pack 6 error in access runtime install I have an access runtime install package that has worked numerous times installing on XP Home. It was built using Office XP developer's packaging wizard.
I tried installing it on a brand new PC running XP Home but it generates the error: Visual Basic 6.0 Setup Toolkit The office system pack cannot be installed on this system because it requires Windows NT 4.0 Service Pack 6 or later.
I use the Date and Time Picker. i know that i can get many other calendars, however, my theory is that i would rather use something that is directly supported by microsoft. but as i found out now that may not be the case.
on of my users updated his Office 2003 after he was having problems using my database (created on 2002 sp3). i know that the same database works on 2003 sp1. however, now microsoft is serving 2003 sp2. so after the update he he can't use the database.
so my questions:
1. how do i generally prevent users from getting update errors. i would like to atleast alert them with a meaningful message that their version of Office needs to be updated.
2. is this true that the Date and Time Picker doesn't work anymore? and if so, why isn't backwards compatible? and how do i prevent this from happening with other controls or scripts?
I have a client table. I have a client product table for ski's Each Client has 1 or more products (skis)
I have a Service Order table and form that I use a drop down control in the Service order to select the customer.In the Service Order Form a I have a continuous Subform for detailed service.
This is where I'm having the problem.In the detail subform I want to select from a drop down box the customers particular ski that I want to service. how to have only that one customers ski's to show up.
In a query I'm trying to return a list of rows sorted by Service Type Ascending and then the last item in the list should be a row called "Add Edit Value".If I 'ORDER BY 2' then the "Add/Edit" row appears at the top which is not what i want.
My SQL: SELECT '' As ServiceTypeID, 'ADD/EDIT VALUES' As ServiceType FROM ServiceTypes UNION SELECT ServiceTypes.ServiceTypeID, ServiceTypes.ServiceType FROM ServiceTypes ORDER BY 1 DESC;
How do I make quotes and invoices submittable with no product and service details? We use sale book tags and have a few tags that are voided. I want to keep them in our records as voided. I've create new quotes for these tags, labelled them as void through products and services, but I can not submit the quote. I think I can not submit the quotes because there is no Total Price amount. How do I submit quotes and then invoices for my records with no Total Price amounts?
Now, I know that something in the UPDATE statement does not match my select statement.What should my Update Statement be, in order to update all the columns in the joined tables?
I have a query with the following criteria in one of the fields:
>=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom())
fom is a function for first of the current month. I need this query to be specific to what month it is when its ran so i want to only have this criteria if the month is > = october. If it isnt October or greater, i want the criteria to reflect this.
>=DateAdd("m",-12,fom()) And <=fom()
Which also works by itself. But when i add it to an iif statement it always produces no results. Below is the iif statement.
Iif(month(date())>=10, >=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom()),>=DateAdd("m",-12,fom()) And <=fom())
I have also added the column name to each expression and it still doesnt produce any results.
I am creating a multi-search form for a student database, where after I enter my search criteria I hit a "Run Query" command button and then it opens a query form with all of my criteria.So far I can search using last name, first name, and middle name. When I try to search with a start date and end date I am have issues.The start date and end date is for the class date. In the query form under the field, class date, for criteria I wrote:
Between IIf([Forms]![Search Form]![Start Date]="",1/1/10,[Forms]![Search Form]![Start Date]) And IIf([Forms]![Search Form]![End Date]="",4/25/15,[Forms]![Search Form]![End Date])
I want it when I write a date in the start date and end date I want it to give me a list of all the students who took the course between those dates. Also, if I leave the dates blank I want it to search all dates. The dates 1/1/10 and 4/25/15 are just the dates I gave because that is far back as my database goes.
I have a text box that I'm trying to control the color of based off an IF then statement. Based off to If's
If [Reports]![Rpt-Paths]![subreport].Report![ModuleCh] = "Ch - A" And [Reports]![Rpt-Run Paths]![subreport].Report![Status] = "0" Then
This part works but I need to add a second one
If [Reports]![Rpt-Paths]![subreport].Report![ModuleCh] = "Ch - B" And [Reports]![Rpt-Run Paths]![subreport].Report![Status] = "0" Then
So I want the text box to be RED if the status of both Ch A and Ch B is 0 but if either one has a status greater than 0 then the text box would be green.
I'm using this code to do some calculations on a form. This code works fine as long as only one IF statment is true. My problem starts when the one of the values is equal (meaning qual is 4) so I try to add < or = to and then I end up with more then one IF statement being true. Is there a better way to do this?
If Me.[qual] < 4 And Me.[completed] < 61 Then Me.[GtoG] = [GtoGtotal] Else End If If Me.[qual] < 4 And Me.[completed] > 61 Then Me.[GtoG] = Me.[qual] + 61 Me.[temp1] = Me.[completed] - 61 Me.[Delayone] = Me.[Delayone] + [temp1] Else End If If Me.[qual] > 4 And Me.[completed] < 61 Then Me.[GtoG] = Me.[completed] + 4 Me.[temp2] = Me.[qual] - 4 Me.[Delaytwo] = Me.[Delaytwo] + [temp2] Else End If If Me.[qual] > 4 And Me.[completed] > 61 Then Me.[GtoG] = 65 Me.[temp1] = Me.[completed] - 61 Me.[Delayone] = Me.[Delayone] + [temp1] Me.[temp2] = Me.[qual] - 4 Me.[Delaytwo] = Me.[Delaytwo] + [temp2] Else End If
I am trying to do something really simple, but my lack of experience in Access has bitten me. I have a report... there is a field called payor_code and one called bill_time. These are both based on tables.
The variable is payor code... it could be 2 different things.
All I want to do is make a calculation for TOTAL that multiplies the bill time, times the correct rate. (which is determined by the payor code)
I want to basically do this:
If payor_code = "02" Then total = Bill_Time * 1 Else total = Bill_Time * 2
