Like Criteria Question using a control from a Form

I am trying to do a like criteria with 2 wildcards using a text box as the parameter. The original data comes from our ERP system that I ODBC to. Everything I read tells me I am writing the criteria correctly but it will not pull any data or I get an ODBC fail message. But if I take out the Form control as the parameter and just type the parameter it works. Here is my criteria Like"*" & [Forms]![NewBlockForm]![Text0] & "*" Does anyone have any suggestions as why this is not working. I am trying to filter down the results that the production people need to view to decid

in access, if then statement

if (Escalation/Assignment = 0) or (Updateheat = 0 )or (Worklog = 0), then 0 for the total. Else total. How would you set this up in design query? Each of the above is a seperate table Query: SELECT DISTINCTROW tblMonitoringData_OLD.[HEAT ID], [tblEscalation/Assignment]![Points Earned]+tblUpdateHeat![Points Earned]+tblWorklog![Points Earned] AS [Documenation Pts], [tblEscalation/Assignment].[Points Earned] AS [Escalation?Assign], tblUpdateHeat.[Points Earned] AS UpdateHeat, tblWorklog.[Points Earned] AS Worklog FROM ((tblMonitoringData_OLD LEFT JOIN [tblEscalation/Assi

Add character to string

I have a simple query that pulls data from a single table containing some basic client information. One field is a text field containing alpha-numeric data. For purposes of a report, I need to add the letter C in front of these numbers. I do not want to update the fields permanently, as there are other circumstances where I do not want C to display. I tried "C"& ""&[CaseID], and variations thereof, but none of them work. I know this must be simple, but I can't seem to figure it out. Any help would be appreciated.

FILTER

How do you filter numbers? ex. 1 through 200 in order?

complex query to pull unique values

I have an inspection table to keep track of rooms in various buildings. These rooms are shared with different supervisors. One inspection of a location would result in x # of inspection records. If the room was shared by 3 supervisors, the same inspection would generate 3 inspection records. Comment field on each record would be different according to the supervisor, were their employees following protocol, safety issues etc. Since there are so many fields, trying to pull unique values is difficult, so I made a new field in the query which concatenates SupervisorID, Bldg & Roo

calculating average days

I am trying to develop a report where in the PartNum footer the average number of days between orders is calculated. I have created the following: Table: tblItemsRecvd Fields: PartNum SupplrName DateOrdrd DateRecd AmtRecd Query: qryItemsRecvd based on tblItemsRcvd PartNum Criteria: [Enter PartNum:] SupplrName DateOrdrd DateRecd AmtRecd Report: rptItemsRecvd based on qryItemsRecvd I would like to calculate the average number of days between orders for the selected part number. Do I do it in the query (how?) or at the report level (how?)? I am using Acces

Multiple tables search

Hi all, Kindly solve my problem. I have a mdb file. tables created t1,t2,t3,t4. fields are almost same in all tables, but the data is different in all tables. fields are.. name, age, mobile_number,place. I want to create a query to get name by mobile_number search. In query execution the input i want to give is mobile number only, it should search the mobile number in all tables and get the correct name. kindly help how can should i create the query.

date and time query

hello, this is my first post in this forum. I have the following problem and i really need some help because i am about to go crazy. I cannot understand why running this code in sql select * from lessons where [date_math] = #29/5/2008# AND ([end_time] > #13:00:00# AND [end_time] <= #14: 30:00#) results in a row like this: id:1150 date_math: 29/5/2008 start_time: 11:30 end_time: 13:00 (!!!) less_name: my_name The most crazy thing is that the following query returns no results select * from lessons where [date_math] = #29/5/2008# AND ([end_time] > #13:00:01(!!!!!!!

Optimization of Between Clause Queries

I have some sql queries involving the use of between clauses, similar to the following: select Colors1.*, sum(Colors2.freq) as TFreq from OldColors as Colors1, OldColors as Colors2 where Colors2.r between Colors1.r - 7 and Colors1.r + 7 and Colors2.g between Colors1.g - 7 and Colors1.g + 7 and Colors2.b between Colors1.b - 7 and Colors1.b + 7 group by Colors1.r, Colors1.g, Colors1.b; The OldColors table has several indexes, including one that is defined as follows: create index RGB on OldColors(r, g, b); Can anyone tell me with a high degree of certainty

Querying for most recent value

I am rather much of a novice when it comes to developing Access databases, but have found myself in a situation at work where I have had to develop two recently. I have a situation where I am trying to query the database to determine placement types of all children in the database. A child may have had more than one placement so I want to ensure that I am retrieving the most recent placement type. This is my table structure: Child Table: includes caseid (primary key), last name, first name, etc. Placement Type: includes placement id (primary key), case id, date of placemen

Looping through data with calculations

I have to loop through the data from one table (demand) and calculate total supply expected depending on the data from the other tables, here is example and expected result: Table A - (supply batches) Item Quantity a 10 b 8 c 12 d 20 Currently in stock C Item Quantity a 0 b 4 c 2 d 0 Table B - (demand) Item Date Quantity a 04-jun-2010 2 a 05-jun-2010 4 a 06-jun-2010 7 a 07-jun-2010 4 a 08-jun-2010 1 b 05-jun-2010 4 b 06-jun-2010 5 b 07-jun-2010 3 b

How to calculate days due?

I have a table (tblTerms) with 2 fields [strTerms] & [dblDaysDue]. The typical terms in the table would be 2%10 Net 30 with [dblDaysDue] having a value of 30. I then take the difference between the ship date from the invoice and the paid date to calculate if they are paying within terms. Everything works well, but now there's a customer with terms of 6/1. How can I calculate if they are paying within terms when the number of days due changes everyday? Thanks Jim

JOIN Query very slow

This is a multi-part message in MIME format. ------=_NextPart_000_0020_01CB0409.76FB2B60 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello, I have the following Input tables Table1 Serial Item Date SerialandDate=20 123 Item1 01-04-2007 12339173=20 123 Item2 01-06-2010 12340330=20 234 Item1 01-10-2006 23438991=20 234 Item3 02-04-2010 23440270=20 234 Item2 01-01-2009 23439814=20 Table2 Serial Date SerialandDate=20 123 01-06-2010 12340330=20 234 02-04-2010 23440270=20

Total by certain time period

I have three fields in a table, and want to determine the total of the occurrences by a certain time period. The data I have looks like this. FGC RDate Occurrences 81 2009/01 1 81 2009/02 1 81 2009/03 1 81 2009/04 1 81 2009/05 5 81 2009/06 84 81 2009/07 1 81 2009/08 6 81 2009/09 3 81 2009/10 1 81 2009/11 5 And I want it to look like this FGC 81 Less Than 3 Months 9 3 Or More Months 91 6 Or More Months 7 9 Or More Months 2 12 Or More M

Maybe Update Query?

I’m floundering about and need someone to point me in the right direction. I have two tables: tblCostCat CostCatNm-PK-text TypeID-text GrpTypeID-text tblTransData ID-PK-number Employee-text CostCatNm-text TransDate-date Hours-number I need to populate the tblTransData with the TypeID in tblCostCat. I’m not that experience with update queries but created one recently for another task and thought I’d use the same logic. I’ll leave out all the details about this because it didn’t work and I’m pretty sure I was going about it wrong. What’s the best or easiest way to

Storing time as integers

I have a call database that includes a field for length of call. I have created a query that includes employee, date, and length of call. I need to calculate the average call length per employee and as a whole. I found one post that suggested the following: "To store time intervals you should store them as integers that represent the number of hours or minutes or seconds, etc.. and then you can perform whatever math you like. The results can then be converted to the hh:nn:ss format for display. " How is this accomplished? Lori

querying unrelated tables

I have two tables - Employee_PunchTime table and Holidays Table. The PunchTime table contains three fields representing EmployeeID, Time-in and Time-out; and the Holiday table contains two fields representing name of holiday and the date. If the payroll period is 5/31/2010 (Mon) thru 6/4/2010 (Fri) and 5/31/2010 is a paid holiday, I need to create a query that shows punched time and paid holiday on a separate column. On the paid holiday, there would obviously be no Time-In and Time-Out record. It will look something like this: EmpID Date Time-in Time-out Paid

How can I query a set of dates but exclude non-working days?

Hi there, I was hoping that someone might be able to help me. My Access knowledge is at best, rather limited, and I am stuck now and cannot proceed further without help! I am building a database of records for work received on certain dates, and completed by certain dates. I need to be able to to query which work loads where received on a given date, and whether or not it has a completed date within a specified limit (say, 3 days). I intend to have a query that displays records that do fall within the target, and a query that displays records that do not. The trick is tha

duplicate results in query

Hi, I am getting duplicate results when I run a query. Here are the details. Bothe tables have the same data only dates have changed. what I am trying to do is find out which dates have changed. The problem is this. Some purhcase orders are split so the same PO and line has 2 dates. when i combine the 2 tables I get 4 lines for these instead of 2. example Vendor PO Line Qty Date X 1234 1 2 7/1/2010 X 1234 1 2 8/1/2010 When i combine this with the other table which is the same for these I get the followin

2007 Concatenation problems

I am using Mr. Hookums formula he gave me for concatenation in Access 2003 and it works fine. As soon as I copy it into 2007 I get error messages. I have tried converting the database to 2007, but get errors. I created 2007 database and imported all files from 2003. Switchboard and concatenation no longer work. Here is what I am getting for messages. Microsoft Visual Basic "Compile error: User-defined type not defined" and here is the code, first three lines below are highlighted yellow; Function Concatenate(pstrSQL As String, _ Optional pstrDelim As String = ", ")

Last record/entry from a table

My table has three columns, AcctNum, TransDate, SeqNum I need a query that only displays the last "SeqNum" when an "AcctNum" had multiple entries on the same day. For example, my table has the following data: AcctNum TransDate SeqNum 1234 6/2/10 698 1234 6/2/10 701 1234 6/2/10 705 In this case, I only want the see the one row with the highest "SeqNum" (which is #705). Thank you, in advance, for your help, I do appreciate it.

deduct order quantity from credit limit

I have on my customer master, a field for credit limit. The user can input the credit limit for that particular customer. When the user then goes in to the order screen, in the line item section, I would like for an message to come up if they have reached their credit limit. So I know I would have to compare the order quantity against the credit limit. Would I do this in a query? Do I need to create a different type of query instead of a standard one? So I would want to see: Customer Credit Limit Qty Ordered Amt Avail ACE Hardware 650

Free Live Voice and Video Chat!Connect with Over 4 Million Members download now

Free Live Voice and Video Chat!Connect with Over 4 Million Members download now on http://www.tkqlhce.com/click-3714215-10516597

PLAY CAR RACE GAMES

PLAY CAR RACE GAMES:- PLAY CAR RACE GAMES ON MY WEB SITE AND ENJOY UR MIND FRESH AND U CAN DOWN LOAD ALSO MY GAMES VISIT http://andhraonlinegames.blogspot.com

Average time with a single entry

Hello there, i have a form with a query that calculates the average processing time for a event and it works fine when there's been more than one entry. I understand that the average of a single time doesn't make much sense, but the user sees BLANK in that field when there's only been one time saved in the database. Is there a way to show the average time if count>2 and show the real time if count=1? Thanks!