Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS ACCESS & have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for

Sponsored Links:

Related Messages:
Query Field String Too Long
Let's say I have a query like:

select [x]+[y] as A, [z]*[A] as B from MyTable.

I have found that sometimes this works and sometimes it doesn't, (Like, it works the first time I run it, but later quits working....)so I've gotten into the habit of repeating my calculations and writing it thus instead:

select [x]+[y] as A, [z]*([x]+[y]) as B from MyTable.

However, I now have some query fields that have so many calculations in them that I exceed the string length that Access allows for a query field. So is there a way to make the shorter SQL work consistently?

Any ideas appreciated.


PS: Here's the field I need to shorten:
AdjFcstDay: IIf(((Sum([AvgDaily])*First([forecastweeks])*5+nz(First([backlog]),0))/(First([forecastweeks])*5))+IIf([wiptotal]<(IIf(First(CDbl(nz([dailystddev],0)))<=0,0,xlnormsinv(First([lowwipflag]))*First([dailystddev]))),((IIf(First(CDbl(nz([dailystddev],0)))<=0,0,xlnormsinv(First([lowwipflag]))*First([dailystddev])))-[wiptotal])/5,IIf([wiptotal]>(IIf(First(CDbl(nz([dailystddev],0)))<=0,0,xlnormsinv(First([highwipflag]))*First([dailystddev]))),((IIf(First(CDbl(nz([dailystddev],0)))<=0,0,xlnormsinv(First([highwipflag]))*First([dailystddev])))-[wiptotal])/5,0))<0,0,(Sum([AvgDaily])*First([forecastweeks])*5+nz(First([backlog]),0))+IIf([wiptotal]<(IIf(First(CDbl(nz([dailystddev],0)))<=0,0,xlnormsinv(First([lowwipflag]))*First([dailystddev]))),((IIf(First(CDbl(nz([dailystddev],0)))<=0,0,xlnormsinv(First([lowwipflag]))*First([dailystddev])))-[wiptotal])/5,IIf([wiptotal]>(IIf(First(CDbl(nz([dailystddev],0)))<=0,0,xlnormsinv(First([highwipflag]))*First([dailystddev]))),((IIf(First(CDbl(nz([dailystddev],0)))<=0,0,xlnormsinv(First([highwipflag]))*First([dailystddev])))-[wiptotal])/5,0)))

View Replies !   View Related
Put Records Returned In A Variable
I can get the number of records to be returned but I want to run a condition if the records returned is zero. To do this, I need to put the number returned into a variable but I haven't been find a way.

For instance, this code does work:

SQL = "SELECT Count(*) AS Records FROM Table1 GROUP BY Table1.Quantity HAVING (((Table1.Quantity)=4));"
conDatabase.Execute SQL

But I want to store the count as a variable. What I want is something that look this (it doesn't work but I just want to give an idea what I'm looking for)

SQL = "SELECT Count(*) AS Records FROM Table1 GROUP BY Table1.Quantity HAVING (((Table1.Quantity)=4));"
Dim hold as Integer
hold = conDatabase.Execute SQL

If(hold = 0) Then
'tell them nothing returned
End If

So how can I go about this?

Update: I see that someone posted a solution a few threads down using queries which did work. Can I still still do the same thing with SQL strings?


View Replies !   View Related
Need Null Results Returned Also
I have a query that i run and now I am adding another column to provide a ETA for an order, when i specify a date I only get results that have that date and when I leave out the date I get too many results. Can someone please let me know how I can modify my query so that I get all the results with an ETA and also all the accounts that do not have an order placed.


View Replies !   View Related
How To Get Only Month And Date Returned?
Say I have a bunch of short dates like 2/12/2006 or 12/21/2005. What function should I use to get 2/12 and 12/21 returned? Datepart doesn't work ...

View Replies !   View Related
Baffled By No Records Returned???
I'm using a very basic query

FROM Drawing
WHERE DRAWING_desc Like '%L%'

I've got a table called Drawing, and a column in that table called Drawing_desc

I want all records returned that have the letter "L" in them.

When I run this query, I get nothing

If I replace the '%L%' with "*" it returns all records like you'd think it would.

What gives?

View Replies !   View Related
Null Records Not Being Returned
I am trying to create a Membership report that shows member activity over several months.

My problem is that the software that gives me the Member Activity does not report on accounts that were not used during that month
and some members do not come in every month.

I have a Member's Details table as well as individual months activity and have queries set to show the results of each month individually (these work, obviously)

The Reports I am trying to get from Access only show the activity of Members that have some movement every month, but totally skips members that are not present for 1 or more.

The report uses these queries but fails to report any member that is not in the tables for all months.


Rank Member FirstName LastName Activity
1 100 Andy Todd 200

Rank Member FirstName LastName Activity

Rank Member FirstName LastName Activity
5 100 Andy Todd 50

The report needs to show: Member FirstName LastName Sept.Rank Sept.Activity Oct.Rank Oct.Activity Nov.Rank Nov.Activity
but will not because there are no values for that member in October.

I am teaching myself Access as I go, so I am sure there is an easy solution.

Any help greatly appreciated.


View Replies !   View Related
Limit Size Of Data RETURNED
One of my tables contains data/records like this: 02092006P1235665

Does anyone know how I would write a query to return only the first 8 characters from these records, not the entire field.

For simplicity lets call the table RECEIPTS and the field RECEIPT_NUMBER

View Replies !   View Related
Eliminate The Number Of Rows Returned Per Each...

I have made a query from different tables; however, my query is returning about 5 rows for each person because some fields in the query return more than 1 row.

Is there anyway to fix this problem?

Thank you,

View Replies !   View Related
Query Problems - 2 Values Returned Instead Of One
I am creating a database for a hyperthetical car hire company. A customer hires a car from and until a certain date. If a new customer decides to hire a car i want to generate a list of cars that he can choose on depending on the other dates from which other cars are hire from and until. I have created a query that generates all of the cars that the new customer can not use.
I have also generated a list of all of the cars, in the database.

I have created a new query takes the numberplates of all the cars and subtracts the numberplates of the cars that are being used.

This is where i hit a problem. For some reason the query is generating the list of number plates twice and then subtracting the numberplates that are being used. This leaves me with with 2 values for every numberplate that can be used and 1value for every numberplate that cannot be used. Can you help me?

I will atach a copy of the database this evening but any help now would be much appriciated: the query that i am having problems with has the title:SEARCH FOR AVAILIABLE CARS.

Thank You

View Replies !   View Related
Counting #of Records Returned In A Query
i have a query that returns anywhere between 0-20ish records, and i need an efficient way to count how many records are returned. any ideas?

i was also wondering if there is a more efficient way to do this:

Code: [Text127] = DLookup("[Comments]", "orderheaderz", "[OrderNumber] = " & GlOrderNumber)[Text175] = DLookup("[Performed]", "orderheaderz", "[OrderNumber] = " & GlOrderNumber)[Text177] = DLookup("[Model]", "orderheaderz", "[OrderNumber] = " & GlOrderNumber)[Text179] = DLookup("[Serial]", "orderheaderz", "[OrderNumber] = " & GlOrderNumber)[Text181] = DLookup("[System]", "orderheaderz", "[OrderNumber] = " & GlOrderNumber)[Text183] = DLookup("[HComments]", "orderheaderz", "[OrderNumber] = " & GlOrderNumber)[Text201] = DLookup("[Labor]", "orderheaderz", "[OrderNumber] = " & GlOrderNumber)

mabey i could condense them into one Dlookup somehow? any ideas?

thnx for your help

View Replies !   View Related
Count Number Of Records Returned From Query
I would like to count the number of records returned from a query, but am unsure how to do this using VBA.

Any help appreciated.


View Replies !   View Related
User-defined Limit For Records Returned
This is driving me nuts. I created a form with which the user can modify their query, and included a box for the number of records to return. However, I can't figure out how to do this without building the SQL in the form itself. I'd rather use an Access query. Any ideas?

View Replies !   View Related
No Data Returned With Criteria From Calendar Control
Hi All. I have a query that is based on 1 table. I am using a form with a calendar control for the users to select the date range. In my table the date format is mm/dd/yyyy. My calendar control is pulling the date format mm/dd/yyyy I need my query to only look at the mm/dd part of the field. In my criteria field inc_date I have Between [Forms]![frm_LookUpEnquiry]![txtSelectDate] And [Forms]![frm_LookUpEnquiry]![txtDate2] I have tried the datepart but Im not sure I am doing it right. Basicly Im not sure how to parse out the date to only look at the mm/dd. Or would this be better done in a SQL query Any Idea's for this would be very appreciated. Im kinda in a bind on this one and its the last thing I have to do to finish this database.

Thanks for all you assitance in advance

View Replies !   View Related
Multiple Field Values Need To Be Returned In ONE Line
I have no idea if this is possible or not.
I have a requirement to pull a list of Doctor's, their address, and which counties they serve. Sounds fine, right? Well, they want all the counties on one line... One record for each unique address.

So, my query would return this
Dr Address County
Dr Smith Main Street Plymouth County
Dr Smith Main Street Sarasota County

But they want it to look like this
Dr Address County
Dr Smith Main Street Plymouth County Sarasota County

Any idea on how to pull that together for them and put it in an Excel spreadsheet?

Many many thanks.

View Replies !   View Related
No Records From A Filter Returned, Want Msgbox To Tell The User So
Okay, I have a unbound form with three combo boxes the selection of these three & then clicking of a filter button opens the relevant form showing records based on the selection. Great that bit working, no major feat.

Trouble is when I select three criterea in the combo boxes that no record(s)exist for and hit the filter button. A blank form appears not very friendly or appealing.

what I'm after is a simple, thats me all over by the way, of a message box appearing to tell the user no records where found.

Any ideas, starters for ten on this one?


View Replies !   View Related
Counting The Number Of Records Returned In A Query
Hello all. I need some help calculating the total number of records returned in a query anytime i run the query.

I have a form that creates an SQL statement and changes the querydef at runtime and displays the results in a listbox. I want to include on my form a percentage of the total records from the table based on the records returned in the query Ex. (5/10) so I can use this information on a report.

What I am trying to ultimately acheive is to calculate an attrition rate based on the number of (clients that leave in a given period / total new clients added in the given period )

thanks for your help!


View Replies !   View Related
Limit Number Of Fields Returned By ODBC Table
Hi all, I've searched this with no success.

I have a ODBC (Firebird) table linked using a DSN connection which has >300 fields. In Access, the linked table only show 256 fields (max for Access). Otherwise the link works fine. I have no control over the source Firebird table.

What I'd like to do is limit which fields from the Firebird table are shown in Access. I don't need all the fields, but I need some that are at the end and are not linked in the current setup.

I'm very much a novice at using ODBC, but can I somehow use SQL or some other method to specify which fields to show in the linked table?

Thanks for any suggestions,

View Replies !   View Related
Wrong Number Of Rows Returned By Subquery With Top Clause
I have a strange 'bug'.

I wrote a query to return the TOP 32 items for a key. Works fine:-

SELECT TOP 32 [my Disks Coefficients sub].[thier Disk ID] FROM [my Disks Coefficients] AS [my Disks Coefficients sub] WHERE [my Disks Coefficients sub].[my Disk ID]=1 GROUP BY [my Disks Coefficients sub].[thier Disk ID], [my Disks Coefficients sub].coefficient ORDER BY [my Disks Coefficients sub].coefficient;

I then plugged this into a query in the WHERE clause using In (Select Top .....;); [changing the =1 condition to pick up the correct id from the emcompassing query]:-

SELECT [my Disks Coefficients].[my Disk ID], [my Disks Coefficients].[thier Disk ID], [my Disks Coefficients].coefficient FROM [my Disks Coefficients] WHERE [my Disks Coefficients].[thier Disk ID] In (SELECT TOP 32 [my Disks Coefficients sub].[thier Disk ID] FROM [my Disks Coefficients] AS [my Disks Coefficients sub] WHERE [my Disks Coefficients sub].[my Disk ID]=[my Disks Coefficients].[my Disk ID] GROUP BY [my Disks Coefficients sub].[thier Disk ID], [my Disks Coefficients sub].coefficient ORDER BY [my Disks Coefficients sub].coefficient;);

This runs fine but the the number of items returned is for each key is less than if I run the Top query by itself for each key seperately? So when I run it stand-alone for ID 1 I get 127 rows (there are many equal coefficients); when I run it as a sub-query I get only 121 rows for ID 1!

Anyone any ideas?


View Replies !   View Related
Removed And Returned Items Keep Showing Up In Queries & Reports
Good afternoon!
I have a database that tracks the withdraws and returns of shared tools.
The withdraw form has a check box on it that is checked to show removed when the item is ordered (this keeps that item from showing up in inventory searches). When the item is returned, that check box is unchecked, showing that the item can be withdrawn again.

When a person withdraws an item their supervisors name is also logged so that we know who to chase down when the item doesn't come back.

I have a query that is supposed to show what items are still outstanding based on a date range (I got the prompt for date range to work..yea me). However, the query shows every time that the item was borrowed.

I borrow a hammer and my supervisor is Bob. I return the hammer. It does not show as still withdrawn in my query.

The next I borrow the same hammer and again my supervisor is Bob. I return the hammer. It does not show as still withdrawn in my query.

The day after I borrow the same hammer and my supervisor is Joe. I return the hammer. It does not show as still withdrawn in my query.

Some other day I borrow the same hammer and my supervisor is Joe. I do not return the hammer. The two times I borrowed the hammer with my supervisor as Bob show up as well as the other time that I borrwed it with my supervisor as Joe shows up as well as the one that shows I still have it.
. One of the criteria in the query is essentially only show the items that have the check box checked (criteria=true). But that apparently does nothing, or (more than likely), I am doing something wrong.

This is the SQL:

SELECT tblOrder.OrderDate, tblOrder.Supervisor, tblEmployee.EmployeeFirst, tblEmployee.EmployeeLast, tblItemSpecifics.Description, tblItemSpecifics.Serial_Number
FROM (tblEmployee INNER JOIN tblOrder ON tblEmployee.GlobalID = tblOrder.GID) INNER JOIN (tblItemSpecifics INNER JOIN tblOrderInfo ON tblItemSpecifics.Serial_Number = tblOrderInfo.Serial_Number) ON tblOrder.OrderNumber = tblOrderInfo.Order_Number
GROUP BY tblItemSpecifics.Removed, tblOrder.OrderDate, tblOrder.Supervisor, tblEmployee.EmployeeFirst, tblEmployee.EmployeeLast, tblItemSpecifics.Description, tblItemSpecifics.Serial_Number
HAVING (((tblItemSpecifics.Removed)=True));

View Replies !   View Related
Help With Expression Builder
When I enter a date into the [Return Text Date] field on my table and then select a value from the [Upgrade Fee] combo box followed by a selection from the [Upgrade Investment Class] combo box i want the value (or Date) in my Eligibility Date field to be amended by the given amount.

I have an expression, listed below, that i want to repeat (copy + Paste would be good) and amend the values. I have entered 2 and that seems to be all it will allow. I want to have 30 possible permutations to the fields 0 - 300 (in 50 intervals) by 5 (1,2,3,4 & 5)

The Code works below but I cant have anymore code in the expression builder

IDEAS please people.......

=IIf([Upgrade Fee]=0 And [Upgrade Investment Class]=1,[Return Text Date]+180,IIf([Upgrade Fee]=0 And [Upgrade Investment Class]=2,[Return Text Date]+150,""))IIf([Upgrade Fee]=0 And [Upgrade Investment Class]=3,[Return Text Date]+120,IIf([Upgrade Fee]=0 And [Upgrade Investment Class]=4,[Return Text Date]+90,""))

View Replies !   View Related
Expression Builder

I need some help.

I want to create a formula (using expression builder or something else) who will separate the word id=276616652 lang=FR temp=80 in a new column (for each word)


by the way, im using this in a log database

View Replies !   View Related
Help Need For Expressions Builder
Hi experts

I have some problems consists of using Conditions.
I have 4 tables in Access:
tbl1_School names, addresses, ID_Number etc..
tbl2_ School features with Number of Clesses, Number of Students, total Area etc.
tbl3_teaching stuff with names, occcupations, Ages ets. and
tbl4_nonteaching personal with names, occupations. ages, etc.

References is ID_Number of schools
I make Query with relations, but do know how to build Expressions Builder for this:
Question 1.
a)If school have between 300&500 Students there are ig.18 teachers,
b)if schools have between 500&800 Students there are ig.25 teachers and until 1500 Students there are ig. 50 teachers.
Question 2.
c)if school have area until 400 m^2 there are needed ig. 8 nonteaching personel,
d)if school have from 400 till 600 m^2 there are needed ig. 15 nonteaching personel.

Numbers I specified like examples ig. were prescribed bye regulation.

If someone want to help me how can I use Expression Builder or VBA Code it would be appreciated.

It means I should use Condition Commands.

Thank You for help.

View Replies !   View Related

Copyright 2005-08, All rights reserved