Hi:
I try to get the value in a real time. There are two subforms inside a mainform. First user enters value into mainform. Than comes to subform1. And after that on the basis of a choice in subform1, the 2nd subform is open. In 2nd subform i create a listbox with that query.
SELECT [tbl_Events].[PPVVOD_Outlet] FROM tbl_Events WHERE [tbl_Events].[ticketnum]=[Forms]![tbl_PPVResearch]![ticketnum];
But it shows empty. [tbl_Events].[PPVVOD_Outlet] is a subform1
Detailed search cannot answer my time problem. Returning after long break in programming, I am trying to create a form to display TimeIn and TimeOut fields such that actioning an OnClick command (or separate tick-box), TimeIn field will hold current time for that record. Need to set the same for a TimeOut field. Both cases time always to be < 24 hours.
Am so rusty on MS Access putting default value as =now() produces an error!! Any basic help to wind up the old mind always appreciated.
I am trying to create a database (duh!). At the moment, I have two tables. One table is a small timetable with the fields:
Day, Airline, Flight Number, Time, Origin, Destination, Aircraft
The other table (Delay) has the following fields:
Date, Day, Airline, Flight Number, Time, Origin, Destination, Delayed by.
I would like to do the following. In Delay I put in the date: 01/07/2005, fine. The Day (field format Date/Time dddd) is also 01/07/2005 which Access transofrms into the word "Friday". Airilne is BA. In Flight Numbers, I want the database to do a query on "TIMETABLE" to search all flights that operate on a Friday as some do not operate daily. (By the way in the 'Day' column of Timetable the values are "Monday Tuesday Wednesday Thursday Friday Saturday Sunday" or "Monday Thursday Saturday" depending on the flight). This query/lookup should be performed right after I type 01/07/2005 in the "day" field of "Table: Delay". A lookup combo box should be displayed when I arrive at the field FLight Number. I select a flight (e.g.: BA001) and automatically the Time, Origin and Destination of the flight should appear in the "Table: Delay".
I have been unsuccessful at even getting past the first hurdle. I tried a query of the "Table: Timetable" and fields "Day" and "Flight Number". The Criteria I do not know what to put in. "Like [Delay]![Day]" or "[Delay]![Day]" does not help and brings up nothing. However, "Like *Monday*" does bring up all the flights which operate on a Monday. However isn't there some way of telling the query to automatically occur after I type in the relevant day in the "Delay" table? Futhermore I would like to avoid (but not totally exclude if it's impossible) macros as eventually I hope to publish this to SQL or whatever.
Okay, seems like it should be simple... maybe I'm wrong though:
Any one know how to do real-time counting of the number of characters in a text box so that once it reaches 3 it automatically moves to the next text box? (Doing a phone number ... area code (int) and phone (long) to make for a total of 6 bytes.)
I've tried several versions of code for the on-change event but I can't seem to find the right way to assess the number of characters currently in the control... I'm pensive about setting an invisible control to just 'count up' because the on-change event would stack it even with a deletion. See most recent attempt below...
Obrigado, ~Chad
Private Sub txtAreaPhone_Change() Dim bytCount As Byte Dim strAPhone As String
If IsNull(Me.txtAreaPhone) = False Then strAPhone = Cstring(Trim(Abs(Me.txtAreaPhone))) bytCount = Len(strAPhone) If bytCount = 3 Then Me.txtPhone.SetFocus Else End If Else
I have a form which im using to store records about companies that I have mailings with. The company name is my unique field as no two companies should have the same name. This form is accessed by several different people and data is input so I dont want duplicates of the company name being produced. Currently, the form wont save a record if it is duplicated but by then I would have filled out the entire form and wasted my time. Is there a way to do the following:
As I enter the company name it would look up exisitng company names. eg if i wanted to enter a new company called 'Dans Plumbing', I would type 'D' and below the text box would display a load of existing records starting with D. after that when I type 'Da' it would only display records starting with Da and so on. All being well, when I have finished typing the company name there should be nothing underneath so that I instantly know its not a duplicate and vice versa.
The possible duplicate doesn't have to be underneath it could auto complete IN the text box a bit like the address bar on a web browser.
I am trying to display a total based on 3 separate combo boxes in a form. there is a name with an associated number value using 2 fields. the values come from a linked value spreadsheet. the name field is "text" and the number is "number". the user selects their name and number using a combo box drop down window. this occurs 3 times in my form and i want to sum them in a 4th combo box. this is about how it should look, where "Total" happens automatically depending on the name selected.
I would like to create a combo box (or something similiar if a combo box simply can't do it) where the list of choices in the pulldown shows only the matches of what a user types in. The list of choices are coming from a field in a table. Also I would like the "search" of the user's input to include what is "within" each choice, e.g. if a user type "ber", then valid results should be bertha, october, robert.
I have a travel time database that should tally the number of minutes traveled and convert them into a time (hours and minutes). I, however, am having difficulty converting the numeric values cleanly. Is there anyway to convert 102 minutes + 100 minutes + 110 minutes = 312 minutes to 5 hours and 12 minutes cleanly? I need to take averages of time traveled and hours worked but cannot do this correctly. Thank you for any help in advance.:eek:
I think my subject heading explains it. How would you add or subtract two short time formatted values and produce a short time value? Also, how would you change a short time value into number minutes. (i.e. 5:30 into 330) Thanks!
Need some ideas on how to detect if Time Now is beteen preset values.
It is in connection with a Machine Monitor system being I am probably going to be asked to develop.
Problem is night shift. They work normally 20:45 to 06:45 following morning
I will be accumulating elapsed minutes of shift and working minutes by running an On Timer event updating both values by 1 each minute providing certain criteria are met.
One of the criteria will be that machine is supposed to be working.
So I was planning to set a flag indicating Working Yes or No based on the Time Now being between start and end times.
This is fine for normal day working and normal day shifts but is a problem with Night Shift.
I have a report that adds time for as many as six people. This isssue is when the time goes over 24 hours in total it drops the hours. The field(s) is formated in "short time" format.
Hi. I need create a query that can compare different time values. My time field format something looks like 12:59 PM. And compare other fields that has +- 10 minutes from my input. It means when I input 12:00 PM to the query inputt then it lists records that have times 11:50 AM and 12:10 PM. But I don't know how to compare these..they are not natural numbers..but they aren't text niether.. I tried to do it [Arrive_Time] + 10 [Arrive_Time] - 10 in the criteria..but..doesn't work.
I Have a problem in which when I create a record I wish the record to automatically record the time it was created. I have achieved this by having a timerecorded field within the table and setting its default value to time().
later on I wish another time to be recorded. This time when the user selected an option box to -1 I wish the time to be stamped into another field. I have used this event procedure to write the time to another field.
Private Sub AOGCleared_AfterUpdate()
'Update Time
If Me.AOGCleared = -1 Then Me.TimeAOGCleared = Time() Else Me.TimeAOGCleared = ""
End If
End Sub
however It records the original time of creation when the obtion box is selected. Can anyone please point me in the direction of how I get access to record this later time please ?
Hi all! This is kind of a philosophical question :)
I’m doing some changes to a “CRM”-database, and need to make a function that changes values in several records in one operation. The problem is, I have now idea on how to approach this problem.
One example; I have made a simple invoice function in my db, which works well. But now I send a lot more invoices then when I made the function. I’ve made a form, which prints the invoices, and I can print several in one “operation”. How can I, in the same operation, also change the status of the invoice record so that it says “Printed”?
If you have any suggestions on the method e.t.c.? Thank you! (I'm using Access 2000)
Hi, I wonder if someone can help me with the following problem:
I have a table with 2 columns,start time and end time, both containing time values in a four digit format, eg 0930 being 9:30am etc.
I have made a query to convert these into the standard format (eg 0930 becomes 09:30), using left/right and & functions.
Now I use the timevalue function to convert the string , eg 09:30m into a recongnised time value.
The problem is now I want to find the difference between the start and end times- but when i try a simple end_time - start_time formule it returns a long number which i dont understand, rather than just giving me the difference betwen the two times.
Im sure there is a simple way to do this which I dont know, can anyone help me out? Thanks in advance!
Hi, I wonder if someone can help me with the following problem:
I have a table with 2 columns,start time and end time, both containing time values in a four digit format, eg 0930 being 9:30am etc.
I have made a query to convert these into the standard format (eg 0930 becomes 09:30), using left/right and & functions.
Now I use the timevalue function to convert the string , eg 09:30m into a recongnised time value.
The problem is now I want to find the difference between the start and end times- but when i try a simple end_time - start_time formule it returns a long number which i dont understand, rather than just giving me the difference betwen the two times.
Im sure there is a simple way to do this which I dont know, can anyone help me out? Thanks in advance!
Hello all, I am trying to dispay results in a query which is based upon time.
e.g.
Sample data - class and class start time
Class one - 7pm Class two - 7pm Class three - 7pm Class Four - 8pm Class Five - 8pm Class Six - 9pm Class Seven - 9pm Class Eight - 9pm Class Nine - 9pm
If i was using the sample data above and the time was between 7pm to 8pm i would like to show only the classes which started at 7pm, or if the time was 8pm to 9pm, only show the classes which started at 8pm and the same for 9pm
I hope i have explained myself clearly, and I will be thankful for any help.
The current challenge I have in MS Access in really giving me a headache. I can’t seem to find an answer or indeed think “outside the box” so I am hoping there is someone who can help.
Below I will outline the design of my database, the problem I have and my proposed solution. My solution is “What I want to do” rather than “How I do it”. If you have the time and patience to look at my problem and suggest how I proceed, I will be really grateful.
CURRENT DATABASE DESIGN
I created a database that imports daily telephone data. The design below is a simplified version of the real thing but it contains the essential information needed to understand my database.
I extract data from the phone system for “Lines” (3 digit code) e.g. ‘301’,’302’ which each have a corresponding line “Description” e.g. ‘New Customers’, ‘Accounts Queries’. There are three main daily extracts (1) Inbound Calls (2) Outbound Calls and (3) Time. Each extract has either “Line” or “Description” as the unique identifier but not both.
There are 50 lines with matching descriptions and each is allocated to one of 20 teams. I have a query which links all three extracts, groups the data by team and date. Therefore this query creates 20 records (teams) for each day (date).
CURRENT QUERY AND TABLES
Tbl Line Lookup (50 records) Line Description Team
Tbl Inbound Calls Line Date Offered Calls Answered Calls Abandoned Calls
Tbl Outbound Calls Description Date Outgoing Calls
Tbl Time Description Date Answer Time Abandon Time Talk Time Wrap Up Time
Qry Grouped By Team Daily
Line – linked to line in Tbl Incoming Calls Description – linked to description in both Tbl Outgoing Calls and Tbl Time
Team – Grouped field Date – Grouped filed Offered Calls - Sum Answered Calls - Sum Abandoned Calls - Sum Outgoing Calls - Sum Answer Time - Sum Abandon Time - Sum Talk Time - Sum Wrap Up Time - Sum
PROBLEM
Customer Services “Team” is made up of six lines (301-306) and Business Partners “Team” is made up of three lines (307-309). For each day my query creates a record for each of these teams based on how the line is allocated in Tbl Line Lookup.
The business had decided that line 304 calls are part of the Business Partners with effect from 01/05/06. If I change the team name associated with 304 in Tbl Line Lookup this will, incorrectly, allocate all information (from 01/01/06 to present) to Business Partners.
I need a mechanism to allocate 304 to Customer Services prior to 30/04/06 and to Business Partners after 01/05/06.
PROPOSED SOLUTION
I want to introduce a new field in Tbl Line Lookup called Effective Date.
Tbl Line Lookup (50 records) Line Description Team Effective Date
Then I want to introduce a new query which links the data in the three data tables and allocates the correct team name by comparing the date in these extracts with the effective date in the Tbl Line Lookup.
I would then run my original query (Qry Grouped By Team Daily) over this query instead of the original tables.
Please can someone tell me if it is possible to look up values in another table to determine the value of a field in the way I have described?
Have a button on a form that when pressed opens a file browse, allows you to pick a text file (May contain different values and in future the structure may change slightly), then import the text file contents into a temp table (2 fields, Setting & Value), then run a query to take different values from the temp table and put them in to different fields in to different tables linked to the same main record where the button is, then save the record and all linked tables data, and then delete the temp table, and display a message box saying something like done.
Each import file will be a single record, but has to many fields to be in one table
I also need to extract only part of Field 1 (Sample Below), the part after the = (150 in below)
once again I need your help, I have no idea how to tackle the following problem. I am taking records of stock market transactions. As a final result I want to have a query which gives me for every end of the day the total value of my portfolio. Therefore I have two tables. The first one, called tblTransactions with columns - among others - Date, Ticker, Quantity, Price does contain my transactions. The second one, called tblQuotes with columns Date, Ticker, LastPrice contains prices for each security traded for every day. The following SQL code gives me the portfolio holdings with the respective LastPrices on an arbitrarily chosen date (03/09/07):
SELECT T.Ticker, sum(T.Qty) AS TotQty, Q.qCl AS [Last Price], (T.Qty*Q.qCl) AS [SubTotalValue] FROM tblTransactions AS T INNER JOIN tblQuotes AS Q ON T.Ticker=Q.qTicker WHERE T.Date<=#3/9/2007# And Q.qDate=#3/9/2007# GROUP BY T.Ticker, Q.qCl, (T.Qty*Q.qCl) HAVING sum(T.Qty) >0 ORDER BY T.Ticker;
This works so far. However, now I struggle with the next step. I want to have a query, which for each date (I could use the date column of tblQuotes) adds all SubTotalValues, i.e. gives me the total value of my portfolio for each day.
Could you give me please some hints on how to proceed from here? I would be very pleased if somebody could help me with that question.
Many will look at this and laugh but its real. My Access Dbs are opened in hidden state and i dont see my forms. This must be the cause. I tried to hide the menubars using ghudsons code,i disabled the shift key. But i dont know what has happened.
I can nolonger view them. I have tried every method out and i think the only option is to repair OS, may be it will work.
I have enabled the database which i disabled the shift key but when i hold down the shift key, it loads but the forms are totally hidden, STRANGE! ijust see the grey access background and when i click on it twice from the task bar.It pops up.
I have tried to make another database, use the autoexec macro to call a form which enables all command bars, but this works only for this database and when i press the shift key down, this database hides the toolbars and i only see the grey access background. This is happening on EVERY DB ive developed on this machine. I thought it was the access that is totally corrupted but i tried to reinstall my office and i still get the same results This is now a NIGHTMARE to me.
When db is shifted to another machine it works fine as before. This is REAL but can i get some suggestions from you folks before i repair my OS.
"relationship must be on the same number of fields with the same data types" data types are same, numbers.
Primary table has just jobnoID that are all unique (primary table) ie 6907, 6908, 6909 etc
second table "jobs by order" (secondary table) has unique orderno(order numbers) in column one say 69071 and 69072, 69073 and jobno in second coloumn say 6907, 6907, 6907 for all the above.
trying to link JobnoID primary table (one to many) to Jobno secondary table (many)
Also need to note. This database is a purchase order database. so am trying to load in all past data from excel. data is in already. so primary table above with jobno is an auto number so will generate the new jobno for us. but had to copy and paste blank fields into table to get records upto the number we are upto now, ie job number 7112. I have only put data in secondary table for jobno's 6885 through to 7112, and even some of these have blank spaces.
Any ideas? Also how do i do a screen capture and dump in here so you can see relationships etc, which would be a whole lot easier to explain. thanks heaps Alastair:rolleyes:
I am building a database for a Real Estate business.
I have 2 tables tblPurchasers and tblVendors.
I intend to write a query that will match the requirements of the purchasers to the properties for sales (from the vendors).
My issue is this (best illustrated via an example);
I run my Query on the 1st April and four properties are produced as being suitable for purchaser 'A'.
Purchaser 'A' immediately deems properties 1 and 2 unsuitable and views property 3 on the 6th April. Property 3 is subsequently deemed unsuitable as well.
I re-run my qury on the 10th April and five properties are produced as now being suitable for purchaser 'A'.
Purchaser 'A' has already dismissed properties 1,2 and 3 so i don't want to show him these again ....... how do I just ensure that properties 4 and 5 appear ?
Purchaser 'A' has already dismissed properties 1,2 and 3 so I don't want to show him these again ....... how do I just ensure that properties 4 and 5 appear ?
Something involving a yes/no box on a form would be the best method for me of implementing this (as it would fit in with the scematic of the database to date).
I imagine the properties (with a small photo) appearing and tick boxes headed 'Unsuitable', 'Viewed' etc be used to dismiss properties, which do then not re-appear.
Ok, I have access on my computer but my boss does not. (I use my personal laptop). When I finish my project is there a way to allow him to use it without access? Like can I just make it a windows application? If so can somebody please point me in the right direction? Thanks